• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

External tables and CallableStatement

 
Ranch Hand
Posts: 56
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

Here's a very weird problem that I'm facing:

  • I have a simple web-app deployed inside Jboss.
  • The app connects to a DB that has some packages and procedures that we have written.
  • These procedures read text files, create external tables out of them, then load the data from these external tables into some internal tables and then create some reports from the internal tables.
  • When we ran this code in our local machine (running apache-tomcat-5.5.23) using Oracle 10g everything worked ok.
  • We deloyed the same WAR on Jboss 4.0.4 using a new schema on a new DB [new box, same version - 10g] and it seems that now we can only go as far as creating the external tables. It does not return any rows when we SELECT back from the external tables.
  • We are using basic JDBC thin drivers to connect. Since a part of the code is working Is there anything inside Jboss or Oracle that needs to changed to allow SELECTs on external tables?


  • We're really so baffled (without any exceptions or errors to chase!) that I dont really know what other information to provide. Please ask whatever questions you have an I can respond.

    Thanks,
    Prashant.
     
    author
    Posts: 4335
    39
    jQuery Eclipse IDE Java
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Any chance there's a firewall issue at play here?
     
    Ranch Hand
    Posts: 338
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Could you post the the from portion of your select statement?
     
    Prashant Sehgal
    Ranch Hand
    Posts: 56
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Firewall issue in JDBC?? Dont think so, because call upto the select are working.

    Here's the SELECT query -



    ...works fine outside the code (if executed as a plain select query from Toad/SQLPLUS).

    Today we noticed that when we changed executeUpdate() to plain execute() the callable statement worked fine for just the one time. For the subsequent times it stopped working again.
     
    Paul Campbell
    Ranch Hand
    Posts: 338
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    If your web application isn't the schema owner, you likely need to include schema name in your table references. When you log into TOAD, are you the schema owner or are you the application user? or are they (from a security stand point, I hope not) the same?

    SELECT
    ACC_GRP_CODE,
    ACC_CURRENCY,
    ACC_CODE,
    S_L,
    SUB_ACC_CODE,
    to_DATE(BALANCE_DATE, 'YYYY-MM-DD') AS BALANCE_DATE,
    LTRIM(AMOUNT, '0') AS AMOUNT,
    DR_CR
    FROM
    schema.EXT_CASH_BALANCES_DATA;
     
    Prashant Sehgal
    Ranch Hand
    Posts: 56
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Well thanks for the tip - I'm in progress of trying it. Although I didn't understand the comment -

    If your web application isn't the schema owner, you likely need to include schema name in your table references.



    I mean, Jboss is giving me a pure JDBC connection to this schema anyway. Once I have the connection, using a CallableStatment, I should able to invoke a stored procedure inside the same schema, without explicitly referring to it by name.

    This the code I'm using to call it:


    To answer your other question - yes, the schema name, username and password are all the same string for us [since we are still within systems testing on UAT environments]. When I login to TOAD, I login as the schema owner.
     
    Paul Campbell
    Ranch Hand
    Posts: 338
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    is the select statement within a package's stored procedure?
     
    Paul Campbell
    Ranch Hand
    Posts: 338
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    does the JBoss user have execute privilege on the stored procedure?
     
    Prashant Sehgal
    Ranch Hand
    Posts: 56
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator

    Originally posted by Paul Campbell:
    is the select statement within a package's stored procedure?



    Yup. The select statment is inside a procedure called extract_balance_info. This procedure is inside a package called CASH_BALANCE_REPORT. There are no IN/OUT parameters. I use the following calls from my Java code to execute the procedure:


    I'm able to see the last log message in my logs. Which means there weren't any exceptions. But actually nothing is inserted from the external into the internal tables. In fact, NO records are returned in the master SELECT cursor from the external table and the procedure just exits gracefully.
    [ October 25, 2007: Message edited by: Prashant Sehgal ]
     
    Prashant Sehgal
    Ranch Hand
    Posts: 56
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator

    Originally posted by Paul Campbell:
    does the JBoss user have execute privilege on the stored procedure?



    It seems he (username is MIGRATE) does have privileges to execute all procedures. Because

    (a) The same code runs from a standalone JDBC connection for the same user;
    (b) The same user is able to call another procedure, I wrote, that just inserts dummy data into a dummy table; and
    (c) Here's what I can see from the user profile in the DB:

     
    Ranch Hand
    Posts: 425
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    From what I understand the external files/tables are generated in JBOSS server. Does Oracle run's on the same server? I don't think you can have directory in Oracle to read the files generated on the JBOSS server, what you\ may have is a local directory in the Oracle 10g Server. Can you check this?
     
    Please enjoy this holographic presentation of our apocalyptic dilemma right after this tiny ad:
    a bit of art, as a gift, the permaculture playing cards
    https://gardener-gift.com
    reply
      Bookmark Topic Watch Topic
    • New Topic