Thanks for the question, Saravana.
Asked: March 04, 2002 - 8:45 pm UTC
Last updated: March 05, 2002 - 8:43 pm UTC
Version: 8.1.7
Viewed 1000+ times
You Asked
I have tried running the IndexTableExample included in the oracle demo. It works fine. Now i have a similar task in project. But i get ORA-1036.
Here is the Procedure that has to be invoked.
---------------------------------------------------
CREATE OR REPLACE TYPE ARRY_TYPE AS TABLE OF VARCHAR2(10);
CREATE OR REPLACE PROCEDURE SPMBTEST3 (
VAR1 IN VARCHAR2,
VAR2 IN ARRY_TYPE,
VAR3 OUT ARRY_TYPE)
IS
BEGIN
DECLARE
wk_Subject VARCHAR2(64);
BEGIN
dbms_output.put_line('SPMBTEST3 start');
dbms_output.put_line('SPMBTEST3 var1:'||var1);
dbms_output.put_line('SPMBTEST3 var2:'||var2(1));
VAR3 := ARRY_TYPE('in2','in3','in4','in5');
dbms_output.put_line('SPMBTEST3 end');
return;
EXCEPTION
WHEN OTHERS THEN
RETURN ;
END;
END;
-------------------------------------------
Now here is the test program
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;
class SPMBTEST
{
public static void main (String args []) throws SQLException, ClassNotFoundException
{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@nectar","forsrs", "forsrs");
run( conn );
conn.close();
}
static void run(Connection conn) throws SQLException
{
System.out.println ("SPMBTEST_TEST");
OracleCallableStatement spmbtest=(OracleCallableStatement)conn.prepareCall("{ call SPMBTEST3( ? ? ? ) }");
String[] values = { "xy","xd","xe" };
int maxLen = values.length;
int currentLen = values.length;
int elemSqlType = OracleTypes.VARCHAR;
int elemMaxLen = 10;
int maxOutLen = 20;
spmbtest.setString( 1, "12345" );
spmbtest.setPlsqlIndexTable (2, values, maxLen, currentLen, elemSqlType, elemMaxLen);
spmbtest.registerIndexTableOutParameter (3, maxOutLen, elemSqlType, elemMaxLen);
spmbtest.executeQuery();
Datum[] outvalues = spmbtest.getOraclePlsqlIndexTable (3);
for (int i=0; i<outvalues.length; i++) System.out.println (outvalues[i].toString());
spmbtest.close();
}
}
Could you please clarify on this.
Also please provide some samples on using Oracle TABLE/RECORD Types in stored procedure.
Can i use Oracle Thin driver to invoke stored procedure have composite data types like nested TABLE and RECORD Types.
Thank you for Your help in Advance.
Kumar
and Tom said...
couple of things wrong with the above:
a) .... nt)conn.prepareCall("{ call SPMBTEST3( ?, ?, ? ) }");
you were missing the commas in there. that was the cause of the 1036
b) you are using the "PLSQL index by table" bind method to bind to an Oracle Object Type. You can only use (this new api available with 817 and up only) this to bind to PLSQL Index By Tables. If you change the PLSQL code to:
create or replace package demo_pkg
as
type arry_type is table of varchar2(10) index by binary_integer;
end;
/
CREATE OR REPLACE PROCEDURE SPMBTEST3 (
VAR1 IN VARCHAR2,
VAR2 IN demo_pkg.ARRY_TYPE,
VAR3 OUT demo_pkg.ARRY_TYPE)
IS
BEGIN
DECLARE
wk_Subject VARCHAR2(64);
BEGIN
VAR3(1) := 'in2';
var3(2) := 'in3';
var3(3) := 'in4';
var3(4) := 'in5';
return;
EXCEPTION
WHEN OTHERS THEN
RETURN ;
END;
END;
/
and add the commas -- your example works dandy.
see
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:986230504001
for how to pass collections (nested tables, varrays) to/fro java and plsql.
As for doing tables of RECORDS -- that's a no-go. Only PLSQL can send tables of records to/fro itself. There are no 3gls that can do tables of records.
Now, collections of object types -- which are pretty much the same as tables of records -- java can do that. Suggest you look at the link provided in
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:433419656020 <code>
to go down that path.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment