Using string inside a procedure inside a dynamic SQL inside a cursor declaration

Question

I have a problem with correct pointing a specific parameter for my select inside the cursor.

Here's what I wrote:

create or replace procedure copy_data

is
ds1 varchar2(50) :='string1';
ds2 varchar2(50) :='string2';
seq1 number; 
seq2 number; 


BEGIN

select NEXT_ID into seq1 from UNIQUE_KEYS where TABLE_NAME='DATA1'; 
select NEXT_ID into seq2 from UNIQUE_KEYS where TABLE_NAME='DATA2'; -
execute immediate 'CREATE SEQUENCE data1_seq START WITH '||seq1||' INCREMENT BY 1';     
execute immediate 'CREATE SEQUENCE data2_seq START WITH '||seq2||' INCREMENT BY 1 CACHE 300'; 

execute immediate 'CREATE TABLE DA1_IDS (OLD_ID NUMBER(10), NEW_ID NUMBER(10))'; 


execute immediate 
'
Insert into DATA1 (ID,NAME,DESCRIPTION) 
select data1_seq.nextval,:ds1,DESCRIPTION
from DATA1 where NAME=:ds2
'
USING ds1, ds2
;


execute immediate
'
DECLARE

    v_oldid DATA2.ID%type;
    v_newid number;
    v_dsfield DATA2%rowtype;


    cursor dsc1 is     
    select dsf.ID, data2_seq.nextval from DATA2 dsf left join DATA1 ds on dsf.DATA1_ID=ds.ID
    where ds.NAME='||'string2'||';
    cursor dsc2 is      
    select dsfid.NEW_ID,dsf.FIELD_NAME,dsf.DESCRIPTION,data1_seq.currval 
    from DATA2 dsf 
    left join DA1_IDS dsfid on dsf.ID=dsfid.OLD_ID;


begin

    open dsc1;
    loop
        fetch dsc1 into v_oldid,v_newid;
        IF dsc1%FOUND THEN
        insert into DA1_IDS values (v_oldid,v_newid);
        else
        exit;
        end if;
    end loop;
    close dsc1;
    open dsc2;
    loop
        fetch dsc2 into v_dsfield;
        IF dsc2%FOUND THEN
        Insert into DATA2 values v_dsfield;
        else
        exit;
        end if;
    end loop;
    close dsc2;


END;'
;

END;

And now, the error is that "string2": invalid identifier. I don't know how to tell my script that there should be a string value there.

Or maybe I just got too far and maybe I should turn everything around?

I used the dynamic SQL for the cursors part because they need to use sequences and the sequences are also created via dynamic SQL, because it's all inside a procedure.

So when using references to sequences in the cursors, I need to hide it inside the dynamic SQL to properly launch it.

But then I don't how to pass a string value inside the select in the cursor.

Please help.

Answer 1

For the immediate error you are getting, you just need to use escpaed single quotes around the string2 literal value; not sure why you have concatenation at the moment but that isn't right. Instead of

    where ds.NAME='||'string2'||';

use

    where ds.NAME=''string2'';

You could also use a bind variable and pass that literal in, as you do in the first dynamic statement.