Test Script SQL Example

[Sam68]

According to the documentation for 9.0.6 for Testing Programs

5.7 Viewing result sets
In a Test Script you are not limited to PL/SQL blocks. You can also execute single SQL statements, including select statements. Executing a select statement will display an additional Results tab page, which displays all selected rows.
==============================================================
The following Test Script generates Oracle Error:
ORA-06550 PLS-00103: Encountered the symbol "SELECT"
------------------------------------------------------------
-- Created on 1/17/2013 by SMALICKI
declare
-- Local variables here
i integer;
begin
-- Test statements here
:A := 'test';
Select t.IMEI_ID
into :A
From CONT_IMEI_HISTORY t
Where t.IMEI_ID = '000000999';
end;
Select t.IMEI_ID
From CONT_IMEI_HISTORY t
Where t.IMEI_ID = '000000999';
--------------------------------------------------------
Does anyone have an example of how to format a select statement for the Test Script envirionment so that the output will be genereated on a new Results tab?
=========================================================
Thx, Steve

 

[Maxmix]

From my experience, you can only execute EITHER a Pl/Sql block OR a single Select (without trailing semi-colon).

Also, you can execute multiple queries from within Pl/Sql block like this:
begin
...
open :first_resultset for select ..... ;
open :second_resultset for select ..... ;
...
end;

But in this case you will have to:
1) Assign Cursor type to each bind variable
2) Execute block
3) Open each result set in separate window

 

[mike]

I see a couple of things. The Bind variable is not set up. Also, after blocks, you need a forward slash to execute it. We don't have the same table, so I can't make sure it will work, but a similar example worked for me. Try this (but remove the mike comments):

-- Created on 1/17/2013 by SMALICKI

VARIABLE AA VARCHAR2(100) -- mike added.
DECLARE
-- Local variables here
i INTEGER;
BEGIN
-- Test statements here
:AA := 'test'; -- Mike Changed.
SELECT t.IMEI_ID
INTO :AA
FROM CONT_IMEI_HISTORY t
WHERE t.IMEI_ID = '000000999';
END;
/ -- Mike added

SELECT t.IMEI_ID
FROM CONT_IMEI_HISTORY t
WHERE t.IMEI_ID = '000000999';

 

[William Robertson]

Mike, that will work in a Command window - OP is asking about Test windows.

A Test window can only contain one statement - either a PL/SQL block or a SQL statement. (Therefore it does not support delimiters between statements, so no trailing ';' for a SQL statement.)