Home » SQL & PL/SQL » SQL & PL/SQL » dynamic cursor
dynamic cursor [message #186847] Wed, 09 August 2006 17:34 Go to next message
Jolanda
Messages: 68
Registered: September 2004
Member
Hello you all,

I have this cursor:

CURSOR c_1 IS
SELECT a.prefix_id
,a.prefix_code
,a.prefix_time1
,a.prefix_time2
FROM my_table a
GROUP BY a.prefix_id
,a.prefix_code
,a.prefix_time1
,a.prefix_time2
HAVING COUNT(*) = 1
AND MAX(a.prefix_number) = 0;
r_c1 c_1;

This same cursor I have to reuse for a lot of tables.

The table_name changes and the prefixes.

Is it possible to set the table_name and the prefixes depending on the parameters?

I know that I can define a cursor with parameters and use this in the WHERE clause. But that is for a cursor with a static table name and fields.

In this case the table name and the prefixes are dynamic.

I'm not sure if this is possible, but hope that someone comes up with an idea.

Thanks in advance

Re: dynamic cursor [message #186851 is a reply to message #186847] Wed, 09 August 2006 19:12 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Yes, you use dynamic SQL when object names (tables, columns, etc.) are not known until runtime.

sql>declare
  2    v_rc sys_refcursor;
  3    v_column_name varchar2(30) := 'dummy';
  4    v_table_name varchar2(30) := 'dual';
  5    v_value varchar2(32);
  6  begin
  7    open v_rc for 'select ' || v_column_name || ' from ' || v_table_name;
  8    loop
  9      fetch v_rc into v_value;
 10      exit when v_rc%notfound;
 11      dbms_output.put_line( v_value );
 12    end loop;
 13    close v_rc;
 14  end;
 15  /
X

PL/SQL procedure successfully completed.
Re: dynamic cursor [message #186950 is a reply to message #186851] Thu, 10 August 2006 04:46 Go to previous messageGo to next message
Jolanda
Messages: 68
Registered: September 2004
Member
Hi Todd,

thanks, it works fine!

How can this solution be used for selecting multiple rows?

Thanks
Re: dynamic cursor [message #186996 is a reply to message #186847] Thu, 10 August 2006 08:30 Go to previous messageGo to next message
anil_sapra
Messages: 35
Registered: May 2006
Location: DELHI
Member

Hi,

I am new to this but I am trying to answer this.
I have taken a single column selection in procedure, but you may add columns as per requirement.

   create or replace procedure orafaq(v_column_name in varchar2,
   v_table_name in varchar2) as
           v_rc sys_refcursor;
           v_value varchar2(100);
       begin
         open v_rc for 'select ' || v_column_name || ' from '    ||v_table_name;
         loop
           fetch v_rc into v_value;
          exit when v_rc%notfound;
         dbms_output.put_line( v_value );
       end loop;
       close v_rc;
    end;


Anil
Re: dynamic cursor [message #187003 is a reply to message #186996] Thu, 10 August 2006 08:47 Go to previous messageGo to next message
Jolanda
Messages: 68
Registered: September 2004
Member
Hi Anil,

Thanks for your reply.

One single column can be fetched into the v_value variable.
And if the selection has more columns...I guess the declaration of v_value has to change ..

maybe a user defined type?

If you try to select 2 or more columns, it's not possible to fetch it in the v_value variable.

And I was looking for a solution to that problem.

Your solution was already given by Todd Smile

Thanks

Re: dynamic cursor [message #187010 is a reply to message #187003] Thu, 10 August 2006 09:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
For multiple values in the SELECT you can just use multiple values in the FETCH clause.

(Based on Todds example)
SQL> declare
  2    v_rc sys_refcursor;
  3    v_column_name varchar2(30) := 'dummy';
  4    v_table_name varchar2(30) := 'dual';
  5    v_value_1 varchar2(32);
  6    v_Value_2 varchar2(32);
  7  begin
  8    open v_rc for 'select ' || v_column_name ||','||v_column_name||' from ' || v_table_name;
  9    loop
 10      fetch v_rc into v_value_1,v_value_2;
 11      exit when v_rc%notfound;
 12      dbms_output.put_line( v_value_1||'---'||v_value_2 );
 13    end loop;
 14    close v_rc;
 15  end;
 16  /
X---X
Re: dynamic cursor [message #187047 is a reply to message #187010] Thu, 10 August 2006 11:43 Go to previous messageGo to next message
Jolanda
Messages: 68
Registered: September 2004
Member
Hello you all,

I have this procedure with a dynamic cursor.

this is the message I get

END;
ORA-00936: missing expression
ORA-06512: at line 16
ORA-06512: at line 48

I just can't seem to find what the problem here is.

Anyone an idea?

Thanks....and Jbottom, till now you helped me a lot...
just wanna thank you for that.




Declare

Procedure myTest (p_tabelnaam IN VARCHAR2,
                  p_prefix    IN VARCHAR2)
IS
  v_sql   VARCHAR2(2000);
  v_rc sys_refcursor;
  v_column_name varchar2(300) := 'dummy';
  v_table_name varchar2(300) := 'dual';
  v_id NUMBER;
  v_code VARCHAR2(2);
  v_time1 DATE;
  v_time2 DATE;
begin

open v_rc for    'select ' || p_tabelnaam || '.'|| p_prefix || '_ID' 
                        || ' ,AND ' || p_tabelnaam || '.'||  p_prefix || '_CODE'
						|| ' ,AND ' || p_tabelnaam || '.'||  p_prefix || '_TIME1'
						|| ' ,AND ' || p_tabelnaam || '.'||  p_prefix || '_TIME2' 
						|| ' FROM ' || p_tabelnaam || ' GROUP BY ' 
						|| p_tabelnaam || '.'||  p_prefix || '_ID'
						|| p_tabelnaam || '.'||  p_prefix || '_CODE'
						|| p_tabelnaam || '.'||  p_prefix || '_TIME1'
						|| p_tabelnaam || '.'||  p_prefix || '_TIME2'
						|| ' HAVING COUNT(*) = 1'
						|| ' AND MAX(' || p_tabelnaam || '.'||  p_prefix || '_NUMBER = 0)';
	loop
	   fetch v_rc into v_id,v_code,v_time1,v_time2   ;
		   dbms_output.put_line( v_id || '-----'||v_code|| '-----'||v_time1|| '-----'||v_time2 );
       exit when v_rc%notfound;
       
    end loop;
close v_rc;
	   

end;
						

begin

my_test('table_x','x');
			
END;


[Updated on: Thu, 10 August 2006 13:34]

Report message to a moderator

Re: dynamic cursor [message #187063 is a reply to message #187047] Thu, 10 August 2006 13:44 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
What are all those ANDs doing in the select list?

Also, you don't need to prefix every column in the select list with the table name.
Re: dynamic cursor [message #187067 is a reply to message #187063] Thu, 10 August 2006 14:14 Go to previous messageGo to next message
Jolanda
Messages: 68
Registered: September 2004
Member
Hi Todd,

I rewrote it...The AND's were a multiple typo Smile

This is what I have now...and now it's complaining about : identief too long....




'select ' || p_prefix || '_ID, ' 
                        || p_prefix || '_CODE, '
						|| p_prefix || '_TIME1, '
						|| p_prefix || '_TIME2' 
						|| ' FROM ' || p_tabelnaam || ' GROUP BY ' 
						||  p_prefix || '_ID'
						||  p_prefix || '_CODE'
						||  p_prefix || '_TIME1'
						||  p_prefix || '_TIME2'
						|| ' HAVING COUNT(*) = 1'
						|| ' AND MAX(' ||  p_prefix || '_VOLGNUMMER = 0)';		




Something is going wrong...but don't know what...

Thanks for all the help!!

Re: dynamic cursor [message #187077 is a reply to message #187067] Thu, 10 August 2006 15:50 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
You need to add commas between your column names in your group by clause.
Re: dynamic cursor [message #187079 is a reply to message #187077] Thu, 10 August 2006 15:52 Go to previous messageGo to next message
Jolanda
Messages: 68
Registered: September 2004
Member
Smile))

Thanks Barbara,

I had the comma's, but deleted them while deleting other things.

Thanks!!
Re: dynamic cursor [message #252964 is a reply to message #187079] Sat, 21 July 2007 01:04 Go to previous messageGo to next message
mudabbir
Messages: 235
Registered: April 2006
Location: Kuwait
Senior Member

can this be used in Forms 6i?
Re: dynamic cursor [message #252974 is a reply to message #252964] Sat, 21 July 2007 02:19 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't wake up an old thread for a question that is not for this forum.
Create a new topic in Forms forum.
You can refer this one in your question if you want.
You'll likely have more and more accurate answers.

Regards
Michel
Previous Topic: refcursor not displaying the output
Next Topic: returning ref cursor in procedures
Goto Forum:
  


Current Time: Tue Dec 06 12:29:44 CST 2016

Total time taken to generate the page: 0.07009 seconds