Re: passing variable to a cursor

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Fri, 09 Sep 2005 04:55:40 GMT
Message-Id: <pan.2005.09.09.04.55.39.480972@sbcglobal.net>

On Thu, 08 Sep 2005 07:55:50 -0700, cbarak wrote:

> I would like to pass in a schema name to a cursor but I'm not sure what
> the syntax is, can someone help...
>
> cursor mycursor (v_schemaname in varchar2) is
> select name from v_schemaname.emp where empid=100;
>
> I've been getting ora-933:SQL command not properly ended.
>
> thanks.
>
> charlin

What you are trying to do is cheating. Here's what a cursor looks like:
  1 declare
  2 cursor csr(name varchar2) is

  3         select empno,job,hiredate from emp 
  4         where ename=name;

  5 begin
  6 for c in csr('KING')
  7 loop
  8 dbms_output.put_line('Empno:'||c.empno||' Job:'||c.job||' Hired   on:'||

   9                        c.hiredate);

 10 end loop;
 11* end;
SQL> /
Empno:7839 Job:PRESIDENT Hired on:17-NOV-81

PL/SQL procedure successfully completed.

To actually modify the SQL statement itself, you need to use execute immediate or DBMS_SQL. No cheating.

-- 
http://www.mgogala.com

Received on Thu Sep 08 2005 - 23:55:40 CDT