| DBMS_METADATA.GET_DDL Inserts Newlines [message #463739] |
Fri, 02 July 2010 19:40  |
Tjbay
Messages: 3
Registered: July 2010
|
Junior Member |
|
|
I'm running into an issue where DBMS_METADATA.GET_DDL inserts newlines into any trigger body over 2000 characters long. Actually, it does so after every 2000 characters, approximately. It won't do it on the exact 2000th character if it's the middle of a word - it will wait for whitespace.
I tried this in several different clients, including Oracle SQL Developer, Perl, and DB Solo, and they all show the same results. I've created a table from the results and selected from that. I'm pretty sure it isn't the client that is formatting the CLOB incorrectly, but the server itself that is doing it.
Is there an option or setting I need to change to fix this? I've tried with PRETTY on and off, but that seems to make no difference.
Simple repro steps:
create table test_ddl(id number primary key);
declare
l_sql varchar2(32000);
begin
l_sql :=
'create or replace trigger test_ddl_trigger
after insert
on test_ddl
for each row
declare
random_sql varchar2(200);
begin
';
for i in 1..200 loop
l_sql := l_sql || ' random_sql := ''Random statement ' || i || ''';' || chr(10);
end loop;
l_sql := l_sql || 'end;';
execute immediate l_sql;
end;
/
select dbms_metadata.get_ddl('TRIGGER', 'TEST_DDL_TRIGGER', user) from dual;
I get newlines in the middle of statement 51, 102, and 152. The output around them looks like this:
random_sql := 'Random statement 151';
random_sql
:= 'Random statement 152';
random_sql := 'Random statement 153';
|
|
|
|
|
|
|
|
| Re: DBMS_METADATA.GET_DDL Inserts Newlines [message #463762 is a reply to message #463739] |
Sat, 03 July 2010 02:58  |
Tjbay
Messages: 3
Registered: July 2010
|
Junior Member |
|
|
Blackswan:
Unfortunately, this project is stuck with 9i for now. I hope we can upgrade within the year, but I need to decide a way around this bug in the meantime.
Michel:
I mentioned that I tried several, including DB Solo, Oracle SQL Developer, and Perl (DBI). Technically, the script is running from Perl so I'd like to find a solution there. However, I encounter the bug in all of them. I can use "select trigger_body from user_triggers..." instead, which does not have the extra newlines. That leads me to believe that the clients I tried are reading LONG data correctly, and that the issue only affects GET_DDL.
Is "set longchunksize XXXX" actually changing the way the server processes the data? Or only how SQL*Plus formats it?
|
|
|
|
|
|
|
|
| Re: DBMS_METADATA.GET_DDL Inserts Newlines [message #464189 is a reply to message #463789] |
Tue, 06 July 2010 14:05 |
Tjbay
Messages: 3
Registered: July 2010
|
Junior Member |
|
|
I just tested this using SQL*Plus with the following commands:
set long 1000000
set longchunksize 1000000
set lines 10000
set pagesize 0
column DDL format a500
select dbms_metadata.get_ddl('TRIGGER', 'TEST_DDL_TRIGGER', user) DDL from dual;
I get the same result. Newlines are inserted at the same places.
I tried the same trigger in an 11g instance. I get the correct DDL, without extra newlines, in all client programs. Based on this, I'm fairly sure this is either a bug in 9.2.0.8 or some configuration setting on the server that is causing the issue. It is not a problem with the client(s).
I cannot switch this project to an 11g backend while it is mid-production. I will have to generate the DDL for triggers using the columns in all_triggers instead, I think.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
