SEO

#1 (**permalink**) 02-25-2008, 03:23 AM

Hi,

I am wondering if there is a way that you can get the SQL query string
that triggers the Oracle Trigger.

what I want to do is to write a INSERT/UPDATE trigger for all the
tables, and in the trigger i will call a java function that will do
some validation on the table name, columns and its data of course.

Or is there a better way to achieve this?

another option I have is to use JPublisher; however, i have no idea
what it really does.

so, anyway has a thought?

thanks~

#2 (**permalink**) 02-25-2008, 03:23 AM

<eric.h.lin@gmail.com> wrote in message
news:1143672415.637031.20580@i40g2000cwc.googlegro ups.com...
:
: another option I have is to use JPublisher; however, i have no idea
: what it really does.
:

sounds like a plan ;-)

#3 (**permalink**) 02-25-2008, 03:23 AM

come on^^~haa

so anyone know how to get the sql query inside the trigger? so i can
pass it to other store procedure / java sp~

thank you~

#4 (**permalink**) 02-25-2008, 03:23 AM

eric.h.lin@gmail.com wrote:
> so anyone know how to get the sql query inside the trigger? so i can
> pass it to other store procedure / java sp~

Perhaps something like this:

CREATE OR REPLACE TRIGGER testit_trg
BEFORE INSERT OR UPDATE ON testit FOR EACH ROW
DECLARE
v_sql_text ORA_NAME_LIST_T;
v_stmt CLOB;
n PLS_INTEGER := ORA_SQL_TXT(v_sql_text);
BEGIN
FOR i IN 1..n LOOP
DBMS_OUTPUT.PUT_LINE(v_sql_text(i));
v_stmt := v_stmt || v_sql_text(i);
END LOOP;
END;
/

#5 (**permalink**) 02-25-2008, 03:23 AM

eric.h.lin@gmail.com schreef:

> Hi,
>
> I am wondering if there is a way that you can get the SQL query string
> that triggers the Oracle Trigger.
>
> what I want to do is to write a INSERT/UPDATE trigger for all the
> tables, and in the trigger i will call a java function that will do
> some validation on the table name, columns and its data of course.
>
> Or is there a better way to achieve this?
>

Yes - as indicated, start reading the documentation!
Some facts:
if your SQL code comes to a point the trigger on the table fires, that
means the table name is correct - no sense in checking that.
if your SQL code uses wrong column names, your trigger won't fire; if
it does fire, your columns are OK - no sense in checking that.
Let your SQL code handle the data validation, or better yet: let the
database do it. Referential contraints in Oracle have been around since
V6, and are working since 7.0.

Need I say more? Time to start treating Oracle as a database, or
rewrite your app to do it's own data storage.

#6 (**permalink**) 02-25-2008, 03:24 AM

Hi all,

The reason I wannna do this is because I want to capture the direct sql
query (using trigger) and send it to a java function. Then parse the
sql query and contruct a web service call according to the table and
column it is trying to insert or update.

maybe there is a better solution for this, I don't know~....

#7 (**permalink**) 02-25-2008, 03:24 AM

eric.h.lin@gmail.com wrote:
> Hi all,
>
> The reason I wannna do this is because I want to capture the direct sql
> query (using trigger) and send it to a java function. Then parse the
> sql query and contruct a web service call according to the table and
> column it is trying to insert or update.
>
> maybe there is a better solution for this, I don't know~....

You're still just describing a process you envision using. Tell us
what it is you are really trying to accomplish. What is the business
problem you are trying to solve -- not the intermediate technical
problem.

#8 (**permalink**) 02-25-2008, 03:24 AM

eric.h.lin@gmail.com wrote:
> Hi all,
>
> The reason I wannna do this is because I want to capture the direct sql
> query (using trigger) and send it to a java function. Then parse the
> sql query and contruct a web service call according to the table and
> column it is trying to insert or update.
>
> maybe there is a better solution for this, I don't know~....

I'm with EdStevens ... what is the result you wish to achieve. This
process seems to be the worst possible way to accomplish most anything.

Where is the SQL coming from you wish to capture?
In what version of Oracle?
Why a Java function rather than PL/SQL?
Have you looked at Advanced Queuing and JMS?
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Get the sql query in oracle trigger