| Executing PL/SQL from a file [message #36917] |
Thu, 03 January 2002 20:53  |
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 #36927 is a reply to message #36917] |
Fri, 04 January 2002 04:24  |
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 |
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 |
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 #36955 is a reply to message #36919] |
Mon, 07 January 2002 10:15 |
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;
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
