Regarding sql substitution

Question

Hi,

When i ran the below queries it's failing in the second query becuase prev_test_ref1 variable is not defined. If i remove the insert statement in the first query ,run again then it's working and using the prev_test_ref1 value from the first sql query in second query. Is it because of variable scope? How can i resolve this with the insert statement.

QUERY1

column prev_test_ref1 new_value prev_test_ref1  ;

insert into testing.test_ref_details(TEST_TYPE,TEST_REF_NO)
select  '1',max(test_ref_no) as prev_test_ref1
from    testing.test_runs_status
where   test_type = 1
and run_status = 1
and test_end_dt = (select last_day(add_months(trunc(sysdate),-6))+2 from dual)
group by test_end_dt
;

QUERY2

column last_test_end_dt new_value last_test_end_dt;

select to_char(test_completion_dt,'DD-MON-YYYY HH24:MI:SS') as last_test_end_dt
from testing.test_runs_status
where test_ref_no = '&prev_test_ref1';

Answer 1

declare
prev_test_ref1 number(10);
begin
  insert into ...select ...;
  select ... into prev_test_ref1 from ...;
end;
/

Answer 2

Hi Arav,

In SQL*Plus substitution variables will only be defined with SELECT statements. Your first insert doesn't return rows so it won't work (think about it: it only returns 1 row inserted., SQL*Plus has no way to know the value inserted.)

I suggest you add a step to save the value into the variable (or use a PL/SQL block):

column prev_test_ref1 new_value prev_test_ref1  ;

SELECT MAX(test_ref_no) AS prev_test_ref1
  FROM testing.test_runs_status
 WHERE test_type = 1
   AND run_status = 1
   AND test_end_dt = (SELECT last_day(add_months(trunc(SYSDATE), -6)) + 2 
                        FROM dual)
 GROUP BY test_end_dt;

 INSERT INTO testing.test_ref_details(TEST_TYPE,TEST_REF_NO) 
    VALUES ('1', &prev_test_ref1);

 SELECT ...

Answer 3

The INSERT statement has a RETURNING clause. We can use this to get access to "unknown" values from the table. The following examples uses RETURNING to get the assigned nextval from a sequence, but we could return any column from the row:

SQL> var prev_id number
SQL> insert into t23 (id, name) values (my_seq.nextval, 'MAISIE')
  2  returning id into :prev_id
  3  /

1 row created.

SQL> select * from t23
  2  where id = :prev_id
  3  /

NAME               ID
---------- ----------
MAISIE            122

SQL> 

Unfortunately the RETURNING clause only works with single-row SQL.

Answer 4

It isn't really clear what the purpose of the whole script is, especially in light of the comment "i have similar sql query which returns multiple rows. In that case i cant have separate insert statement."

If you want to use the results of a select, see if Multi-Table Inserts fit the bill. Your select statement can insert into both the primary table and also a second table (eg a global temporary table). You can then query the global temporary table to see what rows were inserted.