Some PL/SQL snippets of code
- backup and recovery of a table
- interactive input with &
- find keys referencing your table
- group and count
- list column names
- list available tables in a schema
Backup and recovery of a table
First create a backup copy of your table
backup create table MY_TABLE_BACK AS select * from MY_TABLE
After your stuff, recovery the original table
insert into MY_TABLE select * from MY_TABLE_BACK delete all rows delete from MY_TABLE_BACK
Interactive input with &
select * from orders where id = &ID
before executing the query will appear a Dialog asking for variable
Find keys referencing your table
-- find foreign keys select table_name from user_constraints where r_constraint_name in (select constraint_name from user_constraints where constraint_type in ('P','U') and table_name = upper('&tableOfInterest') )
Group and count
Group by and count limit to ten rows
select * from (select count(*), t.id_order from order_cart t group by m.id_order order by count(*) desc) where rownum < 10
Rename Columns
alter table MY_TABLE_NAME rename column OLD_NAME to NEW_NAME
List column names
SELECT column_name FROM user_tab_cols WHERE table_name = 'MY_TABLE_NAME'
List available tables in a schema
The following script list data for available tables in specified schema, and order rows by table name.
SELECT * FROM all_tables WHERE owner = upper('&SCHEMA_NAME') ORDER BY table_name
Since there is the parameter &SCHEMA_NAME, before executing the query will appear a dialog in which you can insert the name of schema.
References:
http://viralpatel.net/blogs/2009/08/fast-data-copy-with-create-table-select-from-in-plsql.html