Yesterday, one of my colleague asked that if he traced a wrap’ed PL/SQL procedure, would the SQL statements show up in the trace ? Very simple thing but at that moment i got, sort of into doubt. So i ran a simple test and yes they do show up ;)
[sql]CREATE OR REPLACE PROCEDURE wrap1 AS v_today DATE; BEGIN SELECT SYSDATE INTO v_today FROM DUAL; END; /
C:\>wrap iname=wrap1.sql
PL/SQL Wrapper: Release 10.2.0.1.0- Production on Fri Sep 18 21:07:49 2009
Copyright (c) 1993, 2004, Oracle. All rights reserved.
Processing wrap1.sql to wrap1.plb
C:\>more wrap1.plb CREATE OR REPLACE PROCEDURE wrap1 wrapped a000000 b2 abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd 7 65 96 uu93le0yJCtORZedJgcWflZ1Jacwg5nnm7+fMr2ywFwWlvJWfF3AdIsJaWnnbSgIv1JfNsJx doRxO75ucVUAc2fTr+Ii4v+onq/3r8q9yOOsrLAP4yRZW6LbYoWa6q9sd7PG7Nk9cpXs+6Y5 tQR4
/[/sql]
And here is the output from the trace file, showing the SQL statement:
[sql]BEGIN wrap1; END;
call count cpu elapsed disk query current rows ---—- —— ——– ———- ———- ———- ———- ———- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.03 0.01 0 0 0 1 Fetch 0 0.00 0.00 0 0 0 0 ---—- —— ——– ———- ———- ———- ———- ———- total 2 0.03 0.02 0 0 0 1
Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 54 \\\******************************************************************************
SELECT SYSDATE FROM DUAL[/sql]
Comments
Comment by Martin Berger on 2009-09-19 00:37:33 +0530
Amardeep,
as you are talking about wrap’ed code and have an example with version 10g you might want to have a look at Antons http://technology.amis.nl/blog/4753/unwrapping-10g-wrapped-plsql
It’s not a walkthrough, but a good start for any one with a basic IT education.
Martin
Comment by Amardeep Sidhu on 2009-09-19 21:44:21 +0530
Martin
Thanks for the link. Coincidently, yesterday i was going through the presentation of Pete mentioned in the link 🙂 .
Amardeep