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

I know it's been some time since the original question, but came back to just summarize how it finished. After many iterations, many struggles with the syntax, the script looks something like this:

create or replace procedure copy_data
AUTHID CURRENT_USER
as
ds1 varchar2(50) :='new_label';
ds2 varchar2(50) :='source_label';
dsid varchar2(200);
seq1 number;
seq2 number;

BEGIN
execute immediate 'CREATE TABLE DSID (DSID NUMBER(10))';
dsid := 'insert into DSID (DSID) select ID from DATA1 where NAME= :ds';
execute immediate dsid USING ds2;
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 
'insert into DA1_IDS (OLD_ID, NEW_ID)
select dsf.ID, data2_seq.nextval from DATA2 dsf inner join DSID ds on dsf.DS_ID=ds.DSID';

execute immediate '
DECLARE
    v_dsfield DATA2%rowtype;

    cursor dsfields2 is 
    select dsfid.NEW_ID,dsf.FIELD_NAME,dsf.DESCRIPTION,...,data1_seq.currval,... 
    from DATA2 dsf 
    inner join DA1_IDS dsfid on dsf.ID=dsfid.OLD_ID
    where dsfid.NEW_ID is not NULL;
begin
    open dsfields2;
    loop
        fetch dsfields2 into v_dsfield;
        EXIT WHEN dsfields2%NOTFOUND OR dsfields2%NOTFOUND IS NULL;
        if dsfields2%ROWCOUNT > 0 THEN
        Insert into DATA2 values v_dsfield;
        end if;
    end loop;
    close dsfields2;   
END;'
;

In reality it has like 10 cursors, built analogically, they all propagate the same IDs of the same key objects in all related tables, and more related tables can be attached to be filled analogically on the fly with the same related IDs

When I was starting it, the general idea of the topic automatically suggested in my head, that it would be nice to have it as a pretty piece of code, like a pl/sql procedure, with loops(cursors), so I could also learn or practise a few things. In the following month I wrote a script doing exactly the same thing, but with a plain sql, without any cursors, loops, not even sequences, just simple inserts to tables :)

But still, what I wrote was used a few times, working perfectly, also on the customer's side. So I'm pasting the "pretty" version as a closure :)

Answer 2

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.