ORA-01036 after rewriting an Oracle insert command

Marco

I am reworking legacy code and came to this query:

sqlDS.InsertCommand = "INSERT INTO SCHEMA.TABLE " +
                      "(ID, DATUM, ID_STATE, G_AM, G_VON, DATUM_BEW) " +
                      "VALUES     (" + ((strukturen.Login)Session["svar_bew"]).ID_Bew.ToString() +
                      ", TO_DATE('" + _datum + "', 'DD.MM.YYYY HH24:MI:SS'), 2, TO_DATE('" + lbl_bew.Text +
                      "', 'DD.MM.YYYY'), 'ID" + ((strukturen.Login)Session["svar_bew"]).ID_Bew.ToString() +
                      "', TO_DATE('" + _datum + "', 'DD.MM.YYYY HH24:MI:SS'))";

Which works, but I want this abomination rewritten into a paramerterized format, for obious reasons.

Now this is my progress so far:

sqlDS.InsertCommand = "INSERT INTO SCHEMA.TABLE (ID, DATUM, ID_STATE, G_AM, G_VON, DATUM_BEW) " +
                      "VALUES (:Id_Bew, TO_DATE(':Datum', 'DD.MM.YYYY HH24:MI:SS'), 2, TO_DATE(':BewAm', 'DD.MM.YYYY'), 'ID' || :Id_Bew, TO_DATE(':Datum', 'DD.MM.YYYY HH24:MI:SS'))";
                        sqlDS.InsertParameters.Add("Id_Bew", ((strukturen.Login)Session["svar_bew"]).ID_Bew.ToString());
                        sqlDS.InsertParameters.Add("Datum", _datum);
                        sqlDS.InsertParameters.Add("BewAm", lbl_bew.Text);

This is the table in question:

COLUMN      |    TYPE
-----------------------
ID          |    NUMBER
DATUM       |    DATE
/*  Columns |    ommited */
ID_STATE    |    NUMBER
G_AM        |    DATE
G_VON       |    CHAR(10 BYTE)
DATUM_BEW   |    DATE

The variables follow no fixed schema yet, so they were declared in German, never mind that in this case. The problem is, that this new code now throws an ORA-01036 an I suspect that it is at this location:

'ID" + ((strukturen.Login)Session["svar_bew"]).ID_Bew.ToString() --> 'ID' || :Id_Bew

In the original SQL query the string "ID" is concatinated with an numeric ID which is pulled out of a session variable. My suspicion is, that I concatinated it the wrong way in my paramerterized query and botched up the syntax.

Question is: How can I resolve this error and bring this insert command into a proper format?

Maheswaran Ravisankar

Bind Variables Should be defined without embedding in single quotes.

':bind_variable' ---> Wrong (Treated as plain string instead)
:bind_variable ---> Right

So, When you attempted to add a bind parameter, it threw ORA-01036: illegal variable name/number

 sqlDS.InsertCommand = "INSERT INTO SCHEMA.TABLE (ID, DATUM, ID_STATE, G_AM, G_VON, DATUM_BEW) " +
                          "VALUES (:Id_Bew, TO_DATE(:Datum, 'DD.MM.YYYY HH24:MI:SS'), 2, TO_DATE(:BewAm, 'DD.MM.YYYY'), 'ID' || :Id_Bew, TO_DATE(:Datum, 'DD.MM.YYYY HH24:MI:SS'))";
                            sqlDS.InsertParameters.Add("Id_Bew", ((strukturen.Login)Session["svar_bew"]).ID_Bew.ToString());
                            sqlDS.InsertParameters.Add("Datum", _datum);
                            sqlDS.InsertParameters.Add("BewAm", lbl_bew.Text);

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Oracle: Order by Union returning ORA-00933: SQL command not properly ended

From Dev

cx_Oracle CREATE TABLE AS returns ORA-01036: illegal variable name/number

From Dev

Oracle ORA-01036 illegal variable name/number for no obvious reason

From Dev

ORA-01722: invalid number on INSERT in C# - Oracle 11g

From Dev

Oracle trigger after insert or delete

From Dev

How to update after insert in the same table in an Oracle trigger?

From Dev

ORA-00917: missing comma when insert not exist in oracle

From Dev

Io exception: Oracle Error ORA-12650 after upgrading Oracle to 12g

From Dev

ORA-01036: illegal variable name/number - oci_bind_by_name

From Dev

Geeting error ORA-01036 while executing Oracle Procedure using C#

From Dev

Oracle Error-ORA-00933: SQL command not properly ended

From Dev

Slick/Oracle PLAIN SQL Get auto generated ID after insert

From Dev

ORA-04043 on Insert

From Dev

Oracle trigger that update record after insert

From Dev

Oracle: Order by Union returning ORA-00933: SQL command not properly ended

From Dev

Oracle Parameters in .net sql queries - ORA-00933: SQL command not properly ended

From Dev

Dapper and Oracle parametrized query - ORA-01036: illegal variable name/number

From Dev

select query with where condition in oracle 10g giving ORA-00933: SQL command not properly ended

From Dev

ORA-00917: missing comma when insert not exist in oracle

From Dev

Oracle throws ORA-12514 after shutdown immediate

From Dev

Oracle Query giving error "ORA-00933: SQL command not properly ended"

From Dev

VB.NET Oracle SQL "INSERT INTO" with "RETURNING INTO" gives ORA-00933 Command Not Properly Ended

From Dev

Python script to move data from a SQL server db to an oracle db keeps giving 'ORA-01036: illegal variable name/number'

From Dev

Oracle Error-ORA-00933: SQL command not properly ended

From Dev

Oracle trigger before and after insert

From Dev

"ORA-00933: SQL command not properly ended" using Oracle.ManagedDataAccess

From Dev

ORA-01036: illegal variable name/number when running query through nodejs

From Dev

Rewriting an Array keys after sorting it

From Dev

After Insert Trigger ORA-01422: fetch returns more than requested number of rows

Related Related

  1. 1

    Oracle: Order by Union returning ORA-00933: SQL command not properly ended

  2. 2

    cx_Oracle CREATE TABLE AS returns ORA-01036: illegal variable name/number

  3. 3

    Oracle ORA-01036 illegal variable name/number for no obvious reason

  4. 4

    ORA-01722: invalid number on INSERT in C# - Oracle 11g

  5. 5

    Oracle trigger after insert or delete

  6. 6

    How to update after insert in the same table in an Oracle trigger?

  7. 7

    ORA-00917: missing comma when insert not exist in oracle

  8. 8

    Io exception: Oracle Error ORA-12650 after upgrading Oracle to 12g

  9. 9

    ORA-01036: illegal variable name/number - oci_bind_by_name

  10. 10

    Geeting error ORA-01036 while executing Oracle Procedure using C#

  11. 11

    Oracle Error-ORA-00933: SQL command not properly ended

  12. 12

    Slick/Oracle PLAIN SQL Get auto generated ID after insert

  13. 13

    ORA-04043 on Insert

  14. 14

    Oracle trigger that update record after insert

  15. 15

    Oracle: Order by Union returning ORA-00933: SQL command not properly ended

  16. 16

    Oracle Parameters in .net sql queries - ORA-00933: SQL command not properly ended

  17. 17

    Dapper and Oracle parametrized query - ORA-01036: illegal variable name/number

  18. 18

    select query with where condition in oracle 10g giving ORA-00933: SQL command not properly ended

  19. 19

    ORA-00917: missing comma when insert not exist in oracle

  20. 20

    Oracle throws ORA-12514 after shutdown immediate

  21. 21

    Oracle Query giving error "ORA-00933: SQL command not properly ended"

  22. 22

    VB.NET Oracle SQL "INSERT INTO" with "RETURNING INTO" gives ORA-00933 Command Not Properly Ended

  23. 23

    Python script to move data from a SQL server db to an oracle db keeps giving 'ORA-01036: illegal variable name/number'

  24. 24

    Oracle Error-ORA-00933: SQL command not properly ended

  25. 25

    Oracle trigger before and after insert

  26. 26

    "ORA-00933: SQL command not properly ended" using Oracle.ManagedDataAccess

  27. 27

    ORA-01036: illegal variable name/number when running query through nodejs

  28. 28

    Rewriting an Array keys after sorting it

  29. 29

    After Insert Trigger ORA-01422: fetch returns more than requested number of rows

HotTag

Archive