Hi Tom,

When new scripts are created by our engineering teams, I would like to store the content of those scripts in the database in a table, execute the script, capture the output, and store the output in the database as well.

Here's what I have so far:

SQL> create or replace directory SCRIPT_FILES as '/export/home/oracle/dbasql/TEST/scripts';

Directory created.

SQL> CREATE TABLE SCRIPT_CONTAINER
2 (
3 SCRIPT_NAME VARCHAR2(500) NOT NULL,
4 SCRIPT_CONTENT CLOB NOT NULL,
5 SCRIPT_OUTPUT CLOB,
6 RELEASE_VERSION VARCHAR2(8) NOT NULL,
7 SCRIPT_APPLIED VARCHAR2(1) NOT NULL,
8 RUN_DATE DATE NOT NULL,
9 RAN_BY VARCHAR2(70) NOT NULL
10 )
11 LOGGING
12 NOCOMPRESS
13 NOCACHE
14 NOPARALLEL
15 NOMONITORING;

Table created.

SQL> CREATE OR REPLACE procedure load_script_to_db(p_filename in varchar2)
2 as
3 l_clob clob;
4 l_bfile bfile;
5 begin
6 insert into script_container (script_name,script_content,release_version,script_applied,run_date,ran_by) val
ues ( p_filename,empty_clob(),'1','N',sysdate,'BLUCAS')
7 returning script_content into l_clob;
8 l_bfile := bfilename( 'SCRIPT_FILES', p_filename );
9 dbms_lob.fileopen( l_bfile );
10 dbms_lob.loadfromfile( l_clob, l_bfile, dbms_lob.getlength(l_bfile));
11 dbms_lob.fileclose( l_bfile );
12 end;
13 /

Procedure created.

SQL> exec load_script_to_db('test.sql');

PL/SQL procedure successfully completed.

SQL> set long 50000 pages 1000
SQL> select * from script_container;

SCRIPT_NAME
--------------------------------------------------------------------------------
SCRIPT_CONTENT
--------------------------------------------------------------------------------
SCRIPT_OUTPUT
--------------------------------------------------------------------------------
RELEASE_ S RUN_DATE
-------- - ---------
RAN_BY
----------------------------------------------------------------------
test.sql
--garbage test script
create table my_objects_db as select * from user_objects;
select count(1) from my_objects_db;
update my_objects_db set object_name=object_name where 1=0;
commit;
exit;


1 N 06-JUN-06
BLUCAS


SQL>


Thanks for the procedure to actually load the data from the text file into the CLOB column. I found that in one of your other posts. What I am thinking about is the following process:

1. Scripts delivered to directory.
2. Scripts loaded from directory into table flagged SCRIPT_APPLIED as 'N'.
3. Script then ran. I could use a simple "@" directive but what about using a job to run the PL/SQL as stored in the SCRIPT_CONTENT column?
4. Capture the output of the script even if no direct "spool on/off" commands are in the script.
5. Make the record sticky (cannot update/delete).

So, seeing I'm at steps 1 and 2, okay, I am looking for guidance on going the next few.

Thanks, Tom. I'm quite surprised I can post a question and I appreciate the amazing work you've done for the Oracle community.

Brian