How to create script file with PL/SQL?

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply