| Executing PL/SQL code from a file [message #18] |
Thu, 03 January 2002 19:25  |
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  |
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 |
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
