PL/SQL Dynamic Loop Value

Question

My goal is to keep a table which contains bind values and arguments, which will later be used by dbms_sql. The below pl/sql example is basic, it's purpose is to illustrate the issue I am having with recalling values from prior loop objects.

The table account_table holds acccount information

CREATE TABLE account_table (account number, name varchar2(100)));

INSERT INTO mytest 
    (account, name) 
VALUES 
    (1 ,'Test');
COMMIT;

The table MYTEST holds bind information

CREATE TABLE mytest (bind_value varchar2(100));

INSERT INTO mytest (bind_value) VALUES ('i.account');
COMMIT;



DECLARE
  v_sql VARCHAR2(4000) := NULL;
  v_ret VARCHAR2(4000) := NULL;
BEGIN
  FOR I IN (
    SELECT account
    FROM account_table
    WHERE ROWNUM = 1
  ) LOOP

    FOR REC IN (
      SELECT *
      FROM mytest
    ) LOOP

      v_sql := 'SELECT ' || rec.bind_value || ' FROM dual';
      EXECUTE IMMEDIATE v_sql INTO v_ret;

      dbms_output.put_line ('Account: ' || v_ret);
    END LOOP;

  END LOOP;
END;
/

I cannot store the name i.account and later use the value that object. My idea was to use NDS; but, while the value of v_sql looks ok (it will read "Select i.account from dual"), an exception of invalid identifier would be raised on i.account. Is there a way to get the value of the object? We are using Oracle 11g2. Thanks!

Why are you trying to run the execute immediate statement? You can just output the value using dbms_output.put_line('Account: ' || i.account), no need for dynamic SQL! — Chris Saxon
– Chris Saxon, Commented Mar 8, 2013 at 8:44
@Chris Saxon: Yes; however, the end goal isn't to output the value of the loop object, but to use that value within a query using DBMS_SQL. DBMS_OUTPUT.PUT_LINE was just used to illustrate that i.account is an invalid identifier in the way it was being used; but the value of i.account would be useful in a query, stored in a table, which is not known at compile time. — user2146026
– user2146026, Commented Mar 8, 2013 at 14:42

Justin Cave · Accepted Answer · 2013-03-07 22:28:09Z

1

Dynamic SQL will not have the context of your PL/SQL block. You cannot use identifiers that are defined in the PL/SQL block in your dynamic SQL statement. So you cannot reference i.account and reference the loop that you have defined outside of the dynamic SQL statement. You could, of course, dynamically generate the entire PL/SQL block but dynamic PL/SQL is generally a pretty terrible approach-- it is very hard to get that sort of thing right.

If you are trying to use the value of i.account, however, you can do something like

v_sql := 'SELECT :1 FROM dual';
EXECUTE IMMEDIATE v_sql
   INTO v_ret
  USING i.account;

That doesn't appear to help you, though, if you want to get the string i.account from a table.

answered Mar 7, 2013 at 22:28

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

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

1 Comment

user2146026 Over a year ago

Thank you for the response, I guess I need a new approach to this project.

David Rolfe · Accepted Answer · 2013-03-08 06:59:29Z

1

Are you always trying to access the same table with the same where clause, but just looking for a different column each time? If so, you could do this:

p_what_I_want := 'ACCOUNT';
--
SELECT decode(p_what_I_want
               ,'ACCOUNT', i.account
               , 'OTHER_THING_1', i.other_thing_1
               , 'OTHER_THING_2', i.other_thing_2
               , 'OTHER_THING_1', i.default_thing) out_thing
INTO l_thing
FROM table;

The above statement is not dynamic but returns a different column on demand...

answered Mar 8, 2013 at 6:59

David Rolfe

211 bronze badge

1 Comment

user2146026 Over a year ago

No, the sql statements are varied each time; but, this approach will prove useful elsewhere.Thanks!

Collectives™ on Stack Overflow

PL/SQL Dynamic Loop Value

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related