Debugging a procedure using TOAD...spooling in a procedure

[mike777]

Hello.
I have a procedure I've created using TOAD.
I guess it really doesn't matter whether I'm using TOAD or SQL*Plus, though.
What I'd really like to do is to be able to output variable values to a text file in the course of executing a procedure. In this case I have something like:
create or replace procedure SomeProc as
mySQL varchar2(1000);
begin
mySQL:='select blahblahblah';
mySQL:=mySQL||'blahblahblahagain';
mySQL:=mySQL||'yetmoreblahblahstill';
put (put_line??)??(mySQL);
spool 'c:\test.txt'
mySQL;
spool off;
-- don't know what I'm doing at this point, these are just a couple of things I've tried...neither worked at all...don't think you can use spool inside a procedure
end SomeProc;
Any suggestions??
Thanking you in advance.
-Mike

 

[sem]

The common way is to spool the target sql string(s) (using DBMS_OUTPUT, PRINT or PROMPT or their combination) into some file and then run it:

set feedback off
set termout off
set heading off

var str varchar2(2000)
exec :str := 'select 1 from dual';
spool cmd.sql
print str
exec dbms_output.put_line('/');
spool off

set termout on
@@cmd
Regards, Dima

 

[mike777]

Thanks, Dima.
Still working on figuring this out. Haven't been successful in getting it to work for me.
-Mike

 

[mike777]

Question is, can these command be used in the middle of a procedure?
I have a procedure that creates 8 (or so) SQL statements. I'd like to be able to extract out all 8 of them at once so I can test them.

They get created like:
mySQL:='insert myTable (field1,field2,field3)';
mySQL:=mySQL||' select field11,field12,field13';
mySQL:=mySQL||' from myOtherTable where ';
mySQL:=mySQL||' field1='||chr(39)||'somevalue'||chr(39);
execute immediate (mySQL);

etc. etc.

So to make sure my syntax is correct, I'd like to be able to follow the last "mySQL:=" with a command that outputs the concatenated string to a file or something.

What do you think? Will it work this way?

Thanks again.

-Mike

 

[jee]

You can use DBMS_OUTPUT.PUT_LINE inside a procedure. The key is that you have to turn the output "on" before you issue the SQL that causes the procedure to be executed.

So after your last "mySQL:=" statement, add
DBMS_OUTPUT.PUT_LINE (mySQL);


You say you are using TOAD. I am assuming you are at the latest version (7.4) - results may vary for earlier versions.
If you are executing from a TOAD SQL Window, select the DBMS OUTPUT tab (bottom portion of the window) and then select the "Turn Output On" button (It is RED when output is off, Green when on). Execute your SQL and the results should be displayed in the lower portion of the SQL Window.

If you are executing a procedure from the TOAD Schema Browser, you need to first open a DBMS OUTPUT window (there is a toolbar button for this or from the VIEW menu, select DBMS OUTPUT). This window should open up with the output turned ON (green button). Execute the procedure and view the results in the DBMS OUTPUT window.

 

[ek03]

Try one of these methods inside the procedure:

dbms_output.put_line('value of myvar:' || myvar);
TOAD has a display screen for output generated through dbms_output - just make sure to turn output on (the button should be green, not red)

or you can output to a file:
declare a file handle -
logFile utl_file.file_type;

open the file (this will overwrite any existing file with the same name)-
utl_file.fopen(logFile, 'c:\logfile.txt');

write to the file -
utl_file.put_line(logFile, 'value of myvar:' || myvar');
utl_file.fflush(logFile);

close the file handle -
utl_file.fclose(logFile);

 

[sem]

The drawback(advantage?) of using utl_file is that it will be created on server, assuming it MAY BE created there. Regards, Dima

 

[jee]

Actually it is possible to use UTL_FILE to access files on other computers than the server (I do this for one of my projects), but there are some hoops you have to jump through:

I am assuming Windows environment - You have to set up a share on the "remote" computer. Your Oracle service has to connect to Windows as an OS user who has access to the share (not the default "System" OS user). Your Oracle parameter file has to specify the path in the UTL_FILE_DIR parameter in UNC notation.

 

[sem]

Thanks for the update, Jee. I mean that utl_file is executed on server thus using it may need some serverside manipulations. Regards, Dima

 

[DaPi]

Thanks jee - that's useful info on using net drives.

I use the "debug" routine from Scott Urman, PL/SQL Programming, Oracle Press (chapter 18) (BTW is that the famous scott/tiger?) it uses UTL_FILE. I'd post the code, but I guess copyright says NO. I did modify it to use the AUDSID in the filename (stops different sessions fighting over the same file). The good, but wasteful, thing is that it closes & reopens the file for each call, so nothing is lost in buffers.

What makes it v.useful to me is that it is often not possible to set serveroutput on when debugging an entire system (e.g when TEST is OK, but PROD fails because it has real data!) - debug does the job. DaPi - no silver bullet