0

I'm using Oracle 11.g I'm having trouble getting Dynamic SQL to work in a particular scenario. I have an array of Authors with the First Name and the Last Name separated by a single space. I need to separate the FN and LN into two field as I loop through the array of authors.

declare
l_pls_block                 varchar2(512);
l_curr_author               varchar2(512);
l_curr_author_ln_varname    varchar2(512);
l_curr_author_ln_value      varchar2(512);
BEGIN
For k in 1 .. 3
    loop
        l_curr_author := 'Joe Schmoe';
        l_curr_author_ln_varname := 'l_P133_AUTHOR' || k || '_LAST_NAME';
        l_pls_block := l_curr_author_ln_varname || 
            ' := trim(substr(l_curr_author, (instr(l_curr_author, '' '')+1)))';
        dbms_output.put_line('l_pls_block is: ' || l_pls_block);
        execute immediate l_pls_block;
        dbms_output.put_line('l_P133_AUTHORx_LAST_NAME is: ' || 
            l_curr_author_ln_varname);
    end loop;
end;

The above code returns: ORA-00900: invalid SQL statement ORA-06512: at line 14 I've tried using "Execute Immediate l_pls_block into xxxx" but I can't get that to work. As a test, I've constructed the statement:

my_test_val := trim(substr(l_curr_author, (instr(l_curr_author, ' ')+1)))';

and that works fine. How can I using Dynamic SQL to referencing changing variable names to obtain a substring of another value? Thanks for looking at this.

user3138025
  • 795
  • 4
  • 17
  • 46
  • It would help if your test case accurately represented the problem you're trying to solve. For example, you mention looping through an array of authors, but there is no array in your test case. – Boneist Feb 16 '18 at 16:27
  • It does not work that way. variables are in the scope of current anonymous block. when you execute a block dynamically, it invokes a subprogram in sql scope. By the way, there is no reason to use dynamic sql here. If you post sample data and expected output, it would be useful. It is likely that your problem can be solved by a simple sql statement. Do not be tempted to use dynamic sql just because the word dynamic brings sophistication to your code. It should be used only when it's absolutely necessary. Choose other good scenarios(google) for dynamic sql if you just wish to practice it. – Kaushik Nayak Feb 16 '18 at 16:37
  • Hello Boneist and Kaushik, I see what you mean. I was under the mis-impression that I needed Dynamic SQL since my variable name was changing. I see now that I don't need to use Dynamic SQL. I can just use the following (without execute immediate): l_curr_author_ln_varname := trim(substr(l_curr_author, (instr(l_curr_author, ' ')+1))); – user3138025 Feb 16 '18 at 17:15

0 Answers0