Creating Triggers with :new Leads to Parameter Binds and ORA-01003 — oracle-tech

    Forum Stats

  • 3,716,000 Users
  • 2,242,928 Discussions
  • 7,845,734 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Creating Triggers with :new Leads to Parameter Binds and ORA-01003

876fbe6d-de22-4ef2-9572-850411674dc5
edited May 2019 in ODP.NET

Dear Mrs

I want to create an new trigger using .NET Oracle command. The trigger function includes the reserved word :NEW in its function:

CREATE OR REPLACE TRIGGER INTABLTRIGBEFORE INSERT ON INTABLTRIG  FOR EACH ROW  DECLARE    tmpVar NUMBER;BEGIN    tmpVar := 0;    SELECT INERPSequenzNummerSequence.NextVal INTO tmpVar FROM dual;    :NEW.SequenzNummer := tmpVar;  EXCEPTION    WHEN OTHERS THEN      Null;  END INTABLTRIG;

I put the SQL function in a CommandText and execute them as ExecuteNonQueryAsync().
This works fine omitting the line 09. :NEW.SequenzNummer : = tmpVar;, but this is not the sense.

If I execute them with the line 09. and the :NEW parameter, I get the Oracle error ORA-01003.

As I know, the :NEW is normally used as parameter binding. So I think, :NEW is interpreted as parameter binding. However I tried to set an oracle parameter for :NEW. I tried to following variants:

OracleParameter oraParam = new OracleParameter(":NEW", null);OracleParameter oraParam = new OracleParameter(":NEW", ":NEW");OracleParameter oraParam = new OracleParameter(":NEW", OracleDbType.RefCursor);OracleParameter oraParam = new OracleParameter("NEW", null);OracleParameter oraParam = new OracleParameter("NEW", ":NEW");OracleParameter oraParam = new OracleParameter("NEW", OracleDbType.RefCursor);

and added them to

oracleCommand.Parameters.Clear();oracleCommand.Parameters.Add(oraParam); // used on of the aboveoracleCommand.CommandText := "---- the SQL from above ----";oracleCommand.ExecuteNonQueryAsync();

But for every variant, I get this ORA-01003 error.

Can anybody tell me how to handle this, so that the :NEW parameter is used as :NEW for the Trigger function and not as parameter binding or howto set the right parameter value, so that I can create the Trigger without error ORA-01003.

Thank you
Kind regards,
Tom

Answers

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited May 2019

    This is DDL and should be executed by some other process - scripted with  proper review process. Sql*plus will handle it fine.

    That said, your exception swallowing is dangerous.

    You don’t need to select from dual to use a sequence.

    You don’t need to assign the sequence value to one variable and then assign that to another.

Sign In or Register to comment.