 |
|
Oracle Tips by Burleson |
Using
DBMS_OUTLN_EDIT.GENERATE_SIGNATURE
The GENERATE_SIGNATURE procedure is mistakenly attributed to the
OUTLN_PKG in the Oracle documentation set, in actuality it is
located in the OUTLN_EDIT_PKG which is synonymed to be
DBMS_OUTLN_EDIT. Passing the GENERATE_SIGNATURE procedure a SQL
statement will return a RAW signature of the SQL which can then be
used to probe the outline tables to see if that SQL has an existing
OUTLINE.
When using
GENERATE_SIGNATURE,
it is probably easiest to create an anonymous PL/SQL script to
return the signature or the SQL and OUTLINE
name to
you. The
signature is a RAW,
so in the
example, shown
below,
we return the outline name.
SQL> set serveroutput on
SQL> declare
2 sql_text varchar2(200);
3 ret_sql_text varchar2(200);
4 ret_outline_name varchar2(32);
5 gen_sig raw(16);
6 begin
7 sql_text := 'select owner, table_name, tablespace_name
8 from
9 dba_tables
10 where owner not in '
11
('||chr(39)||'SYS'||chr(39)||','||chr(39)||'SYSTEM'||chr(39)||')';
12 dbms_outln_edit.generate_signature(sql_text,gen_sig);
13 select a.sql_text, a.name into ret_sql_text, ret_outline_name
14 from dba_outlines a where signature=gen_sig;
15 dbms_output.put_line('Sql text for '||name||': '||ret_sql_text);
16 end;
17* /
Sql text for TEST1: select owner,table_name,tablespace_name
from dba_tables
where
owner not in ('SYS','SYSTEM')
PL/SQL procedure successfully completed.
Once you have identified the SQL statement
for which
you want
to edit the hint structure, you use the DBMS_OUTLN_EDIT
procedure to perform the edits as shown above.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
