Table Name Variable with cursor

[Peter]

Hello All

Is it possible to use a Parameter passed to a procedure as the table name
in a cursor selection statment. I thought the below would work but I get
a error. Does anyone have any ideas??

CREATE OR REPLACE PROCEDURE TEST(TABLENAME IN VARCHAR2) IS

CURSOR c1 IS SELECT MUNI FROM TABLENAME GROUP BY CITY;

c1rec c1%ROWTYPE;

BEGIN
OPEN c1;
LOOP
FETCH c1 INTO c1rec;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(c1rec.CITY);
END LOOP;
CLOSE c1;
END;

Thanks

Peter

[Boris Milrud]

Peter,

Use Native Dynamic SQL introduced in Oracle 8i (or DBMS_SQL package if you
are on Oracle 7.3/8.0) in combination with REF CURSOR type variable. Here
is the modified version of your procedure:

create or replace procedure Test (pTableName in varchar2)
is

type tCsr is ref cursor;
vCsr tCsr;

vSQL varchar2(2000);
vName varchar2(100);

begin

vSQL := 'select city ' ||
'from ' || pTableName || ' ' ||
'group by city';

open vCsr for vSQL;
loop
fetch vCsr into vName;
exit when vCsr%notfound;

dbms_output.put_line(vName);
end loop;
close vCsr;

end Test;
/

Let me know if you have more questions.

Boris.

"Peter " <powp@hotmail.com> wrote:
>
>Hello All
>
>Is it possible to use a Parameter passed to a procedure as the table name
>in a cursor selection statment. I thought the below would work but I get
>a error. Does anyone have any ideas??
>
>CREATE OR REPLACE PROCEDURE TEST(TABLENAME IN VARCHAR2) IS
>
>CURSOR c1 IS SELECT MUNI FROM TABLENAME GROUP BY CITY;
>
>c1rec c1%ROWTYPE;
>
>BEGIN
> OPEN c1;
> LOOP
> FETCH c1 INTO c1rec;
> EXIT WHEN c1%NOTFOUND;
> DBMS_OUTPUT.PUT_LINE(c1rec.CITY);
> END LOOP;
>CLOSE c1;
>END;
>
>Thanks
>
>Peter