CODEWITHSUNDEEP
plsqlplsqldeveloper
RezgHansa
RezgHansa

Reputation: 45

PlSql dynamic cursor Creation with input dynamic table name

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???

Upvotes: 0

Views: 294

Answers (1)

peter.hrasko.sk
peter.hrasko.sk

Reputation: 4141

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.

Upvotes: 1

Related Questions