MISCELLANEOUS SQL*PLUS ENHANCEMENTS

MISCELLANEOUS SQL*PLUS ENHANCEMENTS

Implicit Results on SQL*Plus: SQL*Plus in 12c returns results from an implicit cursor of a PL/SQL block without actually binding it to a RefCursor. The new dbms_sql.return_result procedure will return and formats the results ofSELECT statement query specified within PL/SQL block. The following code descries the usage:

SQL> CREATE PROCEDURE mp1

   res1 sys_refcursor;

BEGIN

  open res1 for Select eno,ename,sal FROM emp;

END;

SQL> execute mp1;

dbms_sql.return_result(res1);

When the procedure is executed, it return the formatted rows on the SQL*Plus.

Display invisible columns: In Part 1 of this series, I have explained and demonstrated about invisible columns new feature. When the columns are defined as invisible, they won’t be displayed when you describe the table structure. However, you can display the information about the invisible columns by setting the following on the SQL*Plus prompt:

SQL> SET COLINVISIBLE ON|OFF

The above setting is only valid for DESCRIBE command. It has not effect on the SELECT statement results on the invisible columns.