Home » SQL & PL/SQL » SQL & PL/SQL » spool to file using a plsql block
spool to file using a plsql block [message #232485] Fri, 20 April 2007 12:49 Go to next message
marks20101
Messages: 74
Registered: May 2005
Member
Is it possible to loop two cursors, then spool the results to a .txt file (seperate .txt files per cursor)? I'm not crazy about an IF within a LOOP, but not sure on any other way to process this.

DECLARE
CURSOR CASES IS
SELECT distinct trim(substr(substr(LOCAL_ERR_MSG,1,56),46,56)) FROM err_rpt_view
WHERE SUBSTR(local_ERR_MSG,1,46) LIKE '%Case record containing case external number =%'
AND job_run_pid=p_job_run_pid;

CURSOR members IS
SELECT distinct trim(substr(substr(LOCAL_ERR_MSG,1,62),50,62)) FROM err_rpt_view
where SUBSTR(local_ERR_MSG,1,62) LIKE '%Unable to return PID from pat_dim using pat_sid =%'
AND job_run_pid=p_job_run_pid;

BEGIN

FOR A1 IN CASES LOOP

IF CASES%FOUND THEN
--...SPOOL TO FILE HERE
ELSE
EXIT;
END IF;

END LOOP;

FOR A2 IN MEMBERS LOOP

IF MEMBERS%FOUND THEN
--...SPOOL TO FILE HERE
ELSE
EXIT;
END IF;

END LOOP;

Hope that is clear.
Thanks,
Re: spool to file using a plsql block [message #232490 is a reply to message #232485] Fri, 20 April 2007 13:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why not directly?

spool f1
SELECT distinct trim(substr(substr(LOCAL_ERR_MSG,1,56),46,56)) FROM err_rpt_view
WHERE SUBSTR(local_ERR_MSG,1,46) LIKE '%Case record containing case external number =%'
AND job_run_pid=:p_job_run_pid;
spool f2
SELECT distinct trim(substr(substr(LOCAL_ERR_MSG,1,62),50,62)) FROM err_rpt_view
where SUBSTR(local_ERR_MSG,1,62) LIKE '%Unable to return PID from pat_dim using pat_sid =%'
AND job_run_pid=:p_job_run_pid;
spool off

Regards
Michel
Re: spool to file using a plsql block [message #232495 is a reply to message #232485] Fri, 20 April 2007 13:46 Go to previous messageGo to next message
marks20101
Messages: 74
Registered: May 2005
Member
Thanks Michel, that does work well, but is there a way to use the accept command to prompt the user to enter the job_run_pid doing it directly?

Thanks,
MS
Re: spool to file using a plsql block [message #232497 is a reply to message #232495] Fri, 20 April 2007 13:56 Go to previous message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes: change :p_job_run_pid with &&your_variable_defined_in_accept

Regards
Michel
Previous Topic: Average in Analytical function
Next Topic: Materialized View
Goto Forum:
  


Current Time: Wed Dec 07 14:26:53 CST 2016

Total time taken to generate the page: 0.12975 seconds