Oracle returning result from dynamic SQL

Question

Hi I am trying to return a value from Oracle using dynamic SQL. I am getting an error SQL command not properly ended. I can't figure out what I am doing wrong. Here is the latest code I've tried:

PROCEDURE get_record_counts
AS
v_EXT_RECCOUNT   VARCHAR2(05) := '0';
BEGIN

 EXECUTE IMMEDIATE 'select count(*)  from ' ||  r_cls.EXT_TABLE ||  ' RETURN v_EXT_RECCOUNT into v_EXT_RECCOUNT ';


END  get_record_counts;

Justin Cave · Accepted Answer · 2014-01-20 19:44:33Z

3

You'd want

EXECUTE IMMEDIATE 'select count(*) from ' || r_cls.ext_table
   INTO v_ext_reccount

assuming that r_cls.ext_table resolves to a varchar2 variable that contains a table name that the caller has appropriate permissions on. In the snippet you posted, that is not a valid variable name but I'm guessing that there is more code that you've removed that declares that variable.

answered Jan 20, 2014 at 19:44

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

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

1 Comment

Marcie Over a year ago

That did the trick. I was trying to add the "into" portion in the quotes of the execute immediate. Thanks!

Collectives™ on Stack Overflow

Oracle returning result from dynamic SQL

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related