OraFAQ Forum: SQL & PL/SQL » Using Variables in Cursors

Home » SQL & PL/SQL » SQL & PL/SQL » Using Variables in Cursors

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Using Variables in Cursors [message #38977]

Mon, 03 June 2002 08:48

Stephen
Messages: 26
Registered: January 2000

Junior Member

Hello,

I am trying to use variable names in a cursor, but I am not sure how to get them to work. Here is my code below:

PROCEDURE prEqualRecords(v_table varchar2, v_count number, v_key varchar2) IS

cursor c_equal is
select count(A.v_key)
from v_table A, v_table@BDCI B
where A.v_key = B.v_key;

How do I denote the v_key and v_table as variables within the select statement in the cursor?

Thanks!!!

Report message to a moderator

Re: Using Variables in Cursors [message #38979 is a reply to message #38977]

Mon, 03 June 2002 09:15

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

You need to use dynamic SQL. Here is an 8i/9i example (if you are pre-8i, use DBMS_SQL):

execute immediate
  'select count(a.' || v_key || ')' ||
  '  from ' || v_table || ' a, ' || v_table || '@bdci b' ||
  ' where a.' || v_key || ' = b.' || v_key into v_count;  -- or whatever variable will hold the count

Report message to a moderator

Previous Topic:	URGENT help on a trigger
Next Topic:	how to delete several columns in a table?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 02:27:39 CDT 2024