Re: Help: Is this sql from Package or not?

Karsten Farrell <kfarrell_at_belgariad.com> wrote in message news:<MPG.1912fb366ee1702b989757_at_news.la.sbcglobal.net>...
> "charlie cs" <cs3526(no-spam)@yahoo.com> said...
> > Guys,
> >
> > Is there any way to detect if a java application is hitting the db using a
> > stored proc vs. a
> > straight sql statement? We'd like to identify what sql we are getting that
> > is not in a stored proc. so we can change the Java code.
> >
> > Any suggestions will be highly appreciated
> >
> You could add functionality to the stored procedure to write an audit
> record to another table whenever it's called. You could write a trigger
> against the table(s) that writes an audit record, including the user who
> fired the trigger.

That may be the most reliable way, short of looking at v$sql where address is the currently running Java session's sql_address.

Other than that, you may simply select sql_text from v$sql and look at the text. Code from stored procedures (packages, functions) look a little different from straight SQL. For instance, this is my procedure:
SQL> create or replace procedure p as begin   2 delete from qwerty;
  3 end;
  4 /

But after I execute it, v$sql shows "DELETE from qwerty". Straight SQLs will show as original text strings in v$sql.

In addition to changing letter case, the PL/SQL engine might also change literals to bind variables in the where clause, adding one more space between some words, and so on.

Yong Huang Received on Mon Apr 28 2003 - 17:25:10 CDT