You Asked

Hi,

I wanted to get the DDL for all objects in a database schema. I'm aware of DBMS_METADATA.GET_DDL to get the DDL from a PL/SQL block but was facing the bellow issue:

The return type is HUGECLOB and I need it in a directly viewable form similar to VARCHAR fields. The solution that I found was using Substr of 4000 bytes, however I noticed that there are scripts that are around 160K bytes in size and these get trimmed. I have tried concatenating byte parts but I always end up getting errors such as the character size is beyond the limit.

Is there any possible solution for this issue?

Thanks in advance!

and Connor said...

Not sure what you mean by "directly viewable form similar to VARCHAR fields"

If the DDL is more than 4k, then you need to have an element on screen that is more than 4k to show it.

The alternative would be to send it back in smaller chunks (see below) - but still you'd need to consolidate that on screen to show it in a meangingful manner.

SQL> CREATE OR REPLACE
  2  function clob_to_varchar2(p_clob  IN  clob) return sys.odcivarchar2list pipelined AS
  3    l_text           VARCHAR2(32767);
  4    l_len            int := dbms_lob.getlength(p_clob);
  5    l_chunk          int := 90;
  6  BEGIN
  7    for i in 0 .. trunc(l_len/l_chunk)
  8    loop
  9      if i < trunc(l_len/l_chunk)
 10      then
 11        l_text := dbms_lob.substr(p_clob,l_chunk,i*l_chunk+1);
 12      else
 13        l_text := dbms_lob.substr(p_clob,mod(l_len,l_chunk),i*l_chunk+1);
 14      end if;
 15      pipe row ( l_text);
 16    end loop;
 17    return;
 18  END;
 19  /

Function created.

SQL>
SQL> select * from table(clob_to_varchar2('Hello There.'));

COLUMN_VALUE
----------------------------------------------------------------------------------------------------------------------------------
Hello There.

1 row selected.

SQL>
SQL> variable c clob
SQL> exec :c := rpad('x',4000,'x');

PL/SQL procedure successfully completed.

SQL> exec :c := :c || rpad('x',4000,'x');

PL/SQL procedure successfully completed.

SQL> exec :c := :c || rpad('y',4000,'y');

PL/SQL procedure successfully completed.

SQL> exec :c := :c || 'lastbits.'

PL/SQL procedure successfully completed.

SQL>
SQL> select * from table(clob_to_varchar2(:c));

COLUMN_VALUE
----------------------------------------------------------------------------------------------------------------------------------
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
...
yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
yyyyyyyyyyyyyyyyyyyyyyyyyyyyyylastbits.

134 rows selected.

SQL>

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thomas, July 18, 2018 - 11:33 am UTC

Thomas, July 18, 2018 - 11:40 am UTC

My requirement is that I would like to access the DDL for objects in another database using database links. Since CLOB fields cannot be accessed over DB link, I'm stuck.
The solution suggested by you provides an output in the form of tabular values which when used encloses the DDL in '()' and has a lot of ',,,,,' if the script is small.

Is there any other cleaner approach to transport CLOB field contents over db link even if byte sizes are around 160K?

July 20, 2018 - 10:31 am UTC

"My requirement is that I would like to access the DDL for objects in another database using database links"

Well... that's what we call a brand new requirement because you never specified that in the original question... I didnt have my crystal ball with me today :-)

If you want bring a clob across the link (before 12.2) you can:

- create a global temp table
- insert into GTT select clob_col from source@dblink

and then use the clob locally from the GTT

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database

Breadcrumb

Question and Answer