How to create script file with PL/SQL?

Question

I have to update several objects in many instances. In order to achieve that I am using DBMS_METADATA to create the DDL statements.

So far I have the DDL statements and I save them in a TABLE. I would like to save said DDL statements in a .sql file for each object.

I know I can do this with C#, but so far I have been able to achieve everything I needed to do with PL/SQL. Is there a way to create the .sql files with PL/SQL?

Answer 1

That's relatively easy. Using the UTL_FILE package in Oracle you can create a file in the servers filesystem and write the output of any PL/SQL statement to it. You can actually also use it to read data from a file. http://psoug.org/reference/utl_file.html

You might also want to read up a little on the Directory object type as well, since that's a prerequisite for using the UTL_FILE package; http://psoug.org/reference/directories.html

-- Here's an example of a procedure that writes ascii data to an existing Oracle directory object. The user running the code needs to have read/write access to the Oracle Directory object.

CREATE PROCEDURE put_local_ascii_data (p_data IN CLOB,
 p_dir IN VARCHAR2,
 p_file IN VARCHAR2) IS
 -- --------------------------------------------------------------------------
 l_out_file UTL_FILE.file_type;
 l_buffer VARCHAR2(32767);
 l_amount BINARY_INTEGER := 32767;
 l_pos INTEGER := 1;
 l_clob_len INTEGER;
 BEGIN
 l_clob_len := DBMS_LOB.getlength(p_data);
 l_out_file := UTL_FILE.fopen(p_dir, p_file, 'w', 32767);
 WHILE l_pos <= l_clob_len LOOP
 DBMS_LOB.read (p_data, l_amount, l_pos, l_buffer);
 IF g_convert_crlf THEN
 l_buffer := REPLACE(l_buffer, CHR(13), NULL);
 END IF;
 UTL_FILE.put(l_out_file, l_buffer);
 UTL_FILE.fflush(l_out_file);
 l_pos := l_pos + l_amount;
 END LOOP;
 UTL_FILE.fclose(l_out_file);
 EXCEPTION
 WHEN OTHERS THEN
 IF UTL_FILE.is_open(l_out_file) THEN
 UTL_FILE.fclose(l_out_file);
 END IF;
 RAISE;
 END;

The p_data parameter is a CLOB that would ( in this case ) contain all the ascii data being written to the p_dir directory and the p_file name ( which gets created at runtime ). You might need to do some clob conversions on your data, depending on the data types in your query results that you'd use to populate the clob object.

• Dzyann: Can you create files with UTL_FILE, I couldn't find the option for it. Also no matter what I do, it always throws invalid file operation. Do you need special permissions to use UTL_FILE? I could create the directory just fine.
• druzin: You sure can. Check out the stuff I just added to my answer
• Dzyann: druzin, is there available a package that allows you creating files on the client?
• druzin: There's not a specific package to achieve this, but you might find this helpful ; dba-oracle.com/t_oracle:__write_a_file_to_to_a_client_pc.htm
• Dzyann: Spool is a SQL Plus command, that is why I didn't consider it. It seems TOAD supports it with limited syntax.