|
sv1040,
I have two answers. First, are you using the RETURNS clause, because that isn't how you get rows fetched into your program. The RETURNS is for getting the results of a delete, update, or other sql command that returns a count or value (like a function). Since you're OPENning the cursor, it knows it's a select and can't have a return. If you want rowcount, you'll have to handle that separately from the OPEN. The rest of my answer may be painfully obvious, but just in case...
For Dynamic SQL (open-for or DBMS_SQL) you use a reference cursor or a number and apply it in the open for or DBMS_SQL.OPEN. Here's some examples:
OPEN/FOR:
DECLARE
TYPE EmpCurTyp IS REF CURSOR; -- define weak REF CURSOR type
emp_cv EmpCurTyp; -- declare cursor variable
my_ename VARCHAR2(15);
my_sal NUMBER := 1000;
BEGIN
OPEN emp_cv FOR -- open cursor variable
'SELECT ename, sal FROM emp WHERE sal > :s' USING my_sal;
...
END;
DBMS_SQL:
Consider a table MULTI_TAB defined as:
create table multi_tab (num number,
dat1 date,
var varchar2(24),
dat2 date)
To select everything from this table and move it into four PL/SQL tables, you could use the following simple program:
declare
c number;
d number;
n_tab dbms_sql.Number_Table;
d_tab1 dbms_sql.Date_Table;
v_tab dbms_sql.Varchar2_Table;
d_tab2 dbms_sql.Date_Table;
indx number := 10;
begin
c := dbms_sql.open_cursor;
dbms_sql.parse(c, 'select * from multi_tab order by 1', dbms_sql);
dbms_sql.define_array(c, 1, n_tab, 5, indx);
dbms_sql.define_array(c, 2, d_tab1, 5, indx);
dbms_sql.define_array(c, 3, v_tab, 5, indx);
dbms_sql.define_array(c, 4, d_tab2, 5, indx);
d := dbms_sql.execute(c);
loop
d := dbms_sql.fetch_rows(c);
dbms_sql.column_value(c, 1, n_tab);
dbms_sql.column_value(c, 2, d_tab1);
dbms_sql.column_value(c, 3, v_tab);
dbms_sql.column_value(c, 4, d_tab2);
exit when d != 5;
end loop;
dbms_sql.close_cursor(c);
Good luck!
|