Missing expression Oracle Query

Question

I have a oracle trigger and it returns the error

Additional information: ORA-00936: missing expression

In my TOAD it shows me the below line.

EXECUTE IMMEDIATE 'UPDATE TBL_NEWS_TYPE SET FULLNAME='|| newsName ||' WHERE ID = SELECT MAX(ID) FROM TBL_NEWS_TYPE)';

In here newsName is varchar2 variable like newsName VARCHAR2(50) / and ID in INTEGER.

Why are you using execute immediate here? There is no reason for it. And if you do have to for some strange reason, then at least use it with parameters. Concatenating values like that is going to give you trouble in the long run. e,g.: execute immediate 'update tbl_news_type set fullname = :1 where id = (...)' using newsname; — user330315
– user330315, Commented Mar 30, 2016 at 6:04

sagi · Accepted Answer · 2016-03-30 05:31:17Z

2

Try this:

EXECUTE IMMEDIATE 'UPDATE TBL_NEWS_TYPE
                   SET FULLNAME='''|| newsName
                   ||''' WHERE ID = (SELECT MAX(ID) FROM TBL_NEWS_TYPE)'

I think you had two problems here:

1) You were missing '(' at the start of the select

2) I think this won't work without putting quote sign wraping the newsName because its a string.

answered Mar 30, 2016 at 5:31

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

TechGuy Over a year ago

Now it shows me this exception ORA-06519: active autonomous transaction detected and rolled back

sagi Over a year ago

And thats the whole code? are you sure this is the part that throwing the exception?

TechGuy Over a year ago

ah! got it i used PRAGMA AUTONOMOUS_TRANSACTION; .. now COMMIT missing.. i aded that and working perfectly.

Collectives™ on Stack Overflow

Missing expression Oracle Query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related