pl/sql insert with multiple bind variable

[Filippo Biondi]

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

[Joel]

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;

--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle...@googlegroups.com
To unsubscribe from this group, send email to
Oracle-PLSQL...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en

[Filippo Biondi]

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 <joho...@gmail.com>

[vasu bandi]

hi to all,
  can any body help me how to prepare for fake experience interview in plsql
please help me any one.............

[Michael Moore]

Yes,

Read through several books on the subject and do the exercises.

Mike