CODEWITHSUNDEEP
sqloracle-databaseplsqlprocedure
user3790692
user3790692

Reputation: 72

Oracle PL/SQL Procedure Table Code

I've a table users on my oracle database:

|------|--------------|-------------------------|----------------|
|  ID  |     NAME     |          EMAIL          |    BIRTHDAY    |
|------|--------------|-------------------------|----------------|
|  1   |     Joe      |       [email protected]      |   1986-10-13   |
|------|--------------|-------------------------|----------------|
|  2   |    Julie     |     [email protected]      |   1990-05-07   |
|------|--------------|-------------------------|----------------|
|  3   |     Dan      |       [email protected]      |   1988-10-20   |
|------|--------------|-------------------------|----------------|

How to create the procedure pr_export() that will return:

CREATE TABLE USERS(ID NUMBER, NAME VARCHAR2(50), EMAIL VARCHAR2(50), BIRTHDAY DATE,  CONSTRAINT users_pk PRIMARY KEY (id));

INSERT INTO USERS VALUES (1, 'Joe', '[email protected]', to_date(1986-10-13, 'YYYY-MM-DD'));
INSERT INTO USERS VALUES (2, 'Julie', '[email protected]', to_date(1990-05-07, 'YYYY-MM-DD')); 
INSERT INTO USERS VALUES (3, 'Dan', '[email protected]', to_date(1988-10-20, 'YYYY-MM-DD'));

I read a tip that I can use like: 

select * from user_tab_cols where table_name = 'USERS';

and cursors but I have no idea how to create the procedure.

Thank you!!!

Upvotes: 0

Views: 195

Answers (1)

wolφi
wolφi

Reputation: 8361

The first half of your procedure pr_export() could use the DBMS_METADATA-Interface:

SELECT dbms_metadata.get_ddl('TABLE','USERS') FROM dual;

The output will look like:

CREATE TABLE "USERS" ("ID" NUMBER, "NAME" VARCHAR2(50 CHAR), "EMAIL" VARCHAR2(50 CHAR), "BIRTHDAY" DATE)

You can tweak the output with parameters like:

dbms_metadata.set_transform_param (dbms_metadata.session_transform,'STORAGE',false);
dbms_metadata.set_transform_param (dbms_metadata.session_transform,'TABLESPACE',false);
dbms_metadata.set_transform_param (dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES', false);
dbms_metadata.set_transform_param (dbms_metadata.session_transform,'REF_CONSTRAINTS', FALSE);
dbms_metadata.set_transform_param (dbms_metadata.session_transform,'CONSTRAINTS', FALSE);
dbms_metadata.set_transform_param (dbms_metadata.session_transform,'PRETTY',FALSE);
dbms_metadata.set_transform_param (dbms_metadata.session_transform,'SQLTERMINATOR',TRUE);

More info on: http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_metada.htm

To use dbms_metadata.set_transform_param outside of a procedure, just surround it with an anonymous block:

BEGIN
  dbms_metadata.set_transform_param(...);
END;
/

Upvotes: 1

Related Questions