Home » SQL & PL/SQL » Client Tools » Executing GET_DDL output in SQLPLUS
Executing GET_DDL output in SQLPLUS [message #377561] Tue, 23 December 2008 08:56 Go to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

We are in process of getting the Oracle Object DDL using DBMS_METADATA.GET_DDL and executing in SQLPLUS for schema optimization process. DDL is extracted with following session setting.

Quote:

dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'PRETTY', TRUE)
dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', TRUE )
dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', TRUE)
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, 'FORCE', TRUE )
dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'CONSTRAINTS', TRUE)
dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'REF_CONSTRAINTS', FALSE)
dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'CONSTRAINTS_AS_ALTER', TRUE)
dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SIZE_BYTE_KEYWORD', TRUE )





We are getting three types of space related issues during DDL genaration . And because of these , these DDL scripts are not working well with SQLPLUS . ( sometimes works good with Toad)

1. SQLTERMINATOR ( ; ) along with ')' is coming after additional space for table's DDL Probably after comment like instruction.

like

CREATE TABLE T1 
( f1 varchar2(10),
  f2 varchar2(20) --> f2 is field 2 of table T1

 );


SQL*PLUS creates issue while executing it.

2. SQLTERMINATOR (/)is coming along the same line of 'END;'

CREATE OR REPLACE PROCEDURE P1 
AS
BEGIN
 NULL;
END;/


Here '/' Come in the same line of 'END;' . So while executing in SQLPLUS its not created properly.

3. Unnecessary space for VIEW DDL . ( eg between UNION )

Create Force View V1 as
Select col1,col2 from  t1 

union

Select col1,col2 from  t2;


This seems to create error in SQLPLUS but works well with TOAD. Is there any setting to restrict it ?

Issues 2 and 3 seems to create more issues as of now .

Any suggestion how to tackle?
Even wants to know whether there are any settings in SQLPLUS to rectify it .

Smile
Rajuvan

[Updated on: Tue, 23 December 2008 09:00]

Report message to a moderator

Re: Executing GET_DDL output in SQLPLUS [message #377568 is a reply to message #377561] Tue, 23 December 2008 09:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is your Oracle version with 4 decimals?

What you posted is the original creation statements or the ones you get? In the latter, post the original ones.

Regards
Michel
Re: Executing GET_DDL output in SQLPLUS [message #377575 is a reply to message #377561] Tue, 23 December 2008 10:10 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Thanks for the response .

Oracle version is 10.2.0.1.0.

I am helpless to post the original code as

1. It is the Test database with original application structure
2. The Procedures are too long to read.

I hope posted create scripts describes the problem in detail.
Forgot to mention . These problems come only for some specific objects (200 out of 4k+ objects) . And even not able to reproduce .

Smile
Rajuvan.

Re: Executing GET_DDL output in SQLPLUS [message #377577 is a reply to message #377575] Tue, 23 December 2008 10:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I will make the standard answer: upgrade to 10.2.0.4. Smile

Regards
Michel
Re: Executing GET_DDL output in SQLPLUS [message #377701 is a reply to message #377561] Wed, 24 December 2008 06:59 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


@Micheal ,

Well, Are you pretty sure this issue will be solved in 10.2.0.4 ?
If so , this is a bug in early versions.

Anyway I am trying to rectufy it by using REPLACE .
Seems satisfactory as of now.

Smile
Rajuvan

Re: Executing GET_DDL output in SQLPLUS [message #377703 is a reply to message #377701] Wed, 24 December 2008 07:09 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Well, Are you pretty sure this issue will be solved in 10.2.0.4 ?

Absolutely not but as you can't reproduce it this is safer way.

Regards
Michel
Previous Topic: Drop Procedure
Next Topic: Connect to Oracle 9i database using PL/SQL Developer
Goto Forum:
  


Current Time: Thu Dec 08 22:26:49 CST 2016

Total time taken to generate the page: 0.07645 seconds