SELECT E1.EMPNO,E2.EMPNO FROM (SELECT ROWNUM r1,empno FROM emp ORDER BY empno DESC)e1,(SELECT ROWNUM r2,empno FROM emp ORDER BY empno ASC) e2 WHERE e1.r1 = e2.r2

[Read more →]

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 [...]

[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. [...]

[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 [...]

[Read more →]