SharonBernal
asked on
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",
ORA-06512: at line 7
Here's the procedure:
CREATE OR REPLACE PROCEDURE HCCEETL.sp_temp_update1(tb
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;
/
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",
ORA-06512: at line 7
Here's the procedure:
CREATE OR REPLACE PROCEDURE HCCEETL.sp_temp_update1(tb
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;
/
ASKER
I tried fixing the space. Get the same error missing expression.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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/yy
execute immediate v_statement using out v_result;
dbms_output.put_line(v_res
end;
/
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/yy
execute immediate v_statement using out v_result;
dbms_output.put_line(v_res
end;
/
ASKER
This works, I see the logic of getting the value first and then assigning it to a variable. Thank you.
Looks like you're missing a space after INTO