Oracle Execute Immediate throws missing keyword error

Question

In my stored procedure I use the following query:

SELECT COUNT(*) INTO COL_NO FROM user_tab_columns WHERE table_name='PAY_SLIP_FEB_16';

This query gives no error (because it shouldn't). But the table name here is fixed so I want to use a variable that will provide the table name. So I changed the query like this:

EXECUTE IMMEDIATE 'SELECT COUNT(*) INTO COL_NO FROM user_tab_columns WHERE table_name='''||TBL_NAME||'''';

This query gives error:

ORA-00905: missing keyword

What did I do wrong? TIA.

N.B. : TBL_NAME is the incoming parameter that will provide the table name, and COL_NAME is just a NUMBER(5,0) type variable.

Gordon Linoff · Accepted Answer · 2016-03-22 10:57:40Z

16

I would expect the code to have the INTO as part of the EXECUTE IMMEDIATE, not in the dynamic query string:

EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM user_tab_columns WHERE table_name='''||TBL_NAME||''''
        INTO COL_NO ;

As a note: I would write this using a separate variable:

v_sql  := 'SELECT COUNT(*) FROM user_tab_columns WHERE table_name = ''@TBL_NAME''';
v_sql := REPLACE(v_sql, '@TBL_NAME', TBL_NAME)

EXECUTE IMMEDIATE v_sql INTO v_COL_NO ;

answered Mar 22, 2016 at 10:57

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

Wahid Masud Over a year ago

what is the advantage of using @TBL_NAME and then replace it after?

Gordon Linoff Over a year ago

@WahidMasud . . . I find it much easier to understand the query and to maintain it over time. Concatenating a bunch of strings together makes it really hard to follow the logic in the subquery.

Collectives™ on Stack Overflow

Oracle Execute Immediate throws missing keyword error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related