Solved

table loop

Posted on 2011-03-03
22
1,116 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 73

Assisted Solution

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

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

Accepted Solution

by:
slightwv (䄆 Netminder) earned 445 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 76

Expert Comment

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

I get it.  I misread the question.
0
 

Author Comment

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

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 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 445 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 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 445 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 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 445 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 445 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 73

Expert Comment

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

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 445 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 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 445 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 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 445 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now