Solved

workaround for dbms_output

Posted on 2014-02-09
18
761 Views
Last Modified: 2014-02-13
I have a code generator which prints a package specification and package body as dbms output. I am hitting the limitation as the number of lines for package body is exceeding the limitation of dbms output. I have two variables in my procedures v_pkg_spec and v_pkg_body which I need to print nicely without facing the limitation. Please let me know what is the best possible way. I dont want to use UTL file as I dont have permissions to log into the directory to fetch the file.

Thanks
0
Comment
Question by:gs79
  • 8
  • 8
  • 2
18 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 39846220
what version are you using?
with any recent version you should be able to print unlimited amount.
but only 32K per line.
0
 

Author Comment

by:gs79
ID: 39846280
I am using 11g r2..

Little bit backround about code. I have a procedure within which I am reading a table and generating a package. The table contains list of database table names. For each table name in this table, the package adds bunch of lines. I am able to generate the package when the table contains list of 34 tables. When I add one more table, its giving that limitation error. The overall lines of the package specification and package body printed in dbms outbput is not exceeding more than 500 lines. Not sure why I hit the limitation so soon. Is there any other way to print what I am trying to print via dbms output

Thanks
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39846296
Did you set the dbms_output buffer to unlimited (NULL)  ?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39846297
to set unlimited output....


 using sqlplus
set serveroutput size unlimited 

Open in new window


 using dbms_output package
dbms_output.enable(null);

Open in new window

0
 

Author Comment

by:gs79
ID: 39846537
I am printing this in a toad session. Let me try running in sql plus

Thanks
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39846545
In more recent versions of Toad it should allow for unlimited buffer size.

If you have an older version you may need to override it by calling dbms_output.enable yourself.

begin
dbms_output.enable(null);
end;


then run your script as normal

or, embed the dbms_output.enable(null);  into your script before you do any other dbms_output calls
0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 100 total points
ID: 39847054
I have a question as to how you are calling DBMS_OUTPUT.  There is a "line" limit to DBMS_OUTPUT of 32K (doc reference here -> http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/d_output.htm#ARPLS67306).  Are you storing up the full package body into a variable and then calling DBMS_OUTPUT.PUT_LINE one time?  If so, that is probably the limit that you are hitting.  If this is the case, call DBMS_OUTPUT.PUT_LINE more often.
0
 

Author Comment

by:gs79
ID: 39847854
Thank you for your responses.
Johnsone as you asked, I am storing full package into a variable and then printing.
After I executed the dbms_output enable proc as suggested by sdstuber, I was able to add few more lines to the package code and the "character buffer too small error went away. Now I am encountering ORA-06512: Numeric or value error. Could this be because of the line limitation as explained by johnsone. If I remove one entry from the table, it generates the package code with few lines lesser and it works fine. When I add an entry, maybe the generator is adding few lines more and the variable that I am printing is exceeding the max limit which is 32767?
I will try to print more often and see if it resolve the issue. please let me know if there is any other methods.

Thanks
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39847896
>>> Now I am encountering ORA-06512: Numeric or value error. Could this be because of the line limitation as explained by johnsone.

Yes, that is it.  I noted that in the very first post.  You can have an unlimited number of output, but no single string can be more than 32K

Change how you extract the code.  If you're reading from dba_source/all_source/user_source you should be able to pull individual lines
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:gs79
ID: 39847962
Well the variable to which I am assigning the package definition script is declared as a clob. Not sure why I am encountering 32 KB limitation. Do I need to split the clob and process to print it in chunks?

Please advice

Thanks
0
 

Author Comment

by:gs79
ID: 39848738
I am able to break the clob into multiple set using the below logic. But as I exceed 32 KB and start printing from next line, there is a discontinuity in the code since some part of the last line goes into the next line. I am using the following logic to break the clob:

        while v_ind <= v_len
        loop
        dbms_output.put_line(dbms_lob.substr(l_pkg_body,32000,v_ind));
        v_ind := v_ind + 32000;
        end loop;

my below code as an example which should appear like this:

---
--
select col_a, col_b into var_a, var_b
from tab_a
..
..

Will appear as follows:

select col_a, co
l_b into var_a, var_b
from tab_a

This will result in syntax error when I take the package code and compile and then I have to manually traverse through the code and correct it. Is there a way to make sure when I break the variable after 32 KB, I also make sure it prints the whole of last line.

The problem with the design is that I am trying to print the clob after building it fully rather than printing it in between. To do the later I have to do lot of code changes.

Thanks
0
 
LVL 34

Expert Comment

by:johnsone
ID: 39849868
Use DBMS_OUTPUT.PUT instead of DBMS_OUTPUT.PUT_LINE.  The PUT_LINE procedure adds the carriage return, PUT does not do that.
0
 

Author Comment

by:gs79
ID: 39850609
I tried with put. I think it tries to print everything in one line? I get the following error:

ORA-20000: ORU-10028: line length overflow, limit of 32767 per line

Is there a way to print the clob variable to file once the clob is constructed without compromising on carriage returns and chr(10) etc..

Thanks
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39850630
no

one line of dbms_output can not be longer than 32767 characters.  It doesn't matter if you write it with put or put_line.  

Internally, the dbms_output buffer is just a collection of varchar2(32767)
so that's a hard limit regardless of the mechanism you use to populate it.


In your code generator, I assume you're reading dba_source, correct?
Then, your data will only have 4000 character lines because that's all the dictionary view exposes.

So, simply don't construct the clob.  Instead, just dump the lines from the source directly.


If you're using dbms_metadata to generate the clob for you, then you'll have to parse the clob yourself to get it into lines of printable length
0
 

Author Comment

by:gs79
ID: 39850765
@sdstuber..I am not reading from dba source but reading from a custom table which contains table names and comma seperated column names per record. From this record with in this table, my code generator is generating insert and merge statements to read from source and load into target in different database with same table structures. As a result when I add more and more tables into the table, the package code generated by code generator is exceeding the limit. I need to do lot of restructuring to print the lines at regular intervals.

The below method almost works. Only that I have to fix the package the code as certain lines in the package move to the next line resulting in syntax error. Is there a way to alter the below to also check for end of the line and < 32000 before printing in the next line:

while v_ind <= v_len
        loop
        dbms_output.put_line(dbms_lob.substr(l_pkg_body,32000,v_ind));
        v_ind := v_ind + 32000;
        end loop;

THanks
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 400 total points
ID: 39850820
create this type and this function

CREATE OR REPLACE TYPE VCARRAY AS TABLE OF VARCHAR2(4000);


CREATE OR REPLACE FUNCTION split_clob(p_clob        IN CLOB,
                                          p_delimiter   IN VARCHAR2 DEFAULT CHR(13) || CHR(10)
                                         )
    RETURN vcarray
    PIPELINED
IS
    c_chunk_limit   CONSTANT INTEGER := 32767;
    v_clob_length            INTEGER := DBMS_LOB.getlength(p_clob);
    v_clob_index             INTEGER;
    v_chunk                  VARCHAR2(32767);
    v_chunk_end              INTEGER;
    v_chunk_length           INTEGER;
    v_chunk_index            INTEGER;
    v_delim_len              INTEGER := LENGTH(p_delimiter);
    v_line_end               INTEGER;
BEGIN
    v_clob_length  := DBMS_LOB.getlength(p_clob);
    v_clob_index   := 1;

    WHILE v_clob_index <= v_clob_length
    LOOP
        /*
            Pull one 32K chunk off the clob at a time.
            This is because it's MUCH faster (approx 25 times faster) to use built in functions
            on a varchar2 type than to use dbms_lob functions on a clob.
            To make sure we don't break a line or field at the 32K limit, go back 4000 characters
            from the end of the chunk to find delimiters.
            Use that delimiter as the end of the chunk so we split on clean lines/fields
        */
        v_chunk         := DBMS_LOB.SUBSTR(p_clob, c_chunk_limit, v_clob_index);

        IF v_clob_index > v_clob_length - c_chunk_limit
        THEN
            -- if we walked off the end the clob,
            -- then the chunk is whatever we picked up at the end
            -- delimited or not
            v_clob_index  := v_clob_length + 1;
        ELSE
            v_chunk_end   := INSTR(v_chunk, p_delimiter, -1);

            IF v_chunk_end = 0
            THEN
                DBMS_OUTPUT.put_line('No delimiters found!');
                RETURN;
            END IF;

            v_chunk       := SUBSTR(v_chunk, 1, v_chunk_end);
            v_clob_index  := v_clob_index + v_chunk_end + v_delim_len - 1;
        END IF;

        /*
            Given a varchar2 chunk split it into lines
        */

        v_chunk_index   := 1;
        v_chunk_length  := NVL(LENGTH(v_chunk), 0);

        WHILE v_chunk_index <= v_chunk_length
        LOOP
            v_line_end  := INSTR(v_chunk, p_delimiter, v_chunk_index);

            IF v_line_end = 0 OR (v_line_end - v_chunk_index) > 4000
            THEN
                PIPE ROW (SUBSTR(v_chunk, v_chunk_index, 4000));
                v_chunk_index  := v_chunk_index + 4000;
            ELSE
                PIPE ROW (SUBSTR(v_chunk, v_chunk_index, v_line_end - v_chunk_index));
                v_chunk_index  := v_line_end + v_delim_len;
            END IF;
        END LOOP;
    END LOOP;

    RETURN;
EXCEPTION
    WHEN no_data_needed
    THEN
        NULL;
END split_clob;
/

Open in new window


then, you can iterate through your clob in lines of 4000 characters at a time


for t in (select column_value text from table(split_clob(l_pkg_body)))
loop
      dbms_output.put_line(t.text);
end loop;


I'm making the assumption that your text is delimited by chr(13) || chr(10)

If that's not the case, then either change the default in the function, or pass the real delimiter as the 2nd parameter to the split_clob function
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39850825
I already had the split_clob function handy.

Alternatively, you could rip the clob parsing code out of it and use it as your text looping.
0
 

Author Comment

by:gs79
ID: 39850983
Works like a charm!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now