Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2004
    Location
    Colombo
    Posts
    4

    Question Unanswered: Passing in an Array to a Stored Procedure

    Hello everyone

    This is my first atempt at the db forums . Hoping to get some answers from some one of you guys/gals who have had this problem or knows how to handle this. Here's my problem.

    I have a stored proc which expects a data type as follows.
    FUNCTION get_info(
    end_dt DATE DEFAULT NULL,
    num_id kdt_num_table) RETURN VARCHAR2;

    kdt_num_table is decalred as:
    TYPE kdt_num_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

    Now I need to call the above proc through my Java code. Since I need to pass in an Array of data in to the stored procedure I'm using the oracle.sql.ARRAY implementation. In order to create this I'm using the ArrayDescriptor. However I'm having trouble creating the ArrayDescriptor. The code I have to create the ArrayDescriptor is;

    ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("KDT_NUM_TABLE", conn);

    I get a an error as follows:
    java.sql.SQLException: invalid name pattern: TESTUSR.KDT_NUM_TABLE
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:134)
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:179)
    at oracle.jdbc.oracore.OracleTypeADT.initMetadata(Ora cleTypeADT.java:503)
    at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeA DT.java:406)
    at oracle.sql.ArrayDescriptor.initPickler(ArrayDescri ptor.java:1952)
    at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor. java:199)

    TESTUSR is my schema name and KDT_NUM_TABLE is in a PRC_TROOP package under this schema.

    I've been trying giving all kinds of names but all the time it gives me the same error. I'd really appreciate if any one of you can help as right now I'm stuck in the mud. Thanks a bunch!!

    -- Imran

  2. #2
    Join Date
    Feb 2004
    Posts
    2

    Re: Passing in an Array to a Stored Procedure

    Originally posted by imranb
    Hello everyone

    This is my first atempt at the db forums . Hoping to get some answers from some one of you guys/gals who have had this problem or knows how to handle this. Here's my problem.

    I have a stored proc which expects a data type as follows.
    FUNCTION get_info(
    end_dt DATE DEFAULT NULL,
    num_id kdt_num_table) RETURN VARCHAR2;

    kdt_num_table is decalred as:
    TYPE kdt_num_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

    Now I need to call the above proc through my Java code. Since I need to pass in an Array of data in to the stored procedure I'm using the oracle.sql.ARRAY implementation. In order to create this I'm using the ArrayDescriptor. However I'm having trouble creating the ArrayDescriptor. The code I have to create the ArrayDescriptor is;

    ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("KDT_NUM_TABLE", conn);

    I get a an error as follows:
    java.sql.SQLException: invalid name pattern: TESTUSR.KDT_NUM_TABLE
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:134)
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:179)
    at oracle.jdbc.oracore.OracleTypeADT.initMetadata(Ora cleTypeADT.java:503)
    at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeA DT.java:406)
    at oracle.sql.ArrayDescriptor.initPickler(ArrayDescri ptor.java:1952)
    at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor. java:199)

    TESTUSR is my schema name and KDT_NUM_TABLE is in a PRC_TROOP package under this schema.

    I've been trying giving all kinds of names but all the time it gives me the same error. I'd really appreciate if any one of you can help as right now I'm stuck in the mud. Thanks a bunch!!

    -- Imran


    Hi,
    You have to declare KDT_NUM_TABLE on schema level and not on package level,,,,

    I hope this will work for you!!

    Thanks,
    SUmit

  3. #3
    Join Date
    Feb 2004
    Location
    Colombo
    Posts
    4
    Thanks alot sumit, I'll try this out.

  4. #4
    Join Date
    Feb 2004
    Location
    Colombo
    Posts
    4

    Smile

    Sumit

    thanks a bunch. It worked. Its after this I got to know that Java would only bind data types defined at the schema level and not the data types defined in the package level.

    Thanks!!
    -- Imran

  5. #5
    Join Date
    Mar 2004
    Posts
    3

    same problem but not working !!!!

    Hello Imran-,
    I'm doing the same thing as you do but still i'm getting the
    invalid name type sql exception, eventhough the type for which i'm creating the array descriptor is declared at the schema level.

    my procedure declartion ia s follows
    PROCEDURE Test_check (
    id IN NUMBER,
    list IN idarr,
    list1 OUT idarr);

    the type declaration if idarr is as follows
    CREATE OR REPLACE TYPE idarr IS TABLE OF NUMBER(38);

    the only difference between u'r type declartion and mine is u have "INDEX BY BINARY_INTEGER" extra and i specified the length.

    Can u post u'r working code and provide me any suggestion if you see anything i'm doing wrong?

    Thanks

    Kalatta

  6. #6
    Join Date
    Feb 2004
    Location
    Colombo
    Posts
    4

    Lightbulb

    Hey kalatta

    I dont think there's anything wrong with the way you've declared your type. However I think I ran into a similar issue too when running my code. That I found that it was due to the parameters I set while calling the stored proc.

    An Invalide name type exception occured when the parameters that was set in the CallableStatement was not matching with the parameters expected by the stored proc.

    Can you share the code where your setting the parameters and calling the stored proc through the java code. I think that would be the place where we should be looking at. If you can post out the code snippet your using to call the proc I might be able to share my 2 cents on it.

    Cheers
    -- Imran

  7. #7
    Join Date
    Mar 2004
    Posts
    3
    Hey Imran-,
    Sorry i speelled the exception wrong it is "java.sql.SQLException: invalid name pattern: TEST.idarr" not invcalid name type and it is failing exactly in the same as it is filed for you that is while trying to create ArrayDescriptor
    ArrayDescriptor arraydesc = ArrayDescriptor.createDescriptor("idarr", oconn);
    Thanks for the reply and shed me some light on this issue

    Thanks

    Kalatta

  8. #8
    Join Date
    Sep 2004
    Posts
    2

    ArrayDescriptor :Fail to construct descriptor: Invalid arguments

    Hi ,

    Here is my code- "NUM_ARRAY" is created at schema level. I am getting SQL exception at run time.
    "Sql Exception:Fail to construct descriptor: Invalid arguments"
    Please help!

    Thank You

    String url = "oracle.jdbc.pool.OracleConnectionPoolDataSour ce";

    Connection conn = new OracleDriver().connect(url,props);
    System.out.println("connection success");
    int intArray[] = { 1,2,3,4,5,6 };

    ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor( "NUM_ARRAY", conn ) ;
    System.out.println( descriptor.getArrayType());

  9. #9
    Join Date
    Oct 2008
    Posts
    1

    Underlying connection

    When using connection pooling make sure you get the underlying connection !

    I got the same error when using Tomcat, and the problem only went away when I added this:
    accessToUnderlyingConnectionAllowed="true"
    to the resource configuration in tomcat's server.xml file.

    <Resource
    name="jdbc/youridentifier"
    ..
    driverClassName="oracle.jdbc.OracleDriver"
    ..
    accessToUnderlyingConnectionAllowed="true"
    />


    Hope this gives you ideas...

    Ciao,

    W.

  10. #10
    Join Date
    Jan 2009
    Posts
    1

    Passing in an Array to a Stored Procedure

    Hi All,

    I have a packaged procedure which has the parameters as,

    PROCEDURE process_adjustment (p_adjustmentkey IN NUMBER,p_adjustmentvalue IN adjustment_value, p_doubleentry_array IN DOUBLE_ENTRY_ARRAY)

    DOUBLE_ENTRY_ARRAY is declared as,

    CREATE OR REPLACE
    type DOUBLE_ENTRY_ARRAY as table of DOUBLEENTRY_VALUE

    DOUBLEENTRY_VALUE is defined as

    Point is that i have to call the packaged procedure process_adjustment from java,

    I am using the oracle.sql.ARRAY implementation.

    Java code:

    connection=ejbGlossary.getOracleConnection();
    conn1 = (OracleConnection) connection;
    stmt = null;
    stmt = conn1.prepareCall("{call pkg_process_adjustments_v1.process_adjustment(?,?, ?)}");
    ( (OraclePreparedStatement) stmt).setLong(1,adjustmentKey);
    ( (OraclePreparedStatement) stmt).setObject(2, adjustmentValue, OracleTypes.STRUCT);
    AdjustmentDoubleEntryValue[] arrDoubleEntry = (AdjustmentDoubleEntryValue[]) doubleEntries.toArray(new AdjustmentDoubleEntryValue[]{});
    ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor( "DOUBLE_ENTRY_ARRAY", conn1 );
    ARRAY array_to_pass = new ARRAY( descriptor, conn1, arrDoubleEntry);
    ( (OraclePreparedStatement) stmt).setARRAY(3,array_to_pass);
    ( (OraclePreparedStatement) stmt).execute();

    while executing i get the following error,


    Caused by: java.sql.SQLException: Fail to construct descriptor: Invalid arguments
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:134)
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:179)
    at oracle.jdbc.dbaccess.DBError.check_error(DBError.j ava:1130)
    at oracle.sql.StructDescriptor.createDescriptor(Struc tDescriptor.java:125)
    at oracle.sql.STRUCT.toSTRUCT(STRUCT.java:614)
    at oracle.jdbc.oracore.OracleTypeADT.toDatum(OracleTy peADT.java:241)
    at oracle.jdbc.oracore.OracleTypeADT.toDatumArray(Ora cleTypeADT.java:302)
    at oracle.jdbc.oracore.OracleTypeUPT.toDatumArray(Ora cleTypeUPT.java:117)
    at oracle.sql.ArrayDescriptor.toOracleArray(ArrayDesc riptor.java:1517)
    at oracle.sql.ARRAY.<init>(ARRAY.java:117)
    at com.db.ember.server.session.adjust.AdjustmentProce ssBean.processAdjustment(AdjustmentProcessBean.jav a:123)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:585)
    at com.inprise.ejb.ConcreteMethod.invoke(ConcreteMeth od.java:32)
    at com.inprise.ejb.EJBContext.invoke(EJBContext.java: 209)
    at com.inprise.ejb.Dispatcher.doInvoke(Dispatcher.jav a:1530)
    at com.inprise.ejb.Dispatcher.invokeJACCSecurityCheck (Dispatcher.java:1138)
    at com.inprise.ejb.Dispatcher.invokeSecurityCheck(Dis patcher.java:1148)
    at com.inprise.ejb.Dispatcher.invoke(Dispatcher.java: 893)
    at com.inprise.ejb.Dispatcher.invoke(Dispatcher.java: 665)
    at com.inprise.ejb.EJBHome.dispatcherInvokeBeanMethod (EJBHome.java:85)
    at com.inprise.ejb.EJBHome$ComponentInterfaceMethodCa che.invokeDispatcherMethod(EJBHome.java:1571)
    at com.inprise.ejb.EJBHome.invokeDispatcherMethod(EJB Home.java:63)
    at com.inprise.ejb.Dispatcher.invoke(Dispatcher.java: 469)


    Can any one help me to fix this issue?

    Thanks,
    Ananth G

  11. #11
    Join Date
    Jan 2009
    Posts
    1

    Passing in an Array to a Stored Procedure

    Hi All,

    I have a packaged procedure which has the parameters as,

    PROCEDURE process_adjustment (p_adjustmentkey IN NUMBER,p_adjustmentvalue IN adjustment_value, p_doubleentry_array IN DOUBLE_ENTRY_ARRAY)

    DOUBLE_ENTRY_ARRAY is declared as,

    CREATE OR REPLACE
    type DOUBLE_ENTRY_ARRAY as table of DOUBLEENTRY_VALUE

    DOUBLEENTRY_VALUE is defined as

    Point is that i have to call the packaged procedure process_adjustment from java,

    I am using the oracle.sql.ARRAY implementation.

    Java code:

    connection=ejbGlossary.getOracleConnection();
    conn1 = (OracleConnection) connection;
    stmt = null;
    stmt = conn1.prepareCall("{call pkg_process_adjustments_v1.process_adjustment(?,?, ?)}");
    ( (OraclePreparedStatement) stmt).setLong(1,adjustmentKey);
    ( (OraclePreparedStatement) stmt).setObject(2, adjustmentValue, OracleTypes.STRUCT);
    AdjustmentDoubleEntryValue[] arrDoubleEntry = (AdjustmentDoubleEntryValue[]) doubleEntries.toArray(new AdjustmentDoubleEntryValue[]{});
    ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor( "DOUBLE_ENTRY_ARRAY", conn1 );
    ARRAY array_to_pass = new ARRAY( descriptor, conn1, arrDoubleEntry);
    ( (OraclePreparedStatement) stmt).setARRAY(3,array_to_pass);
    ( (OraclePreparedStatement) stmt).execute();

    while executing i get the following error,


    Caused by: java.sql.SQLException: Fail to construct descriptor: Invalid arguments
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:134)
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:179)
    at oracle.jdbc.dbaccess.DBError.check_error(DBError.j ava:1130)
    at oracle.sql.StructDescriptor.createDescriptor(Struc tDescriptor.java:125)
    at oracle.sql.STRUCT.toSTRUCT(STRUCT.java:614)
    at oracle.jdbc.oracore.OracleTypeADT.toDatum(OracleTy peADT.java:241)
    at oracle.jdbc.oracore.OracleTypeADT.toDatumArray(Ora cleTypeADT.java:302)
    at oracle.jdbc.oracore.OracleTypeUPT.toDatumArray(Ora cleTypeUPT.java:117)
    at oracle.sql.ArrayDescriptor.toOracleArray(ArrayDesc riptor.java:1517)
    at oracle.sql.ARRAY.<init>(ARRAY.java:117)
    at com.db.ember.server.session.adjust.AdjustmentProce ssBean.processAdjustment(AdjustmentProcessBean.jav a:123)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:585)
    at com.inprise.ejb.ConcreteMethod.invoke(ConcreteMeth od.java:32)
    at com.inprise.ejb.EJBContext.invoke(EJBContext.java: 209)
    at com.inprise.ejb.Dispatcher.doInvoke(Dispatcher.jav a:1530)
    at com.inprise.ejb.Dispatcher.invokeJACCSecurityCheck (Dispatcher.java:1138)
    at com.inprise.ejb.Dispatcher.invokeSecurityCheck(Dis patcher.java:1148)
    at com.inprise.ejb.Dispatcher.invoke(Dispatcher.java: 893)
    at com.inprise.ejb.Dispatcher.invoke(Dispatcher.java: 665)
    at com.inprise.ejb.EJBHome.dispatcherInvokeBeanMethod (EJBHome.java:85)
    at com.inprise.ejb.EJBHome$ComponentInterfaceMethodCa che.invokeDispatcherMethod(EJBHome.java:1571)
    at com.inprise.ejb.EJBHome.invokeDispatcherMethod(EJB Home.java:63)
    at com.inprise.ejb.Dispatcher.invoke(Dispatcher.java: 469)


    Can any one help me to fix this issue?

    Thanks,
    Ananth G

  12. #12
    Join Date
    Jun 2015
    Posts
    1

    Thumbs up

    ************************************************** ***************************************
    ************************************************** ***************************************
    Check if the return type from public String getSQLTypeName() throws SQLException .
    This is ideally, an auto generated method which returns null. It should not be null but rather return the sqlType.
    This should resolve the above problem

    ************************************************** ***************************************
    ************************************************** ***************************************


    Quote Originally Posted by ananthvg View Post
    Hi All,

    I have a packaged procedure which has the parameters as,

    PROCEDURE process_adjustment (p_adjustmentkey IN NUMBER,p_adjustmentvalue IN adjustment_value, p_doubleentry_array IN DOUBLE_ENTRY_ARRAY)

    DOUBLE_ENTRY_ARRAY is declared as,

    CREATE OR REPLACE
    type DOUBLE_ENTRY_ARRAY as table of DOUBLEENTRY_VALUE

    DOUBLEENTRY_VALUE is defined as

    Point is that i have to call the packaged procedure process_adjustment from java,

    I am using the oracle.sql.ARRAY implementation.

    Java code:

    connection=ejbGlossary.getOracleConnection();
    conn1 = (OracleConnection) connection;
    stmt = null;
    stmt = conn1.prepareCall("{call pkg_process_adjustments_v1.process_adjustment(?,?, ?)}");
    ( (OraclePreparedStatement) stmt).setLong(1,adjustmentKey);
    ( (OraclePreparedStatement) stmt).setObject(2, adjustmentValue, OracleTypes.STRUCT);
    AdjustmentDoubleEntryValue[] arrDoubleEntry = (AdjustmentDoubleEntryValue[]) doubleEntries.toArray(new AdjustmentDoubleEntryValue[]{});
    ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor( "DOUBLE_ENTRY_ARRAY", conn1 );
    ARRAY array_to_pass = new ARRAY( descriptor, conn1, arrDoubleEntry);
    ( (OraclePreparedStatement) stmt).setARRAY(3,array_to_pass);
    ( (OraclePreparedStatement) stmt).execute();

    while executing i get the following error,


    Caused by: java.sql.SQLException: Fail to construct descriptor: Invalid arguments
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:134)
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:179)
    at oracle.jdbc.dbaccess.DBError.check_error(DBError.j ava:1130)
    at oracle.sql.StructDescriptor.createDescriptor(Struc tDescriptor.java:125)
    at oracle.sql.STRUCT.toSTRUCT(STRUCT.java:614)
    at oracle.jdbc.oracore.OracleTypeADT.toDatum(OracleTy peADT.java:241)
    at oracle.jdbc.oracore.OracleTypeADT.toDatumArray(Ora cleTypeADT.java:302)
    at oracle.jdbc.oracore.OracleTypeUPT.toDatumArray(Ora cleTypeUPT.java:117)
    at oracle.sql.ArrayDescriptor.toOracleArray(ArrayDesc riptor.java:1517)
    at oracle.sql.ARRAY.<init>(ARRAY.java:117)
    at com.db.ember.server.session.adjust.AdjustmentProce ssBean.processAdjustment(AdjustmentProcessBean.jav a:123)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:585)
    at com.inprise.ejb.ConcreteMethod.invoke(ConcreteMeth od.java:32)
    at com.inprise.ejb.EJBContext.invoke(EJBContext.java: 209)
    at com.inprise.ejb.Dispatcher.doInvoke(Dispatcher.jav a:1530)
    at com.inprise.ejb.Dispatcher.invokeJACCSecurityCheck (Dispatcher.java:1138)
    at com.inprise.ejb.Dispatcher.invokeSecurityCheck(Dis patcher.java:1148)
    at com.inprise.ejb.Dispatcher.invoke(Dispatcher.java: 893)
    at com.inprise.ejb.Dispatcher.invoke(Dispatcher.java: 665)
    at com.inprise.ejb.EJBHome.dispatcherInvokeBeanMethod (EJBHome.java:85)
    at com.inprise.ejb.EJBHome$ComponentInterfaceMethodCa che.invokeDispatcherMethod(EJBHome.java:1571)
    at com.inprise.ejb.EJBHome.invokeDispatcherMethod(EJB Home.java:63)
    at com.inprise.ejb.Dispatcher.invoke(Dispatcher.java: 469)


    Can any one help me to fix this issue?

    Thanks,
    Ananth G

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •