Home » SQL & PL/SQL » SQL & PL/SQL » Executing PL/SQL code from a file
Executing PL/SQL code from a file [message #18] Thu, 03 January 2002 19:25 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 @<filename>. 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 code from a file [message #22 is a reply to message #18] Thu, 03 January 2002 23:19 Go to previous messageGo to next message
j.smith
Messages: 21
Registered: December 2001
Junior Member
Let say all your sql files are located in a directory called: c:blah

and every time you type: @filename.sql you want it to automatically run the file. Insteading of having to type: @ c:blahfilename.sql

this is what you do in windows 2k pro:
right click on the SQL Plus shortcut / choose properties .

The target should point to the SQLPLUSW.EXE file.
Now change the start in field: to c:blah or what ever dirctory your sql files are stored in.
click ok. and open up sql* plus.

now evertime you run a file in the c:blah directory, all you need to type is @filename.sql or @filename.what_ever_extension ....

keep in mind, the changes you made is only for this shortcut, and does not change the setting of any other shortcut.. so each sql*plus shortcut you will need to make these changes seperatly.

----------------------------------------------------------------------
Re: Executing PL/SQL code from a file [message #25 is a reply to message #22] Thu, 03 January 2002 23:45 Go to previous messageGo to next message
Manish
Messages: 79
Registered: December 2000
Member
Thanks. It works. I have one more doubt. In the following code, an exception (User-Defined Exception) is being raised. Why?

DECLARE
err_msg VARCHAR2(100);
filehandler UTL_FILE.FILE_TYPE;
BEGIN
filehandler := UTL_FILE.FOPEN('C:windowsdesktop','Oracle_error_msgs','w');
FOR err_num IN 1..9999 LOOP
err_msg := SQLERRM(-err_num);
UTL_FILE.PUTF(filehandler, err_msg);
END LOOP;
UTL_FILE.FCLOSE(filehandler);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('....Exception....');
dbms_output.put_line(SQLERRM);
END;
/

Can I give path of the file like this: 'C:windowsdesktop'?

----------------------------------------------------------------------
Re: Executing PL/SQL code from a file [message #329 is a reply to message #25] Tue, 05 February 2002 17:23 Go to previous message
Abul Fazal
Messages: 39
Registered: February 2002
Member
The Basic Parts of a PL/SQL Program are
1. Declaration ( Optional)
2. Execution
3. Exception Handling (Optional)
Exception Handling deals with any exception (or Errors) that might arise, b'cos nobody in this world is an accurate programmer. Whenever an error arises the program flow immediately passes to the exception part of the program. Most of the Books can give u an intro to Exceptions.
UTL_FILE can accept directory path as an input file as long as the directory exists & has the neccessary permissions.
Previous Topic: Output sql in a loop
Next Topic: Where not exists
Goto Forum:
  


Current Time: Sun Sep 19 03:21:35 CDT 2021