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.

From my point of view there is no reason to make execute immediate 'Insert into DATA1 ... and execute immediate 'DECLARE ... as dynamic SQL. — Wernfried Domscheit
– Wernfried Domscheit, Commented Jun 20, 2018 at 15:18
At the point they are erroring, are string1 and string2 supposed to be literal values (in which case looks like you just need to have them in escaped quotes); or are they supposed to come from the ds1 and ds2 variables? It isn't clear why you are creating objects on the fly - that's usually a bad idea.. Why aren't the sequences permanent objects; and you could use a collection for da1_ids. I think, it's a bit hard to follow... — Alex Poole
– Alex Poole, Commented Jun 20, 2018 at 15:24
I couldn't use the "CREATE SEQUENCE..." without using dynamic sql from inside the procedure. And then because I used dynamic for creating the sequences, I couldn't use the references to them in the cursors, therefore the "DECLARE..." part is also in dynamic. string1 and string2 are not variables, those are literal values. — Miczi
– Miczi, Commented Jun 20, 2018 at 16:08
Yes, but the question is why you're trying to create objects inside the procedure at all. Schema-level objects are usually created once as part of an initial build or under change control. Presumably something else has to drop those objects before the procedure can be run again. And creating and dropping objects commits automatically, as well as being expensive. — Alex Poole
– Alex Poole, Commented Jun 20, 2018 at 16:51
Because I planned it to be a one-time script only, which would create the procedure, a separate script to just execute it. The procedure would also have all the drops at the end to clean. — Miczi
– Miczi, Commented Jun 20, 2018 at 17:56

Alex Poole · Accepted Answer · 2018-06-20 15:39:47Z

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.

answered Jun 20, 2018 at 15:39

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

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

1 Comment

Miczi Over a year ago

Thanks for reply. I was certain I already tried that simple way and I checked again and there is also an error with inserting null values by the cursor. Earlier I thought the value is not recognized by the cursor, but now I realized there might be something wrong with the join there, not the value itself. I need to verify the data or add something to prevent inserting nulls attempts.

Miczi · Accepted Answer · 2020-01-29 16:06:26Z

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 :)

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related