Generating SQL Script Dynamically

Generating SQL Script Dynamically

Yes, I know Brute Force is not efficient but it solves the problem. I am not going to explain what Brute Force is? If you are here for definition, you are in a wrong page!! In this short article, I’m going to show you how Brute Force Algorithm can come handy to database developers and DBAs to expedite or to automate the manual work. It is up to you to decide when to use Brute Force vs script, tools or others.

Scenario: You are asked to compile the describe/desc of all the tables from your schema.  All_Tables view  has your table information. You can list all your tables first using the query and manually write the describe syntax for each table. This takes time, if you have over 10 tables and chances of making mistakes is higher when you write the syntax manually.

Solution: Automate it, why? its faster and you don't make mistake!!

SELECT 'Describe' 
       || ' ' 
       || owner 
       || '.' 
       || table_name 
       || ';' 
FROM   all_tables 
WHERE  owner = 'PRABINBANIYA'; 

Spool the query result to a file and then run the script file manually to get the desire result.

Sample Output

DESCRIBE prabinbaniya.dummy;
DESCRIBE prabinbaniya.customer;
DESCRIBE prabinbaniya.ord;
DESCRIBE prabinbaniya.employee_temp;
DESCRIBE prabinbaniya.item;
DESCRIBE prabinbaniya.price;
DESCRIBE prabinbaniya.product;
DESCRIBE prabinbaniya.emp;

Another Example:

SELECT 'Grant Select on '
|| owner
||'.'
|| table_name
|| ' to SELECT_ONLY_ROLE;'
FROM dba_tables
WHERE owner LIKE 'SALES_ADMIN';

Similarly, you can create generate DDL or DML script dynamically. Now, think of scenarios where you can apply brute force to generate SQLsyntax.

In a next tutorial, I will show you how you can generate and execute these commands without having to save it in a file.