Table Name Variable with cursor

[Guest_imported]

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

 

[BilliaUK]

Peter

You need dynamic SQL for object references. I follow a rule of thumb - use DBMS_SQL for anything where you will need to execute many times as this parses once, executes many times. Use Native Dynamic SQL for anything where you will not execute many times (e.g. DDL, small number of row fetches) as this will soft-parse every time you execute. DBMS_SQL takes a lot more code, but if you are cycling through millions of rows in a cursor (I would use array fetching), then the lack of soft-parsing can be very significant.

If you must use Native Dynamic SQL (most people do - it's MUCH easier), then use the OPEN FOR syntax:-

DECLARE
TYPE typ_ref_cursor IS REF CURSOR;
rc typ_ref_cursor;
v_sql VARCHAR2(100);
v_tab VARCHAR2(30) := 'USER_TABLES'; -- pretend parameter
v_ret VARCHAR2(30);
BEGIN
OPEN rc FOR 'SELECT table_name FROM '||v_tab';
LOOP
FETCH rc INTO v_ret;
DBMS_OUTPUT.PUT_LINE(v_ret);
END LOOP;
CLOSE rc;
END;
/

Very easy to code, just not very efficient on multiple row fetches.

Regards

Adrian

 

[Turkbear]

Hi,
I do not believe you can pass a tablename as an IN
parameter to your Proc, since the c1rec%ROWTYPE
declaration needs to 'know' the tablename to determine what the %ROWTYPE is and, at compile time, no tablename is 'known', so it fails..

I have not been able to find a workaround, but maybe someone else has.

Sorry,

 

[sem]

In fact your cursor c1 does not contain CITY field, only MUNI . But if you need to select only 1 field of predefined type you need no c1%ROWTYPE, just a variable of base type. This in turn means that you may use the decision suggested by Adrian.