Generate DDL for any Object using DMS_METADATA Oracle Package

You can get DDL for any object in Oracle Database using the package DBMS_METADATA.get_ddl procedure. Here is an example.

SQL> set serveroutput on;
SQL> DECLARE
  2     dummy                         CLOB;
  3  BEGIN
  4     dummy                      :=
  5                  DBMS_METADATA.get_ddl ('TABLE',        - Object Type
  6                                         'EMP',                        - Table Name
  7                                         'APP_D01'                        - Schema Name
  8                                        );
  9     DBMS_OUTPUT.put_line (DBMS_LOB.SUBSTR (dummy,
 10                                            4000,
 11                                            1
 12                                           ));
 13  END;
 14  /

  CREATE TABLE "APP_D01"."EMP"
   (    "PARAM_NM" VARCHAR2(10) NOT
NULL ENABLE,
        "DESC_TXT" VARCHAR2(50) NOT NULL ENABLE,
        "PARAM_TYP"
VARCHAR2(1) NOT NULL ENABLE,
        "PARAM_VAL" VARCHAR2(80) NOT NULL ENABLE,

CONSTRAINT "EMP_PK" PRIMARY KEY ("PARAM_NM")
  USING INDEX PCTFREE 10
INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576
MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT)
  TABLESPACE "CTS_VSD01"  ENABLE,
         CONSTRAINT
"ATLS_PRM_TYP_CK" CHECK (param_typ in ('V','I','F','B')  ) ENABLE
   ) PCTFREE
10 PCTUSED 70 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536
NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1
FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "CTS_VSD01"


PL/SQL procedure successfully completed.

SQL>


Published with Blogger-droid v1.6.8

Get and Read file names in a directory using SQL - PL/SQL

I got this interesting method to list / access all files in a user specified directory using SQL and PL/SQL. Here is the reference and method

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:439619916584

First you need to grant JAVAUSERPRIV to schema user as sys

GRANT JAVAUSERPRIV to app_owner;


Connect as schema user and execute these scripts


SQL> CREATE GLOBAL TEMPORARY TABLE dir_list
  2       ( filename VARCHAR2(255) )
  3      ON COMMIT DELETE ROWS
  4  /

Table created.

SQL> create or replace
  2          and compile java source named "DirList"
  3      as
  4      import java.io.*;
  5      import java.sql.*;
  6
  7      public class DirList
  8      {
  9      public static void getList(String directory)
 10                        throws SQLException
 11     {
 12         File path = new File( directory );
 13         String[] list = path.list();
 14        String element;
 15
 16        for(int i = 0; i < list.length; i++)
 17         {
 18             element = list[i];
 19             #sql { INSERT INTO DIR_LIST (FILENAME)
 20                    VALUES (:element) };
 21         }
 22     }
 23
 24     }
 25     /

Java created.

SQL> create or replace
  2       procedure get_dir_list( p_directory in varchar2 )
  3    as language java
  4      name 'DirList.getList( java.lang.String )';
  5     /

Procedure created.

SQL> exec get_dir_list('/applns/oracle/dbs');

PL/SQL procedure successfully completed.

SQL> select * from dir_list;

FILENAME
--------------------------------------------------------------------------------

initdw.ora
init.ora
orapwCTSD1
lkCTSD1
hc_CTSD1.dat
spfileCTSD1.ora
orapwMVS01
orapwC9W01
lkC9W01
hc_C9W01.dat
spfileC9W01.ora

FILENAME
--------------------------------------------------------------------------------

lkMVS01
hc_MVS01.dat
spfileMVS01.ora
orapwCWY01
lkCWY01
hc_CWY01.dat
spfileCWY01.ora
orapwCVS01
orapwM9W01
lkM9W01
hc_M9W01.dat

FILENAME
--------------------------------------------------------------------------------

spfileM9W01.ora
lkCVS01
hc_CVS01.dat
spfileCVS01.ora
orapwCCX01
lkCCX01
hc_CCX01.dat
spfileCCX01.ora
lkinstMCSEK
hc_MNH01.dat
initCVS01.ora

FILENAME
--------------------------------------------------------------------------------

hc_MEK01.dat
hc_MCSEK.dat
initCTSD1.ora
test
cts.dmp
lkinstMEK01
hc_MVS02.dat
hc_CCM1.dat
hc_MCM1.dat
initCWY01.ora

43 rows selected.

SQL>