Toad World® Forums

Testing SQL*Plus scripts


#1

In my application I use the combination of SQLPlus and PL/SQL to complete a certain task. SQLPlus is used for creating fixed-width reports (through its SPOOL capability). PL/SQL populates a temporary table from which teh report is generated in SQL*Plus.

My question is whether it is possible to test a SQLPlus script in the Quest Code Tester. It is the SQLPlus script , not a PL/SQL procedure, that creates a file on disk whose content I need to test. Is there a way to do it in the tool?

Thank you.

Michael


#2

Not at this time unfortunately. We are currently evaluating adding support for testing native SQL statements.

Toad does have a debugger for SQLPlus and it’s own SQLPlus engine for executing scripts. It also has a compare files feature which might allow you to manually compare files for correctness, but that wouldn’t be in the vein of true automated testing.

Regards,
Jeff Smith
Product Manager


#3

Michael,

you might still be able to do this via the dbms_scheduler package:

– shameless plug from
http://www.orafaq.com/faq/can_one_execute_an_operating_system_command_from_pl_sql
CREATE OR REPLACE PROCEDURE EXE_CMD(P_COMMEND IN VARCHAR2)
IS
BEGIN
dbms_scheduler.create_job(job_name => ‘myjob’,
job_type => ‘executable’,
job_action => P_COMMEND,
enabled => TRUE,
auto_drop => TRUE);
END;

– contents of c:\bla.sql

spool c:\bla.out
select * from global_name;
/
exit
/

– call exe_cmd from your QuestCodeTester script
begin
exe_cmd(‘sqlplus scott/tiger@demodb @ c:\bla.sql’);
end;

and read the contents of bla.out back to QuestCodeTester via the utl_file package.

HTH & kind regards
Frank