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
