Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2007

    Unanswered: How to insert a string of concatenated values into a table


    to insert into table i have a concatenated string of column names and corresponding string column values in a comma delimited form like..

    column_name_string is like col_name1,col_name2,col_name4,col_name3

    column_values_string like col_value1,col_value2,col_value4,col_value3

    is there any way to insert into table with query like
    insert into table_1(column_name_string) values(column_values_string) ...?

    I can split the string into varrays and loop in them and can there any other simple approach..

  2. #2
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    I can think of no easy or better solution.
    How can you be sure the value is properly "aligned" with the desired column?
    Keep in mind that Oracle expects strings to be enclosed in single quote marks.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2007
    The java process calling the stored procedure and sending these two strings make sure that column names and column values are aligned properly

  4. #4
    Join Date
    Aug 2004
    You can do it with EXECUTE IMMEDIATE (dynamic SQL), but beware of hard parses and SQL injection because you won't be able to use bind variables by doing so . Also beware of implicit conversions if you don't have string only columns (I give an example of how to deal with a NUMBER column and with a DATE column below).

    rbaraer@Ora10g> CREATE TABLE Table1(
        col1 VARCHAR2(10),
        col2 VARCHAR2(10),
        col3 NUMBER,
        col4 DATE)
    TABLESPACE USERS;  2    3    4    5    6
    Table created.
    rbaraer@Ora10g> DECLARE
      2      strColumnsList VARCHAR2(4000) := 'col1, col2, col3, col4';
      3      strValuesList VARCHAR2(4000) := '''val1'', ''val2'', TO_NUMBER(''301.5''), TO_DATE(''06/12/2007'', ''DD/MM/YYYY'')';
      4  BEGIN
      5      EXECUTE IMMEDIATE 'INSERT INTO Table1('||strColumnsList||') VALUES('||strValuesList||')';
      6  END;
      7  /
    PL/SQL procedure successfully completed.
    rbaraer@Ora10g> SELECT * FROM Table1;
    COL1       COL2             COL3 COL4
    ---------- ---------- ---------- ---------
    val1       val2            301.5 06-DEC-07
    HTH & Regards,

    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts