Home » SQL & PL/SQL » SQL & PL/SQL » Read config values from sql script
Read config values from sql script [message #314742] Thu, 17 April 2008 12:30 Go to next message
McLan
Messages: 36
Registered: April 2008
Member
I have configuration file named product.cnf
That contains information such as below:
No of Employees: 3000
No. of Retired Employees: 2000
Application name : read_conf.sql

In the read_conf.sql script , is it possible to read configuration values from product.cnf?

How can I do that?
Cheers,
McLan
Re: Read config values from sql script [message #314746 is a reply to message #314742] Thu, 17 April 2008 12:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, use utl_file.

Regards
Michel
Re: Read config values from sql script [message #314784 is a reply to message #314746] Thu, 17 April 2008 14:54 Go to previous messageGo to next message
McLan
Messages: 36
Registered: April 2008
Member
I have sql script as below:
set serveroutput on size 1000000;
create or replace directory base_dir as '/u01/src/user1/LT/sql';
grant read on directory base_dir to public;
declare
input_file utl_file.file_type;
input_buffer varchar2(4000);
begin
input_file := utl_file.fopen ('base_dir','product.cnf', 'R');
utl_file.get_line (input_file, input_buffer);
dbms_output.put_line(input_buffer);
utl_file.fclose(input_file);
end;
/

when I run the script, I get below error:
SQL> @rpvt.sql

Directory created.


Grant succeeded.

declare
*
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 33
ORA-06512: at "SYS.UTL_FILE", line 436
ORA-06512: at line 5

Any Idea whats the wrong?
Re: Read config values from sql script [message #314809 is a reply to message #314784] Thu, 17 April 2008 17:28 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
Base_dir needs to be in upper case when passed to utl_file.fopen, because by default it is created in upper case, so you reference it in upper case.

input_file := utl_file.fopen ('BASE_DIR','product.cnf', 'R');

[Updated on: Thu, 17 April 2008 17:28]

Report message to a moderator

Re: Read config values from sql script [message #314841 is a reply to message #314784] Fri, 18 April 2008 00:35 Go to previous message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Also 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) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Previous Topic: dynamically create table
Next Topic: Oracle error
Goto Forum:
  


Current Time: Mon Dec 05 02:49:23 CST 2016

Total time taken to generate the page: 0.13840 seconds