Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Print a big file in DBMS_OUTPUT.PUT_LINE

LuKKaFeb 12 2019 — edited Feb 12 2019

Hello,

I am using Oracle 12c.

I have a scenario where I need to print one package content (Create package*****) in to a file by using SPOOL.

I am using DBMS_METADATA.GET_DDL to get the script from DB.

But problem is when I am trying to print it is showing error like :

6502-ORA-06502: PL/SQL: numeric or value error

Problem is I think in DBMS OUTPUT we can not print a big file.

Can you please suggest me how can I print this in a SPOOL file?

PLSQL Block:

SPOOL "C:\Spool\ddl.sql"

DECLARE

   v_ddl CLOB;

BEGIN

SELECT DBMS_METADATA.GET_DDL(REPLACE(object_type,' ','_'), object_name, owner) object_metadata into v_ddl

  FROM all_objects

WHERE object_name = 'TEST_PKG';

DBMS_OUTPUT.PUT_LINE(v_ddl);

END;

SPOOL OFF;

Comments
Post Details
Added on Feb 12 2019
5 comments
698 views