Home » Developer & Programmer » Precompilers, OCI & OCCI » IN/OUT Parameter Is NO-WORK with Procedure or Function ....
IN/OUT Parameter Is NO-WORK with Procedure or Function .... [message #111531] Thu, 17 March 2005 03:33
kuan62
Messages: 3
Registered: March 2005
Junior Member
Hi~ All,

I don't know what's wrong with OCCI 10g Instant Client.
I tried to call procedure and function, then I could not get correct result. It seems my parameter doesn't work.

*My platform is Win2K (VC++6) and HP-UX (aCC).

=========== [TEST RESULT] ============
callfun - invoking a PL/SQL function having IN, IN/OUT and IN/OUT parameters
Executing the block :BEGIN :1 := demo_fun (:2, :3, :4); END;
Update Count : 1
Printing the INOUT & INOUT parameters:
ret = 10
v2 = ?? ............. >> Expect to be "[IN-OUT_1]"!!
v3 = col1=[10], col2=[IN-OUT_1]
occiproc - done

========== FUNCTION =============
CREATE OR REPLACE FUNCTION demo_fun (col1 IN NUMBER, col2 IN OUT VARCHAR2,
col3 IN OUT VARCHAR2) RETURN INTEGER AS
temp VARCHAR2(100);
BEGIN
temp := col3;
col3 := 'col1=[' || col1 || '], col2=[' || col2 || ']' ;
col2 := temp;
RETURN col1;
END;
/

=========== [OCCI CODE] ============
try
{
cout << "callfun - invoking a PL/SQL function having IN, IN/OUT and IN/OUT ";
cout << "parameters" << endl;
Statement *stmt = conn->createStatement("BEGIN :1 := demo_fun (:2, :3, :4); END;");
cout << "Executing the block :" << stmt->getSQL() << endl;

stmt->registerOutParam (1, OCCIINT);
stmt->setInt (2, 10);
stmt->setString (3, "IN-OUT_1");
stmt->setString (4, "IN-OUT_2");
stmt->registerOutParam(4, OCCISTRING, 100);

int updateCount = stmt->executeUpdate ();
cout << "Update Count : " << updateCount << endl;

cout << "Printing the INOUT & INOUT parameters:" << endl;
cout << "ret = " << stmt->getInt (1) << endl;
cout << "v2 = " << stmt->getString (3) << endl;
cout << "v3 = " << stmt->getString (4) << endl;

conn->terminateStatement (stmt);
cout << "occiproc - done" << endl;
}
catch(SQLException ex)
{
cout<<"Exception thrown for executeQuery"<<endl;
cout<<"Error number: "<< ex.getErrorCode() << endl;
cout<<ex.getMessage() << endl;
}

BTW, As I know OCCI programing guide told us "...if there has IN/OUT parameter then just use setXXX() and getXXX() without registerOutParam()." Is it truth? If I ignored registerOutParam(), I will get an exception as below:

Error number: 6502
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "ESMS.DEMO_FUN", line 6
ORA-06512: at line 1

Previous Topic: OCI Error while Executing
Next Topic: pro*C
Goto Forum:
  


Current Time: Wed Nov 25 22:47:41 CST 2020