Re: OCI interacts with stored procedure having RECORD parameter
Date: 1996/09/03
Message-ID: <50i1u4$eps_at_inet-nntp-gw-1.us.oracle.com>#1/1
In article <x6eu3tk7jxp.fsf_at_mdt26awm.i-have-a-misconfigured-system-so-shoot-me>, Ming-Ding Tsao <mtsao_at_cas.org> writes:
|>
|> Question: In R7.3.2 using OCI in C language to call a stored procedure
|> with a RECORD parameter as OUT how to use obndra() or obindps() to
|> bind the local output variable to store the passed RECORD value ?
|>
|>
|> For example:
|>
|> CREATE OR REPLACE PACKAGE test AS
|>
|> TYPE TestType is RECORD (
|> f1 NUMBER(10)
|> f2 RAW(32760));
|>
|>
|> PROCEDURE get_row(
|> in1 IN INTEGER,
|> out1 OUT TestType);
|>
|> END test;
|>
|> The results of odessp() for above "test.get_row" procedure would
|> be(partially):
|> Overload Level Pos procName Datatype
|> 0 0 1 IN1 2
|> 0 0 2 AROW 250
|> 0 1 1 F1 2
|> 0 1 2 F2 23
|>
|> static text plsql_block[] =
|> "begin \
|> test.get_row(:in1, :out1 );\
|> end;";
|>
|> In C using OCI to execute above PL/SQL block, if using obndra() I
|> doubt it can work with ":out1" directly. Should it be three call for AROW,
|> F1, F2 separately or just for F1 and F2 ? how to do it ?
|>
|> If using obindps() with operation code as "array of structure" how do you
|> relate to above RECORD ?
|>
|> If above questions can be answered, can OCI handle "nested" RECORD
|> parameter ?
You can't bind a C struct directly to a PL/SQL record with the current release of OCI and Oracle (you can't do it with the precompilers, either). However, you can still do this, with a block like:
static text plsql_block[] =
"DECLARE \
v_BindVar test.TestType; \
BEGIN \
test.get_row(:in1, :v_BindVar); \
:out1 := v_BindVar.f1; \
:out2 := v_BindVar.f2; \
END;"; You create a PL/SQL record local to the anonymous block which you submit to the database, and then bind the fields one-by-one within the block.
|>
|> Thanks in advance, Ming Tsao
|> --
Scott Urman Oracle Corporation surman_at_us.oracle.com
Author of _Oracle PL/SQL Programming_ ISBN 0-07-882176-2 Published by Oracle Press - http://www.osborne.com/oracle/index.htm
"The opinions expressed here are my own, and are not necessarily that of Oracle Corporation"
Received on Tue Sep 03 1996 - 00:00:00 CEST