StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You can use dba_triggers to extract the text of the trigger into CREATE or replace statements. But due to some of the columns being LONG datatype you will have trouble extracting them as VARCHAR2. This can be resolved by using Tom Kytes package which is lost somewhere on the oracle site. I include my own version which you may have to modify to meet your needs.</p> <p>Run the select, insert your when clause and then run the create or replace statements.</p> <p>This won't work due to the trigger_body being a long datatype</p> <pre><code>select 'CREATE OR REPLACE TRIGGER '|| description ||trigger_body from dba_triggers where owner = 'Your schema' </code></pre> <p>but this should work if your triggers are not more than 4000 characters</p> <pre><code>select 'CREATE OR REPLACE TRIGGER '|| description || ADMIN.LONG_HELP.SUBSTR_OF('select trigger_body from dba_triggers where trigger_name = :0', 1,4000,'0',dt.trigger_name) from dba_triggers dt where owner = 'YourSchema'; CREATE OR REPLACE PACKAGE ADMIN.LONG_HELP /****************************************************************************** NAME: LONG_HELP PURPOSE: Read fields of type long. (commonly found in data dictionary) REVISIONS: Ver Date Author Description --------- ---------- --------------- ------------------------------------ 1.0 10/27/2011 1. Created this package. based on Tom Kyte's column here http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:839298816582 note that it only retrieves the first 4000 characters of any LONG column USAGE in a WHERE INSTR( ADMIN.LONG_HELP.SUBSTR_OF('SELECT text from all_views where view_name =:o ', 1,4000,'o',m2.obj_name),m1.FK_ID) > 0 ******************************************************************************/ --AUTHID CURRENT_USER --removed to get around ORA-29470: Effective userid or roles are not the same as when cursor was parsed --restrict usage to admin schema for Oracle 11g AS FUNCTION substr_of (p_query IN VARCHAR2, p_from IN NUMBER, p_for IN NUMBER, p_name1 IN VARCHAR2 DEFAULT NULL , p_bind1 IN VARCHAR2 DEFAULT NULL , p_name2 IN VARCHAR2 DEFAULT NULL , p_bind2 IN VARCHAR2 DEFAULT NULL , p_name3 IN VARCHAR2 DEFAULT NULL , p_bind3 IN VARCHAR2 DEFAULT NULL , p_name4 IN VARCHAR2 DEFAULT NULL , p_bind4 IN VARCHAR2 DEFAULT NULL ) RETURN VARCHAR2; END LONG_HELP; / CREATE OR REPLACE PACKAGE BODY ADMIN.LONG_HELP AS g_cursor NUMBER := DBMS_SQL.open_cursor; g_query VARCHAR2 (32765); PROCEDURE bind_variable (p_name IN VARCHAR2, p_value IN VARCHAR2) IS BEGIN IF (p_name IS NOT NULL) THEN DBMS_SQL.bind_variable (g_cursor, p_name, p_value); END IF; END BIND_VARIABLE; FUNCTION substr_of (p_query IN VARCHAR2, p_from IN NUMBER, p_for IN NUMBER, p_name1 IN VARCHAR2 DEFAULT NULL , p_bind1 IN VARCHAR2 DEFAULT NULL , p_name2 IN VARCHAR2 DEFAULT NULL , p_bind2 IN VARCHAR2 DEFAULT NULL , p_name3 IN VARCHAR2 DEFAULT NULL , p_bind3 IN VARCHAR2 DEFAULT NULL , p_name4 IN VARCHAR2 DEFAULT NULL , p_bind4 IN VARCHAR2 DEFAULT NULL ) RETURN VARCHAR2 AS /****************************************************************************** NAME: LONG_HELP.SUBSTR_OF PURPOSE: CONVERT long data fields into VARCHAR2 WHOSE DATA IS CHANGED: none WHAT USES THIS: WHERE ARE THE RESOURCES NEEDED: ******************************************************************************/ l_buffer VARCHAR2 (4000); l_buffer_len NUMBER; BEGIN IF (NVL (p_from, 0) <= 0) THEN raise_application_error (-20002, 'From must be >= 1 (positive numbers)'); END IF; IF (NVL (p_for, 0) NOT BETWEEN 1 AND 4000) THEN raise_application_error (-20003, 'For must be between 1 and 4000'); END IF; IF (p_query <> g_query OR g_query IS NULL) THEN IF (UPPER (TRIM (NVL (p_query, 'x'))) NOT LIKE 'SELECT%') THEN raise_application_error (-20001, 'This must be a select only'); END IF; DBMS_SQL.parse (g_cursor, p_query, DBMS_SQL.native); g_query := p_query; END IF; bind_variable (p_name1, p_bind1); bind_variable (p_name2, p_bind2); bind_variable (p_name3, p_bind3); bind_variable (p_name4, p_bind4); DBMS_SQL.define_column_long (g_cursor, 1); IF (DBMS_SQL.execute_and_fetch (g_cursor) > 0) THEN DBMS_SQL.column_value_long (g_cursor, 1, p_for, p_from - 1, l_buffer, l_buffer_len); END IF; RETURN l_buffer; END substr_of; END LONG_HELP; / </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload