Random Notes

• Run profload.sql as sys and proftab.sql as the profile user to install DBMS_PROFILER to measure execution time of a pl/sql program unit.
• Oracle implicitly converts the BLOB data type to RAW

DBMS_OUTPUT to log progress or debugging messages are :

1. Each line is limited to 255 bytes
   
2. Output is limited to 1,000,000 bytes maximum
3. The output only appears at the end of the script being run

UTL_FILE.PUTF to print formatted output to a file without newline formatting.


Which one of the following built-in packages do you use to work around the previous 1,000,000 hard limit on the buffer size of DBMS_OUTPUT for any session when directly writing output from PL/SQL?

• UTL_FILE

SQL> desc user_object_size
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(30)
TYPE VARCHAR2(18)
SOURCE_SIZE NUMBER
PARSED_SIZE NUMBER
CODE_SIZE NUMBER
ERROR_SIZE NUMBER

SOURCE_SIZE

Description of USER_OBJECT_SIZE.SOURCE_SIZE: "Size of the source, in bytes. Must be in memory during compilation, or dynamic recompilation"

PARSED_SIZE

Description of USER_OBJECT_SIZE.PARSED_SIZE: "Size of the parsed form of the object, in bytes. Must be in memory when an object is being compiled that references this object"

CODE_SIZE

Description of USER_OBJECT_SIZE.CODE_SIZE: "Code size, in bytes. Must be in memory when this object is executing"

ERROR_SIZE

Description of USER_OBJECT_SIZE.ERROR_SIZE: "Size of error messages, in bytes. In memory during the compilation of the object when there are compilation errors"


Use \1 \2 \3 .... for backreferences in regex

set serveroutput on format wrapped
the above sqlplus command will enable to print blank lines as given below.
begin
dbms_output.put_line('Line 1');
dbms_output.put_line('');
dbms_output.put_line('Line 3');
end;
/