Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ORA-00936 using a SELECT INTO statement with Variables

Posted on 2012-04-03
6
Medium Priority
?
1,043 Views
Last Modified: 2012-04-03
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
Comment
Question by:SharonBernal
6 Comments
 
LVL 20

Expert Comment

by:gatorvip
ID: 37803509
>>'SELECT max(etl_aud_id) INTO'

Looks like you're missing a space after INTO
0
 

Author Comment

by:SharonBernal
ID: 37803581
I tried fixing the space. Get the same error missing expression.
0
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 2000 total points
ID: 37803633
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 71

Expert Comment

by:Qlemo
ID: 37803702
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
 
LVL 21

Expert Comment

by:flow01
ID: 37803721
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
 

Author Closing Comment

by:SharonBernal
ID: 37803833
This works, I see the logic of getting the value first and then assigning it to a variable. Thank you.
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question