Stored Procedures

Nakuru1234
Nakuru1234 used Ask the Experts™
on
What is the best way to configure files into a database? For example, I have data in tables. I want to use that data to populate the configuration files for GTU database. Can I use a stored procedure to write to some flat files using the records in the table? If yes, how can I do that?

TIA,
N
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
johnsoneSenior Oracle DBA
Commented:
Use the UTL_FILE package.  The documentation is here:

http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/u_file.htm#BABGGEDF

Be aware that it can only write to file systems accessible to the database server.  If it is not mounted on that server it cannot be written to.  However, you can use other UTL packages to move files via HTTP or FTP or other transports to get it to its final destination.
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Personally, I would use sqlplus and spool out the data.  This way does not have the restriction of having to write to the databsae srever nor does it have the possible complexity of writing PL/SQL.

Author

Commented:
I understand. I will try that method. So by any means, we'd rather have unix scripts rather than stored procs for writing flat files?

TIA,
N
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>we'd rather have unix scripts rather than stored procs for writing flat files?

Stored procedures will keep everything inside the database.  It can also offer more flexibility since it is procedural code not a simple script.

That said, you can generate some pretty complex reports/formats using sqlplus.  I would not write sqlplus off because of a perceived limitation.
Senior Oracle DBA
Commented:
Sorry, I would not write off SQL*Plus either.  I have certainly used it for writing out and gathering information.

My apparent misperception is that the question was asked in the form of doing this within a stored procedure.  I thought there was more going on that simply writing out files.  If it is strict select then definitely go with SQL*Plus.

If there is more processing than a simple select involved, then I would use either a pure stored procedure approach, or a stored procedure that wrote to a global temporary table and then select from that spooling with SQL*Plus.

There is always more than one way to do everything.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial