aspose file tools*
The moose likes JDBC and the fly likes External tables and CallableStatement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "External tables and CallableStatement" Watch "External tables and CallableStatement" New topic
Author

External tables and CallableStatement

Prashant Sehgal
Ranch Hand

Joined: Jun 20, 2003
Posts: 56
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.
    Scott Selikoff
    Saloon Keeper

    Joined: Oct 23, 2005
    Posts: 3704
        
        5

    Any chance there's a firewall issue at play here?


    My Blog: Down Home Country Coding with Scott Selikoff
    Paul Campbell
    Ranch Hand

    Joined: Oct 06, 2007
    Posts: 338
    Could you post the the from portion of your select statement?
    Prashant Sehgal
    Ranch Hand

    Joined: Jun 20, 2003
    Posts: 56
    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

    Joined: Oct 06, 2007
    Posts: 338
    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

    Joined: Jun 20, 2003
    Posts: 56
    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

    Joined: Oct 06, 2007
    Posts: 338
    is the select statement within a package's stored procedure?
    Paul Campbell
    Ranch Hand

    Joined: Oct 06, 2007
    Posts: 338
    does the JBoss user have execute privilege on the stored procedure?
    Prashant Sehgal
    Ranch Hand

    Joined: Jun 20, 2003
    Posts: 56
    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

    Joined: Jun 20, 2003
    Posts: 56
    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:

    Purushoth Thambu
    Ranch Hand

    Joined: May 24, 2003
    Posts: 425
    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?
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: External tables and CallableStatement