hi to all, can any body help me how to prepare for fake experience interview in plsql please help me any one.............
On Thu, Sep 15, 2011 at 5:24 PM, Joel <[email protected]> wrote: > 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
