Regarding sql substitution

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 resolv开发者_如何学Pythone 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';

In SQLPlus 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., SQLPlus 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 ...

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

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.

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.