Results 1 to 11 of 11

Thread: Dynamic Sql

  1. #1
    Join Date
    Sep 2003
    Location
    Kuwait
    Posts
    27

    Unanswered: Dynamic Sql

    Hello Gurus,

    We wrote a trigger that calls a stored java pgm to generate the dml statement. We are using the execute immediate statement. It goes something like this:

    var_dml := java(var_data);
    execute immediate var_dml;

    If the var_dml variable contains an insert statement, it is executed without any problem. However, if the variable contains an update statement, I get an invalid sql statement error - ora-00900. What do you think is the problem?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    My first bet would be that the SQL statement is NOT valid.
    Print it out or save it to a file & then try in via SQL*Plus.

  3. #3
    Join Date
    Sep 2003
    Location
    Kuwait
    Posts
    27
    Originally posted by anacedent
    My first bet would be that the SQL statement is NOT valid.
    Print it out or save it to a file & then try in via SQL*Plus.
    The update statement executes in SQLPLUS without any problem.

  4. #4
    Join Date
    Jun 2003
    Posts
    294
    The structure is: execute immetiate 'String';

    What does java(var_data) return?

    You have to give an string to the execute immediate sentence or a varchar or varchar2 type variable.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    What does the failing update statement look like?

  6. #6
    Join Date
    Oct 2003
    Posts
    87
    Originally posted by tangcov
    The update statement executes in SQLPLUS without any problem.
    Using correct schemaName, permissions, session variables, etc.
    Oracle - DB2 - MS Access -

  7. #7
    Join Date
    Sep 2003
    Location
    Kuwait
    Posts
    27
    Originally posted by andrewst
    What does the failing update statement look like?
    The java(var_data) function returns a varchar2 string.

    The statement looks like the following:

    update tab_name set field = 1245, field1 = to_number(123455, '999999'), field3 = null where field = 1245

  8. #8
    Join Date
    Oct 2003
    Location
    St.Louis,MO
    Posts
    120
    datatype of field3??

  9. #9
    Join Date
    Oct 2003
    Location
    St.Louis,MO
    Posts
    120
    Originally posted by tlael
    datatype of field3??

    Sorry, what I meant is are nulls allowed in field 3

  10. #10
    Join Date
    Oct 2003
    Location
    St.Louis,MO
    Posts
    120
    It would help to see the failing update SQL and the insert SQL that is working for comparison.

  11. #11
    Join Date
    Sep 2003
    Location
    Kuwait
    Posts
    27
    Originally posted by tlael
    Sorry, what I meant is are nulls allowed in field 3
    Yes.. Nulls are allowed in field 3.

Posting Permissions

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