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 ...
Today one of my colleague was working on development of a screen in Oracle Forms to give the end user an option to schedule a job using dbms_scheduler. With the hope that i would be able to explain it properly, the whole scenario is like this: User will log in to the application with his username (Lets say USER01) and password (basically every application user is a database user). He is provided with a screen where he can enter details about the job and the code behind the button calls a PL/SQL procedure in the main application schema (lets say APP1) which in turn uses DBMS_SCHEDULER.CREATE_JOB to schedule the new job. The ultimate task of the job is to move data from one table in the first database to a table in the second database using a DB Link. There is a VPD policy applied on all the application users to restrict the view of data. Policy function uses SYS_CONTEXT to fetch some information about the logged in user. The main application user APP1 is exempted from policy and can see the whole data. Things seem to work fine till the schedule part. But when the job runs it hits ORA-02070: database does not support operator SYS_CONTEXT in this context as SYS_CONTEXT and DB link doesn’t go together. ...
I came across a very nice post about Autonomous Transactions in Oracle written by Kevin Meade on orafaq. Thought about sharing the link. His blog also has some very nice stuff. Comments Comment by Aman…. on 2008-03-09 12:28:30 +0530 Nice one Sidhu.learned some thing new about AT. 🙂 Cheers, Aman….
Today one of my colleague was working on a simple PL/SQL procedure. Based on some logic it was returning count(*) from all_tab_columns for few tables. It gave count incorrectly for one table out of around fifty in total. He just hard coded the table name and ran it but again it showed count as zero. Then he took the code out of procedure and wrote it in DECLARE, BEGIN, END and after running it showed the correct count. But ran as database procedure it always shows incorrectly. ...