Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Posts
    41

    Unanswered: mulitiple update queries to execute in one single shot

    i have a problem in oracle

    i have a query statement like

    insert stmt 1; insert stmt 2; insert stmt 3; insert
    stmt 4; insert stmt 5;

    i.e. i will store all the insert statement in one single string with ; separation

    insert stmt 1; insert stmt 2; insert stmt 3; insert
    stmt 4; insert stmt 5;

    how to execute this statement in oracle. it gives me

    ORA-00911: invalid character

    eg:
    UPDATE table_name SET xyz = 'O' WHERE abc = '975b' ; UPDATE table_name SET xyz = 'O' WHERE abc = '1234'

    i replaced ; with / and still it fails. now i want to know how to execute muliple select statements in oracle.

    Note i have this query in a variable and i am executing this through .net environment.

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    you can do this in a block.

    PHP Code:
    topicadm@Topic_Devcreate table a (a number);

    Table created.

    Elapsed00:00:00.01
    topicadm
    @Topic_Devcreate table b (a number);

    Table created.

    Elapsed00:00:00.00
    topicadm
    @Topic_Devinsert into a values (1); insert into b values (2);
    insert into a values (1); insert into b values (2)
                            *
    ERROR at line 1:
    ORA-00911invalid character


    Elapsed
    00:00:00.00
    topicadm
    @Topic_Dev> declare begin insert into a values (1); insert into b values (2); end;
      
    2  /

    PL/SQL procedure successfully completed.

    Elapsed00:00:00.00
    topicadm
    @Topic_Devselect from a;

             
    A
    ----------
             
    1

    Elapsed
    00:00:00.00
    topicadm
    @Topic_Devselect from b;

             
    A
    ----------
             

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jul 2004
    Location
    spain
    Posts
    2

    Post If I understood ...

    You have an array with all sql statements and you can launch and per time

    Eg:

    matrix (0) = 'Insert into xxx values (yyyy,zzzz)
    matrix (1) = 'Insert into xxx values (yyyy,zzzz)

    then throw the database object you can invoke the execute statement so the sql statement will be executed .

    Note!: Don´t forget to execute "commit work" before all manipulation statements executed

  4. #4
    Join Date
    Jan 2003
    Posts
    41

    batch update in .net (oracle db)

    i have a batch of insert / update statements in an array and when i try to execute the array through .net i am getting error. i dont know what i am doing wrong. below is my statement.

    arrQuery[0] = "INSERT INTO abc ";
    arrQuery[0] += "(col1, col2, col3 )";
    arrQuery[0] += " VALUES('" + strcol1 + "','" ;
    arrQuery[0] += strcol2 + "','";
    arrQuery[0] += strcol3 + "'); ";
    arrQuery[0] = "INSERT INTO abc ";
    arrQuery[0] += "(col1, col2, col3 )";
    arrQuery[0] += " VALUES('" + strcol1 + "','" ;
    arrQuery[0] += strcol2 + "','";
    arrQuery[0] += strcol3 + "'); ";
    arrQuery[0] = "INSERT INTO abc ";
    arrQuery[0] += "(col1, col2, col3 )";
    arrQuery[0] += " VALUES('" + strcol1 + "','" ;
    arrQuery[0] += strcol2 + "','";
    arrQuery[0] += strcol3 + "'); ";

    iResult=objExec.ExecuteCommand(arrQuery);
    I execute this command but this gives error

    i cannot use stored procedures since the query is common for both mssql and oracle. (Note the same query i am able to execute in mssql)

  5. #5
    Join Date
    Apr 2004
    Posts
    246
    use some common sense, and apply the above responses to your code:

    arrQuery[0] = "BEGIN INSERT INTO abc ";
    arrQuery[0] += "(col1, col2, col3 )";
    ...
    arrQuery[0] += strcol3 + "'); END; ";
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    This is not a good approach. You should be using a prepared statement with bind variables.

    Set up the statement like this:

    arrQuery[0] = "INSERT INTO abc (col1, col2, col3 ) VALUES(?,?,?);";

    Then bind the first set of values and execute, bind the next set and execute, until done. Much more efficient.

Posting Permissions

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