| How to see the complete content of trigger_body in user_triggers [message #468754] |
Mon, 02 August 2010 01:52  |
oracle_user1
Messages: 45
Registered: May 2008
|
Member |
|
|
Dear all,
Column Trigger_body in table user_trigger is long data type.
I want to find out the certain 'table name' was using in
trigger.
Below is my sql statement:
select trigger_name
from user_triggers
where TRIGGER_BODY like '%tb_test%' or TRIGGER_BODY like '%TB_TEST%';
then it show me a error :
where TRIGGER_BODY like '%tb_test%'
*
ERROR at line 3:
ORA-00932: inconsistent datatypes
I had set long 32767 before execute above statement
If you have any solution or tip please let me know...
Thanks.
Regards,
oracle user
|
|
|
|
|
|
|
|
|
|
| Re: How to see the complete content of trigger_body in user_triggers [message #468939 is a reply to message #468763] |
Mon, 02 August 2010 09:57  |
oracle_user1
Messages: 45
Registered: May 2008
|
Member |
|
|
Dear all,
Thanks all your tips, i had try it, but still can't work.
Now i want to sharing the solution with you all.
Actually i get this solution from other website.
CREATE TABLE t1 (
col1 VARCHAR2(20),
col2 VARCHAR2(20));
ALTER TABLE t1
ADD CONSTRAINT cc_t1_col1
CHECK (col1 IN ('Yes', 'No', '?'));
CREATE OR REPLACE TRIGGER BIT_T1
BEFORE INSERT
ON t1
FOR EACH ROW
BEGIN
:NEW.col1 := 'Yes';
END BIT_T1;
/
CREATE TABLE t2 (
col1 VARCHAR2(20),
col2 VARCHAR2(20));
ALTER TABLE t2
ADD CONSTRAINT cc_t2_col1
CHECK (col1 IN ('Yes', 'No', 'X'));
CREATE OR REPLACE TRIGGER BIT_T2
BEFORE INSERT
ON t2
FOR EACH ROW
BEGIN
:NEW.col1 := 'No';
END BIT_T2;
/
SELECT trigger_name, trigger_body
FROM user_triggers;
SELECT constraint_name, search_condition
FROM user_constraints;
We will start our exploration with wild card searches. Lets say we are looking for all of the rows with the value 'Yes'. There should be one of them in USER_TRIGGERS.
desc user_triggers
-- none of these work
SELECT trigger_name, trigger_body
FROM user_triggers
WHERE trigger_body LIKE '%Yes%';
SELECT trigger_name, trigger_body
FROM user_triggers
WHERE TO_CHAR(trigger_body) LIKE '%Yes%';
SELECT trigger_name, trigger_body
FROM user_triggers
WHERE TO_CLOB(trigger_body) LIKE '%Yes%';
SELECT trigger_name, trigger_body
FROM user_triggers
WHERE TO_LOB(trigger_body) LIKE '%Yes%';
DECLARE
c CLOB;
BEGIN
SELECT TO_LOB(trigger_body)
INTO c
FROM user_triggers
WHERE rownum = 1;
END;
/
-- and yet this works
CREATE TABLE tlob (
testcol CLOB);
INSERT INTO tlob
SELECT TO_LOB(trigger_body)
FROM user_triggers;
SELECT * FROM tlob;
So how do we get only those values we want? Adding a WHERE clause with SUBSTR doesn't work. One obvious solution is to put all trigger bodies into a global temporary table and then select from there. For example:
DROP TABLE tlob PURGE;
CREATE GLOBAL TEMPORARY TABLE tlob (
testcol CLOB);
-- Step 1:
INSERT INTO tlob
SELECT TO_LOB(trigger_body)
FROM user_triggers;
-- Step 2:
SELECT *
FROM tlob
WHERE testcol LIKE '%Yes%';
or
DELETE FROM tlob
WHERE testcol NOT LIKE '%Yes%';
But is there a way to do this without inserting into a table? Unfortunately there isn't except under some very special, not general conditions so for our purposes we will say no. You must use LONG to LOB solely for purposes of version. And there is nothing you can do with LONG in a WHERE clause.
Simply put ... if you have a LONG, and you wish to do more than just display it, you must use TO_LOB to convert it during either an INSERT INTO <table> or a CREATE TABLE <table_name> AS. Everything else is just wasting time.
Thanks.
Regards,
oracle user
|
|
|
|
|
|
|
|
| Re: How to see the complete content of trigger_body in user_triggers [message #468947 is a reply to message #468939] |
Mon, 02 August 2010 10:29 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.
Explain in what it "does not work".
If you have any error, use SQL*Plus and copy and paste your session.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
