tag:blogger.com,1999:blog-58827669902830825282024-03-13T07:47:30.956-04:00Phil's Oracle Exadata, SQL, and PL/SQL tipsA Blog dedicated to making Oracle SQL and PL/SQL development easier and more fun. This is the place for Exadata, helpful data modeling, development, administration, and architecture tips.
I plan to learn from the blog as well - so if you have helpful tips - please feel free to share them in your comments!Philhttp://www.blogger.com/profile/01851424617108510453noreply@blogger.comBlogger12125tag:blogger.com,1999:blog-5882766990283082528.post-3311821978958950152017-01-26T09:30:00.001-05:002017-01-26T09:30:30.206-05:00How to export Oracle Virtual Private Database (a.k.a.: VPD, or "Row Level Security") policy creation statements<div style="text-align: left;">
Hey crew,</div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
Long time no post.</div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
Have you ever had to drop an Oracle VPD policy from a table, and then realized that it isn't very easy to put it back?</div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
Hopefully this little snippet will help with backing up the VPD policy definition so that you can easily add it back later.</div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
I hope this is helpful:</div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
<span style="font-family: "Courier New", Courier, monospace; font-size: xx-small;">-- Build the ADD policy statement from the dictionary<br />SELECT 'BEGIN<br /> DBMS_RLS.add_policy (object_schema => ' || DBMS_ASSERT.enquote_literal (str => object_owner) || '<br /> , object_name => ' || DBMS_ASSERT.enquote_literal (str => object_name) || '<br /> , policy_name => ' || DBMS_ASSERT.enquote_literal (str => policy_name) || '<br /> , function_schema => ' || DBMS_ASSERT.enquote_literal (str => pf_owner) || '<br /> , policy_function => ' || DBMS_ASSERT.enquote_literal (str => CASE WHEN package IS NOT NULL <br /> THEN package || '.'<br /> END<br /> || FUNCTION<br /> ) || '<br /> , statement_types => ' || DBMS_ASSERT.enquote_literal (str => LTRIM (RTRIM (CASE sel<br /> WHEN 'YES' THEN 'SELECT'<br /> END<br /> || ','<br /> || CASE ins<br /> WHEN 'YES' THEN 'INSERT'<br /> END<br /> || ','<br /> || CASE upd<br /> WHEN 'YES' THEN 'UPDATE'<br /> END<br /> || ','<br /> || CASE del<br /> WHEN 'YES' THEN 'DELETE'<br /> END<br /> , ','<br /> )<br /> , ','<br /> )<br /> ) || '<br /> , update_check => ' || CASE WHEN chk_option = 'YES' THEN 'TRUE' ELSE 'FALSE' END || '<br /> , enable => ' || CASE WHEN enable = 'YES' THEN 'TRUE' ELSE 'FALSE' END || '<br /> , static_policy => ' || CASE WHEN static_policy = 'YES' THEN 'TRUE' ELSE 'FALSE' END || '<br /> , policy_type => DBMS_RLS.' || policy_type || '<br /> , long_predicate => ' || CASE WHEN long_predicate = 'YES' THEN 'TRUE' ELSE 'FALSE' END || '<br /> , sec_relevant_cols => ' || DBMS_ASSERT.enquote_literal (str => (SELECT listagg (dsrc.sec_rel_column, ',') WITHIN GROUP (ORDER BY dsrc.sec_rel_column ASC) AS col_list <br /> FROM dba_sec_relevant_cols dsrc<br /> WHERE dsrc.object_owner = dp.object_owner<br /> AND dsrc.object_name = dp.object_name<br /> AND dsrc.policy_name = dp.policy_name<br /> )<br /> ) || '<br /> , sec_relevant_cols_opt => ' || CASE WHEN EXISTS (SELECT 1<br /> FROM dba_sec_relevant_cols dsrc<br /> WHERE dsrc.object_owner = dp.object_owner<br /> AND dsrc.object_name = dp.object_name<br /> AND dsrc.policy_name = dp.policy_name<br /> AND dsrc.column_option = 'ALL_ROWS'<br /> ) THEN 'DBMS_RLS.ALL_ROWS'<br /> ELSE 'NULL'<br /> END || '<br /> );<br />END;<br />/' AS stmt<br /> FROM dba_policies dp<br /> WHERE object_owner = '<<table_owner>>'<br /> AND object_name = '<<table_name>>';</table_name></table_owner></span></div>
<div style="text-align: left;">
<span style="font-family: Courier New;"><br /></span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">Take care!</span></div>
<div style="text-align: left;">
<br /></div>
Philhttp://www.blogger.com/profile/01851424617108510453noreply@blogger.com0tag:blogger.com,1999:blog-5882766990283082528.post-44595783954096517532016-05-09T09:07:00.000-04:002016-05-09T09:07:05.325-04:00Pivot/Unpivot Examples (with multi-column UNPIVOT)Hey fellow database enthusiasts,<br />
<br /><br />
Long time no post...<br />
<br /><br />
I figured I would post some examples of "PIVOT" and "UNPIVOT" because I feel like the Oracle documentation doesn't give enough.<br />
<br /><br />
I've constructed some of my own - which includes a "multi-column" UNPIVOT operation as well.<br />
<br /><br />
Take these and try them on your own in your own database - see if you can apply the techniques used in the examples with real data as well...<br />
<br /><br />
Here goes:<br />
<span style="font-family: "Courier New", Courier, monospace;"><br /></span><br />
<span style="font-family: "Courier New", Courier, monospace;">/*</span><span style="font-family: "Courier New", Courier, monospace;"> ------------------------------------------------------------------- */</span><br />
<span style="font-family: "Courier New", Courier, monospace;">-- Pivot Example (rows-to-columns)<br />WITH row_based_table AS (<br />SELECT 1 AS ID, 'jack' AS code, 123.33 AS sales, 999 AS units FROM dual<br />UNION ALL<br />SELECT 1 AS ID, 'tom' AS code, 456.44 AS sales, 199 AS units FROM dual<br />UNION ALL<br />SELECT 1 AS ID, 'bob' AS code, 789.55 AS sales, 100 AS units FROM dual<br />)<br />SELECT *<br /> FROM row_based_table<br />PIVOT<br />(SUM (sales) AS sales<br />,SUM (units) AS units<br />FOR code IN ('jack' AS jack, 'tom' AS tom, 'bob' AS bob)<br />);</span><br />
<span style="font-family: "Courier New", Courier, monospace;">/* ------------------------------------------------------------------- */</span><br />
<span style="font-family: "Courier New", Courier, monospace;">-- Multi-Column Un-Pivot Example (columns-to-rows)<br />WITH column_based_table AS (<br />SELECT 1 AS ID<br /> , 'jack' AS guy_a<br /> , 'jill' AS gal_a<br /> --<br /> , 'tom' AS guy_b<br /> , 'tanya' AS gal_b<br /> --<br /> , 'bob' AS guy_c<br /> , 'belinda'AS gal_c<br /> FROM dual<br />)<br />SELECT *<br /> FROM column_based_table <br /> UNPIVOT EXCLUDE NULLS ((guy, gal)<br /> FOR couple IN (<br /> (guy_a, gal_a) AS 'a couple'<br /> , (guy_b, gal_b) AS 'b couple'<br /> , (guy_c, gal_c) AS 'c couple'<br /> )<br />);</span><br />
<span style="font-family: "Courier New", Courier, monospace;">/* ------------------------------------------------------------------- */</span><br />
<span style="font-family: "Courier New", Courier, monospace;">-- Un-Pivot Example (columns-to-rows)<br />WITH column_based_table AS (<br />SELECT 1 AS ID, 'jack' AS A, 'tom' AS b, 'bob' AS c FROM dual<br />)<br />SELECT *<br /> FROM column_based_table <br /> UNPIVOT EXCLUDE NULLS (val<br /> FOR code IN (<br /> a AS 'a column'<br /> , b AS 'b column'<br /> , c AS 'c column'<br /> )<br />);</span><br />
<span style="font-family: Courier New;"><br /></span><br />
<span style="font-family: Courier New;">Take care!</span>Philhttp://www.blogger.com/profile/01851424617108510453noreply@blogger.com0tag:blogger.com,1999:blog-5882766990283082528.post-81298731581070803782015-07-06T14:02:00.003-04:002015-07-06T15:37:54.174-04:00Beware of empty partitions!Hey crew,<br />
<br />
Oracle partitioning is a great feature to use for your data warehouse - particularly for your fact table. A common, best practice approach is to RANGE partition your fact table by a DATE column - choosing a range with a Year, Quarter, Month, Week, or Day interval.<br />
<br />
One thing that we learned the "hard way" was that having a large number of empty partitions in our fact table(s) caused very poor performance in our Exadata queries that ran MUCH better in a case where there were no empty partitions.<br />
<br />
As mentioned in my previous post - we typically copy only a few weeks of transaction data down to our development environments because we do not have nearly as much space or compute resources there to house/process it.<br />
<br />
One mistake we used when exporting our fact table (while filtering down to subsets of dates) - using Oracle's Data Pump Export tool (expdp) - is that we exported the entire table DDL - meaning all partitions got created when we imported into development.<br />
<br />
While all partitions were there - only a small number were actually populated. We partition by DATE range - typically with partitions that hold one week of point-of-sale transactional data.<br />
<br />
So - in our case - we had like 8 populated partitions, with over 100 empty ones in development (all 108 would be populated in Production).<br />
<br />
We did all of the stuff you are supposed to do - we ensured that stats were up to date - but developers still complained that "Dev was too slow... We must work in Production!".<br />
<br />
I was immediately suspicious - Dev had way LESS data than Production - so even with reduced resources - it shouldn't be that bad...<br />
<br />
While investigating SQL Monitor reports from Dev and comparing them to similar reports from Production - I noticed that in Development - the Cost Based Optimizer estimated VERY LOW row counts from the fact table - while Production got it much closer.<br />
<br />
Our queries spanned multiple partitions - so they used global stats - which were accurate in both places.<br />
<br />
What I deduced from this was that Oracle was actually taking the total row count of the table (Global stats) divided by the total number of partitions to "guess" the cardinality that would be returned from the each partition of the fact table.<br />
<br />
In development - that formula had a much smaller numerator (due to smaller overall row count) - but a denominator that was just as large (108) as production's.<br />
<br />
When I dropped the empty partitions - something magical happened - Oracle got the cardinality estimates much closer to reality - and performance picked up because some bad nested loop joins turned back into hash joins (as they should have been based upon the actual cardinality) - and queries performed in a manner consistent with Production.<br />
<br />
I wanted something in my toolbox to help prevent this in the future - so I developed this script to search the partitions of the fact table for data, as well as to generate "ALTER TABLE x DROP PARTITION y;" commands to drop those empty partitions. Please be VERY CAREFUL with this - test it in development to ensure it works properly for you...<br />
<br />
<span style="font-family: "Courier New", Courier, monospace;">-- Generate statements to drop empty partitions from the fact table(s)<br />WITH t AS (<br />SELECT table_owner<br /> , table_name<br /> , partition_name<br /> , TO_NUMBER (EXTRACTVALUE (XMLTYPE (DBMS_XMLGEN.getxml ('SELECT COUNT(*) AS rows_exist FROM ' <br /> || DBMS_ASSERT.enquote_name (str => table_owner)<br /> || '.' <br /> || DBMS_ASSERT.enquote_name (str => table_name)<br /> || ' PARTITION (' <br /> || DBMS_ASSERT.enquote_name (str => partition_name)<br /> || ') WHERE ROWNUM <= 1'<br /> )<br /> )<br /> , '/ROWSET/ROW/ROWS_EXIST'<br /> )<br /> ) AS rows_exist<br /> FROM all_tab_partitions<br /> WHERE table_owner = 'WH'<br /> AND table_name IN ('POINT_OF_SALE_FACTS')<br />ORDER BY table_owner<br /> , table_name<br /> , partition_position<br />)<br />SELECT 'ALTER TABLE ' <br /> || DBMS_ASSERT.enquote_name (str => table_owner)<br /> || '.' <br /> || DBMS_ASSERT.enquote_name (str => table_name)<br /> || ' DROP PARTITION ' <br /> || DBMS_ASSERT.enquote_name (str => partition_name)<br /> || ';' AS stmt<br /> , t.*<br />FROM t<br />WHERE rows_exist = 0<br />;</span><br />
<br />
<span style="font-family: "Courier New", Courier, monospace;"> </span><span style="font-family: inherit;">Basically - this script performs dynamic SQL from SQL (pretty cool) - using DBMS_XMLGEN<span style="font-family: inherit;">. It lets you <span style="font-family: inherit;">"probe" each partition for data - it does NOT rely on stats which can get s<span style="font-family: inherit;">tale...</span></span></span></span><br />
<br />
<span style="font-family: inherit;"><span style="font-family: inherit;"><span style="font-family: inherit;"><span style="font-family: inherit;">I<span style="font-family: inherit;"> hope this is helpful for your efforts in finding/dropping empty partitions...</span></span></span></span></span><br />
<br />
<span style="font-family: inherit;"><span style="font-family: inherit;"><span style="font-family: inherit;"><span style="font-family: inherit;"><span style="font-family: inherit;"><span style="font-family: inherit;">God bless...</span> </span> </span></span></span></span>Philhttp://www.blogger.com/profile/01851424617108510453noreply@blogger.com3tag:blogger.com,1999:blog-5882766990283082528.post-81555221602599162852015-07-06T13:39:00.002-04:002015-07-06T13:41:42.583-04:00Finding Contiguous Date Ranges in your Data - using Recursive SQL<span style="font-family: inherit;">Hey team, </span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">I found myself needing to write a query to detect the contiguous date ranges available in my warehouse's Point of Sale fact data table (in Development).</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">While we have data for every day in Production, in Development we use a technique that only copies a few weeks from the same month of two consecutive years - this lets us calculate "Year-over-Year" growth - while limiting the data segment size.</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">In such cases - finding the MIN/MAX is not acceptible, because it will look like I have data for about 13 months - while in actuality I only have data for two months (April 2014 and April 2015 for example).</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">I decided not to "google" my problem because I wanted a brain-teaser SQL problem (I love those) - but I think I came up with something kind of cool - a recursive SQL solution using Oracle's CONNECT BY syntax. I haven't yet tackled it with ANSI recursive SQL - but I plan to convert this approach soon.</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">Anyway - here goes:</span><br />
<span style="font-family: "Courier New", Courier, monospace;">WITH dates AS (<br /> SELECT DISTINCT<br /> TRUNC (date_id, 'DD') AS date_id<br /> FROM (SELECT TO_DATE ('01-APR-2014', 'DD-MON-YYYY') + LEVEL - 1 AS date_id<br /> FROM dual<br /> CONNECT BY LEVEL <= 60<br /> UNION ALL<br /> SELECT TO_DATE ('01-APR-2015', 'DD-MON-YYYY') + LEVEL - 1 AS date_id<br /> FROM dual<br /> CONNECT BY LEVEL <= 90<br /> ) fact_table /* Here we are simulating a fact table having unbalanced date ranges available */<br /> )<br /> , recursive_dates AS (<br /> SELECT dates.date_id<br /> /* CONNECT_BY_ISLEAF tells me that I am at the bottom of the hierarchy in the recursive join for a branch */<br /> , CONNECT_BY_ISLEAF AS date_id_is_leaf_flag<br /> /* CONNECT_BY_ROOT always returns the top node in the recursive join - where "LEVEL" = 1 */<br /> , CONNECT_BY_ROOT date_id AS root_date_id<br /> /* The LEVEL expression tells me how many levels deep I am in the recursive join */<br /> , LEVEL AS date_id_level<br /> FROM dates<br /> /* Here we are finding any dates which do NOT have a preceding date (one day prior) in our "DATES" data set */<br /> START WITH date_id NOT IN (SELECT date_id + 1<br /> FROM dates<br /> )<br /> /* Here we are recursively joining the data set to itself to find the next date that is one day in the future from the current row's date */<br /> CONNECT BY PRIOR date_id = /* NEXT */ (date_id - 1)<br /> )<br /> SELECT root_date_id AS min_date_id<br /> , date_id AS max_date_id<br /> FROM recursive_dates rd<br /> WHERE date_id_is_leaf_flag = 1<br /> ORDER BY min_date_id </span><br />
<span style="font-family: "Courier New", Courier, monospace;">/</span><br />
<br />
<span style="font-family: inherit;">I hope this is helpful - can you think of other ways to tackle it?</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">Phil </span> <br />
<span style="font-family: "Courier New", Courier, monospace;"><br /></span>Philhttp://www.blogger.com/profile/01851424617108510453noreply@blogger.com0tag:blogger.com,1999:blog-5882766990283082528.post-59477750693880338602014-05-07T09:55:00.001-04:002016-06-30T11:36:41.239-04:00Oracle table (or query) CHECKSUM functionFor a long time now, I've wanted table (or query) checksum functionality like the SQL Server "CHECKSUM_AGG" function (detailed here: <a href="http://technet.microsoft.com/en-us/library/ms188920.aspx">http://technet.microsoft.com/en-us/library/ms188920.aspx</a>).<br />
<br />
I couldn't find anything "built-in" (if you do know of such a function, please comment below) - so I wrote my own.<br />
<br />
Please note - I don't use MD5 - I use ORA_HASH - but I think this is "close enough for government work". I multiply the output of ORA_HASH by the base-10 log of the column number in the result set (+1) so that:<br />
<br />
<div style="background-color: #eeeeee; font-family: "Courier New",Courier,monospace;">
SELECT 'X' AS x, 'Y' AS y FROM dual</div>
<br />
and <br />
<br />
<div style="background-color: #eeeeee; font-family: "Courier New",Courier,monospace;">
SELECT 'Y' AS x, 'X' AS y FROM dual;</div>
<br />
Do not have the same checksums...<br />
<br />
Anyway - here goes - hopefully this works well for you:<br />
<br />
<div style="background-color: #eeeeee; font-family: "Courier New",Courier,monospace;">
CREATE OR REPLACE FUNCTION marketplace."GET_QUERY_CHECKSUM" (<br /> p_query IN CLOB,<br /> p_output_checksum_query IN INTEGER DEFAULT 0,<br /> p_use_result_cache_hint IN INTEGER DEFAULT 0,<br /> p_primary_key_column_name IN all_tab_columns.column_name%TYPE DEFAULT NULL<br />)<br /> RETURN NUMBER<br /> AUTHID CURRENT_USER<br />IS<br /> /***********************************************************************************************<br /> Package : GET_QUERY_CHECKSUM<br /> Author : Philip Moore<br /> Creation Date : 05-FEB-2014 A.D.<br /> Version : 1.0<br /> Purpose: This function will return a checksum for a query which is passed in. It can be used<br /> to quickly determine if the result set of the query had changed. There is a low<br /> risk for collisions (false positive match) - however the risk is quite low.<br />
------------------------------------------------------------------------------------------------<br /> Modification History<br /> Date: Version: Modified by: Modifications:<br /> ---------- -------- ------------ --------------------------------------------------<br />
***********************************************************************************************/<br />
-- Variables<br /> l_theCursor INTEGER DEFAULT DBMS_SQL.open_cursor;<br /> l_descTbl DBMS_SQL.desc_tab;<br /> l_colCnt NUMBER;<br /> l_return NUMBER;<br /> l_hash_column_sql CLOB;<br /> l_this_column_sql VARCHAR2 (32767);<br /> l_primary_key_column_sql VARCHAR2 (32767);<br /> l_sql_stmt CLOB;<br />
-- Private-scope procedure<br /> PROCEDURE cleanup<br /> IS<br /> BEGIN<br /> IF DBMS_SQL.is_open (c => l_theCursor)<br /> THEN<br /> DBMS_SQL.close_cursor (c => l_theCursor);<br /> END IF;<br /> END cleanup;<br />BEGIN<br /> -- Parse the Query<br /> DBMS_SQL.parse (c => l_theCursor,<br /> STATEMENT => p_query,<br /> language_flag => DBMS_SQL.native);<br />
-- Get the column names, data types, etc..<br /> DBMS_SQL.describe_columns (c => l_theCursor,<br /> col_cnt => l_colCnt,<br /> desc_t => l_descTbl);<br />
-- Close our cursor<br /> DBMS_SQL.close_cursor (c => l_theCursor);<br />
-- Build our HASH SQL...<br /> <<column_loop>><br /> FOR i IN 1 .. l_colCnt<br /> LOOP<br /> l_this_column_sql := <br /> ' + ('<br /> || TO_CHAR (LOG (10, i + 1))<br /> || ' * '<br /> || CASE WHEN l_descTbl (i).col_null_ok THEN 'COALESCE (' END<br /> || 'ORA_HASH ('<br /> || DBMS_ASSERT.enquote_name (str => l_descTbl (i).col_name)<br /> || ')'<br /> || CASE<br /> WHEN l_descTbl (i).col_null_ok<br /> THEN<br /> ', ORA_HASH ('<br /> || DBMS_ASSERT.enquote_literal (<br /> str => l_descTbl (i).col_name)<br /> || '))'<br /> END<br /> || ')';<br /> l_hash_column_sql :=<br /> l_hash_column_sql<br /> || l_this_column_sql;</column_loop><br />
IF ( p_primary_key_column_name IS NOT NULL<br /> AND l_descTbl (i).col_name = p_primary_key_column_name<br /> )<br /> OR <br /> ( p_primary_key_column_name IS NULL<br /> AND i = 1<br /> ) THEN<br /> l_primary_key_column_sql := LTRIM (l_this_column_sql, ' +');<br /> END IF;<br /> END LOOP column_loop;<br />
l_sql_stmt :=<br /> 'SELECT '<br /> || CASE<br /> WHEN p_use_result_cache_hint = 1 THEN '/*+ result_cache */ '<br /> END<br /> || 'COUNT (*) + ROUND (COALESCE (SUM ('<br /> || l_primary_key_column_sql<br /> || ' * ('<br /> || LTRIM (l_hash_column_sql, ' +')<br /> || ')), 0), 0) AS hash_total FROM ('<br /> || p_query<br /> || ')';<br />
IF (p_output_checksum_query = 1)<br /> THEN<br /> DBMS_OUTPUT.PUT_LINE (SUBSTR (l_sql_stmt, 1, 32767));<br /> END IF;<br />
EXECUTE IMMEDIATE l_sql_stmt INTO l_return;<br />
RETURN l_return;<br />EXCEPTION<br /> WHEN OTHERS<br /> THEN<br /> cleanup ();<br /> RAISE;<br />END get_query_checksum;<br />/</div>
<br />
<br />
<br /><br />
Demo of usage:<br />
<div style="background-color: #eeeeee; font-family: "Courier New",Courier,monospace;">
SELECT get_query_checksum (p_query => 'SELECT * FROM all_objects') AS checksum<br />
FROM dual;</div>
Philhttp://www.blogger.com/profile/01851424617108510453noreply@blogger.com0tag:blogger.com,1999:blog-5882766990283082528.post-22450214045337958602013-10-03T17:14:00.000-04:002013-10-03T17:14:27.678-04:00RESOLVE_UTIL - A way to programmatically resolve object details - such as schema, qualified identifier, etc.While working in Datawarehousing - and with PL/SQL in particular, it is very handy to be able to resolve object owners for the purposes of clearly identifying which object you are actually performing SELECT, or DML operations on. It is also very handy for logging purposes - as it eliminates any doubt (such as a messed up synonym, etc.)
It is also nice to be able to resolve an object owner for a caller in an invoker's rights routine - so that the procedure operates on THEIR copy of an object - much like a SELECT statement would if they didn't qualify the table name.
So - for this reason - I developed RESOLVE_UTIL - a PL/SQL package which simplifies the DBMS_UTILITY.NAME_RESOLVE procedure and wraps it with very handy functions.
It also works across database links!
For example - if I want to see who owns the "EMP" table - I just run this query:
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>
SELECT resolve_util.resolve_table_schema (p_table_identifier => 'EMP') AS emp_owner
FROM dual;
</code></pre>
If I really want to see if "SCOTT.EMP" is a table in SCOTT's schema, and not a synonym pointing somewhere else - I can run:
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>
SELECT resolve_util.resolve_table_qualified_name (p_table_identifier => 'SCOTT.EMP') AS x
FROM dual;
</code></pre>
There are many more uses of this - which I will elaborate on later...
It has been tested in 11gR2 (11.2.0.3).
Anyway - here is the source code:
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>
create or replace
FUNCTION "BUILD_STRING" (p_str IN VARCHAR2
, p1 IN VARCHAR2
, p2 IN VARCHAR2 DEFAULT NULL
, p3 IN VARCHAR2 DEFAULT NULL
, p4 IN VARCHAR2 DEFAULT NULL
, p5 IN VARCHAR2 DEFAULT NULL
, p6 IN VARCHAR2 DEFAULT NULL
, p7 IN VARCHAR2 DEFAULT NULL
, p8 IN VARCHAR2 DEFAULT NULL
, p9 IN VARCHAR2 DEFAULT NULL
, p10 IN VARCHAR2 DEFAULT NULL
, p_null_string IN VARCHAR2 DEFAULT '(null)'
, p_cleanup_extra_newlines IN BOOLEAN DEFAULT TRUE)
RETURN VARCHAR2 AS
-- Constants
oracr CONSTANT VARCHAR2 (1) := CHR (10);
-- Variables
l_temp_str VARCHAR2 (4000);
l_replace_str_array dbms_sql.varchar2a;
BEGIN
l_temp_str := p_str;
-- Load the "P#" arguments into an array...
l_replace_str_array (1) := p1;
l_replace_str_array (2) := p2;
l_replace_str_array (3) := p3;
l_replace_str_array (4) := p4;
l_replace_str_array (5) := p5;
l_replace_str_array (6) := p6;
l_replace_str_array (7) := p7;
l_replace_str_array (8) := p8;
l_replace_str_array (9) := p9;
l_replace_str_array (10) := p10;
-- Loop through the input string - relacing any "%s" strings with its corresponding "P{number}" argument value...
FOR i IN 1 .. 10 LOOP
l_temp_str := REGEXP_REPLACE (l_temp_str, '%s', NVL (l_replace_str_array (i), p_null_string), 1, 1);
END LOOP;
-- Now replace any "\n" characters with the real new-line character...
l_temp_str := REPLACE (l_temp_str, '\n', oracr);
IF p_cleanup_extra_newlines THEN
l_temp_str := REGEXP_REPLACE (l_temp_str, '(' || CHR(10) || ')\1+', '\1');
END IF;
RETURN l_temp_str;
END build_string;
/
/* =========================================================================================== */
create or replace
PACKAGE "RESOLVE_UTIL"
AUTHID CURRENT_USER
AS
/*************************************************************************
* Program : resolve_util
* Version : 1.0
* Author : Philip Moore - Dunnhumby USA
* Date : 29-JAN-2013
* Purpose : This package contains utilies to help resolve
* Oracle object details, such as the schema,
* the database link, real object name, etc.
* It allows programmatic emulation of the SQL resolving engine.
* This package makes it easy for example to see who owns a Table
* or View - so you can constrain the "OWNER" column in the
* Data Dictionary views just by knowing the object name.
*
* Change History
* Date Programmer Description
* -----------------------------------------------------------------------
* 29-JAN-2013 Philip Moore Initial Version
*
*************************************************************************/
-- Constants
c_package_name CONSTANT VARCHAR2 (100) := 'RESOLVE_UTIL';
c_pkg_proc_or_function_ctxt CONSTANT PLS_INTEGER := 1;
c_table_view_mview_or_seq_ctxt CONSTANT PLS_INTEGER := 2;
c_type_ctxt CONSTANT PLS_INTEGER := 3;
c_synonym_ctxt CONSTANT PLS_INTEGER := 5;
c_procedure_top_level_ctxt CONSTANT PLS_INTEGER := 7;
c_function_top_level_ctxt CONSTANT PLS_INTEGER := 8;
c_package_ctxt CONSTANT PLS_INTEGER := 9;
c_index_ctxt CONSTANT PLS_INTEGER := 9;
-- Custom Types
TYPE object_record_type IS RECORD (
schema all_objects.owner%TYPE
, name all_objects.object_name%TYPE
, db_link all_db_links.db_link%TYPE
, object_number all_objects.object_id%TYPE
, object_type all_objects.object_type%TYPE
, qualified_object_name VARCHAR2 (100)
);
-- Global Variables
g_debug_mode BOOLEAN := FALSE;
-- Procedures and Functions
FUNCTION build_db_link_string (
p_db_link IN VARCHAR2
)
RETURN VARCHAR2;
PROCEDURE name_resolve (
name IN VARCHAR2
, CONTEXT IN NUMBER
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
, SCHEMA OUT VARCHAR2
, part1 OUT VARCHAR2
, part2 OUT VARCHAR2
, dblink OUT VARCHAR2
, part1_type OUT NUMBER
, object_number OUT NUMBER
);
/* ------------------------------------------------------------ */
FUNCTION resolve_object (
p_object_identifier IN VARCHAR2
, p_object_type IN all_objects.object_type%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN object_record_type;
/* ------------------------------------------------------------ */
-- Overloaded to allow resolution by supplying the name, type, and schema...
FUNCTION resolve_object (
p_object_name IN all_objects.object_name%TYPE
, p_object_type IN all_objects.object_type%TYPE
, p_object_schema IN all_objects.owner%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN object_record_type;
/* ------------------------------------------------------------ */
FUNCTION resolve_object_schema (
p_object_identifier IN VARCHAR2
, p_object_type IN all_objects.object_type%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN all_objects.owner%TYPE;
/* ------------------------------------------------------------ */
FUNCTION resolve_object_type (
p_object_identifier IN VARCHAR2
, p_object_type IN all_objects.object_type%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN all_objects.object_type%TYPE;
/* ------------------------------------------------------------ */
FUNCTION resolve_object_name (
p_object_identifier IN VARCHAR2
, p_object_type IN all_objects.object_type%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN all_objects.object_name%TYPE;
/* ------------------------------------------------------------ */
FUNCTION resolve_object_db_link (
p_object_identifier IN VARCHAR2
, p_object_type IN all_objects.object_type%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN all_db_links.db_link%TYPE;
/* ------------------------------------------------------------ */
FUNCTION resolve_object_number (
p_object_identifier IN VARCHAR2
, p_object_type IN all_objects.object_type%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN all_objects.object_id%TYPE;
/* ------------------------------------------------------------ */
FUNCTION resolve_object_qualified_name (
p_object_identifier IN VARCHAR2
, p_object_type IN all_objects.object_type%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN VARCHAR2;
/* ------------------------------------------------------------ */
FUNCTION get_identifier_string (p_object_name IN all_objects.object_name%TYPE
, p_object_type IN all_objects.object_type%TYPE
, p_object_schema IN all_objects.owner%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN VARCHAR2;
/* ------------------------------------------------------------ */
-- Also works for views...
FUNCTION resolve_table_schema (
p_table_identifier IN VARCHAR2
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN all_tables.owner%TYPE;
/* ------------------------------------------------------------ */
-- Also works for views...
FUNCTION resolve_table_qualified_name (
p_table_identifier IN VARCHAR2
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN VARCHAR2;
/* ------------------------------------------------------------ */
-- Also works for views...
FUNCTION get_table_identifier_string (p_table_name IN all_tables.table_name%TYPE
, p_table_schema IN all_tables.owner%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN VARCHAR2;
/* ------------------------------------------------------------ */
FUNCTION resolve_table_object (
p_table_identifier IN VARCHAR2
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN object_record_type;
/* ------------------------------------------------------------ */
-- Overloaded to allow resolution by supplying the name and schema...
FUNCTION resolve_table_object (
p_table_name IN all_objects.object_name%TYPE
, p_table_schema IN all_objects.owner%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN object_record_type;
END RESOLVE_UTIL;
/
/* =========================================================================================== */
create or replace
PACKAGE BODY "RESOLVE_UTIL" AS
/*************************************************************************
* Program : resolve_util
* Version : 1.0
* Author : Philip Moore - Dunnhumby USA
* Date : 29-JAN-2013
* Purpose : This package contains utilies to help resolve
* Oracle object details, such as the schema,
* the database link, real object name, etc.
* It allows programmatic emulation of the SQL resolving engine.
* This package makes it easy for example to see who owns a Table
* or View - so you can constraint the "OWNER" column in the
* Data Dictionary views just by knowing the object name.
*
* Change History
* Date Programmer Description
* -----------------------------------------------------------------------
* 29-JAN-2013 Philip Moore Initial Version
*
*************************************************************************/
/* ------------------------------------------------------------ */
FUNCTION build_db_link_string (
p_db_link IN VARCHAR2
)
RETURN VARCHAR2 IS
-- Variables
l_return VARCHAR2 (100);
BEGIN
IF p_db_link IS NOT NULL THEN
l_return := '@' || LTRIM (p_db_link, '@');
END IF;
RETURN l_return;
END build_db_link_string;
/* ------------------------------------------------------------ */
PROCEDURE name_resolve (
name IN VARCHAR2
, CONTEXT IN NUMBER
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
, SCHEMA OUT VARCHAR2
, part1 OUT VARCHAR2
, part2 OUT VARCHAR2
, dblink OUT VARCHAR2
, part1_type OUT NUMBER
, object_number OUT NUMBER
) AS
-- Constants
c_program_name CONSTANT VARCHAR2 (100) := c_package_name || '.NAME_RESOLVE';
-- Variables
l_plsql_call VARCHAR2 (4000);
l_db_link VARCHAR2 (100) := build_db_link_string (p_db_link => db_link);
BEGIN
l_plsql_call :=
'BEGIN '
|| ' DBMS_UTILITY.NAME_RESOLVE'
|| l_db_link
|| ' ( name => :name'
|| ' , context => :context'
|| ' , schema => :schema'
|| ' , part1 => :part1'
|| ' , part2 => :part2'
|| ' , dblink => :dblink'
|| ' , part1_type => :part1_type'
|| ' , object_number => :object_number'
|| ' ); '
|| 'END;';
EXECUTE IMMEDIATE l_plsql_call
USING IN name
, IN CONTEXT
, OUT SCHEMA
, OUT part1
, OUT part2
, OUT dblink
, OUT part1_type
, OUT object_number;
IF g_debug_mode THEN
dbms_output.put_line (build_string (p_str => '%s -> DBMS_UTILITY.NAME_RESOLVE%s call result: '
|| '\n ( name /* IN */ => ''%s'''
|| '\n , context /* IN */ => %s'
|| '\n , schema /* OUT */ => ''%s'''
|| '\n , part1 /* OUT */ => ''%s'''
|| '\n , part2 /* OUT */ => ''%s'''
|| '\n , dblink /* OUT */ => ''%s'''
|| '\n , part1_type /* OUT */ => %s'
|| '\n , object_number /* OUT */ => %s'
|| '\n ); '
, p1 => c_program_name
, p2 => l_db_link
, p3 => name
, p4 => CONTEXT
, p5 => SCHEMA
, p6 => part1
, p7 => part2
, p8 => dblink
, p9 => part1_type
, p10 => object_number
, p_null_string => NULL
));
END IF;
END name_resolve;
/* ------------------------------------------------------------ */
FUNCTION get_object_type_context (
p_object_type IN all_objects.object_type%TYPE
)
RETURN PLS_INTEGER IS
-- Variables
l_return PLS_INTEGER;
BEGIN
l_return :=
CASE UPPER (TRIM (p_object_type))
WHEN 'PACKAGE' THEN c_pkg_proc_or_function_ctxt
WHEN 'PROCEDURE' THEN c_pkg_proc_or_function_ctxt
WHEN 'FUNCTION' THEN c_pkg_proc_or_function_ctxt
WHEN 'TABLE' THEN c_table_view_mview_or_seq_ctxt
WHEN 'VIEW' THEN c_table_view_mview_or_seq_ctxt
WHEN 'SEQUENCE' THEN c_table_view_mview_or_seq_ctxt
WHEN 'MATERIALIZED VIEW' THEN c_table_view_mview_or_seq_ctxt
WHEN 'TYPE' THEN c_type_ctxt
WHEN 'SYNONYM' THEN c_synonym_ctxt
WHEN 'INDEX' THEN c_index_ctxt
END;
RETURN l_return;
END get_object_type_context;
/* ------------------------------------------------------------ */
FUNCTION resolve_object (
p_object_identifier IN VARCHAR2
, p_object_type IN all_objects.object_type%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN object_record_type AS
-- Variables
l_context NUMBER;
l_schema_out all_objects.owner%TYPE;
l_part1_out VARCHAR2 (255);
l_part2_out VARCHAR2 (255);
l_db_link_out all_db_links.db_link%TYPE;
l_part1_type_out NUMBER;
l_object_number_out NUMBER;
l_return object_record_type;
l_db_link VARCHAR2 (100) := build_db_link_string (p_db_link => db_link);
l_sql_stmt VARCHAR2 (255);
BEGIN
name_resolve (name => p_object_identifier
, CONTEXT => get_object_type_context (p_object_type => p_object_type)
, db_link => db_link
, SCHEMA => l_schema_out
, part1 => l_part1_out
, part2 => l_part2_out
, dblink => l_db_link_out
, part1_type => l_part1_type_out
, object_number => l_object_number_out
);
l_return.SCHEMA := l_schema_out;
l_return.name := l_part1_out;
l_return.db_link := l_db_link_out;
l_return.object_number := l_object_number_out;
l_sql_stmt := 'SELECT object_type
FROM all_objects' || l_db_link || '
WHERE object_id = :l_object_number_out';
-- Get the Object Type if possible... (doesn't work if the object is in a remote database)
BEGIN
EXECUTE IMMEDIATE l_sql_stmt
INTO l_return.object_type
USING l_object_number_out;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_return.object_type := NULL;
END;
l_return.qualified_object_name := DBMS_ASSERT.enquote_name (str => l_return.SCHEMA
, capitalize => FALSE
)
|| '.'
|| DBMS_ASSERT.enquote_name (str => l_return.name
, capitalize => FALSE
)
|| l_db_link;
RETURN l_return;
END resolve_object;
/* ------------------------------------------------------------ */
-- Overloaded to allow resolution by supplying the name, type, and schema...
FUNCTION resolve_object (
p_object_name IN all_objects.object_name%TYPE
, p_object_type IN all_objects.object_type%TYPE
, p_object_schema IN all_objects.owner%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN object_record_type AS
BEGIN
RETURN resolve_object (p_object_identifier => DBMS_ASSERT.enquote_name (str => p_object_schema)
|| '.'
|| DBMS_ASSERT.enquote_name (str => p_object_name)
, p_object_type => p_object_type
, db_link => db_link
);
END resolve_object;
/* ------------------------------------------------------------ */
FUNCTION resolve_object_schema (
p_object_identifier IN VARCHAR2
, p_object_type IN all_objects.object_type%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN all_objects.owner%TYPE AS
-- Variables
l_object_record object_record_type;
l_return all_objects.owner%TYPE;
BEGIN
l_object_record := resolve_object (p_object_identifier => p_object_identifier
, p_object_type => p_object_type
, db_link => db_link
);
l_return := l_object_record.SCHEMA;
RETURN l_return;
END resolve_object_schema;
/* ------------------------------------------------------------ */
FUNCTION resolve_object_type (
p_object_identifier IN VARCHAR2
, p_object_type IN all_objects.object_type%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN all_objects.object_type%TYPE AS
-- Variables
l_object_record object_record_type;
l_return all_objects.object_type%TYPE;
BEGIN
l_object_record := resolve_object (p_object_identifier => p_object_identifier
, p_object_type => p_object_type
, db_link => db_link
);
l_return := l_object_record.object_type;
RETURN l_return;
END resolve_object_type;
/* ------------------------------------------------------------ */
FUNCTION resolve_object_name (
p_object_identifier IN VARCHAR2
, p_object_type IN all_objects.object_type%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN all_objects.object_name%TYPE AS
-- Variables
l_object_record object_record_type;
l_return all_objects.object_name%TYPE;
BEGIN
l_object_record := resolve_object (p_object_identifier => p_object_identifier
, p_object_type => p_object_type
, db_link => db_link
);
l_return := l_object_record.name;
RETURN l_return;
END resolve_object_name;
/* ------------------------------------------------------------ */
FUNCTION resolve_object_db_link (
p_object_identifier IN VARCHAR2
, p_object_type IN all_objects.object_type%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN all_db_links.db_link%TYPE AS
-- Variables
l_object_record object_record_type;
l_return all_db_links.db_link%TYPE;
BEGIN
l_object_record := resolve_object (p_object_identifier => p_object_identifier
, p_object_type => p_object_type
, db_link => db_link
);
l_return := l_object_record.db_link;
RETURN l_return;
END resolve_object_db_link;
/* ------------------------------------------------------------ */
FUNCTION resolve_object_number (
p_object_identifier IN VARCHAR2
, p_object_type IN all_objects.object_type%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN all_objects.object_id%TYPE AS
-- Variables
l_object_record object_record_type;
l_return all_objects.object_id%TYPE;
BEGIN
l_object_record := resolve_object (p_object_identifier => p_object_identifier
, p_object_type => p_object_type
, db_link => db_link
);
l_return := l_object_record.object_number;
RETURN l_return;
END resolve_object_number;
/* ------------------------------------------------------------ */
FUNCTION resolve_object_qualified_name (
p_object_identifier IN VARCHAR2
, p_object_type IN all_objects.object_type%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN VARCHAR2 AS
-- Variables
l_object_record object_record_type;
l_return VARCHAR2 (100);
BEGIN
l_object_record := resolve_object (p_object_identifier => p_object_identifier
, p_object_type => p_object_type
, db_link => db_link
);
l_return := l_object_record.qualified_object_name;
RETURN l_return;
END resolve_object_qualified_name;
/* ------------------------------------------------------------ */
FUNCTION get_identifier_string (p_object_name IN all_objects.object_name%TYPE
, p_object_type IN all_objects.object_type%TYPE
, p_object_schema IN all_objects.owner%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN VARCHAR2
IS
-- Variables
l_return VARCHAR2 (255);
l_object_record object_record_type;
BEGIN
l_object_record := resolve_object (p_object_name => p_object_name
, p_object_type => p_object_type
, p_object_schema => p_object_schema
, db_link => db_link
);
l_return := l_object_record.qualified_object_name;
RETURN l_return;
END get_identifier_string;
/* ------------------------------------------------------------ */
-- Also works for views...
FUNCTION resolve_table_schema (
p_table_identifier IN VARCHAR2
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN all_tables.owner%TYPE AS
BEGIN
RETURN resolve_object_schema (p_object_identifier => p_table_identifier
, p_object_type => 'TABLE'
, db_link => db_link
);
END resolve_table_schema;
/* ------------------------------------------------------------ */
-- Also works for views
FUNCTION resolve_table_qualified_name (
p_table_identifier IN VARCHAR2
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN VARCHAR2 AS
BEGIN
RETURN resolve_object_qualified_name (p_object_identifier => p_table_identifier
, p_object_type => 'TABLE'
, db_link => db_link
);
END resolve_table_qualified_name;
/* ------------------------------------------------------------ */
-- Also works for views...
FUNCTION get_table_identifier_string (p_table_name IN all_tables.table_name%TYPE
, p_table_schema IN all_tables.owner%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN VARCHAR2
IS
BEGIN
RETURN get_identifier_string (p_object_name => p_table_name
, p_object_type => 'TABLE'
, p_object_schema => p_table_schema
, db_link => db_link
);
END get_table_identifier_string;
/* ------------------------------------------------------------ */
FUNCTION resolve_table_object (
p_table_identifier IN VARCHAR2
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN object_record_type
IS
BEGIN
RETURN resolve_object (p_object_identifier => p_table_identifier
, p_object_type => 'TABLE'
, db_link => db_link
);
END resolve_table_object;
/* ------------------------------------------------------------ */
-- Overloaded to allow resolution by supplying the name and schema...
FUNCTION resolve_table_object (
p_table_name IN all_objects.object_name%TYPE
, p_table_schema IN all_objects.owner%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN object_record_type
IS
BEGIN
RETURN resolve_object (p_object_name => p_table_name
, p_object_schema => p_table_schema
, p_object_type => 'TABLE'
, db_link => db_link
);
END resolve_table_object;
/* ------------------------------------------------------------ */
END resolve_util;
/
</code></pre>Philhttp://www.blogger.com/profile/01851424617108510453noreply@blogger.com0tag:blogger.com,1999:blog-5882766990283082528.post-59393245660022927542013-09-06T09:58:00.000-04:002013-09-06T10:02:12.518-04:00I'm speaking at Oracle OpenWorld 2013!I'll be speaking at Oracle OpenWorld 2013! My session: <a href="https://oracleus.activeevents.com/2013/connect/sessionDetail.ww?SESSION_ID=3412">CON3412 - Extreme Performance with <i>Very</i> Big Data, Using Oracle Exadata X3-8</a> - will be on September Thursday, Sep 26, 11:00 AM - 12:00 PM PDT at Moscone South - 300.
I hope to see you there!
<a href="http://www.oracle.com/go/?&Src=7328809&Act=403&pcode=WWMK11054264MPP001" imageanchor="1" ><img border="0" src="http://www.oracleimg.com/us/dm/h2fy11/183033-oow-tlkt-imspeaking-160x160-1951074.gif" /></a>
Philhttp://www.blogger.com/profile/01851424617108510453noreply@blogger.com0tag:blogger.com,1999:blog-5882766990283082528.post-71594237093479694832010-06-22T15:12:00.001-04:002011-02-01T16:33:52.109-05:00Tom Kyte's Pro*C "array_flat" CSV file Unloader - with enhancementsHi all,<br /><br />It's been a long time since I've posted (I've been a lazy blogger).<br /><br />Recently I had the need to unload a CSV file from a rather complex query in Oracle 10g Release 2. I just could not find a SQL*PLus solution that suited me, so I stumbled upon Tom Kyte's "array_flat" solution - found here in its orginal glory:<br /><a href="http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:459020243348">http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:459020243348</a><br /><br />Tom's solution uses Pro*C code to connect to Oracle and then dump the output of a query to the stdout, while dumping the query text and column headings to the stderr.<br /><br />Tom's solution of course is the bomb, but I wanted to improve upon it by adding the ability to change the delimiter character, and optionally specify an enclosure character (such as double-quote for example), and also have the ability to designate what should be output in the case of NULL values.<br /><br />I'm VERY new to Pro*C programming, and I suspect others are too - so I'm posting in hopes that this helps some other poor souls that are Pro*C newbs also.<br /><br />Ok - so here's the modified code - <strong>shamelessly</strong> taken from Tom's code found in the URL above - and modified to add the enhancements mentioned earlier:<br /><br /><pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000; background-color: #eee;font-size: 12px;border: 1px dashed #999999;line-height: 14px;padding: 5px; overflow: auto; width: 100%"><code>#include <stdio.h><br />#include <string.h><br />#include <ctype.h><br />#include <stdlib.h><br /><br />#define MAX_VNAME_LEN 30<br />#define MAX_INAME_LEN 30<br /><br />static char * USERID = NULL;<br />static char * SQLSTMT = NULL;<br />static char * ARRAY_SIZE = "10";<br />static char * DELIMITER = ",";<br />static char * ENCLOSURE = "\"";<br />static char * NULL_STRING = NULL;<br /><br />#define vstrcpy( a, b ) \<br />(strcpy( a.arr, b ), a.len = strlen( a.arr ), a.arr)<br /><br />EXEC SQL INCLUDE sqlca;<br />EXEC SQL INCLUDE sqlda;<br /><br />extern SQLDA *sqlald();<br />extern void sqlclu();<br /><br /><br /><br />static void die( char * msg )<br />{<br /> fprintf( stderr, "%s\n", msg );<br /> exit(1);<br />}<br /><br /><br />/*<br /> this array contains a default mapping<br /> I am using to constrain the<br /> lengths of returned columns. It is mapping,<br /> for example, the Oracle<br /> NUMBER type (type code = 2) to be 45 characters<br /> long in a string.<br />*/<br /><br />static int lengths[] =<br />{ -1, 0, 45, 0, 0, 0, 0, 0, 2000, 0, 0,<br /> 18, 25, 0, 0, 0, 0, 0, 0, 0, 0,<br /> 0, 0, 512, 2000 };<br /><br /><br />static void process_parms( argc, argv )<br />int argc;<br />char * argv[];<br />{<br />int i;<br /><br /> for( i = 1; i < argc; i++ )<br /> {<br /> if ( !strncmp( argv[i], "userid=", 7 ) )<br /> USERID = argv[i]+7;<br /> else<br /> if ( !strncmp( argv[i], "sqlstmt=", 8 ) )<br /> SQLSTMT = argv[i]+8;<br /> else<br /> if ( !strncmp( argv[i], "arraysize=", 10 ) )<br /> ARRAY_SIZE = argv[i]+10;<br /> else<br /> if ( !strncmp( argv[i], "delimiter=", 10 ) )<br /> DELIMITER = argv[i]+10;<br /> else<br /> if ( !strncmp( argv[i], "enclosure=", 10 ) )<br /> ENCLOSURE = argv[i]+10;<br /> else<br /> if ( !strncmp( argv[i], "null_string=", 12 ) )<br /> NULL_STRING = argv[i]+12;<br /> else<br /> {<br /> fprintf( stderr,<br /> "usage: %s %s %s %s %s %s\n",<br /> argv[0],<br /> "userid=xxx/xxx sqlstmt=query ",<br /> "arraysize=<NN> ",<br /> "delimiter=x ",<br /> "enclosure=x ",<br /> "null_string=x ");<br /> exit(1);<br /> }<br /> }<br /> if ( USERID == NULL || SQLSTMT == NULL )<br /> {<br /> fprintf( stderr,<br /> "usage: %s %s %s %s %s %s\n",<br /> argv[0],<br /> "userid=xxx/xxx sqlstmt=query ",<br /> "arraysize=<NN> ",<br /> "delimiter=x ",<br /> "enclosure=x ",<br /> "null_string=x ");<br /> exit(1);<br /> }<br />}<br /><br />static void sqlerror_hard()<br />{<br /> EXEC SQL WHENEVER SQLERROR CONTINUE;<br /><br /> fprintf(stderr,"\nORACLE error detected:");<br /> fprintf(stderr,"\n% .70s \n", sqlca.sqlerrm.sqlerrmc);<br /><br /> EXEC SQL ROLLBACK WORK RELEASE;<br /> exit(1);<br />}<br /><br /><br /><br />static SQLDA * process_1(char * sqlstmt, int array_size, char * delimiter, char * enclosure )<br />{<br />SQLDA * select_dp;<br />int i;<br />int j;<br />int null_ok;<br />int precision;<br />int scale;<br />int size = 10;<br /><br /> fprintf( stderr, "Unloading '%s'\n", sqlstmt );<br /> fprintf( stderr, "Array size = %d\n", array_size );<br /><br /><br /> EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();<br /> EXEC SQL PREPARE S FROM :sqlstmt;<br /> EXEC SQL DECLARE C CURSOR FOR S;<br /><br /> if ((select_dp = sqlald(size,MAX_VNAME_LEN,MAX_INAME_LEN))<br /> == NULL )<br /> die( "Cannot allocate memory for select descriptor." );<br /><br /> select_dp->N = size;<br /> EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;<br /> if ( !select_dp->F ) return NULL;<br /><br /> if (select_dp->F < 0)<br /> {<br /> size = -select_dp->F;<br /> sqlclu( select_dp );<br /> if ((select_dp =<br /> sqlald (size, MAX_VNAME_LEN, MAX_INAME_LEN))<br /> == NULL )<br /> die( "Cannot allocate memory for descriptor." );<br /> EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;<br /> }<br /> select_dp->N = select_dp->F;<br /><br /> for (i = 0; i < select_dp->N; i++)<br /> select_dp->I[i] = (short *) malloc(sizeof(short) *<br /> array_size );<br /><br /> for (i = 0; i < select_dp->F; i++)<br /> {<br /> sqlnul (&(select_dp->T[i]),<br /> &(select_dp->T[i]), &null_ok);<br /> if ( select_dp->T[i] <<br /> sizeof(lengths)/sizeof(lengths[0]) )<br /> {<br /> if ( lengths[select_dp->T[i]] )<br /> select_dp->L[i] = lengths[select_dp->T[i]];<br /> else select_dp->L[i] += 5;<br /> }<br /> else select_dp->L[i] += 5;<br /><br /> select_dp->T[i] = 5;<br /> select_dp->V[i] = (char *)malloc( select_dp->L[i] *<br /> array_size );<br /><br /> for( j = MAX_VNAME_LEN-1;<br /> j > 0 && select_dp->S[i][j] == ' ';<br /> j--);<br /> fprintf (stderr,<br /> "%s%.*s", i?",":"", j+1, select_dp->S[i]);<br /> }<br /> fprintf( stderr, "\n" );<br /><br /><br /> EXEC SQL OPEN C;<br /> return select_dp;<br />}<br /><br /><br />static void process_2( SQLDA * select_dp, int array_size, char * delimiter, char * enclosure, char * null_string )<br />{<br />int last_fetch_count;<br />int row_count = 0;<br />short ind_value;<br />char * char_ptr;<br />int i,<br /> j;<br /><br /> for ( last_fetch_count = 0;<br /> ;<br /> last_fetch_count = sqlca.sqlerrd[2] )<br /> {<br /> EXEC SQL FOR :array_size FETCH C<br /> USING DESCRIPTOR select_dp;<br /><br /> for( j=0; j < sqlca.sqlerrd[2]-last_fetch_count; j++ )<br /> {<br /> for (i = 0; i < select_dp->F; i++)<br /> {<br /> ind_value = *(select_dp->I[i]+j);<br /> char_ptr = select_dp->V[i] +<br /> (j*select_dp->L[i]);<br /><br /> printf( "%s%s%s%s", i?delimiter:"",<br /> enclosure,<br /> ind_value?null_string:char_ptr,<br /> enclosure );<br /> }<br /> row_count++;<br /> printf( "\n" );<br /> }<br /> if ( sqlca.sqlcode > 0 ) break;<br /> }<br /><br /> sqlclu(select_dp);<br /><br /> EXEC SQL CLOSE C;<br /><br /> EXEC SQL COMMIT WORK;<br /> fprintf( stderr, "%d rows extracted\n", row_count );<br />}<br /><br /><br /><br />main( argc, argv )<br />int argc;<br />char * argv[];<br />{<br />EXEC SQL BEGIN DECLARE SECTION;<br />VARCHAR oracleid[50];<br />EXEC SQL END DECLARE SECTION;<br />SQLDA * select_dp;<br /><br /><br /> process_parms( argc, argv );<br /><br /> /* Connect to ORACLE. */<br /> vstrcpy( oracleid, USERID );<br /><br /> EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();<br /><br /> EXEC SQL CONNECT :oracleid;<br /> fprintf(stderr, "\nConnected to ORACLE as user: %s\n\n",<br /> oracleid.arr);<br /><br /> EXEC SQL ALTER SESSION<br /> SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';<br /><br /> select_dp = process_1( SQLSTMT, atoi(ARRAY_SIZE), DELIMITER, ENCLOSURE );<br /> process_2( select_dp , atoi(ARRAY_SIZE), DELIMITER, ENCLOSURE, NULL_STRING );<br /><br /> /* Disconnect from ORACLE. */<br /> EXEC SQL COMMIT WORK RELEASE;<br /> exit(0);<br />}<br /><br /></code></pre><br /><br />Ok - now you may ask - how the heck do I compile the thing? This is where I had my trouble as well.<br /><br />Well - Here are the steps I took to get it to compile and working:<br />1) Save the code to a file - in my case I chose "unloader.pc" <br />2) Create a folder in your home directory on the UNIX server where you are going to compile this code. For example: "mkdir /home/phil/unloader".<br />3) FTP the file in ascii mode to the UNIX server directory created in step 2 above. Make sure that the server has the Oracle client installed. In my case I used Oracle 10g Release 2 client (10.2.0.3). Make sure you've run ". oraenv" to have all of your Oracle environment variables set up!<br />4) Copy the contents of directory $ORACLE_HOME/precomp/demo/proc/ to my directory created in step 2 - example unix command:<br />"cp $ORACLE_HOME/precomp/demo/proc/*.* /home/phil/unloader"<br />5) Modify your personal copy of the "demo_proc.mk" file - by adding our new Pro*C file to the list defined by the keyword "SAMPLES" - like so:<br /><br /><pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000; background-color: #eee;font-size: 12px;border: 1px dashed #999999;line-height: 14px;padding: 5px; overflow: auto; width: 100%"><code><br /># SAMPLES is a list of the c proc sample programs.<br /># CPPSAMPLES is a list of the c++ proc sample programs.<br /># OBJECT_SAMPLES is a list of the proc sample programs using the new<br /># type features. You must install ott in addition to proc to run some<br /># of those samples.<br />SAMPLES=<em><strong>unloader</strong></em> sample1 sample2 sample3 sample4 sample6 sample7 sample8 \<br /> sample9 sample10 sample11 sample12 oraca sqlvcp cv_demo \<br /> ansidyn1 ansidyn2 cpdemo1 cpdemo2 scdemo1 scdemo2<br /><br /></code></pre><br /><br />6) Set your "LD_LIBRARY_PATH" environment variable to include: "$ORACLE_HOME/lib" (if it doesn't already have it) - you can do that like this:<br />export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib<br /><br />7) Now you're ready to compile - just issue this command:<br />make -f ./demo_proc.mk unloader<br /><br />After it completes (you may get warnings - but this is ok) - you should have an executable file called "unloader" with no extension. If you have this file - your compile worked - and you should be ready for execution.<br />-------------------------------<br /><br />Now for some execution examples:<br /><br /><strong>Example 1 - Unload a simple query against the dual table:</strong><pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000; background-color: #eee;font-size: 12px;border: 1px dashed #999999;line-height: 14px;padding: 5px; overflow: auto; width: 100%"><code><br />[phil@unixserver:/home/phil/unloader]<br />$ ./unloader userid="scott/tiger@db" sqlstmt="select * from dual" arraysize=100 \<br />> delimiter="|" enclosure='"' null_string="(null)"<br /><br />Connected to ORACLE as user: scott/tiger@db<br /><br />Unloading 'select * from dual'<br />Array size = 100<br />DUMMY<br />"X"<br />1 rows extracted<br /><br /></code></pre><br /><br /><strong>Example 2 - Unload a bigger SQL Query from a file - send the data to one file - and the query and column headings to another file:</strong><br />Step 1) Store our query in a file - like so:<br />echo "SELECT 1 AS num_field, 'ABC' AS text_field FROM dual" > ./query.sql<br />Step 2) Run this command:<br /><pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000; background-color: #eee;font-size: 12px;border: 1px dashed #999999;line-height: 14px;padding: 5px; overflow: auto; width: 100%"><code><br />[phil@unixserver:/home/phil/unloader]<br />$ ./unloader userid="scott/tiger@db" sqlstmt="`cat ./query.sql`" \<br />> arraysize=100 delimiter="|" enclosure='"' null_string="(null)" 1>./stdout.csv 2>./stderr.txt<br /></code></pre><br /><br />Here is the output from stdout.csv:<br /><pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000; background-color: #eee;font-size: 12px;border: 1px dashed #999999;line-height: 14px;padding: 5px; overflow: auto; width: 100%"><code>[phil@unixserver:/home/phil/unloader]<br />$ cat stdout.csv<br />"1"|"ABC"<br /></code></pre><br /><br />Here is the output from stderr.txt:<br /><pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000; background-color: #eee;font-size: 12px;border: 1px dashed #999999;line-height: 14px;padding: 5px; overflow: auto; width: 100%"><code>[phil@unixserver:/home/phil/unloader]<br />$ cat stderr.txt<br /><br />Connected to ORACLE as user: scott/tiger@db<br /><br />Unloading 'SELECT 1 AS num_field, 'ABC' AS text_field FROM dual'<br />Array size = 100<br />NUM_FIELD,TEXT_FIELD<br />1 rows extracted<br /><br /></code></pre><br /><br />-------------------<br /><br />I hope this has been useful. I know that Pro*C can be intimidating - hopefully this example can make it a little easier for you. I am still very new, and I know there is probably a WAY better way to compile the Pro*C code - but hey - this worked!<br /><br />This solution was tested on an HP-UX 64-bit Itanium server - and worked with both Oracle 9.2 and 10.2 Databases.<br /><br />Good luck, and God bless!.<br /><br />PhilPhilhttp://www.blogger.com/profile/01851424617108510453noreply@blogger.com4tag:blogger.com,1999:blog-5882766990283082528.post-47305062826762431522009-07-22T10:39:00.000-04:002009-07-22T10:52:13.468-04:00How to get the name of the Partition within a table that a ROWID comes from...Oracle's partitioning feature is extremely handy for allowing you to break up a huge table into smaller, more manageable pieces.<br /><br />I use it extensively for Datawarehousing - where it allows one to add and remove fact data partitioned by time/date very efficiently.<br /><br />What is hard, however - is to find out which partition a particular row comes from within a table. Well - not any more. Just create this handy little function to get the name of the partition that any row comes from (using the "ROWID" pseudocolumn).<br /><br />Here goes:<br /><br /><pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000; background-color: #eee;font-size: 12px;border: 1px dashed #999999;line-height: 14px;padding: 5px; overflow: auto; width: 100%"><code>CREATE OR REPLACE FUNCTION get_partition_name_for_rowid (<br /> p_rowid IN ROWID)<br /> RETURN VARCHAR2 AS<br /> -- Variables<br /> l_all_objects_rec all_objects%ROWTYPE;<br /> l_partition_name all_tab_partitions.partition_name%TYPE;<br /> -- Custom Exceptions<br /> rowid_not_from_a_partition EXCEPTION;<br />BEGIN<br /> SELECT /*+ FIRST_ROWS */<br /> *<br /> INTO l_all_objects_rec<br /> FROM all_objects<br /> WHERE data_object_id = dbms_mview.pmarker (p_rowid);<br /><br /> IF l_all_objects_rec.object_type = 'TABLE PARTITION' THEN<br /> l_partition_name := l_all_objects_rec.subobject_name;<br /> ELSE<br /> RAISE rowid_not_from_a_partition;<br /> END IF;<br /><br /> RETURN l_partition_name;<br />EXCEPTION<br /> WHEN rowid_not_from_a_partition THEN<br /> raise_application_error (-20101<br /> , build_string (p_input_string => 'ROWID: %s is in table: %s - which is not partitioned.'<br /> , str1 => p_rowid<br /> , str2 => l_all_objects_rec.object_name));<br /> WHEN OTHERS THEN<br /> RAISE;<br />END get_partition_name_for_rowid;<br />/<br /><br /></code></pre><br /><br /><br />Just run this SQL to test it:<br /><br /><pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000; background-color: #eee;font-size: 12px;border: 1px dashed #999999;line-height: 14px;padding: 5px; overflow: auto; width: 100%"><code>CREATE TABLE part_table<br /> (partition_key INTEGER<br /> , some_other_column VARCHAR2 (30)<br /> )<br />PARTITION BY RANGE (partition_key)<br /> ( PARTITION p1 VALUES LESS THAN (50)<br /> , PARTITION p2 VALUES LESS THAN (MAXVALUE)<br /> )<br />;<br /><br />INSERT INTO part_table<br />(partition_key, some_other_column)<br />SELECT ROWNUM<br /> , TO_CHAR (ROWNUM)<br /> FROM all_objects<br /> WHERE ROWNUM <= 100;<br /> <br />SELECT get_partition_name_for_rowid (ROWID) AS partition_name<br /> , partition_key<br /> , some_other_column<br /> FROM part_table<br /> WHERE partition_key BETWEEN 47 AND 52;<br /><br /></code></pre><br /><br />Semper FidelisPhilhttp://www.blogger.com/profile/01851424617108510453noreply@blogger.com0tag:blogger.com,1999:blog-5882766990283082528.post-18896866466184290412009-07-10T22:50:00.000-04:002009-07-10T23:03:32.384-04:00Handy Function for parsing strings in PL/SQL!One common function needed for PL/SQL is some sort of string parsing solution. For instance, if you want to parse out the "3rd" position of a string delimited by an "/" character.<br /><br />My buddy Todd had written a handy function in Visual Basic for Applications, and even converted it for use in Oracle. After I saw Tom Kyte use a combination of INSTR and SUBSTR to parse strings, I rewrote Todd's approach to perform a little better.<br /><br />Here's the code in case you are interested in this handy little string function:<br /><br /><pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000; background-color: #eee;font-size: 12px;border: 1px dashed #999999;line-height: 14px;padding: 5px; overflow: auto; width: 100%"><code>CREATE OR REPLACE FUNCTION parsestring (<br /> p_string IN VARCHAR2<br /> , p_delimiter IN VARCHAR2<br /> , p_position IN INTEGER<br />)<br />/**************************************************************<br /> Function: parsestring<br /><br /> Version: 2.0<br /><br /> Author: Philip Moore - (version 2.0)<br /><br /> Original Author: Todd Ward - (version 1.0)<br /> <br /> Date: 23-JAN-2006<br /> <br /> Description: This function's purpose is to return<br /> the "p_position"th portion of the string<br /> "p_string", delimited by "p_delimiter".<br /> <br /> Example call: parsestring('a/b/c', '/', 2) Returns: 'b'<br />**************************************************************/<br />RETURN VARCHAR2 <br />AUTHID CURRENT_USER<br />PARALLEL_ENABLE<br />DETERMINISTIC<br />AS<br />-- Variables<br /> v_txt VARCHAR2 (32767);<br />BEGIN<br /> v_txt := p_delimiter || p_string || p_delimiter;<br /> RETURN SUBSTR (v_txt<br /> , INSTR (v_txt<br /> , p_delimiter<br /> , 1<br /> , p_position<br /> ) + LENGTH (p_delimiter)<br /> , INSTR (v_txt<br /> , p_delimiter<br /> , 1<br /> , p_position + 1<br /> ) - INSTR (v_txt<br /> , p_delimiter<br /> , 1<br /> , p_position<br /> ) - LENGTH (p_delimiter)<br /> );<br />EXCEPTION<br /> WHEN OTHERS THEN<br /> RETURN NULL;<br />END parsestring;<br />/<br /><br />SHOW ERRORS;<br /><br /></code></pre><br /><br />Now - note that this will not perform as well as using the SUBSTR and INSTR combination of functions shown in the source code itself - because of the PL/SQL context switches when run in the SQL engine. But I personally think that it is MUCH easier to remember the syntax for calling "PARSESTRING" than for calling the complex SUBSTR/INSTR combination.<br /><br />I've used this function many thousands of times over the years - and I believe that you will find it handy as well.<br /><br />Good luck!Philhttp://www.blogger.com/profile/01851424617108510453noreply@blogger.com0tag:blogger.com,1999:blog-5882766990283082528.post-38058998474041658052009-06-23T16:10:00.000-04:002009-06-25T14:48:29.177-04:00REGEXP_REPLACE / REGEXP_LIKE for Oracle 9iI really love using the REGEXP_REPLACE function and REGEXP_LIKE operator in Oracle 10G - and have been spoiled by their ease of use as a result.<br /><br />One of my clients, however - is sort of "stuck" on Oracle 9iR2 due to regression testing cost requirements of an upgrade. The problem is that we still have to do development on the 9iR2 database - and I REALLY want my regular expressions!<br /><br />So - I figured that I would take a stab at writing my own REGEXP_REPLACE and REGEXP_LIKE functions.<br /><br />Here's the source code - in case you want the same functionality in Oracle 9iR2:<br /><br /><br /><pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000; background-color: #eee;font-size: 12px;border: 1px dashed #999999;line-height: 14px;padding: 5px; overflow: auto; width: 100%"><code>create or replace<br />FUNCTION regexp_replace (<br /> source_char IN VARCHAR2<br /> , pattern IN VARCHAR2<br /> , replace_string IN VARCHAR2<br /> , POSITION IN PLS_INTEGER DEFAULT 1<br /> , occurrence IN PLS_INTEGER DEFAULT 0<br /> , match_parameter IN VARCHAR2 DEFAULT NULL)<br /> RETURN VARCHAR2 AS<br />/*************************************************************************<br /> * Program : regexp_replace<br /> * Version : 1.0<br /> * Author : Philip Moore<br /> * Date : 20-JUN-2009 Anno Domini<br /> * Purpopse : This provides a pseudo "REGEXP_REPLACE" function for Oracle 9iR2<br /> * Warnings : Do NOT compile in an Oracle 10GR2 database (or above)!<br /> *************************************************************************/<br /> -- Variables<br /> l_source_string VARCHAR2 (32767);<br /> l_temp_string VARCHAR2 (32767);<br /> l_flags VARCHAR2 (10);<br /> l_occurrence PLS_INTEGER;<br /> l_end_of_pattern_pos PLS_INTEGER;<br /> l_string_pos PLS_INTEGER;<br />BEGIN<br /> -- Substr the source_char to start at the position specified<br /> l_source_string := SUBSTR (source_char, POSITION);<br /><br /> -- Set up the flags argument<br /> IF occurrence = 0 THEN<br /> l_flags := 'g';<br /> ELSE<br /> l_flags := NULL;<br /> END IF;<br /><br /> l_flags := l_flags || match_parameter;<br /><br /> -- Now replace the regular expression pattern globally if "g"<br /> IF INSTR (l_flags, 'g') > 0 THEN<br /> owa_pattern.CHANGE (line => l_source_string<br /> , from_str => pattern<br /> , to_str => replace_string<br /> , flags => l_flags);<br /> -- Not a global replace - loop until the "occurrence"th occurrence is replaced...<br /> ELSE<br /> l_string_pos := 0;<br /> l_occurrence := 0;<br /> WHILE l_string_pos < LENGTH (l_source_string) AND l_occurrence < occurrence LOOP<br /> l_string_pos := l_string_pos + 1;<br /><br /> l_end_of_pattern_pos := owa_pattern.amatch (line => l_source_string<br /> , from_loc => l_string_pos<br /> , pat => pattern<br /> , flags => match_parameter);<br /><br /> IF l_end_of_pattern_pos != 0 THEN<br /> l_occurrence := l_occurrence + 1;<br /> END IF;<br /> END LOOP;<br /><br /> IF l_occurrence = occurrence THEN<br /> l_temp_string := SUBSTR (l_source_string<br /> , l_string_pos<br /> , (l_end_of_pattern_pos - l_string_pos));<br /> owa_pattern.CHANGE (line => l_temp_string<br /> , from_str => pattern<br /> , to_str => replace_string<br /> , flags => l_flags);<br /> l_source_string := SUBSTR (l_source_string<br /> , 1<br /> , l_string_pos - 1) || l_temp_string || SUBSTR (l_source_string, l_end_of_pattern_pos);<br /> END IF;<br /> END IF;<br /><br /> -- Piece the string back together if needed...<br /> IF POSITION > 1 THEN<br /> l_source_string := SUBSTR (source_char<br /> , 1<br /> , (POSITION - 1)) || l_source_string;<br /> END IF;<br /><br /> RETURN l_source_string;<br />END regexp_replace;<br />/<br /><br /><br />CREATE OR REPLACE FUNCTION regexp_like (source_char IN VARCHAR2<br /> , pattern IN VARCHAR2<br /> , match_parameter IN VARCHAR2 DEFAULT NULL)<br />RETURN INTEGER<br />AS<br />/*************************************************************************<br /> * Program : regexp_like<br /> * Version : 1.0<br /> * Author : Philip Moore<br /> * Date : 20-JUN-2009 Anno Domini<br /> * Purpopse : This provides a pseudo "REGEXP_LIKE" operator for Oracle 9iR2<br /> * Warnings : Do NOT compile in an Oracle 10GR2 database (or above)!<br /> *************************************************************************/<br /> -- Variables<br /> l_return INTEGER;<br />BEGIN<br /> IF owa_pattern.match (line => source_char<br /> , pat => pattern<br /> , flags => match_parameter) THEN<br /> l_return := 1;<br /> ELSE<br /> l_return := 0;<br /> END IF;<br /><br /> RETURN l_return;<br />END regexp_like;<br />/<br /><br /></code></pre><br /><br /><span style="font-family:Courier New;"></span><br /><strong><span style="font-family:arial;">The Functions in Action:</span></strong><br /><span style="font-family:arial;">Here's a demo of my new Oracle 9iR2 REGEXP_REPLACE function:</span><br /><br /><pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000; background-color: #eee;font-size: 12px;border: 1px dashed #999999;line-height: 14px;padding: 5px; overflow: auto; width: 100%"><code>SELECT regexp_replace ('5138675309', '(\d{3})(\d{3})(\d{4})', '(\1) \2-\3') AS phone <br />FROM v$version <br />WHERE banner = 'Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production'; <br /><br />Result: <br />PHONE <br />---------------- <br />(513) 867-5309 <br /><br /></code></pre><br /><br /><span style="font-family:arial;"><strong></strong></span><br /><span style="font-family:arial;"><strong>Some Watchouts: </strong></span><br /><span style="font-family:arial;">The "OWA_PATTERN" package isn't as powerful as the built-in REGEXP% functions that Oracle 10g Provides - for example - it doesn't support the "" OR operator. It is also much slower than those natively built-in 10g functions - so don't use these to find the last digit in pi :)</span><br /><br /><span style="font-family:arial;">I hope these are helpful in your "stuck in Oracle 9i" endeavors!<br /><br /><strong>Special thanks </strong>to Greg Houston's <a href="http://formatmysourcecode.blogspot.com/">http://formatmysourcecode.blogspot.com/</a> for helping me format the code for blogger!<br /></span>Philhttp://www.blogger.com/profile/01851424617108510453noreply@blogger.com10tag:blogger.com,1999:blog-5882766990283082528.post-14297102750695157282009-06-18T12:50:00.001-04:002009-06-25T14:54:12.257-04:00DBMS_METADATA Across Database Links!As a Data Modeler and Developer, I've always wanted a very easy way to get Table/Function/Procedure/View DDL SQL from a remote database - via Database Link.<br /><br /><br /><br />The DBMS_METADATA.GET_DDL function is quite handy for getting DDL in the SAME database - but it isn't quite so easy to use to get DDL from a remote database.<br /><br /><br /><br />So - in the spirit of the USMC - I chose to adapt, improvise, and overcome - by writing my own interface to DBMS_METADATA - called "REMOTE_DBMS_METADATA".<br /><br /><br /><br />If you want the source code - here goes:<br /><pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000; background-color: #eee;font-size: 12px;border: 1px dashed #999999;line-height: 14px;padding: 5px; overflow: auto; width: 100%"><code>create or replace PACKAGE boolean_pkg<br />IS<br /> FUNCTION bool_to_str (boolean_in IN BOOLEAN)<br /> RETURN VARCHAR2;<br /><br /> FUNCTION str_to_bool (string_in IN VARCHAR2)<br /> RETURN BOOLEAN;<br /><br /> FUNCTION true_value<br /> RETURN VARCHAR2;<br /><br /> FUNCTION false_value<br /> RETURN VARCHAR2;<br />END boolean_pkg;<br />/<br /><br />SHOW ERRORS;<br /><br /><br />create or replace PACKAGE BODY boolean_pkg<br />IS<br /> c_true CONSTANT VARCHAR2 (5) := 'TRUE';<br /> c_false CONSTANT VARCHAR2 (5) := 'FALSE';<br /><br />/***************************************************/<br /> FUNCTION bool_to_str (boolean_in IN BOOLEAN)<br /> RETURN VARCHAR2<br /> IS<br /> BEGIN<br /> IF boolean_in<br /> THEN<br /> RETURN c_true;<br /> ELSIF NOT boolean_in<br /> THEN<br /> RETURN c_false;<br /> ELSE<br /> RETURN NULL;<br /> END IF;<br /> END bool_to_str;<br /><br />/***************************************************/<br /> FUNCTION str_to_bool (string_in IN VARCHAR2)<br /> RETURN BOOLEAN<br /> IS<br /> BEGIN<br /> IF string_in = c_true<br /> THEN<br /> RETURN TRUE;<br /> ELSIF string_in = c_false<br /> THEN<br /> RETURN FALSE;<br /> ELSE<br /> RETURN NULL;<br /> END IF;<br /> END str_to_bool;<br /><br />/***************************************************/<br /> FUNCTION true_value<br /> RETURN VARCHAR2<br /> IS<br /> BEGIN<br /> RETURN c_true;<br /> END true_value;<br /><br /> FUNCTION false_value<br /> RETURN VARCHAR2<br /> IS<br /> BEGIN<br /> RETURN c_false;<br /> END false_value;<br />/***************************************************/<br /><br />END boolean_pkg;<br />/<br /><br />SHOW ERRORS;<br /><br /><br />create or replace PACKAGE REMOTE_DBMS_METADATA AS<br />/*************************************************************************<br /> * Program : remote_dbms_metadata<br /> * Version : 1.0<br /> * Author : Philip Moore<br /> * Date : 20-JUN-2009 Anno Domini<br /> * Purpopse : This package provides access to DDL for objects in a remote<br /> Oracle database via Database Links.<br /> * Warnings : This package has only been tested in Oracle 9iR2 and 10gR2.<br /> *************************************************************************/<br /><br />-- Types<br /> TYPE session_transform_param_rec IS RECORD (parameter_name VARCHAR2 (50)<br /> , value_datatype PLS_INTEGER<br /> , varchar2_value VARCHAR2 (50)<br /> , boolean_value BOOLEAN<br /> , number_value NUMBER);<br /><br /> TYPE session_transform_params_type IS TABLE OF session_transform_param_rec INDEX BY VARCHAR2 (50);<br /> TYPE db_link_session_transform_type IS TABLE OF session_transform_params_type INDEX BY all_db_links.db_link%TYPE;<br /><br />-- Global Constants<br /> c_varchar2_type CONSTANT PLS_INTEGER := 1;<br /> c_boolean_type CONSTANT PLS_INTEGER := 2;<br /> c_number_type CONSTANT PLS_INTEGER := 3;<br /><br />-- Global Variables<br /> g_session_transform_params db_link_session_transform_type;<br /><br />-- Custom Exceptions<br /> handle_not_open EXCEPTION;<br /> PRAGMA EXCEPTION_INIT (handle_not_open, -31600);<br /><br />-- Procedures and Functions<br /> FUNCTION OPEN (<br /> db_link IN VARCHAR2 DEFAULT NULL<br /> , object_type IN VARCHAR2<br /> , version IN VARCHAR2 DEFAULT 'COMPATIBLE'<br /> , model IN VARCHAR2 DEFAULT 'ORACLE'<br /> )<br /> RETURN NUMBER;<br /><br /> PROCEDURE dbms_metadata_close (<br /> db_link IN VARCHAR2 DEFAULT NULL<br /> , handle IN NUMBER<br /> );<br /><br /> PROCEDURE set_count (<br /> db_link IN VARCHAR2 DEFAULT NULL<br /> , handle IN NUMBER<br /> , value IN NUMBER<br /> );<br /><br /> PROCEDURE set_filter (<br /> db_link IN VARCHAR2 DEFAULT NULL<br /> , handle IN NUMBER<br /> , name IN VARCHAR2<br /> , value IN VARCHAR2<br /> );<br /><br /> PROCEDURE set_filter (<br /> db_link IN VARCHAR2 DEFAULT NULL<br /> , handle IN NUMBER<br /> , name IN VARCHAR2<br /> , value IN BOOLEAN<br /> );<br /><br /> FUNCTION add_transform (<br /> db_link IN VARCHAR2 DEFAULT NULL<br /> , handle IN NUMBER<br /> , name IN VARCHAR2<br /> , encoding IN VARCHAR2 DEFAULT NULL<br /> )<br /> RETURN NUMBER;<br /><br /> PROCEDURE set_transform_param (<br /> db_link IN VARCHAR2 DEFAULT NULL<br /> , transform_handle IN NUMBER<br /> , name IN VARCHAR2<br /> , value IN VARCHAR2<br /> );<br /><br /> PROCEDURE set_transform_param (<br /> db_link IN VARCHAR2 DEFAULT NULL<br /> , transform_handle IN NUMBER<br /> , name IN VARCHAR2<br /> , value IN BOOLEAN DEFAULT TRUE<br /> );<br /><br /> FUNCTION fetch_ddl_text (<br /> db_link IN VARCHAR2 DEFAULT NULL<br /> , handle IN NUMBER<br /> , partial OUT NUMBER<br /> )<br /> RETURN VARCHAR2;<br /><br /> FUNCTION fetch_clob (<br /> db_link IN VARCHAR2 DEFAULT NULL<br /> , handle IN NUMBER<br /> )<br /> RETURN CLOB;<br /> <br /> FUNCTION fetch_ddl_clob (<br /> db_link IN VARCHAR2 DEFAULT NULL<br /> , handle IN NUMBER<br /> )<br /> RETURN CLOB;<br /><br /> FUNCTION fetch_ddl (<br /> db_link IN VARCHAR2 DEFAULT NULL<br /> , handle IN NUMBER<br /> )<br /> RETURN ku$_ddls;<br /><br /> PROCEDURE set_default_table_transforms (<br /> db_link IN VARCHAR2 DEFAULT NULL<br /> , table_transform_handle IN NUMBER<br /> );<br /> <br /> PROCEDURE set_default_index_transforms (<br /> db_link IN VARCHAR2 DEFAULT NULL<br /> , index_transform_handle IN NUMBER<br /> );<br /><br /> FUNCTION get_ddl (db_link IN VARCHAR2 DEFAULT NULL<br /> , object_type IN VARCHAR2<br /> , name IN VARCHAR2<br /> , schema IN VARCHAR2 DEFAULT NULL<br /> , version IN VARCHAR2 DEFAULT 'COMPATIBLE'<br /> , model IN VARCHAR2 DEFAULT 'ORACLE'<br /> , transform IN VARCHAR2 DEFAULT 'DDL') <br /> RETURN CLOB;<br /><br /> FUNCTION get_dependent_ddl (<br /> db_link IN VARCHAR2 DEFAULT NULL<br /> , object_type IN VARCHAR2<br /> , base_object_name IN VARCHAR2<br /> , base_object_schema IN VARCHAR2 DEFAULT NULL<br /> , version IN VARCHAR2 DEFAULT 'COMPATIBLE'<br /> , model IN VARCHAR2 DEFAULT 'ORACLE'<br /> , transform IN VARCHAR2 DEFAULT 'DDL'<br /> , object_count IN NUMBER DEFAULT 10000)<br /> RETURN CLOB;<br /> <br />END REMOTE_DBMS_METADATA;<br />/<br /><br />SHOW ERRORS;<br /><br /><br />create or replace<br />PACKAGE BODY remote_dbms_metadata AS<br />/*************************************************************************<br /> * Program : remote_dbms_metadata<br /> * Version : 1.0<br /> * Author : Philip Moore<br /> * Date : 20-JUN-2009 Anno Domini<br /> * Purpopse : This package provides access to DDL for objects in a remote<br /> Oracle database via Database Links.<br /> * Warnings : This package has only been tested in Oracle 9iR2 and 10gR2.<br /> *************************************************************************/<br /><br />-- SubProcedures<br />/* ------------------------------------- */<br /> FUNCTION build_db_link_string (<br /> p_db_link IN VARCHAR2<br /> )<br /> RETURN VARCHAR2 IS<br /> -- Variables<br /> l_return VARCHAR2 (100);<br /> BEGIN<br /> IF p_db_link IS NOT NULL THEN<br /> l_return := '@' || LTRIM (p_db_link, '@');<br /> END IF;<br /><br /> RETURN l_return;<br /> END build_db_link_string;<br /><br />/* ------------------------------------- */<br /> FUNCTION open (<br /> db_link IN VARCHAR2 DEFAULT NULL<br /> , object_type IN VARCHAR2<br /> , version IN VARCHAR2 DEFAULT 'COMPATIBLE'<br /> , model IN VARCHAR2 DEFAULT 'ORACLE'<br /> )<br /> RETURN NUMBER IS<br /> -- Variables<br /> l_plsql_call VARCHAR2 (4000);<br /> l_return NUMBER;<br /> l_db_link VARCHAR2 (100) := build_db_link_string (p_db_link => db_link);<br /> BEGIN<br /> l_plsql_call :=<br /> 'BEGIN '<br /> || ' :l_return := DBMS_METADATA.OPEN'<br /> || l_db_link<br /> || ' (object_type => :object_type '<br /> || ', version => :version '<br /> || ', model => :model '<br /> || ' ); '<br /> || 'END;';<br /><br /> EXECUTE IMMEDIATE l_plsql_call<br /> USING OUT l_return<br /> , IN object_type<br /> , IN version<br /> , IN model;<br /><br /> RETURN l_return;<br /> END open;<br /><br />/* ------------------------------------- */<br /> PROCEDURE dbms_metadata_close (<br /> db_link IN VARCHAR2 DEFAULT NULL<br /> , handle IN NUMBER<br /> ) IS<br /> -- Variables<br /> l_plsql_call VARCHAR2 (4000);<br /> l_db_link VARCHAR2 (100) := build_db_link_string (p_db_link => db_link);<br /> BEGIN<br /> l_plsql_call := 'BEGIN DBMS_METADATA.CLOSE' || l_db_link || ' (handle => :handle); END;';<br /><br /> EXECUTE IMMEDIATE l_plsql_call<br /> USING IN handle;<br /> END dbms_metadata_close;<br /><br />/* ------------------------------------- */<br /> PROCEDURE set_count (<br /> db_link IN VARCHAR2 DEFAULT NULL<br /> , handle IN NUMBER<br /> , value IN NUMBER<br /> ) IS<br /> -- Variables<br /> l_plsql_call VARCHAR2 (4000);<br /> l_db_link VARCHAR2 (100) := build_db_link_string (p_db_link => db_link);<br /> BEGIN<br /> l_plsql_call := 'BEGIN DBMS_METADATA.SET_COUNT' || l_db_link || ' (handle => :handle, value => :value); END;';<br /><br /> EXECUTE IMMEDIATE l_plsql_call<br /> USING IN handle, IN value;<br /> END set_count;<br /><br />/* ------------------------------------- */<br /> PROCEDURE set_filter (<br /> db_link IN VARCHAR2 DEFAULT NULL<br /> , handle IN NUMBER<br /> , name IN VARCHAR2<br /> , value IN VARCHAR2<br /> ) IS<br /> -- Variables<br /> l_plsql_call VARCHAR2 (4000);<br /> l_db_link VARCHAR2 (100) := build_db_link_string (p_db_link => db_link);<br /> BEGIN<br /> l_plsql_call :=<br /> 'BEGIN '<br /> || ' DBMS_METADATA.SET_FILTER'<br /> || l_db_link<br /> || ' (handle => :handle '<br /> || ', name => :name '<br /> || ', value => :value '<br /> || '); '<br /> || 'END;';<br /><br /> EXECUTE IMMEDIATE l_plsql_call<br /> USING IN handle<br /> , IN name<br /> , IN value;<br /> END set_filter;<br /><br />/* ------------------------------------- */<br /> PROCEDURE set_filter (<br /> db_link IN VARCHAR2 DEFAULT NULL<br /> , handle IN NUMBER<br /> , name IN VARCHAR2<br /> , value IN BOOLEAN<br /> ) IS<br /> -- Variables<br /> l_plsql_call VARCHAR2 (4000);<br /> l_db_link VARCHAR2 (100) := build_db_link_string (p_db_link => db_link);<br /> BEGIN<br /> l_plsql_call :=<br /> 'BEGIN '<br /> || ' DBMS_METADATA.SET_FILTER'<br /> || l_db_link<br /> || ' (handle => :handle '<br /> || ', name => :name '<br /> || ', value => '<br /> || boolean_pkg.bool_to_str (boolean_in => value)<br /> || '); '<br /> || 'END;';<br /><br /> EXECUTE IMMEDIATE l_plsql_call<br /> USING IN handle, IN name;<br /> END set_filter;<br /><br />/* ------------------------------------- */<br /> FUNCTION add_transform (<br /> db_link IN VARCHAR2 DEFAULT NULL<br /> , handle IN NUMBER<br /> , name IN VARCHAR2<br /> , encoding IN VARCHAR2 DEFAULT NULL<br /> )<br /> RETURN NUMBER IS<br /> -- Variables<br /> l_plsql_call VARCHAR2 (4000);<br /> l_return NUMBER;<br /> l_db_link VARCHAR2 (100) := build_db_link_string (p_db_link => db_link);<br /> BEGIN<br /> l_plsql_call :=<br /> 'BEGIN '<br /> || ' :l_return := DBMS_METADATA.ADD_TRANSFORM'<br /> || l_db_link<br /> || ' (handle => :handle '<br /> || ', name => :name '<br /> || ', encoding => :encoding '<br /> || '); '<br /> || 'END;';<br /><br /> EXECUTE IMMEDIATE l_plsql_call<br /> USING OUT l_return<br /> , IN handle<br /> , IN name<br /> , IN encoding;<br /><br /> RETURN l_return;<br /> END add_transform;<br /><br />/* ------------------------------------- */<br /> PROCEDURE set_transform_param (<br /> db_link IN VARCHAR2 DEFAULT NULL<br /> , transform_handle IN NUMBER<br /> , name IN VARCHAR2<br /> , value IN VARCHAR2<br /> ) IS<br /> -- Variables<br /> l_plsql_call VARCHAR2 (4000);<br /> l_db_link VARCHAR2 (100) := build_db_link_string (p_db_link => db_link);<br /> l_session_transform_param_rec session_transform_param_rec;<br /> BEGIN<br /> IF transform_handle = DBMS_METADATA.session_transform THEN<br /> /* Since we don't hold a session open for a remote database - we have to<br /> store the session transformation parameters in a local global structure */<br /> l_session_transform_param_rec.parameter_name := UPPER (TRIM (name));<br /> l_session_transform_param_rec.value_datatype := c_varchar2_type;<br /> l_session_transform_param_rec.varchar2_value := value;<br /> g_session_transform_params (db_link) (l_session_transform_param_rec.parameter_name) := l_session_transform_param_rec;<br /> ELSE<br /> l_plsql_call :=<br /> 'BEGIN '<br /> || ' DBMS_METADATA.SET_TRANSFORM_PARAM'<br /> || l_db_link<br /> || ' (transform_handle => :transform_handle '<br /> || ', name => :name '<br /> || ', value => :value '<br /> || '); '<br /> || 'END;';<br /><br /> EXECUTE IMMEDIATE l_plsql_call<br /> USING IN transform_handle<br /> , IN name<br /> , IN value;<br /> END IF;<br /> END set_transform_param;<br /><br />/* ------------------------------------- */<br /> PROCEDURE set_transform_param (<br /> db_link IN VARCHAR2 DEFAULT NULL<br /> , transform_handle IN NUMBER<br /> , name IN VARCHAR2<br /> , value IN BOOLEAN DEFAULT TRUE<br /> ) IS<br /> -- Variables<br /> l_plsql_call VARCHAR2 (4000);<br /> l_db_link VARCHAR2 (100) := build_db_link_string (p_db_link => db_link);<br /> l_session_transform_param_rec session_transform_param_rec;<br /> BEGIN<br /> IF transform_handle = DBMS_METADATA.session_transform THEN<br /> /* Since we don't hold a session open for a remote database - we have to<br /> store the session transformation parameters in a local global structure */<br /> l_session_transform_param_rec.parameter_name := UPPER (TRIM (name));<br /> l_session_transform_param_rec.value_datatype := c_boolean_type;<br /> l_session_transform_param_rec.boolean_value := value;<br /> g_session_transform_params (db_link) (l_session_transform_param_rec.parameter_name) := l_session_transform_param_rec;<br /> ELSE<br /> l_plsql_call :=<br /> 'BEGIN '<br /> || ' DBMS_METADATA.SET_TRANSFORM_PARAM'<br /> || l_db_link<br /> || ' (transform_handle => :transform_handle '<br /> || ', name => :name '<br /> || ', value => '<br /> || boolean_pkg.bool_to_str (boolean_in => value)<br /> || '); '<br /> || 'END;';<br /><br /> EXECUTE IMMEDIATE l_plsql_call<br /> USING IN transform_handle, IN name;<br /> END IF;<br /> END set_transform_param;<br /><br />/* ------------------------------------- */<br /> PROCEDURE set_transform_param (<br /> db_link IN VARCHAR2 DEFAULT NULL<br /> , transform_handle IN NUMBER<br /> , name IN VARCHAR2<br /> , value IN NUMBER<br /> ) IS<br /> -- Variables<br /> l_plsql_call VARCHAR2 (4000);<br /> l_db_link VARCHAR2 (100) := build_db_link_string (p_db_link => db_link);<br /> l_session_transform_param_rec session_transform_param_rec;<br /> BEGIN<br /> IF transform_handle = DBMS_METADATA.session_transform THEN<br /> /* Since we don't hold a session open for a remote database - we have to<br /> store the session transformation parameters in a local global structure */<br /> l_session_transform_param_rec.parameter_name := UPPER (TRIM (name));<br /> l_session_transform_param_rec.value_datatype := c_number_type;<br /> l_session_transform_param_rec.number_value := value;<br /> g_session_transform_params (db_link) (l_session_transform_param_rec.parameter_name) := l_session_transform_param_rec;<br /> ELSE<br /> l_plsql_call :=<br /> 'BEGIN '<br /> || ' DBMS_METADATA.SET_TRANSFORM_PARAM'<br /> || l_db_link<br /> || ' (transform_handle => :transform_handle '<br /> || ', name => :name '<br /> || ', value => :value '<br /> || '); '<br /> || 'END;';<br /><br /> EXECUTE IMMEDIATE l_plsql_call<br /> USING IN transform_handle<br /> , IN name<br /> , IN value;<br /> END IF;<br /> END set_transform_param;<br /><br />/* ------------------------------------- */<br /> FUNCTION fetch_ddl_text (<br /> db_link IN VARCHAR2 DEFAULT NULL<br /> , handle IN NUMBER<br /> , partial OUT NUMBER<br /> )<br /> RETURN VARCHAR2 IS<br /> -- Variables<br /> l_plsql_call VARCHAR2 (4000);<br /> l_db_link VARCHAR2 (100) := build_db_link_string (p_db_link => db_link);<br /> l_return VARCHAR2 (32767);<br /> BEGIN<br /> l_plsql_call := 'BEGIN :l_return := DBMS_METADATA.fetch_ddl_text' || l_db_link || ' (handle => :handle, partial => :partial); END;';<br /><br /> EXECUTE IMMEDIATE l_plsql_call<br /> USING OUT l_return<br /> , IN handle<br /> , OUT partial;<br /><br /> RETURN l_return;<br /> END fetch_ddl_text;<br /><br />/* ------------------------------------- */<br /> FUNCTION fetch_clob (<br /> db_link IN VARCHAR2 DEFAULT NULL<br /> , handle IN NUMBER<br /> )<br /> RETURN CLOB IS<br /> -- Variables<br /> l_partial PLS_INTEGER;<br /> l_return CLOB;<br /> BEGIN<br /> -- Loop until the partial flag is 0<br /> LOOP<br /> l_return := l_return || fetch_ddl_text (db_link => db_link<br /> , handle => handle<br /> , partial => l_partial<br /> );<br /> EXIT WHEN l_partial = 0;<br /> END LOOP;<br /><br /> RETURN l_return;<br /> END fetch_clob;<br /><br />/* ------------------------------------- */<br /> FUNCTION fetch_ddl_clob (<br /> db_link IN VARCHAR2 DEFAULT NULL<br /> , handle IN NUMBER<br /> )<br /> RETURN CLOB IS<br /> -- Variables<br /> l_return CLOB;<br /> BEGIN<br /><br /> -- Keep fetching until we get an error...<br /> <<fetch_loop>><br /> LOOP<br /> BEGIN<br /> l_return := l_return || fetch_clob (db_link => db_link, handle => handle);<br /> EXCEPTION<br /> WHEN DBMS_METADATA.invalid_argval THEN<br /> EXIT fetch_loop;<br /> END;<br /> END LOOP fetch_loop;<br /><br /> RETURN l_return;<br /> END fetch_ddl_clob;<br /><br />/* ------------------------------------- */<br /> FUNCTION fetch_ddl (<br /> db_link IN VARCHAR2 DEFAULT NULL<br /> , handle IN NUMBER<br /> )<br /> RETURN ku$_ddls IS<br /> -- Variables<br /> l_temp_clob CLOB;<br /> l_return ku$_ddls := ku$_ddls ();<br /> BEGIN<br /><br /> -- Keep fetching until we get an error...<br /> <<fetch_loop>><br /> LOOP<br /> BEGIN<br /> l_temp_clob := fetch_clob (db_link => db_link, handle => handle);<br /> l_return.EXTEND;<br /> l_return (l_return.COUNT).ddltext := l_temp_clob;<br /> EXCEPTION<br /> WHEN DBMS_METADATA.invalid_argval THEN<br /> EXIT fetch_loop;<br /> END;<br /> END LOOP fetch_loop;<br /><br /> RETURN l_return;<br /> END fetch_ddl;<br /><br />/* ------------------------------------- */<br /> PROCEDURE set_default_table_transforms (<br /> db_link IN VARCHAR2 DEFAULT NULL<br /> , table_transform_handle IN NUMBER<br /> ) IS<br /> BEGIN<br /> set_transform_param (db_link => db_link<br /> , transform_handle => table_transform_handle<br /> , name => 'PRETTY'<br /> , value => TRUE<br /> );<br /> set_transform_param (db_link => db_link<br /> , transform_handle => table_transform_handle<br /> , name => 'SQLTERMINATOR'<br /> , value => TRUE<br /> );<br /> set_transform_param (db_link => db_link<br /> , transform_handle => table_transform_handle<br /> , name => 'SIZE_BYTE_KEYWORD'<br /> , value => FALSE<br /> );<br /> set_transform_param (db_link => db_link<br /> , transform_handle => table_transform_handle<br /> , name => 'SEGMENT_ATTRIBUTES'<br /> , value => TRUE<br /> );<br /> set_transform_param (db_link => db_link<br /> , transform_handle => table_transform_handle<br /> , name => 'STORAGE'<br /> , value => FALSE<br /> );<br /> set_transform_param (db_link => db_link<br /> , transform_handle => table_transform_handle<br /> , name => 'TABLESPACE'<br /> , value => TRUE<br /> );<br /> set_transform_param (db_link => db_link<br /> , transform_handle => table_transform_handle<br /> , name => 'CONSTRAINTS_AS_ALTER'<br /> , value => TRUE<br /> );<br /> END set_default_table_transforms;<br /><br />/* ------------------------------------- */<br /> PROCEDURE set_default_index_transforms (<br /> db_link IN VARCHAR2 DEFAULT NULL<br /> , index_transform_handle IN NUMBER<br /> ) IS<br /> BEGIN<br /> set_transform_param (db_link => db_link<br /> , transform_handle => index_transform_handle<br /> , name => 'PRETTY'<br /> , value => TRUE<br /> );<br /> set_transform_param (db_link => db_link<br /> , transform_handle => index_transform_handle<br /> , name => 'SQLTERMINATOR'<br /> , value => TRUE<br /> );<br /> END set_default_index_transforms;<br /><br />/* ------------------------------------- */<br /> PROCEDURE apply_session_transform_params (<br /> db_link IN VARCHAR2<br /> , transform_handle IN NUMBER<br /> ) IS<br /> -- Variables<br /> l_array_index VARCHAR2 (50);<br /> BEGIN<br /> IF db_link IS NOT NULL THEN<br /> -- Apply any global session trasformation parameters which have been set prior to this call...<br /> l_array_index := g_session_transform_params (db_link).FIRST;<br /><br /> WHILE l_array_index IS NOT NULL LOOP<br /> CASE g_session_transform_params (db_link) (l_array_index).value_datatype<br /> WHEN c_varchar2_type THEN<br /> set_transform_param (db_link => db_link<br /> , transform_handle => transform_handle<br /> , name => g_session_transform_params (db_link) (l_array_index).parameter_name<br /> , value => g_session_transform_params (db_link) (l_array_index).varchar2_value<br /> );<br /> WHEN c_boolean_type THEN<br /> set_transform_param (db_link => db_link<br /> , transform_handle => transform_handle<br /> , name => g_session_transform_params (db_link) (l_array_index).parameter_name<br /> , value => g_session_transform_params (db_link) (l_array_index).boolean_value<br /> );<br /> WHEN c_number_type THEN<br /> set_transform_param (db_link => db_link<br /> , transform_handle => transform_handle<br /> , name => g_session_transform_params (db_link) (l_array_index).parameter_name<br /> , value => g_session_transform_params (db_link) (l_array_index).number_value<br /> );<br /> END CASE;<br /><br /> l_array_index := g_session_transform_params (db_link).NEXT (l_array_index);<br /> END LOOP;<br /> END IF;<br /> <br /> EXCEPTION<br /> WHEN NO_DATA_FOUND THEN<br /> NULL;<br /> END apply_session_transform_params;<br /><br />/* ------------------------------------- */<br /> FUNCTION get_ddl (<br /> db_link IN VARCHAR2 DEFAULT NULL<br /> , object_type IN VARCHAR2<br /> , name IN VARCHAR2<br /> , SCHEMA IN VARCHAR2 DEFAULT NULL<br /> , version IN VARCHAR2 DEFAULT 'COMPATIBLE'<br /> , model IN VARCHAR2 DEFAULT 'ORACLE'<br /> , transform IN VARCHAR2 DEFAULT 'DDL'<br /> )<br /> RETURN CLOB IS<br /> -- Variables<br /> l_return CLOB;<br /> l_handle NUMBER;<br /> l_transform NUMBER;<br /><br /> -- Private Sub-Procedure<br /> PROCEDURE cleanup IS<br /> BEGIN<br /> dbms_metadata_close (db_link => db_link, handle => l_handle);<br /> EXCEPTION<br /> WHEN handle_not_open THEN<br /> NULL;<br /> END cleanup;<br /> BEGIN<br /> l_handle := remote_dbms_metadata.open (db_link => db_link, object_type => object_type);<br /> set_count (db_link => db_link<br /> , handle => l_handle<br /> , value => 1<br /> );<br /> set_filter (db_link => db_link<br /> , handle => l_handle<br /> , name => 'NAME'<br /> , value => name<br /> );<br /> set_filter (db_link => db_link<br /> , handle => l_handle<br /> , name => 'SCHEMA'<br /> , value => SCHEMA<br /> );<br /> l_transform := add_transform (db_link => db_link<br /> , handle => l_handle<br /> , name => transform<br /> );<br /> apply_session_transform_params (db_link => db_link, transform_handle => l_transform);<br /> l_return := fetch_ddl_clob (db_link => db_link, handle => l_handle);<br /> cleanup;<br /> RETURN l_return;<br /> EXCEPTION<br /> WHEN OTHERS THEN<br /> cleanup;<br /> RAISE;<br /> END get_ddl;<br /><br />/* ------------------------------------- */<br /> FUNCTION get_dependent_ddl (<br /> db_link IN VARCHAR2 DEFAULT NULL<br /> , object_type IN VARCHAR2<br /> , base_object_name IN VARCHAR2<br /> , base_object_schema IN VARCHAR2 DEFAULT NULL<br /> , version IN VARCHAR2 DEFAULT 'COMPATIBLE'<br /> , model IN VARCHAR2 DEFAULT 'ORACLE'<br /> , transform IN VARCHAR2 DEFAULT 'DDL'<br /> , object_count IN NUMBER DEFAULT 10000<br /> )<br /> RETURN CLOB IS<br /> -- Variables<br /> l_return CLOB;<br /> l_handle NUMBER;<br /> l_transform NUMBER;<br /><br /> -- Private Sub-Procedure<br /> PROCEDURE cleanup IS<br /> BEGIN<br /> dbms_metadata_close (db_link => db_link, handle => l_handle);<br /> EXCEPTION<br /> WHEN handle_not_open THEN<br /> NULL;<br /> END cleanup;<br /> BEGIN<br /> l_handle := remote_dbms_metadata.open (db_link => db_link, object_type => object_type);<br /> set_filter (db_link => db_link<br /> , handle => l_handle<br /> , name => 'BASE_OBJECT_NAME'<br /> , value => base_object_name<br /> );<br /> set_filter (db_link => db_link<br /> , handle => l_handle<br /> , name => 'BASE_OBJECT_SCHEMA'<br /> , value => base_object_schema<br /> );<br /> set_count (db_link => db_link<br /> , handle => l_handle<br /> , value => object_count<br /> );<br /> l_transform := remote_dbms_metadata.add_transform (db_link => db_link<br /> , handle => l_handle<br /> , name => transform<br /> );<br /> apply_session_transform_params (db_link => db_link, transform_handle => l_transform);<br /> l_return := fetch_ddl_clob (db_link => db_link, handle => l_handle);<br /> cleanup;<br /> RETURN l_return;<br /> EXCEPTION<br /> WHEN OTHERS THEN<br /> cleanup;<br /> RAISE;<br /> END get_dependent_ddl;<br />END remote_dbms_metadata;<br />/<br /><br />SHOW ERRORS<br /><br /><br /></code></pre><br /></span><span style="font-family:arial;"></span><br /></span><br /><span style="font-family:arial;"><strong>Watchouts: </strong>Remember - since PL/SQL Packages require DIRECT granted permissions (not through a role) - the schema you compile this package in requiresj EXECUTE privileges on the SYS.DBMS_METADATA package.</span><br /><br /><span style="font-family:Arial;"></span><br /><br /><span style="font-family:Arial;">Also - using DBMS_METADATA.GET_DDL requires the "SELECT_CATALOG_ROLE" if you are using it to get DDL in a schema other than your own - so the user you have defined your database link to use MUST have that privilege in the remote database for this to work.</span><br /><br /><span style="font-family:Arial;"></span><br /><br /><span style="font-family:Arial;"><strong>How to use: </strong>Just call it from SQL!</span><br /><br /><span style="font-family:Arial;">Example call:</span><br /><br /><span style="font-family:Arial;"></span><br /><br /><pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000; background-color: #eee;font-size: 12px;border: 1px dashed #999999;line-height: 14px;padding: 5px; overflow: auto; width: 100%"><code>select remote_dbms_metadata.get_ddl ('PROD_LINK'<br /> , 'TABLE'<br /> , 'EMP'<br /> , 'SCOTT') AS table_ddl<br /> , remote_dbms_metadata.get_dependent_ddl ('PROD_LINK'<br /> , 'INDEX'<br /> , 'EMP'<br /> , 'SCOTT') AS index_ddl<br />FROM dual; <br /></code></pre><br /><br /><strong><span style="font-family:Arial;"></span></strong><br /><strong>Notes:</strong><br /><span style="font-family:arial;">This package has been tested in Oracle 9iR2 and 10GR2.<br /></span><br /><br />Good luck...</p><br /><br /><strong>Special thanks </strong>to Greg Houston's <a href="http://formatmysourcecode.blogspot.com/">http://formatmysourcecode.blogspot.com/</a> for helping me format the code for blogger!<br /></span>Philhttp://www.blogger.com/profile/01851424617108510453noreply@blogger.com11