Using EXECUTE IMMEDIATE with a concatenated string with a variable

Question

This question is about an environment where 'create' permissions are not allowed. The versions are: Oracle Database 10g and PL/SQL 8.0.0.1480

Below is a small sample to show what I’m trying to accomplish which is to cycle through a set of ‘fields’ in a collection and populate them with some values. Each ‘field’ has the same root name ‘MyCol_’ followed by a number.

For Example: MyCol_1, MyCol_2, ... , MyCol_n.

I am able to build up my concatenation but I can not get the EXECUTE IMMEDIATE to work.

I can run these lines where the 'MyCol_1' is a hard coded value:

MyCol_1.EXTEND;        
MyCol_1(1) := 'abc';

But when I replace those hard coded value with a concatenated string it fails

v_LoopCounter:=1;        
v_MyTestCode := 'MyCol_' || v_LoopCounter || '.EXTEND' ;
DBMS_OUTPUT.PUT_LINE('Present value of v_MyTestCode: '||v_MyTestCode);
execute immediate v_MyTestCode;     -- <<<-- ERRORS ON THIS LINE <<<--

The error presented is: ORA-00900: invalid SQL statement

I have tried to follow the advice given from this site: http://docs.oracle.com/cd/B10501_01/appdev.920/a97269/pc_13dyn.htm

...store the dynamic SQL statement in a character string, which must be a host variable or quoted literal. When you store the SQL statement in the string, omit the keywords EXEC SQL and the ';' statement terminator.

And also the advice from: http://www.databasejournal.com/features/oracle/article.php/2109681/EXECUTE-IMMEDIATE-option-for-Dynamic-SQL-and-PLSQL.htm

I've tried many variations on EXECUTE IMMEDIATE such as EXEC, EXECUTE without any success.

Here is the full code sample. This is just some beta code to try and debug this issue. I hope this will not distract from the question being about using EXECUTE IMMEDIATE with a concatenated string. I’ve tried to keep the sample code simple and well documented. Hopefully this helps to explain the situation.

DECLARE

   TYPE MyNestedTable IS TABLE of varchar2(100);       

   MyCol_1 MyNestedTable;
   MyCol_2 MyNestedTable;

   v_LoopCounter  NUMBER;

   v_MyTestCode   VARCHAR2(200);
   v_SomeValue    VARCHAR2(200);

BEGIN

    MyCol_1 := MyNestedTable();
    MyCol_2 := MyNestedTable(); 

    v_LoopCounter:=1;

    v_MyTestCode := 'MyCol_' || v_LoopCounter || '.EXTEND' ;
    DBMS_OUTPUT.PUT_LINE('Present value of v_MyTestCode: '||v_MyTestCode);
    EXECUTE IMMEDIATE v_MyTestCode;     -- <<<-- ERRORS ON THIS LINE <<<--

    v_SomeValue := 'abc';
    v_MyTestCode := 'MyCol_' || v_LoopCounter || ':= '||v_SomeValue;
    DBMS_OUTPUT.PUT_LINE('Present value of v_MyTestCode: '||v_MyTestCode);
    EXECUTE IMMEDIATE v_MyTestCode;     -- <<<-- ERRORS ON THIS LINE <<<--


        MyCol_2.EXTEND;
        MyCol_2(1) := 200;
        DBMS_OUTPUT.PUT_LINE('MyCol_2 '||MyCol_2(1));    
        DBMS_OUTPUT.PUT_LINE('MyCol_1 '|| ', '||'MyCol_2 ');            DBMS_OUTPUT.PUT_LINE(MyCol_1(1)|| ', '||MyCol_2(1));

END;

@Yahia- Sorry, I don't understand. [quote]EXECUTE IMMEDIATE 'SELECT :1 FROM DUAL' USING MyCol_1;[/quote] appears to still have MyCol_1 as a hard coded name. Should I be doing something with 'DUAL' — user1652509
– user1652509, Commented Sep 14, 2012 at 20:09
Looks like you're trying to do dynamic PL/SQL, which requires a begin ... end;. But more importantly, dynamic SQL and dynamic PL/SQL are run in a different scope, and can only see SQL objects, and variables that you pass to it. I think you could pass a variable as an OUT parameter and set it, but you're probably better off rethinking your design. — Jon Heller
– Jon Heller, Commented Sep 14, 2012 at 23:14
You say you want to "cycle through a set of ‘fields’ in a collection" but your example is trying to loop (with no LOOP stmt) through a few different collections (not "fields" in a collection). Do you want MyCol_x to represent a collection or a row or an element? Hard to figure what you're really going for here. — tbone
– tbone, Commented Sep 15, 2012 at 17:17
Let's forget about your code, which is too painful to understand, and start again. What business problem are you trying to solve? Because there has to be a better way than populating loads of single attribute collections with daft names in dynamic SQL. — APC
– APC, Commented Sep 16, 2012 at 5:03

Anjan Biswas · Accepted Answer · 2012-09-15 03:39:58Z

3

Collection.EXTEND is not an SQL statement in its entirety, it is a PLSQL statement used to extend a collection. Having said this the below code will never work-

v_LoopCounter:=1;        
v_MyTestCode := 'MyCol_' || v_LoopCounter || '.EXTEND' ;
execute immediate v_MyTestCode;

However, if the dynamic sql is an entire PLSQL stub/anonymous block, EXECUTE IMMEDIATE will work on that. Like

v_LoopCounter:=1;        
v_MyTestCode := 'begin MyCol_' || v_LoopCounter || '.EXTEND;end;' ;
execute immediate v_MyTestCode;

this may serve the purpose, but if the above is done from within a stored proc itself then the scope of the .EXTEND is questionable (i.e. un-tested). You may try and see if it works.

answered Sep 15, 2012 at 3:39

Anjan Biswas

8,0126 gold badges51 silver badges78 bronze badges

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

1 Comment

user1652509 Over a year ago

Hello All – Thanks for the help and insight. @Annjawn – Your answer was the concept I was looking for. I’m stuck working with some immoveable obstacles for this project, such as having no ‘create’ permissions, so this has lead to some very tortured logic to code around the situation. I’ll rework the logic and code to try and find someway through this. Thanks again.

Collectives™ on Stack Overflow

Using EXECUTE IMMEDIATE with a concatenated string with a variable

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related