| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Pro*C stored procedure with table of rec out parameter
A copy of this was sent to "Ondrej 'Andy' Brablc" <brablc_at_seznam.nospam>
(if that email address didn't require changing)
On Thu, 1 Apr 1999 14:33:47 +0200, you wrote:
PL/SQL Records can only be passed to other pl/sql routines. There are no bindings from any 3gl to plsql records.
Can you use cursor variables? Instead of filling up a table of records with the result of a query, can you just open a cursor variable and return the result set to the client? that way you get your 'table of records'
All Oracle Documentation is available online, on the web at http://technet.oracle.com/ You need to sign up for a free account and can start access all of the documentation right after that. (its in html and pdf format).
>Hi overthere,
>
>I'm trying to write Pro*C program, but I have problems with structures.
>I have this in package ANDYT:
>
>type t_rec_subject is record (
> uname VARCHAR2(10),
> ident VARCHAR2(10),
> length integer
>);
>type t_tab_rec_subject is table of t_rec_subject index by binary_integer;
>procedure demo_proc(p_uname in varchar2, p_subj out
>ANDYT.t_tab_rec_subject);
>
>And I want to get all lines from output parameter:
>
>EXEC SQL BEGIN DECLARE SECTION;
>VARCHAR Username[10];
> VARCHAR Ident[10];
>int Length;
> int Count;
>EXEC SQL END DECLARE SECTION;
>
>
>EXEC SQL EXECUTE
> declare
> S ANDYT.t_tab_rec_subject;
> begin
> ANDYT.demo_proc(:Username,S);
> :Count := S.count;
> for i in 1..S.count loop
> :Username := S(i).uname;
> :Ident := S(i).ident;
> :Length := S(i).length;
> end loop;
> end;
>END-EXEC;
>
>I know that I will get only the last row from S, but I need them all.
>I've tried to make a structure in C simillar to t_rec_subject, and
>then an array of this structure, let say Subj[20], but it doesn't work.
>I cannot even pass Subj instead of S to procedure call neither
>can I assign Subj[i-1].Username := S(i).uname. It seems that
>host variables cannot be of type structure or moreover array of
>structures.
>
>My idea is about to call external function from the PL/SQL block,
>which will allocate enough memory in the array and insert one row
>after another into this array.
>
>EXEC SQL EXECUTE
> declare
> S ANDYT.t_tab_rec_subject;
> begin
> ANDYT.demo_proc(:Username,S);
> array_allocate(S.count);
> for i in 1..S.count loop
> array_insert(S(i).uname,S(i).ident,S(i).length);
> end loop;
> end;
>END-EXEC;
>
>Is it possible to do it and to have this external function defined in
>the same Pro*C file? Or is there any other way how to do this job.
>
>I have not manual for precompilers and it cannot be bought in any
>shop in Prague (Czech republic). This is also the only thing
>I need to know, so I hope that this newsgroup will be faster then
>ordering this book.
>
> Andy
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |