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
Previous Topic: error :PCC-F-02081, CMD-Line : Unterminated option value list or value list was truncated.
Next Topic: User_Exit in Forms9i Linux
Goto Forum:
  


Current Time: Fri Mar 29 01:35:44 CDT 2024