ORA-00936 using a SELECT INTO statement with Variables

I can't have not been able to determine why the below statements fail. They run fine when the table name is hard coded, here are the statement:

select max(etl_aud_id) into v_etl_aud_id from prov_avg_cst_stg;

select  cst_sched_id into v_cst_sched_id from prov_avg_cst_stg
   where
   etl_aud_id=v_etl_aud_id
   group by cst_sched_id;

but as a parameter it fails to execute. Any ideas?

Get ORA-00936: missing expression
ORA-06512: at "HCCEETL.SP_TEMP_UPDATE1", line 13
ORA-06512: at line 7

Here's the procedure:

CREATE OR REPLACE PROCEDURE HCCEETL.sp_temp_update1(tbl_in in varchar2)
AS
v_sql varchar2(5000);
v_etl_aud_id number(10);
v_line1 varchar2(40);
v_cst_sched_id varchar2(40);

BEGIN
-- Get audit ID of records in staging table

   v_sql := 'SELECT max(etl_aud_id) INTO' || v_etl_aud_id|| ' FROM ' ||tbl_in;

execute immediate v_sql;
   
    v_line1 := 'Audit ID: '||v_etl_aud_id;
    dbms_output.put_line (v_line1);

v_sql := null;

 v_sql := 'SELECT CST_SCHED_ID INTO '
 ||V_CST_SCHED_ID||' FROM '
 ||TBL_IN|| ' WHERE ETL_AUD_ID='
 || V_ETL_AUD_ID
 ||'GROUP BY CST_SCHED_ID';

EXECUTE IMMEDIATE v_sql;

   v_line1 :='Cost Sched ID: '|| v_cst_sched_id;
    dbms_output.put_line (v_line1);

    commit;
    end;
/
SharonBernalAsked:
Who is Participating?
 
MikeOM_DBACommented:
Try:
  v_sql := 'SELECT max(etl_aud_id) FROM ' ||tbl_in;

  EXECUTE IMMEDIATE v_sql INTO v_etl_aud_id;

Open in new window

:p
PS: Execute immediate will execute native sql and  'INTO' is PL/SQL.
0
 
gatorvipCommented:
>>'SELECT max(etl_aud_id) INTO'

Looks like you're missing a space after INTO
0
 
SharonBernalAuthor Commented:
I tried fixing the space. Get the same error missing expression.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Certainly it doesn't make sense to concat the value of the var into the SQL after INTO.
  v_sql := 'SELECT max(etl_aud_id) INTO' || v_etl_aud_id|| ' FROM ' ||tbl_in;
will resolve to something along
  v_sql := 'SELECT max(etl_aud_id) INTO FROM ' ||tbl_in;
since v_etl_aud_id will be NULL at that point.

The execute immediate as shown in http:#a37803633 is correct; the result var is not part of the dynamic SQL but the PL/SQL command, as stated.
0
 
flow01Commented:
Tough I prefer the solution of MikeOM_DBA using native sql
you can also use a pl/sql block in the execute immediate

example:

declare
  v_statement varchar2(255);
  v_result varchar2(100);
begin
  v_statement := 'begin select to_char(sysdate,''dd/mm/yyyy'') into :rsl from dual;end;';
  execute immediate v_statement using out v_result;
  dbms_output.put_line(v_result);
end;
/
0
 
SharonBernalAuthor Commented:
This works, I see the logic of getting the value first and then assigning it to a variable. Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.