CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloper
user080320
user080320

Reputation: 31

Spool each table name in Oracle SQL Developer

I have thousands of TABLES in our database. Instead of individually creating a script to spool out all columns from the application tables. I want to loop through all the tables in alltables TABLE with tablename like 'TB_%'.

I tried to use this solution in my oracle sql developer but I am getting an error that the table does not exist. I am not a SQL expert and not familiar on how to program it in SQL. Below is the code I did:

set line 400 
set pagesize 2000
set colsep |

DECLARE

CURSOR get_all_tables IS
SELECT table_name
FROM all_tables
WHERE table_name like 'TB_%';

BEGIN
  FOR i IN get_all_tables 
LOOP
 spool 'C:\documents\script\' || i.table_name || '.txt'
 execute immediate 'select * from ' || i.table_name;
 spool off;
END LOOP;
END;

What I want is, for each tables found in all_tables that have table_name like 'TB_%' should create a file with spool filename equivalent to tablename. And, it should spool out all the columns for each table. Can anyone help me to check the codes I did?

Thank you

Upvotes: 0

Views: 163

Answers (1)

Littlefoot
Littlefoot

Reputation: 142743

That won't work; spool is a SQL*Plus command, you can't use it in PL/SQL.

Though, as you use SQL Developer, then use it properly.

  • go to "Tools" menu and choose "Database Export"
  • select connection
  • set checkboxes; for simplicity, I left only "Export DDL" and chose "Save as separate files". You'd select "Export data" as well; if you leave "insert" format, you'd get INSERT INTO statements in export file
  • in "Types to export", select tables only
  • in "Specify objects", filter on table names. I don't have any "TB" tables, but I do have "DE" ones (after "Lookup" button, move them to the right side)
  • click "Finish"
  • result: two files in my temp directory, both containing CREATE TABLE statements (you'd get INSERT INTO as well, if you chose "Export data")

enter image description here

Upvotes: 2

Related Questions