Home » SQL & PL/SQL » SQL & PL/SQL » Not getting output for other schemas (oracle 10g)
Not getting output for other schemas [message #441650] Tue, 02 February 2010 07:12 Go to next message
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 #441652 is a reply to message #441650] Tue, 02 February 2010 07:25 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you get any error?
This one will help to know what the probem is.

Regards
Michel
Re: Not getting output for other schemas [message #441659 is a reply to message #441650] Tue, 02 February 2010 07:44 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
First of all why cant you use "dbms_metadata.get_xml" built in directly?

Next answer For Michel Question too///

any error?

sriram Smile
Re: Not getting output for other schemas [message #441721 is a reply to message #441650] Tue, 02 February 2010 21:33 Go to previous messageGo to next message
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 #441730 is a reply to message #441721] Tue, 02 February 2010 22:47 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
>I tried with one ananymous program (just select an object data of other schema), works fine.
Privileges acquired via ROLE do NOT apply within NAMED PL/SQL procedures.

It appears you have simple permissions problem.
I am not sure which privilege against which object is needed to obtain schema.object metadata.

Re: Not getting output for other schemas [message #442551 is a reply to message #441650] Mon, 08 February 2010 23:29 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: Not getting output for other schemas [message #444434 is a reply to message #441650] Mon, 22 February 2010 00:13 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
>How to make my program having invokers/callers right?

http://www.lmgtfy.com/?q=oracle+invoker+rights
Re: Not getting output for other schemas [message #444484 is a reply to message #441650] Mon, 22 February 2010 03:54 Go to previous message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi BlackSwan,
Thank you. I got output.
Regards,
Madhavi.
Previous Topic: JSP limitations
Next Topic: Copy Command in a procedure
Goto Forum:
  


Current Time: Tue Sep 27 17:56:14 CDT 2016

Total time taken to generate the page: 0.09109 seconds