| Executing GET_DDL output in SQLPLUS [message #377561] |
Tue, 23 December 2008 08:56  |
 |
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 .
Rajuvan
[Updated on: Tue, 23 December 2008 09:00] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
