Home » SQL & PL/SQL » SQL & PL/SQL » Executing PL/SQL from a file
Executing PL/SQL from a file [message #36917] Thu, 03 January 2002 20:53 Go to next message
Manish
Messages: 79
Registered: December 2000
Member
I have a PL/SQL code that I wish to execute from SQL*Plus. I can do that using @. But I have to place the .sql file in ...OracleOra81BIN (directory where SQLPLUSW.EXE is present). What settings do I have to change to execute this .sql file if it's placed in a directory other than ...OracleOra81BIN?

----------------------------------------------------------------------
Re: Executing PL/SQL from a file [message #36919 is a reply to message #36917] Thu, 03 January 2002 21:54 Go to previous messageGo to next message
Sandipta N Biswas
Messages: 7
Registered: December 2001
Junior Member
just type the path name after the @
like
@c:aa.sql
where aa.sql is located at c:

----------------------------------------------------------------------
Re: Executing PL/SQL from a file [message #36927 is a reply to message #36917] Fri, 04 January 2002 04:24 Go to previous messageGo to next message
RYAN
Messages: 22
Registered: December 2000
Junior Member
haven't worked with windows too much (mostly unix), but the first thing that comes to mind is removing the C: from the fopen command.

also, since ur running this as an anonymous block, you might try forward slashes in your path.

the last thing that comes to mind is some utl_file bugs that i've seen from 7.3 through 8.1: the fopen command barfs on directory permissions sometimes. while this really shouldn't be a factor on windows, on UNIX, the destination directory permission must be 777 (rwx to owner, group, world), otherwise, and append command will fail, and the utl_file.put_line command just pukes.

anyway, not much help, but some ideas to try.

r.

----------------------------------------------------------------------
Re: Executing PL/SQL from a file [message #36929 is a reply to message #36917] Fri, 04 January 2002 04:39 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
I have some questions for you,
1)did you set utl_file_dir parameter in init.ora file?

2) did you restart DB after setting?

3)does c:windowsdesktop exist on DB server machine?

4) are you trying to write to server machine or your (client) machine?

If answers for question..
1) is yes, remove slash after desktop..
change it to c:windowsdesktop in utl_file_dir parameter and while opening file
using utl_file.

2) you have to start db after setting above said parameter in init.ora.

3) path should exist on DB server machine.

4) utl_file reads/writes only files on Database server machine not client machine.
(if your system is standalone system (means db server installed on your system), it's ok)

HTH
Suresh

----------------------------------------------------------------------
Re: Executing PL/SQL from a file [message #36943 is a reply to message #36919] Sun, 06 January 2002 19:02 Go to previous messageGo to next message
Manish
Messages: 79
Registered: December 2000
Member
Thanks, Suresh, for detailed hints. But it doesn't seem to work. First of all, surprisingly, the entry utl_file_dir is *missing* from init.ora file. Anyway, I appended the following entry to init.ora:
utl_file_dir = c:windowsdesktop.
I restarted the database. And in my PL/SQL file too I am referring the file directory by utl_file (as you said). It gives me the following error:
PLS-00226: package 'UTL_FILE' used as variable reference. The file in which I am trying to write does exist on my machine.
Any more hints as to why is this happening? And, yes, I am on a standalone system. If you want I can mail you the code.
Re: Executing PL/SQL from a file [message #36952 is a reply to message #36919] Mon, 07 January 2002 06:24 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
can u send your code to mail address? or post it here
Re: Executing PL/SQL from a file [message #36955 is a reply to message #36919] Mon, 07 January 2002 10:15 Go to previous message
raghav banuru
Messages: 6
Registered: January 2002
Junior Member
write a small plsql block in the file and give some meaningful name and call that file from the sql prompt

Ex

declare
x_tmp varchar2(100);
x_status_out varchar2(1);
x_message_out varchar2(80);
begin
owner.procedurename ;-- if any parameters are there like this (x_status_out,sysdate,x_message_out);
-- dbms_output.put_line('x_tmp is '||to_char(x_tmp));
end;
/
Previous Topic: Use result from function in a Anonymous Block
Next Topic: Modified Date and Time of a Row
Goto Forum:
  


Current Time: Tue Aug 11 17:48:13 CDT 2020