SQL & PL/SQL

Hi All,

I'm trying to create a procedure with several "In Parameters" and once inputted it creates a temporary table that I'll use to loop through with a cursor but the cursor can't seem to recognize the "In" parameter. The temporary table that is created is based on several criteria which you'll see in the "IN" parameters section at the top. Once these parameters are entered, it creates a temporary table of the source/production table that I'll use in my cursor for loop. However, as I mentioned, the cursor declare doesn't seem to recognize variables in the 'from' clause. I'll post my code below so you can have a look. If anyone can help me with correcting this I would appreciate it. Initially I didn't even want to create the temporary table...I wanted to essentially declare a cursor by selecting data into the cursor from the production tables using the "IN" parameters at the top but that doesn't seem to work. All I really want to do is to create a dynamic cursor based on input variables in my procedure. Here's the code:

CREATE OR REPLACE PROCEDURE HSA_TGT.IP_EPISODES (
pSOURCE_TABLE IN varchar2
, pSOURCE_PT_IDENTIFIER_COL IN varchar2
, pSOURCE_PRIMARY_KEY IN varchar2
, pSOURCE_START_DT_COL IN varchar2
, pSOURCE_FINISH_DT_COL IN varchar2
, pSOURCE_FILTER IN varchar2
, pSOURCE_FILTER2 IN varchar2
, pOUTPUT_FINAL_TABLE IN varchar2
, pOUTPUT_TEMP_TABLE IN varchar2
, pTRANSFER_TIME IN number
)

AUTHID CURRENT_USER AS

BEGIN

begin execute immediate 'DROP TABLE '||pOUTPUT_FINAL_TABLE;EXCEPTION WHEN others THEN NULL; END;

begin execute immediate 'DROP TABLE '||pOUTPUT_TEMP_TABLE;EXCEPTION WHEN others THEN NULL; END;

begin execute immediate 'CREATE TABLE '||pOUTPUT_FINAL_TABLE||'(PT_IDENTIFIER varchar2(25),FIRST_PRIME_KEY NUMBER(20,0),LAST_PRIME_KEY NUMBER(20,0),START_DT date,FINISH_DT date,FIRST_START_DT date,LAST_FINISH_DT date,span number(5,0))';

begin execute immediate 'CREATE TABLE '||pOUTPUT_TEMP_TABLE||' AS SELECT '
||pSOURCE_PT_IDENTIFIER_COL||' AS PT_IDENTIFIER, '
||pSOURCE_PRIMARY_KEY||' AS PRIMARY_KEY, '
||pSOURCE_START_DT_COL||' AS START_DT, '
||pSOURCE_FINISH_DT_COL||' AS FINISH_DT FROM '
||pSOURCE_TABLE
||' WHERE '
||pSOURCE_FILTER
||' AND '
||pSOURCE_FILTER2;END;


insert_stmt varchar2(32000):= 'insert into '|| pOUTPUT_table
||' (PT_IDENTIFIER,FIRST_PRIME_KEY,LAST_PRIME_KEY,START_DT,FINISH_DT,FIRST_START_DT,LAST_FINISH_DT,span) values (:1,:2,:3,:4,:5,:6,:7,:8)';

declare

cursor c_all_recs is
select *
from pOUTPUT_TEMP_TABLE;
order by pSOURCE_PT_IDENTIFIER_COL,pSOURCE_FINISH_DT_COL;

v_pt_identifier varchar2(20);
v_fdate date;
v_ldate date;
v_inpbeg date;
v_inpend date;
v_inpbeg_primekey number(20,0);
v_inpend_primekey number(20,0);
v_span number(5,0);


begin

for a_rec in c_all_recs loop
-- Several If Conditions --

EXECUTE IMMEDIATE insert_stmt using v_pt_identifier,v_inpbeg_primary_key,v_inpend_primekey,v_fdate,v_ldate,v_inpbeg,v_inpend,v_span;
end loop;


end;

end IP_EPISODES;
/