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;
