Home » SQL & PL/SQL » SQL & PL/SQL » using Spool in procedure (WIN XP,Oacle 10g)
using Spool in procedure [message #330597] Mon, 30 June 2008 09:48 Go to next message
freakabhi
Messages: 74
Registered: November 2007
Location: mumbai
Member
Hi All...

How can I use Spool statement to create a file on a remote location in Procedure.This is my requirement, I always get "Invalid statement" error.

SPOOL //shared drive/trig/trigger.txt


what to do with this?

from
FREAKABHI
Re: using Spool in procedure [message #330603 is a reply to message #330597] Mon, 30 June 2008 09:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SPOOL is a SQL*Plus command not a PL/SQL statement.
Use UTL_FILE package.

Regards
Michel
Re: using Spool in procedure [message #330614 is a reply to message #330597] Mon, 30 June 2008 10:33 Go to previous messageGo to next message
freakabhi
Messages: 74
Registered: November 2007
Location: mumbai
Member
Hi...

thanks for quick reply.....

can you give some sample snippet for using UTL_FILE
package..I never used it before.

thanks in advance.

from
Freakabhi
Re: using Spool in procedure [message #330615 is a reply to message #330597] Mon, 30 June 2008 10:36 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
I am sorry to see that both the SEARCH function on this forum & GOOGLE are broken for you.

Please wait patiently while repairs are completed.
A more complete response will follow shortly.
Re: using Spool in procedure [message #330617 is a reply to message #330614] Mon, 30 June 2008 10:43 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
There is something you need to consider.
Spool is client side. You can invoke and write to anywhere.
UTL_FILE is server side. Which means, you can inovke it from
anywhere but this remote directory
you trying to write should be visible to Oracle database server.

Re: using Spool in procedure [message #330676 is a reply to message #330597] Mon, 30 June 2008 14:48 Go to previous messageGo to next message
mandeepmandy
Messages: 79
Registered: May 2008
Location: USA
Member

Look at this:
create or replace procedure utl_file_test_read (
path in varchar2,
filename in varchar2)
is
input_file utl_file.file_type;
input_buffer varchar2(4000);
begin
input_file := utl_file.fopen (path,filename, 'R');

utl_file.get_line (input_file, input_buffer);
dbms_output.put_line(input_buffer);
utl_file.get_line (input_file, input_buffer);
dbms_output.put_line(input_buffer);
utl_file.fclose(input_file);

--exception
-- when others then null;
end;

[Updated on: Mon, 30 June 2008 14:48]

Report message to a moderator

Re: using Spool in procedure [message #330681 is a reply to message #330676] Mon, 30 June 2008 15:21 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) .
Use the "Preview Message" button to verify.

In addition, I don't think that open in read mode and get_line will answer the question that is to WRITE.

Regards
Michel

[Updated on: Thu, 03 July 2008 13:54]

Report message to a moderator

Previous Topic: How to load special characters into oracle database
Next Topic: how to delete duplicate rows?
Goto Forum:
  


Current Time: Sat Dec 10 22:13:40 CST 2016

Total time taken to generate the page: 0.06556 seconds