PlSql dynamic cursor Creation with input dynamic table name

Question

Hi My PL/SQL function is

create or replace
PROCEDURE DiffrencesIdentifyerProc(db_nameFirst VARCHAR2,db_nameSecond VARCHAR2,table_name VARCHAR2) IS

dbremort varchar2(100):= db_nameFirst;
dblocal varchar2(100):= db_nameSecond;
tableName varchar2(100) := table_name;

CURSOR cur IS
SELECT * FROM s1.ENTITYFIELDS  MINUS SELECT * FROM s2.ENTITYFIELDS;
BEGIN
FOR e IN cur
LOOP
dbms_output.put_line(e.ATTACH_NAME ); 
END LOOP; 
END DiffrencesIdentifyerProc;

What I want is in

CURSOR cur IS
 SELECT * FROM s1.ENTITYFIELDS  MINUS SELECT * FROM s2.ENTITYFIELDS;

Select Part passed paramters to be setted like

SELECT * FROM dbremort.dbremort   MINUS SELECT * FROM dbremort.dbremort ;

How can I do that???

peter.hrasko.sk · Accepted Answer · 2014-10-30 10:08:04Z

1

Build/construct your query to a varchar2 (or clob when on Oracle 11g or newer) variable, then use native dynamic SQL's open for ... statement for opening the cursor and fetch statement for fetching from it.

Remember that you'll have to have a statically declared structure for fetching the data into from the cursor, anyway, so your dynamic queries will have to have identical number of selected columns with identical (or implicitly convertable) data types.

edited Oct 30, 2014 at 10:08

answered Oct 30, 2014 at 10:01

peter.hrasko.sk

4,2012 gold badges21 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PlSql dynamic cursor Creation with input dynamic table name

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related