Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1060
  • Last Modified:

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;
/
0
SharonBernal
Asked:
SharonBernal
1 Solution
 
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
QlemoC++ DeveloperCommented:
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now