Hi Joel,
thanks for your help!
In your example you know that you need 3 a_VALUES_ARRAY items and you can
write the USING clause in an explicit way.
...USING a_VALUES_ARRAY(1), a_VALUES_ARRAY(2), a_VALUES_ARRAY(3);
In my problem the clause above is dynamic, I have to use to build up the
USING clause all the a_VALUES_ARRAY elements....
So I should something like that:
FOR item in 0..a_VALUES_ARRAY.LAST;
LOOP
s_USING_CLAUSE := s_USING_CLAUSE || ', a_VALUES_ARRAY(' || item || ')';
END LOOP;
.... USING s_USING_CLAUSE;
Thx
Filippo
2011/9/15 Joel <[email protected]>
> Can't just use the a_VALUES_ARRAY -> "PLS-00457: expressions have to be of
> SQL types".
>
> DROP TABLE tab_a purge;
>
> CREATE TABLE TAB_A
> (
> A VARCHAR2(100 BYTE),
> B VARCHAR(100 BYTE),
> C VARCHAR(100 BYTE)
> );
>
> DECLARE
> TYPE t_ARRAY IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
>
> a_VALUES_ARRAY t_ARRAY;
> s_SQL_STMT VARCHAR2(4000);
> s_COL_INS VARCHAR2(4000):='A,B,C';
> s_PLACEH VARCHAR2(4000):=':VAL1,:VAL2,:VAL3';
> BEGIN
> a_VALUES_ARRAY(1) := 'Test';
> a_VALUES_ARRAY(2) := 'Test';
> a_VALUES_ARRAY(3) := 'Test';
>
> s_SQL_STMT := 'INSERT INTO TAB_A (' || s_COL_INS || ') VALUES ('
> || s_PLACEH || ')';
> EXECUTE IMMEDIATE s_SQL_STMT USING a_VALUES_ARRAY(1), a_VALUES_ARRAY(2),
> a_VALUES_ARRAY(3);
> COMMIT;
> END;
>
>
>
>
> On Thu, Sep 15, 2011 at 5:08 AM, Filippo Biondi <[email protected]>wrote:
>
>> Hi all,
>>
>> I have this problem: In a loop I have to create dynamically an insert
>> statement with a number of bind variables that is not always the same.
>>
>>
>> TYPE t_ARRAY IS TABLE OF VARCHAR2(100) INDEX BY NUMBER;
>>
>> a_VALUES_ARRAY t_ARRAY;
>>
>> <LOOP #1>
>>
>> <SUB_LOOP fetching s_COL names and v_VALUES values >
>>
>> s_COL_INS := s_COL_INS || ' ,' || s_COL; /* for ex. A,B,C */
>> s_PLACEH := s_PLACEH || ' ,:VAL' || n_COUNTER; /* for ex.
>> :VAL1,:VAL2,:VAL3*/
>>
>> a_VALUES_ARRAY[ n_COUNTER ] := v_VALUES ;
>> n_COUNTER := n_COUNTER +1;
>>
>> <END SUB_LOOP>
>>
>>
>> s_SQL_STMT := 'INSERT INTO TAB_A (' || s_COL_INS || ') VALUES (' ||
>> s_PLACEH || ')';
>>
>> EXECUTE IMMEDIATE
>> s_SQL_STMT USING <a_VALUES_ARRAY>; /* (???) Here is the problem */
>>
>>
>> <LOOP #2>
>> I can got s_COL_INS = B,C,E,F
>>
>> <LOOP #3>
>> I can got s_COL_INS = A,F
>>
>> and so on...
>>
>> How can use the varray' values in the using statement???
>>
>> Thanks in advance!
>> Filippo
>>
>> --
>> You received this message because you are subscribed to the Google
>> Groups "Oracle PL/SQL" group.
>> To post to this group, send email to [email protected]
>> To unsubscribe from this group, send email to
>> [email protected]
>> For more options, visit this group at
>> http://groups.google.com/group/Oracle-PLSQL?hl=en
>>
>
> --
> You received this message because you are subscribed to the Google
> Groups "Oracle PL/SQL" group.
> To post to this group, send email to [email protected]
> To unsubscribe from this group, send email to
> [email protected]
> For more options, visit this group at
> http://groups.google.com/group/Oracle-PLSQL?hl=en
>
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
