| Not getting output for other schemas [message #441650] |
Tue, 02 February 2010 07:12  |
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
|
Senior Member |
|
|
Hi,
One function returns table metadata in xml form. This works for current schema but not for other schemas. Is it a privilege problem?
my code is..
CREATE OR REPLACE FUNCTION TEST.F_DBEG2
RETURN XMLTYPE
AS
Handle NUMBER;
V_OUTPUT_TOTAL XMLTYPE;
V_CNT NUMBER := 0;
BEGIN
DBMS_OUTPUT.PUT_LINE('START THE PROGM');
Handle := DBMS_METADATA.OPEN('DATABASE_EXPORT');
DBMS_OUTPUT.PUT_LINE('Open the handle');
DBMS_METADATA.SET_COUNT(HANDLE, 1000);
DBMS_METADATA.set_filter (Handle, 'INCLUDE_PATH_EXPR', '=''TABLE''');
DBMS_METADATA.SET_FILTER(Handle,'NAME_EXPR', 'IN (''TEST'')','SCHEMA');
DBMS_METADATA.SET_FILTER(Handle,'NAME_EXPR', 'IN (''DEPT'')','TABLE');
DBMS_OUTPUT.PUT_LINE('Filter the required objects');
V_OUTPUT_TOTAL := DBMS_METADATA.FETCH_XML(Handle);
DBMS_OUTPUT.PUT_LINE('converted into xml');
return v_output_TOTAL;
END;
/
but when i select the object of another schema, got output.
SELECT * FROM TEST_NEW.DEPT;
Which privilege i need?
Regards,
Madhavi.
|
|
|
|
|
|
|
|
| Re: Not getting output for other schemas [message #441721 is a reply to message #441650] |
Tue, 02 February 2010 21:33  |
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
|
Senior Member |
|
|
Hi,
For above function, i didn't get any error when i used other schemas in dbms_metadata.set_filter procedure.
I got o/p (in xml form) when i use current schema name. when i use other schema, o/p comes as empty xml. Even object not available, we get empty xml only without returning any error but object is available in the schema what i passed to set_filter procedure.
I tried with one ananymous program (just select an object data of other schema), works fine.
For one automation project requires above functionality. Here i dont want to use in browser interface so i went for programing.
Regards,
Madhavi.
|
|
|
|
|
|
| Re: Not getting output for other schemas [message #442551 is a reply to message #441650] |
Mon, 08 February 2010 23:29 |
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
|
Senior Member |
|
|
Hi,
I able to get the metadata of an object in the current schema in xml format but failed for other schema objects in the same database. Even having EXPORT FULL DATABASE , IMPORT FULL DATABASE and SELECT_CATALOG_ROLE privileges why iam not able to get this?
I didn't get any error while executing. I got empty xml as output.
I able to get the metadata of another database objects (using DB LINK) successfully. Using dblink also i tried to get the metadata but got empty xml only as previous.
my code is
CREATE OR REPLACE FUNCTION F_DBEG3(V_Table VARCHAR2)
RETURN XMLTYPE
AS
Handle NUMBER;
V_OUTPUT_TOTAL XMLTYPE;
V_CNT NUMBER := 0;
BEGIN
DBMS_OUTPUT.PUT_LINE('START THE PROGM');
Handle := DBMS_METADATA.OPEN('DATABASE_EXPORT');
DBMS_OUTPUT.PUT_LINE('Open the handle');
DBMS_METADATA.SET_COUNT(HANDLE, 1000);
DBMS_METADATA.set_filter (Handle, 'INCLUDE_PATH_EXPR', '=''TABLE''');
DBMS_METADATA.SET_FILTER(Handle,'NAME', V_Table,'TABLE');
DBMS_OUTPUT.PUT_LINE('Filter the required objects');
V_OUTPUT_TOTAL := DBMS_METADATA.FETCH_XML(Handle);
DBMS_OUTPUT.PUT_LINE('converted into xml');
return v_output_TOTAL;
END;
Is it a privilege issue or error in my program?
Regards,
Madhavi.
|
|
|
|
| Re: Not getting output for other schemas [message #442554 is a reply to message #441650] |
Mon, 08 February 2010 23:41 |
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
|
Senior Member |
|
|
Hi,
I added one set filter to filter the schema. I got the following error for this (when add 13th line).
SQL> CREATE OR REPLACE FUNCTION F_DBEG3(V_Schema VARCHAR2, V_Table VARCHAR2)
2 RETURN XMLTYPE
3 AS
4 Handle NUMBER;
5 V_OUTPUT_TOTAL XMLTYPE;
6 V_CNT NUMBER := 0;
7 BEGIN
8 DBMS_OUTPUT.PUT_LINE('START THE PROGM');
9 Handle := DBMS_METADATA.OPEN('DATABASE_EXPORT');
10 DBMS_OUTPUT.PUT_LINE('Open the handle');
11 DBMS_METADATA.SET_COUNT(HANDLE, 1000);
12 DBMS_METADATA.set_filter (Handle, 'INCLUDE_PATH_EXPR', '=''TABLE''');
13 DBMS_METADATA.SET_FILTER(Handle,'NAME', V_Schema,'SCHEMA');
14 DBMS_METADATA.SET_FILTER(Handle,'NAME', V_Table,'TABLE');
15 DBMS_OUTPUT.PUT_LINE('Filter the required objects');
16 V_OUTPUT_TOTAL := DBMS_METADATA.FETCH_XML(Handle);
17 DBMS_OUTPUT.PUT_LINE('converted into xml');
18 return v_output_TOTAL;
19 END;
20 /
Function created.
error is...
SQL> SELECT F_DBEG3('EMACH', 'ACT_TAF_T') FROM DUAL;
ERROR:
ORA-31608: specified object of type MATERIALIZED_VIEW_LOG not found
ORA-06512: at "SYS.DBMS_METADATA", line 1546
ORA-06512: at "SYS.DBMS_METADATA", line 1583
ORA-06512: at "SYS.DBMS_METADATA", line 1891
ORA-06512: at "SYS.DBMS_METADATA", line 3629
ORA-06512: at "MACHRAJS.F_DBEG3", line 16
no rows selected
But the above selected object is a table but not a materialized view. And also the object is available in the specified schema ( get empty xml with out any error if object not available).
Regards,
Madhavi.
|
|
|
|
| Re: Not getting output for other schemas [message #444433 is a reply to message #441650] |
Mon, 22 February 2010 00:09 |
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
|
Senior Member |
|
|
Hi,
My function returns the metadata of an object in the current schema succesfully but failed to get the same for other schemas in the current database.
function doesn't throw any errors when selecting the other schema objects. Simply it returns empty xml.
I have gone through the doc. They mentioned a point helps for me is....
Quote:
In stored procedures, functions, and definers-rights packages, roles (such as SELECT_CATALOG_ROLE) are disabled. Therefore, such a PL/SQL program can only fetch metadata for objects in its own schema. If you want to write a PL/SQL program that fetches metadata for objects in a different schema (based on the invoker's possession of SELECT_CATALOG_ROLE), you must make the program invokers-rights.
How to make my program having invokers/callers right?
Please suggest me.
Regards,
Madhavi.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
