Export DDL to place on server

Question

I want to write SQL procedure which writes file with DDL commands from certain schema to directorium on server. I done that using spool command and save it locally but I need it on server every time I ran it. I am using Oracle RDBMS.

you can create a shell script which executes your procedure and then moves the newly created file to the server where you want to place it — Sandeep, Apr 26 '17 at 10:22
Are you sure you're not re-inventing DataPump with the `content=metadata_only` option? — APC, Apr 26 '17 at 11:04

score 0 · Answer 1 · answered Apr 26 '17 at 12:00

DBMS_METADATA can be your friend. Using its procedure GET_DDL() can give you DDL scripts. You can use UTL_FILE to write it into a "Oracle DB directory" (this is exact Oracle object).

Or you can use an external script to connect to DB, using DBMS_METADATA to get you a DDL.

In these cases you need to script it with selecting USER_OBJECTS (or all/dba objects), etc.

Or you can use 3rd party tools like eg. Oraschemadoc is.

score 0 · Answer 2 · answered Apr 26 '17 at 12:29

This will export ddl of all object in current schema.

CREATE OR REPLACE PROCEDURE ddl_import
    IS
        file_ctl UTL_FILE.FILE_TYPE;
        CURSOR object_cv IS
        SELECT OBJECT_NAME,OBJECT_TYPE FROM USER_OBJECTS;
        code VARCHAR2(10000);
    BEGIN
        FOR cur in object_cv
        LOOP
            file_ctl:=UTL_FILE.FOPEN('ORADIR',cur.object_name||'.sql','W');
            code:=DBMS_METADATA.GET_DDL(CUR.OBJECT_TYPE,CUR.OBJECT_NAME,'ORATUT');
            UTL_FILE.PUTF(file_ctl,code);
            UTL_FILE.FCLOSE(file_ctl);
        END LOOP;
    END ddl_import;

But before you compile and execute this procedure make sure you have done following things in step.

STEP1 : Create directory in host(OS) environment and grant READ/WRITE to oracle user.

STEP2: Execute CREATE OR REPLACE DIRECTORY ORADIR AS ‘D:/dir_name’; dir_name is name of the directory which you have created in step1.

STEP3: Ask your DBA to grant you execute permission on UTL_FILE. Or if you have credentials of DBA then execute following- GRANT EXECUTE ON UTL_FILE TO user_name; user_name will be of course you.

score 0 · Answer 3 · answered Apr 26 '17 at 14:27

1) Here is description how to extract ddl for schema. Extract whole schema

We have to change

-- add 
dest_clob clob; 
-- change
    DBMS_OUTPUT.PUT_LINE (DDL); 
    to 
    DBMS_LOB.APPEND (dest_clob, DDL);

at the end

DBMS_XSLPROCESSOR.CLOB2FILE(dest_clob,dest_directory, my_schema.sql)

dest_directory is Oracle Directory with read/write privileges

Export DDL to place on server

3 Answers3