Re: HELP: Returning varchar array from PL/SQL to C

You have set this up correctly. Unfortunately, this capability is not available with the precompilers at the moment. The method that the kernel and the precompiler use for dynamic SQL doesn't support arrays. I would expect this to be available in future versions of the precompilers (and have logged enhancement requests to that end).

In article <3199lf$j35_at_k2.San-Jose.ate.slb.com>, jgg_at_waldo.corte-madera.geoquest.slb.com (John Gillespie) writes:
|> I need some help on correctly structuring a dynamic SQL
|> call in C to a PL/SQL procedure. I get error 0306 -
|> PLS-00306: wrong number or types of arguments in call to 'PKG.GET_LAB'
|>
|> Note: The actual program retrieves the PL/SQL statement at run-time:
|>
|> PRO*C Program Fragment:
|> EXEC SQL BEGIN DECLARE SECTION;
|> VARCHAR sqlstmt[2048]; /* ORACLE COPY OF SQL STATEMENT */
|> VARCHAR query_key[24]; /* KEY TO QUERY ON */
|> short ind0; /* indicator variable */
|> VARCHAR return_string[100][256]; /* STRING FETCHED */
|> short ind1[100];
|> EXEC SQL END DECLARE SECTION;
|>
|> sqlstmt.len = sprintf(sqlstmt.arr,
|> "BEGIN PKG.GET_LAB(:Key, :StringArray); END;");
|>
|> EXEC SQL PREPARE S FROM :sqlstmt;
|> EXEC SQL DECLARE CUR CURSOR FOR S;
|>
|> EXEC SQL OPEN CUR USING :query_key:ind0, :return_string:ind1;
|> --> Returns 6550 here
|>
|> The PL/SQL to define the package is:
|>
|> create or replace package pkg as
|> type CharArrayType is table of varchar2(256)
|> index by binary_integer;
|> procedure get_lab(query_key in varchar2, theLabel out CharArrayType);
|> end;
|> /
|>
|> create or replace package body pkg as
|> procedure get_lab(query_key in varchar2, theLabel out CharArrayType) is
|> cursor theCursor (theKey varchar2) is
|> select fluid_type||':'||sum(cum_prior)
|> from well_monthly_prod
|> where uwi = theKey
|> group by fluid_type;
|> i binary_integer;
|> begin
|> open theCursor (query_key);
|> for i in 1..100 loop
|> fetch theCursor into theLabel(i);
|> if theCursor%NOTFOUND then
|> close theCursor;
|> exit;
|> end if;
|> end loop;
|> end get_lab;
|> end pkg;
|> /
|>
|> I have tried making CharArrayType varchar2(255), but I get the same
|> error.
|>
|> What else should I change?
|>
|> Vitae:
|>
|> SunOS 4.1.3
|> Oracle 7.0.15.4.0
|> PL/SQL 2.0.17.1.0
|> PRO*C 1.5.9.0.1
|>
|> Thanks in advance!
Received on Fri Jul 29 1994 - 22:54:10 CEST