Home » Developer & Programmer » Precompilers, OCI & OCCI » How to reterive values from Stored Procedure
How to reterive values from Stored Procedure [message #132188] |
Thu, 11 August 2005 05:19 |
DevSenthil
Messages: 1 Registered: August 2005 Location: Bangalore
|
Junior Member |
|
|
Hello,
I am new to OCI program , Could you please help me to get the values returned by the procedure PASS_INT in the package DEMO_PASSING_PKG to my C++ application through OCI.
I have created a package which is shown below:
CREATE OR REPLACE TYPE LONGARRAY AS TABLE OF NUMBER
/
CREATE OR REPLACE PACKAGE DEMO_PASSING_PKG
AS
PROCEDURE PASS_INT ( P_OUT OUT LONGARRAY)
AS
LANGUAGE JAVA
NAME 'demo_passing_pkg.pass_int_array( oracle.sql.ARRAY[] )';
END DEMO_PASSING_PKG;
/
-- Java Program
CREATE OR REPLACE AND COMPILE
JAVA SOURCE NAMED "demo_passing_pkg"
AS
import java.io.*;
import java.sql.*;
import java.math.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;
public class demo_passing_pkg extends Object{
public static void pass_int_array( oracle.sql.ARRAY[] p_out )
throws java.sql.SQLException,IOException
{
//p_out = new oracle.sql.ARRAY[1];
long arrObjID[] = new long[3];
arrObjID[0] = 2123;
arrObjID[1] = 2124;
arrObjID[2] = 2125;
Connection conn = new OracleDriver().defaultConnection();
oracle.sql.ArrayDescriptor arrayDesc = oracle.sql.ArrayDescriptor.createDescriptor("LONGARRAY" ,conn);
p_out[0] = new oracle.sql.ARRAY(arrayDesc, conn,arrObjID);
}
}
I have created a Java Program which returns an array of values. Here executing the PL/SQL , the values are returned properly without any problem
DECLARE
L_OUT LONGARRAY := LONGARRAY ();
BEGIN
DEMO_PASSING_PKG.PASS_INT(L_OUT);
FOR I IN 1 .. L_OUT.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE ('l_out('|| I || ') = ' || L_OUT (I) );
END LOOP;
END;
I have tried by using an OCI program to get the array values from stored procedure. But it produces an error message "Wrong Number of Type of Arguments".
C++ Program
text *storedproc_str = (text *) "DECLARE \
L_OUT LONGARRAY := LONGARRAY ();\
BEGIN \
DEMO_PASSING_PKG.PASS_INT(:L_OUT);\
END;";
int main()
{
ret_code = OCIInitialize(OCI_DEFAULT, 0,0,0,0);
error_check(error_hdl, ret_code);
ret_code = OCIEnvInit(&env_hdl, OCI_DEFAULT, 0,0);
error_check(error_hdl, ret_code);
ret_code = OCIHandleAlloc(env_hdl, (dvoid**)&error_hdl, OCI_HTYPE_ERROR, 0,0);
error_check(error_hdl, ret_code);
ret_code = OCIHandleAlloc(env_hdl, (dvoid**)&stmt_hdl, OCI_HTYPE_STMT,0,0);
error_check(error_hdl, ret_code);
ret_code = OCILogon(env_hdl, error_hdl, &service_hdl,
(text *) "testproj", 8, (text*)"test", 4, NULL,0);
error_check(error_hdl, ret_code);
printf("\nConnected to Oracle\n");
ret_code = OCIStmtPrepare(stmt_hdl, error_hdl, (text*)storedproc_str,
strlen((char*)storedproc_str), OCI_NTV_SYNTAX, OCI_DEFAULT);
error_check(error_hdl, ret_code);
ret_code = OCIBindByName(stmt_hdl, &bnd1p, error_hdl, (text *) ":L_OUT",
strlen(":L_OUT"), (ub1 *) &arrID, (sword) sizeof(arrID), SQLT_INT,
(dvoid *) &arrID, (ub2 *) 0, (ub2*) 0, (ub4) 0, (ub4 *) 0,
OCI_DEFAULT);
error_check(error_hdl, ret_code);
ret_code = OCIStmtExecute(service_hdl, stmt_hdl, error_hdl, 4,0,NULL
,NULL, OCI_DEFAULT);
error_check(error_hdl, ret_code);
}
But on Executing the C++ Program ( using OCI ), I faced an error "ORA-06550 Wrong Number or types of arguments in call to PASS_INT".
Please kindly help me out to solve this problem. I am struggling to solve this problem
Thanks in Advance
Senthil
|
|
|
Goto Forum:
Current Time: Sun Sep 08 15:06:20 CDT 2024
|