OraFAQ Forum: Precompilers, OCI & OCCI » How to reterive values from Stored Procedure

Home » Developer & Programmer » Precompilers, OCI & OCCI » How to reterive values from Stored Procedure

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 21 14:29:36 CST 2024