-- use print_table to get a formatted query result
-- exec print_table('select * from v$session_longops');
create or replace procedure print_table( p_query in varchar2 )
AUTHID CURRENT_USER
is
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_descTbl dbms_sql.desc_tab;
l_colCnt number;
begin
execute immediate
'alter session set
nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns
( l_theCursor, l_colCnt, l_descTbl );
for i in 1 .. l_colCnt loop
dbms_sql.define_column
(l_theCursor, i, l_columnValue, 4000);
end loop;
l_status := dbms_sql.execute(l_theCursor);
while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
for i in 1 .. l_colCnt loop
dbms_sql.column_value
( l_theCursor, i, l_columnValue );
dbms_output.put_line
( rpad( l_descTbl(i).col_name, 30 )
|| ': ' ||
l_columnValue );
end loop;
dbms_output.put_line( '-----------------' );
end loop;
execute immediate
'alter session set nls_date_format=''dd-MON-rr'' ';
exception
when others then
execute immediate
'alter session set nls_date_format=''dd-MON-rr'' ';
raise;
end;
/
-- show execution plan by optimizer:
SQL> set autotrace traceonly explain
SQL> select *
2 from emp, dept
3 where emp.deptno = dept.deptno
4 and emp.job = 'CLERK';
Execution Plan
--------------------------------------------------------------------------------------
Plan hash value: 877088642
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 468 | 7 (15) | 00:00:01 |
|* 1 | HASH JOIN | | 4 | 468 | 7 (15) | 00:00:01 |
|* 2 | TABLE ACCESS FULL | EMP | 4 | 348 | 3 (0) | 00:00:01 |
| 3 | TABLE ACCESS FULL | DEPT | 4 | 120 | 3 (0) | 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
-----------------------------------------------------------------------------------------
1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
2 - filter("EMP"."JOB"='CLERK')
Note
--------
- dynamic sampling used for this statement
Conditional Compilation
PL/SQL has lots of new stuff. The first thing I noticed was conditional compilation, something I missed from my days as a C programmer. Conditional compilation is the ability to make the compiler effectively ignore code (or not) at will. It may not sound too useful at first, but it is. With conditional compilation,
* You can leave your really slow debug code in your application now—and turn it on and off at will.
* You can program assertions as you might in C. Each subprogram can test the values of its inputs, for example, and verify that they meet some criteria. These tests can be active during the whole development cycle and inactive for production. However, they remain as formal documentation of the conditions upon which the unit depends, and can be simply reactivated for debugging a production-time bug.
* You can write version-independent code—by compiling one set of code for version X and a different set of code for version Y—without having two sets of code. Check out the new DBMS_DB_VERSION supplied package.
* You can support best practices during unit testing. For production, helper subprograms will be declared only in a package's body. But when you want to invoke them from a unit-testing framework, you need to declare them in the specification. By conditionally compiling these declarations, you can have your cake and eat it too: you honor your modular design by hiding what should be hidden in your shipped code, but you allow separate compilation units to see the hidden elements for testing purposes. Further, you can cause helper subprograms to produce expected (data-dependent) exceptions so that you can test the robustness of code that calls your code without the effort of contriving the exceptional data conditions.
Listing 2 shows a quick view of what conditional compilation implies and how it works.
Code Listing 2: Conditional PL/SQL compilation example
SQL> create or replace procedure p
2 as
3 begin
4 $IF $$debug_code $THEN
5 dbms_output.put_line( 'Our debug code' );
6 dbms_output.put_line( 'Would go here' );
7 $END
8 dbms_output.put_line( 'And our real code here' );
9 end;
10 /
Procedure created.
Notice how the "debug" code is not printed and the code compiles without the $$debug_code value being defined.
SQL> exec p
And our real code here
PL/SQL procedure successfully completed.
By simply enabling the debug_code variable, we can enable that debug code. Note that "debug_code" is my name—it is not a special name; you can define your own variables at will.
SQL> alter procedure P compile
2 plsql_ccflags = 'debug_code:true' reuse settings;
Procedure altered.
SQL> exec p
Our debug code
Would go here
And our real code here
PL/SQL procedure successfully completed.
