1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

text Wrapped in output

Discussion in 'SQL PL/SQL' started by hiswapna, Aug 18, 2009.

  1. hiswapna

    hiswapna Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    I am trying to get the DDL of the stored procedures... but the output is having the lines as wrapped

    set trimspool ON
    set heading off;
    --SET linesize 300
    set echo off;
    Set pages 999;
    set long 90000;


    SPOOL C:\proc_name_test'
    SELECT DBMS_METADATA.GET_DDL('PROCEDURE',proc_name_test') FROM DUAL
    /
    SPOOL OFF

    out put is

    CREATE OR REPLACE PROCEDURE proc_name_test(
    abc IN OUT NUMBER,
    fm_gmt IN VARCHAR2,
    )
    AS
    fm_gmt_tms TIMESTAMP(6) := TO_TIMESTAMP(tar_fm_gmt,'DD-MON-YYYY HH24:MI:SS');
    v_th_gmt_tms TIMESTAMP(6) := to_timestamp('31-dec-2009 11:59:59','dd-mon-yyyy
    hh:mi:ss');
    ---etc e
    --etc

    the hh:mi:ss is getting wrapped up and coming in next line....I dont want any lines to get wrapped up in the spool ...how do i fix this
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You've commented the one command that would prevent that from happening:

    --SET linesize 300

    You may need to set the linesize longer than 300 but that is the command that will keep each line of text from breaking with a newline. And text WILL 'wrap' on your screen, but it shouldn't 'wrap' to the next line in your spool file.
     
  3. hiswapna

    hiswapna Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    even if I uncomment it its not working...the final spool file is having a line break
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    make Alias for Get_Ddl function and format the column with high width.


    Code (SQL):
    SET trimspool ON
    SET heading off;
    SET linesize 300
    SET echo off;
    SET pages 999;
    SET long 90000;
    Col DDL format a10000


    SPOOL C:\proc_name_test'
    SELECT DBMS_METADATA.GET_DDL('
    PROCEDURE',proc_name_test') DDL FROM DUAL
    /
    SPOOL OFF
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That doesn't work as DBMS_METADATA.GET_DDL formats the output as a LONG with 80-characters per 'line':

    Code (SQL):
    SQL> COLUMN norge format a2000
    SQL> SELECT dbms_metadata.get_ddl('PACKAGE','LOCK_DEMO') norge FROM dual;

    NORGE
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    --------------------------------------------------

      CREATE OR REPLACE PACKAGE "BING"."LOCK_DEMO" IS
     v_lockname   VARCHAR2(12) := 'control_lock';
     v_lockhandle VARCHAR2(200);
     v_result     PLS_INTEGER;

    -- obtain a lock
    PROCEDURE request_lock(p_ltype INTEGER, p_retval OUT INTEGER);
    -- release an existing lock
    PROCEDURE release_lock(p_retval OUT INTEGER);
    -- view the stored handle

    NORGE
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    --------------------------------------------------
    FUNCTION see_handle RETURN VARCHAR2;
    -- decode lock request
    FUNCTION decode_req(p_result PLS_INTEGER) RETURN VARCHAR2;
    -- decode lock release
    FUNCTION decode_rel(p_result PLS_INTEGER) RETURN VARCHAR2;

    END lock_demo;
    CREATE OR REPLACE PACKAGE BODY "BING"."LOCK_DEMO" IS

    PROCEDURE request_lock(p_ltype IN INTEGER, p_retval OUT INTEGER) IS
    BEGIN

    NORGE
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    --------------------------------------------------
      IF v_lockhandle IS NULL THEN
        dbms_lock.allocate_unique(v_lockname, v_lockhandle);
        p_retval := dbms_lock.request(v_lockhandle, p_ltype);
      END IF;
    END request_lock;
    ------------------------------------------------------------
    PROCEDURE release_lock(p_retval OUT INTEGER) IS
    BEGIN
      IF v_lockhandle IS NOT NULL THEN
        p_retval := dbms_lock.release(v_lockhandle);
      END IF;

    NORGE
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    --------------------------------------------------
    END release_lock;
    ------------------------------------------------------------
    FUNCTION see_handle RETURN VARCHAR2 IS
    BEGIN
      IF v_lockhandle IS NOT NULL THEN
        RETURN v_lockhandle;
      ELSE
        RETURN 'Not Allocated';
      END IF;
    END see_handle;
    ------------------------------------------------------------

    NORGE
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    --------------------------------------------------
    FUNCTION decode_req(p_result PLS_INTEGER) RETURN VARCHAR2 IS
     retval VARCHAR2(20);
    BEGIN
      SELECT DECODE(p_result,0,'Success',1,'Timeout',2,'Deadlock',
      3,'Parameter Error',4,'Already owned',5,'Illegal Lock Handle')
      INTO retval
      FROM dual;

      RETURN retval;
    END decode_req;
    ------------------------------------------------------------

    NORGE
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    --------------------------------------------------
    FUNCTION decode_rel(p_result PLS_INTEGER) RETURN VARCHAR2 IS
     retval VARCHAR2(20);
    BEGIN
      SELECT DECODE(p_result,0,3, 'Parameter Error',4, 'Already owned',
      5, 'Illegal Lock Handle')
      INTO retval
      FROM dual;

      RETURN retval;
    END decode_rel;
    ------------------------------------------------------------

    NORGE
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    --------------------------------------------------
    END lock_demo;



    SQL>
    This is simply how DBMS_METADATA.GET_DDL generates output.
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    But it works ......

    Code (SQL):
    SQL> SELECT DBMS_METADATA.GET_DDL('PROCEDURE','TEST_DBMS_METADATA')
      2  FROM DUAL;

    DBMS_METADATA.GET_DDL('PROCEDURE','TEST_DBMS_METADATA')
    --------------------------------------------------------------------------------

      CREATE OR REPLACE PROCEDURE "SCOTT"."TEST_DBMS_METADATA"
    IS
    BEGIN
    DBMS_OUTPUT.PUT_LINE('You may need to set the linesize longer than 300 but that
    is the command that will keep each line of text from breaking with a newline. An
    d text WILL '
    'wrap'' on your screen, but it shouldn''t ''wrap'' to the next line
     in your spool file'
    );
    END;



    SQL> COLUMN DDL Format a2000
    SQL> SET linesize 2000
    SQL> SELECT DBMS_METADATA.GET_DDL('PROCEDURE','TEST_DBMS_METADATA') DDL
      2  FROM DUAL;

    DDL
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      CREATE OR REPLACE PROCEDURE "SCOTT"."TEST_DBMS_METADATA"
    IS
    BEGIN
    DBMS_OUTPUT.PUT_LINE('You may need to set the linesize longer than 300 but that is the command that will keep each line of text from breaking with a newline. And text WILL ''wrap'' on your screen, but it shouldn''t ''wrap'' to the next line in your spool file');
    END;



    SQL>
     
    You need to combine Set linesize and column formatting at same time.
     
  7. hiswapna

    hiswapna Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    Hello Raj

    I am executing this from SQLPlus client and I am not getting the result.
    The text is still getting wrapped to the next line

    Rgds
    Swapna
     
  8. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    SET LINESIZE , column formatting and DBMS_METADATA.GET_DDL aliasing will do the trick.

    Show us what you tried.
     
  9. hiswapna

    hiswapna Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    I execute the steps

    SET trimspool ON
    SET heading off;
    SET echo off;
    SET pages 999;
    SET long 90000;
    COLUMN DDL Format a2000
    SET linesize 2000

    SPOOL C:\proc_name_test

    SELECT DBMS_METADATA.GET_DDL('PROCEDURE','TEST') DDL FROM DUAL
    /

    SPOOL OFF



    CREATE OR REPLACE PROCEDURE "ODS"."TEST"
    (
    ltdate IN OUT VARCHAR2)
    AS
    v_th_gmt_tms TIMESTAMP(6) := to_timestamp('31-dec-2009 11:59:59','dd-mon-yyyy
    hh:mi:ss');
    BEGIN
    dbms_output.put_line('date is :'|| v_th_gmt_tms);
    END;
     
  10. @nk!t

    @nk!t Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    United States
    Hi..
    Was this resolved ? I am facing a similar issue. While generating the DDL the big tables' DDL is not getting generated entirely but only upto some columns only. Kindly advice.

    Thanks
     
  11. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Prior to your post, this thread was last updated six years ago. If the information you need is not in the post as it currently exists, do you really think the original poster is still following this thread... or will remember the issue at this late date?

    Re-opening threads that have been inactive for more than a couple of months is very bad forum etiquette. The proper form is to create a new post and describe your problem. If it is similar to an older post, then provide a link that refers to the older post. Adding a new problem at the bottom of a thread dealing with someone else's is likely to generate confusion and make it harder for the experts on this forum to assist.