Home » SQL & PL/SQL » SQL & PL/SQL » suitable set filter (oracle 10g)
suitable set filter [message #445483] Tue, 02 March 2010 02:02 Go to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
A function returns the comments of all objects of the schema using metadata api. I used DATABASE_EXPORT as object_type for open function. which name (eg: base_object_name, base_object_schema...) should i use to get comments of single object. My code is..
/
  1  CREATE OR REPLACE function f_depen_obj
  2  return clob
  3  as
  4  a number;
  5  b number;
  6  abc clob;
  7  DEFQ CLOB;
  8  begin
  9  a := dbms_metadata.open('DATABASE_EXPORT');
 10  DBMS_METADATA.SET_FILTER(A,'INCLUDE_PATH_EXPR','=''COMMENT''');
 11  DBMS_METADATA.SET_FILTER(A,'NAME','EMP','TABLE');
 12  DBMS_METADATA.SET_FILTER(A,'NAME','SCOTT','SCHEMA');
 13  --DBMS_METADATA.SET_FILTER(A,'BASE_OBJECT_NAME','EMP');
 14  --DBMS_METADATA.SET_FILTER(A,'BASE_OBJECT_SCHEMA','SCOTT');
 15  B:= DBMS_METADATA.ADD_TRANSFORM(A,'DDL');
 16  LOOP
 17  ABC := DBMS_METADATA.FETCH_CLOB(A);
 18  EXIT WHEN ABC IS NULL;
 19  IF DEFQ IS NULL THEN
 20  DEFQ := ABC;
 21  ELSE
 22  DEFQ := DEFQ || ABC;
 23  END IF;
 24  END LOOP;
 25  RETURN DEFQ;
 26* END;

I got comments of all objects when i commented 11th,12th lines. Now i want the comments of EMP table of SCOTT schema. I got the following error ..
SQL> select f_depen_obj from dual;
ERROR:
ORA-31603: object "EMP" of type TABLE not found in schema "EMP"
ORA-06512: at "SYS.DBMS_METADATA", line 1546
ORA-06512: at "SYS.DBMS_METADATA", line 1583
ORA-06512: at "SYS.DBMS_METADATA", line 1901
ORA-06512: at "SYS.DBMS_METADATA", line 3806
ORA-06512: at "SYS.DBMS_METADATA", line 3784
ORA-06512: at "SCOTT.F_DEPEN_OBJ", line 17



no rows selected

i got output (comments of single object) when i use COMMENT instead of DATABASE_EXPORT in open function(line no: 9). But i need to use DATABASE_EXPORT as a object_type.
Which name is suitable for set_filter procedure instead of 'NAME'?
Regards,
Madhavi.
Re: suitable set filter [message #445534 is a reply to message #445483] Tue, 02 March 2010 06:43 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
Looks to me like maybe your set_filter commands are wrong. The first one looks good but not the other two. Read this. In particular consider this example from the documentation:

Quote:
You can use the same text expression filter multiple times with different values. All the filter conditions will be applied to the query. For example, to get objects with names between Felix and Oscar, do the following:
dbms_metadata.set_filter(handle,'NAME_EXPR','>=''FELIX''');
dbms_metadata.set_filter(handle,'NAME_EXPR','<=''OSCAR''');


makes me think you are missing quotes around your values and that kind of thing.

Additionally I question that you have provided true code. The syntax of the SET_FILTER call says you can only pass three variables, yet your lines 11 and 12 are passing four. OK so my document is from 9.2 and maybe the procedure has been updated since. But... Is this real code or not. If not, post the real stuff before you ask any more questions.

DBMS_METADATA.SET_FILTER (
   handle  IN NUMBER,
   name    IN VARCHAR2,
   value   IN VARCHAR2);
DBMS_METADATA.SET_FILTER (        
   handle  IN NUMBER,
   name    IN VARCHAR2,
   value   IN BOOLEAN DEFAULT TRUE);


Kevin
Re: suitable set filter [message #445599 is a reply to message #445483] Tue, 02 March 2010 21:54 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi Kevin,
Thanks for reply. I got output for above function (I tried based on your advice). The above function is real code only. In oracle 10g, there are 4 oarameters for set_filter procedure.
Quote:

DBMS_METADATA.SET_FILTER (
handle IN NUMBER,
name IN VARCHAR2,
value IN VARCHAR2,
object_type_path IN VARCHAR2 DEFAULT NULL);

DBMS_METADATA.SET_FILTER (
handle IN NUMBER,
name IN VARCHAR2,
value IN BOOLEAN DEFAULT TRUE,
object_type_path IN VARCHAR2 DEFAULT NULL);

DBMS_METADATA.SET_FILTER (
handle IN NUMBER,
name IN VARCHAR2,
value IN NUMBER,
object_type_path IN VARCHAR2 DEFAULT NULL);



Once again thank you.
regards,
Madhavi.
Re: suitable set filter [message #445711 is a reply to message #445483] Wed, 03 March 2010 07:40 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
You are welcome. I am glad you were able to figure it out.

Also, thanks for the update on 10g syntax.

Kevin
Previous Topic: executing a procedure containg a global temporary rable
Next Topic: performance compare -> insert select and insert select using cursor
Goto Forum:
  


Current Time: Sun Sep 25 19:42:11 CDT 2016

Total time taken to generate the page: 0.10410 seconds