Re: Help on calling a stored procedure (with JDBC)

In message <f0r53q$kq6$3_at_independence.tornevall.net>, 4 Integration <4integration_at_gmail.com> writes
>4 Integration wrote:
>> Hello,
>> I don not have much knowledge in store procedures and need to help
>>to get started.
>> I have got a signature of an Oracle stored procedure that I need to
>>call from a java app (JDBC thin driver).
>> Can I determine if there are optional parameters from the signature?
>> Which data types are used?
>> ...
>
>When I am calling the stored procedure with
>
>CallableStatement cstmt = conn.prepareCall("{call
>LibInvTrans.CreateInvTrans( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
>?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
>?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
>?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )} ") ;
>int i = 1;
>cstmt.setString(i++, "Inv Lock"); /* "Inv Lock" or "Inv UnLock" */
>cstmt.setString(i++, "50"); //:qty,
>cstmt.setString(i++, "40"); //:qty,
>cstmt.setString(i++, "COMP");
>cstmt.setString(i++, "VO 102056");
>// ....more
>cstmt.setString(i++, "N"); /* Customs Uploaded */
>cstmt.setString(i++, "DMGD");
>cstmt.setString(i++, null); /* Print Label */
>cstmt.setString(i++, null); /* ASN ID */
>cstmt.setString(i++, null); /* Customer ID */
>cstmt.setString(i++, null); /* CE_Duty_Stamp */
>
>cstmt.execute();
>
>
>I get:
>java.sql.SQLException: ORA-06550: line 1, column 7:
>PLS-00221: 'CREATEINVTRANS' is not a procedure or is undefined
>ORA-06550: line 1, column 7:
>PL/SQL: Statement ignored
>
>What could be the reason??
>
>

CreateInvTrans appears to be a function. Unlike some other languages, you can't ignore the return value of a function.

I can't remember exactly. but you need to do something like

begin

?:=package.function(?,?,?,?);

end'

and bind the first ? to a variable.

-- 
Jim Smith
Ponder Stibbons Limited <http://oracleandting.blogspot.com/>
RSS <http://oracleandting.blogspot.com/atom.xml>