Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SELECT DISTINCT With OPEN cursor FOR

dbachaasMay 15 2007 — edited May 15 2007
Hello.
I have the following procedure. All it does is open a cursor for an SQL string passed into it, and return the open cursor.
PROCEDURE sp_execute_dynamic (hold_input_string IN CLOB,
hold_cursor OUT hold_cursor_type) IS
BEGIN
OPEN hold_cursor FOR TO_CHAR(hold_input_string);
END sp_execute_dynamic;

It works fine except when I perform SELECT DISTINCT. I get the following error.
SQL> declare
2 TYPE hold_cursor_type IS REF CURSOR;
3 hold_cursor hold_cursor_type;
4 hold_object_name VARCHAR2(1024);
5 hold_object_type VARCHAR2(1024);
6 begin
7 dynamic_sql_pkg.sp_execute_dynamic('select distinct object_name from user_objects where object_
name in (''PLAN_TABLE'',''DBA_OBJECTS'')',hold_cursor);
8 loop
9 fetch hold_cursor into hold_object_name, hold_object_type;
10 exit when hold_cursor%NOTFOUND;
11 dbms_output.put_line('Object Name = '||hold_object_name||' Object Type = '||hold_object_type);
12 end loop;
13 end;
14 /
declare
*
ERROR at line 1:
ORA-01007: variable not in select list
ORA-06512: at line 9

It does the same thing with SELECT UNIQUE or SELECT with a GROUP BY. Can anyone tell me why this happens and what I could to to work around it?
Thanks
Chris
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 12 2007
Added on May 15 2007
2 comments
647 views