?
Solved

table loop

Posted on 2011-03-03
22
Medium Priority
?
1,167 Views
Last Modified: 2012-05-11
I have this script that collects all the table metadata information. Is there a way to loop through all the tables in the schema replacing tior with each table name?

declare

v_file_handle UTL_FILE.file_type;


begin


v_file_handle := UTL_FILE.fopen('DATA_MODEL', 'tior.sql', 'w', 32767);

FOR cur_rep IN
(select dbms_metadata.get_ddl('TABLE','TIOR','TIS') output from dual
)

LOOP

UTL_FILE.put_line(v_file_handle, cur_rep.output);

END LOOP;

UTL_FILE.fclose(v_file_handle);


v_file_handle := UTL_FILE.fopen('DATA_MODEL', 'tior.sql', 'A', 32767);

FOR cur_rep IN
(SELECT DBMS_METADATA.get_dependent_ddl ('INDEX', 'TIOR', 'TIS') output from dual
)

LOOP

UTL_FILE.put_line(v_file_handle, cur_rep.output);

END LOOP;

UTL_FILE.fclose(v_file_handle);


v_file_handle := UTL_FILE.fopen('DATA_MODEL', 'tior.sql', 'A', 32767);

FOR cur_rep IN
(SELECT DBMS_METADATA.get_dependent_ddl ('TRIGGER', 'TIOR', 'TIS') output from dual
)

LOOP

UTL_FILE.put_line(v_file_handle, cur_rep.output);

END LOOP;


UTL_FILE.fclose(v_file_handle);


v_file_handle := UTL_FILE.fopen('DATA_MODEL', 'tior.sql', 'A', 32767);

FOR cur_rep IN
(select dbms_metadata.get_dependent_ddl('REF_CONSTRAINT','TIOR','TIS') output from dual
)

LOOP

UTL_FILE.put_line(v_file_handle, cur_rep.output);

END LOOP;

UTL_FILE.fclose(v_file_handle);




v_file_handle := UTL_FILE.fopen('DATA_MODEL', 'tior.sql', 'A', 32767);

FOR cur_rep IN
(SELECT dbms_lob.substr((DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT','TIOR','TIS')),4000,1) output from dual
)

LOOP

UTL_FILE.put_line(v_file_handle, cur_rep.output);

END LOOP;

UTL_FILE.fclose(v_file_handle);


v_file_handle := UTL_FILE.fopen('DATA_MODEL', 'tior.sql', 'A', 32767);

FOR cur_rep IN
(SELECT DBMS_METADATA.get_ddl ('TABLE', 'TIOR', 'TIS') output FROM all_tables
WHERE owner = 'TIS' AND table_name = 'TIOR'
UNION ALL
SELECT DBMS_METADATA.get_dependent_ddl ('COMMENT', 'TIOR', 'TIS') output FROM (SELECT table_name, owner
                                                                               FROM all_col_comments
                                                                               WHERE owner = 'TIS' AND table_name = 'TIOR' AND comments IS NOT NULL
UNION
SELECT table_name, owner
FROM SYS.all_tab_comments
WHERE owner = 'TIS'  
   AND table_name = 'TIOR'
   AND comments IS NOT NULL))

LOOP

UTL_FILE.put_line(v_file_handle, cur_rep.output);

END LOOP;

UTL_FILE.fclose(v_file_handle);

end;

/
0
Comment
Question by:msimons4
  • 10
  • 10
  • 2
22 Comments
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 220 total points
ID: 35030951
each of the ddl functions returns a clob.

replace(ddlfunction(),'TIOR',cur_rep.table_name)
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1780 total points
ID: 35030952
looping through table names is pretty simple:
for i in (select table_name from user_tables where table_name in ('T1','T2')
loop
...
do stuff
...
end loop;

Before you just wrap that loop around all you code, what exactly are you trying to do with all that?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35030965
oh....  replacing existing names with 'new' names.

I get it.  I misread the question.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:msimons4
ID: 35030980
Trying to create a sepatate directory for each table with all its metadata information.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35031123
directory or file?

In other words: Are you wanting to loop through a list of tables and write the contents from every DBMS_METADATA call to a table specific file?
0
 

Author Comment

by:msimons4
ID: 35031154
A specific directory. Do you have a way to change tior to loop through all the tables? Is there a way to introduce a dynamic query?
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1780 total points
ID: 35031202
I apologize.  I'm just not understanding what you want here.

>>A specific directory

I believe for utl_file to write to a separate directory, you will need to create that directory as an Oracle database object.  You cannot jusr add a sub-folder to the file name.

For the rest of what you posted,  can you provide some sample data and expected results.

Something like: I have 5 tables.  I want to loop through all 5 and for each one make several DBMS_METADATA calls.  I would like each table to be written to it's own file (I know you said directory but I don't think that's possible unless you create 5 Oracle Directories).

for table1:
I want a file called: table1_indexes.txt
etc...
0
 

Author Comment

by:msimons4
ID: 35031247
Sorry, I would like to, in 1 directory, have a table1.sql, table2.sql file with all the metadata for each table. The script does it for 1 table. I have 2000 tables in this schema. Is there a way to loop through the tables?
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1780 total points
ID: 35031399
Take a look at the following example.  I believe it has the basics of what you want.

You should be able to add the additional calls to DBMS_METADATA and open/close and additional files.

If you have any questions on this, please ask.

Note:  As sdstuber mentioned, those calls return a CLOB.  UTL_FILE.PUT_LINE can only handle 32767 characters.  If any of those calls return a CLOB greater than that, it will error.

If that is a possibility, you'll need an inner loop using UTL_FILE.PUT (not PUT_LINE) and write out the data 32K at a time.


drop table tab1 purge;
create table tab1(col1 char(1), col2 char(1));

create index tab1_idx1 on tab1(col1);
create index tab1_idx2 on tab1(col2);

drop table tab2 purge;
create table tab2(col1 char(1));

create or replace directory myDir as 'C:\';

declare
	v_handle UTL_FILE.file_type;
	myClob	clob;

begin
	for currentTable in (select table_name from user_tables where table_name in ('TAB1','TAB2')) loop

		v_handle := UTL_FILE.FOPEN('MYDIR',currentTable.table_name || '.sql','w',32767);

		-- nested block in case the call returns nothing
		begin
		select dbms_metadata.get_dependent_ddl('INDEX',currentTable.table_name,'BUD') into myClob from dual;
		exception
			when others then
				myClob := null;
		end;

		utl_file.put_line(v_handle,myClob);

		UTL_FILE.fclose(v_handle);

	end loop;
end;
/

Open in new window

0
 

Author Comment

by:msimons4
ID: 35037174
So I am trying to put just my index metadata code into you loop, the procedure completes sucessfully by nothing appears in the directory? Here is the code, thanks:



declare

v_file_handle UTL_FILE.file_type;

begin

for currentTable in (select table_name from user_tables where table_name in ('TIOR','TIIN')) loop


v_file_handle := UTL_FILE.fopen('DATA_MODEL', currentTable.table_name || '.sql', 'w', 32767);

FOR cur_rep IN
(SELECT DBMS_METADATA.get_dependent_ddl ('INDEX', currentTable.table_name, 'TIS') output from dual
)

LOOP

UTL_FILE.put_line(v_file_handle, cur_rep.output);

END LOOP;

UTL_FILE.fclose(v_file_handle);

end loop;

end;

/
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1780 total points
ID: 35037228
Are you sure you're looking in the correct folder?

How was 'DATA_MODEL' defined?

select owner,directory_path from dba_directories where directory_name='DATA_MODEL';
0
 

Author Comment

by:msimons4
ID: 35037360
Sure, because when I run it without the loop I get results.
0
 

Author Comment

by:msimons4
ID: 35037371
This gets results:

declare

v_file_handle UTL_FILE.file_type;

begin


v_file_handle := UTL_FILE.fopen('DATA_MODEL', 'ddl_indexes_metadata.sql', 'w', 32767);

FOR cur_rep IN
(SELECT DBMS_METADATA.get_dependent_ddl ('INDEX', 'TIOR', 'TIS') output from dual
)

LOOP

UTL_FILE.put_line(v_file_handle, cur_rep.output);

END LOOP;

UTL_FILE.fclose(v_file_handle);

end;

/
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1780 total points
ID: 35037538
What version of Oracle are you using (please provide all 4 numbers)?

I ran the code I posted in http:#a35031399 using 10.2.0.3 and know it works.

You might try creating a variable for file_name and use that.

Using your last test see below.

If that works, move the variable inside the table loop in my code.




declare

v_file_handle UTL_FILE.file_type;
file_name varchar2(50) := 'ddl_indexes_metadata.sql';
begin


v_file_handle := UTL_FILE.fopen('DATA_MODEL', file_name, 'w', 32767); 

FOR cur_rep IN 
(SELECT DBMS_METADATA.get_dependent_ddl ('INDEX', 'TIOR', 'TIS') output from dual
) 

LOOP 

UTL_FILE.put_line(v_file_handle, cur_rep.output); 

END LOOP; 

UTL_FILE.fclose(v_file_handle);

end;

/

Open in new window

0
 

Author Comment

by:msimons4
ID: 35037715
10.2.0.4

Even this produces no file in the directory:

declare
      v_handle UTL_FILE.file_type;
      myClob      clob;

begin
      for currentTable in (select table_name from user_tables where table_name in ('TIOR','TIIN')) loop

            v_handle := UTL_FILE.FOPEN('DATA_MODEL',currentTable.table_name || '.sql','w',32767);

            -- nested block in case the call returns nothing
            begin
            select dbms_metadata.get_dependent_ddl('INDEX',currentTable.table_name,'TIS') into myClob from dual;
            exception
                  when others then
                        myClob := null;
            end;

            utl_file.put_line(v_handle,myClob);

            UTL_FILE.fclose(v_handle);

      end loop;
end;

/
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35037758
is your cursor of tables returning any rows?
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1780 total points
ID: 35037802
Not sure what is going on.

I created the same directory name as your example.  Changed the table name list to match tables I have and I get the files.

When you hardcoded the filename like:
v_file_handle := UTL_FILE.fopen('DATA_MODEL', 'ddl_indexes_metadata.sql', 'w', 32767);


did you look in the file of just that the file exists?

Look in the file and see if it contains the DDL.

If the select doesn't return any rows, you will not get any files.

As the user running the code, does this return rows:
select table_name from user_tables where table_name in ('TIOR','TIIN');
create or replace directory data_model as 'c:\';
declare
      v_handle UTL_FILE.file_type;
      myClob      clob;

begin
      for currentTable in (select table_name from user_tables where table_name in ('TAB1','TAB2','TAB99')) loop

            v_handle := UTL_FILE.FOPEN('DATA_MODEL',currentTable.table_name || '.sql','w',32767);

            -- nested block in case the call returns nothing
            begin
            select dbms_metadata.get_dependent_ddl('INDEX',currentTable.table_name,'TIS') into myClob from dual;
            exception
                  when others then
                        myClob := null;
            end;

            utl_file.put_line(v_handle,myClob);

            UTL_FILE.fclose(v_handle);

      end loop;
end;

/

Open in new window

0
 

Author Comment

by:msimons4
ID: 35038226
Thats the problem I'm logged in as sys, I'll retry it.
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1780 total points
ID: 35038301
>>Thats the problem I'm logged in as sys

you can always change to dba_tables and add owner='SOMEOWNER' to the where clause.
0
 

Author Comment

by:msimons4
ID: 35038317
I logged in as the user, got results from select table_name from ect. but  still nothing from:

declare
      v_handle UTL_FILE.file_type;
      myClob      clob;

begin
      for currentTable in (select table_name from user_tables where table_name in ('TIOR','TIIN')) loop

            v_handle := UTL_FILE.FOPEN('DATA_MODEL',currentTable.table_name || '.sql','w',32767);

            -- nested block in case the call returns nothing
            begin
            select dbms_metadata.get_dependent_ddl('INDEX',currentTable.table_name,'TIS') into myClob from dual;
            exception
                  when others then
                        myClob := null;
            end;

            utl_file.put_line(v_handle,myClob);

            UTL_FILE.fclose(v_handle);

      end loop;
end;

/
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1780 total points
ID: 35038361
No files or no data in the files?

If hardcoding the filename works and using the variable name doesn't and that is the only difference, you might have an obscure bug.

I ran what I posted using 10.2.0.3 on Windows XP so I know it works.  If the same codes doesn't work for you, there is something specific to your configuration.

You will probably need to contact Oracle Support to figure out he specifics.
0
 

Author Comment

by:msimons4
ID: 35060482
It does work, thanks.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question