Clear a DBMS_SQL.VARCHAR2S variable

Question

How do we clear out a DBMS_SQL.VARCHAR2S variable so that it can be reused? The declaration goes as below:

DECLARE
   l_updt_stmnt            DBMS_SQL.VARCHAR2S;

BEGIN
   Open LOOP
    l_updt_stmnt(1) := 'some dynamic sql statements';
    <parse the above statement>
   Close LOOP;

EXCEPTION
WHEN OTHERS THEN
  <Print statement>
END;

I just want to reuse the l_updt_stmnt to generate dynamic sql statements multiple times.

Can anyone help.?

You can just assign a new value, or null. What problem are you having with this? And why are you using a collection if you're only using one element? — Alex Poole
– Alex Poole, Commented Nov 12, 2014 at 9:09
That was just an example that i was giving. the procedure doesn't use one element. It has many. Assigning NULL does not work. And .DELETE method works as mentioned below. Thanks for your response @Alexpoole. — Karthik Swaminathan
– Karthik Swaminathan, Commented Nov 12, 2014 at 12:08

Rusty · Accepted Answer · 2014-11-12 09:15:52Z

5

As DBMS_SQL.VARCHAR2S is defined as PL/SQL Associated Array (Collection):

type varchar2s is table of varchar2(256) index by binary_integer;

to clear out all values you need to call DELETE method:

begin
...
   l_updt_stmnt.DELETE; -- cleanup array
...
end;
/

answered Nov 12, 2014 at 9:15

Rusty

2,13812 silver badges13 bronze badges

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

Collectives™ on Stack Overflow

Clear a DBMS_SQL.VARCHAR2S variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related