REF Cursor

[yassinemannai]

hi,
please read this code

procedure proc(tname varchar2,colnom varchar2) is
cursor cur is select colnom from tname;
begin
...
end;
the compiler generate error;


procedure proc1(tname varchar2,colnom varchar2) is
type curseur is REF CURSOR;
cur curseur;
begin
open cur for select colnom from tname;
loop
...
end loop;
end;
the compiler generate error;

please help me ;

 

[sem]

Can you provide more complete info e.g. either your ... code or compiler error? The part you've provided does not contain any errors if both table tname and column colnom exist and your ... code is also correct.

 

[stevecal]

You need to do the ref cursor TYPE inside the package header rather than the body of your proc in order to make it externally available. Presumably you are trying to make the cursor available to something like e.g. a JDBC call, in which case you do not even have to loop it, just open the cursor.

 

[sem]

I do apologize for my message, for not everything's Ok.

You may open ref cursor for a string sql if you're on 8.1.7+. In this case you should write in proc1 smth like
..open cur for 'select ' || colnom ||' from ' || tname;

As for the proc you can not declare explicit cursor for dynamic sql statement so can not pass table/column as parameters.

 

[Swany]

You are trying to use a ref cursor in the place of using the DBMS_SQL package.

REF CURSORS, just like standard cursors can not use dynamic SQL. The only difference between ref cursors and explicit cursors is really that cursor references can be passed between procedures easily, without having to pass data with PL/SQL tables.

If you are using Oracle 8i you can use "Native Dynamic SQL" which is easier to use and faster then DBMS_SQL.

http://technet.oracle.com/doc/server.815/a67842/toc.htm

If you are using Oracle 8 you will need to use DBMS_SQL.

http://technet.oracle.com/doc/server.815/a67842/toc.htm

To access those URLs you will need to have an Oracle Technet signon. If you don't already have one, they are free.

 

[sem]

I do use ref cursor with dynamic sql for opening different cursors depending on some additional parameters. I use such procedures with Oracle Forms to built blocks for advanced queries based not only on where/order by clauses but also on the set of tables to query from.

Here's a piece of some dummy code working on Oracle 8.1.6

SQL> create or replace package ref_test
2 as
3 type rc is ref cursor;
4 procedure test(pc in out rc) ;
5 end;
6 /

Package created.

SQL> create or replace package body ref_test
2 as
3 procedure test(pc in out rc) is
4 begin
5 open pc for 'select 1 from dual';
6 end;
7 end;
8 /

Package body created.

SQL> var c refcursor
SQL> exec ref_test.testc)

PL/SQL procedure successfully completed.

SQL> print c

1
----------
1

 

[Swany]

That isn't dynamic SQL though. It is still set of preparsed sql statements that are executed when conditions are met..


Dynamic sql means constructing SQL statements realtime and running them.

For instance, getting a list of all objects owned by a certain user, then executing a TRUNCATE TABLE or other DDL/DML on them which isn't available from basic PL/SQL SQL.

 

[Swany]

Example:
procedure exec_sql (ddl_text in varchar2) AS
c integer;
rows_processed integer;
begin
c := dbms_sql.open_cursor;
dbms_sql.parse(c, ddl_text, dbms_sql.native);
rows_processed := dbms_sql.execute(c);
dbms_sql.close_cursor(c);
exception
when others then
dbms_sql.close_cursor(c);
raise;
end;


procedure truncate_data_by_owner(p_owner in varchar2)
AS
CURSOR v_cursor is
SELECT table_name
from all_tables
where owner = p_owner;

BEGIN
OPEN v_cursor;
FOR each_table in v_cursor
LOOP
exec_sql('truncate table ' || each_table.table_name);
END LOOP;
CLOSE v_cusor;
END;

 

[sem]

Isn't it easy enough to add some conditions just for the sake of choice to make it really dynamic?

if some_param = 1
then
open pc for 'select 1 from dual';
elsif some_param = 2
then
open pc for 'select 2 from dual';
else
open pc for 'select some_column from some_table';
end if;

Of course, this technique lacks for ddl support and works only on 8i, but it's much easy to code than using pre-8i style dbms_sql. I'm sure that the next step will be eliminating this restriction.
And after all it seems to me that the question is about the dynamic creation of select statements, isn't it, yassinemannai?