0

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?

1 Answer 1

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.

6
  • 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.
    – Dzyann
    Jan 8, 2015 at 20:56
  • You sure can. Check out the stuff I just added to my answer
    – druzin
    Jan 9, 2015 at 11:15
  • druzin, is there available a package that allows you creating files on the client?
    – Dzyann
    Jan 9, 2015 at 15:42
  • 1
    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
    – druzin
    Jan 9, 2015 at 17:30
  • 1
    Spool is a SQL Plus command, that is why I didn't consider it. It seems TOAD supports it with limited syntax.
    – Dzyann
    Jan 9, 2015 at 17:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.