As per my experience when working with PL/SQL code, DBMS_OUTPUT is one of the most frequently used package to display debugging information.
But frequently when using 10G R1 and earlier versions, we come across something like as follows:
ORA-20000: ORU-10028: line length overflow, limit of 255 bytes per line
Single line limit of 255 bytes exceeded.
ORA-20000: ORU-10027: buffer overflow, limit of bytes
Maximum amount of 1 million bytes per session exceeded.
10G R2 increases the single length line limit to 32,767 bytes and the overall session limit is removed altogether.
Note: Now myself and my team use our own procedure called LOGIT for debugging, I will […]

[Read more →]

This information for the guys who are new to SQL*Plus.
One can generate static HTML pages from SQL*Plus (8.1.6 and above) by setting the MARKUP option to HTML ON.
This can be done following two ways:
1. From command line when opening sqlplus specifying -MARKUP “HTML ON” as parameters
Example:
C:\>SQLPLUS -MARKUP “HTML ON”
2. From SQL*Plus, through “SET MARKUP HTML ON” command.
Example:
SQL> SET MARKUP HTML ON
SQL&gt> SPOOL C:\dept.html
SQL&gt> SELECT * FROM dept;
SQL&gt> SPOOL OFF

Bookmark this post

[…]

[Read more →]

Today let me tell you about customization of SQL*Plus prompt. How we can use predefined variables to customize the SQL*Plus prompt so that we know where we are working. This information is important whether you are a DBA or a Developer.
Following are the two files which are available at $ORACLE_HOME/SQLPLUS/admin through which we can configure and change the SQL*Plus prompt:
1. GLOGIN.SQL
2. LOGIN.SQL
When SQL*Plus starts after completion of connection to database it first look for GLOGIN.SQL and then LOGIN.SQL to execute their content.
Prior to Oracle 10g, GLOGIN.SQL used to execute only once while opening the […]

[Read more →]