Home » SQL & PL/SQL » Client Tools » DBMS_METADATA.GET_DDL Inserts Newlines (Oracle 9.2.0.8)
DBMS_METADATA.GET_DDL Inserts Newlines [message #463739] Fri, 02 July 2010 19:40 Go to next message
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 #463741 is a reply to message #463739] Fri, 02 July 2010 19:56 Go to previous messageGo to next message
BlackSwan
Messages: 26606
Registered: January 2009
Location: SoCal
Senior Member
I see you are running on obsoleted V9.
Does behavior change on newer, supported versions?
If you post a test case, we could trying it locally & report back.
Just a suggestion & feel free to ignore if so inclined.
Re: DBMS_METADATA.GET_DDL Inserts Newlines [message #463748 is a reply to message #463741] Sat, 03 July 2010 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 66648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Always post your client program. For SQL*PLus:

set long 1000000000
set longchunksize 1000000000


Regards
Michel

[Updated on: Sat, 03 July 2010 01:25]

Report message to a moderator

Re: DBMS_METADATA.GET_DDL Inserts Newlines [message #463762 is a reply to message #463739] Sat, 03 July 2010 02:58 Go to previous messageGo to next message
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 #463763 is a reply to message #463762] Sat, 03 July 2010 03:16 Go to previous messageGo to next message
Michel Cadot
Messages: 66648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is "set longchunksize XXXX" actually changing the way the server processes the data? Or only how SQL*Plus formats it?

Both (assuming server means server process dedicated to the session).

You have the same kind of parameter in Perl but in Perl your program chooses how to display it.

Regards
Michel
Re: DBMS_METADATA.GET_DDL Inserts Newlines [message #463789 is a reply to message #463763] Sat, 03 July 2010 08:57 Go to previous messageGo to next message
BlackSwan
Messages: 26606
Registered: January 2009
Location: SoCal
Senior Member
>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.
If in fact the problem is within the server, then my thinking changing the DB might make a difference.
In the past I have utilized an unsanctioned version to make my life easier.
Sometimes it is better to ask forgiveness, than permission.
Re: DBMS_METADATA.GET_DDL Inserts Newlines [message #464189 is a reply to message #463789] Tue, 06 July 2010 14:05 Go to previous messageGo to next message
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.
Re: DBMS_METADATA.GET_DDL Inserts Newlines [message #464192 is a reply to message #464189] Tue, 06 July 2010 15:18 Go to previous message
Mahesh Rajendran
Messages: 10690
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
The case is not reproducible in 10g and above.
Don't have a 9.2.0.8 in vicinity.
Previous Topic: Installing Toad on Ubuntu 8.04
Next Topic: Does Toad 8.5 work with Oracle 10gr database?
Goto Forum:
  


Current Time: Mon Oct 21 05:11:40 CDT 2019