PL / SQL

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

Create a backup copy of your table

backup
create table MY_TABLE_BACK
AS
select * from MY_TABLE

Restore the table from the backup

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 referencing your table of interest.

-- 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

• https://stackoverflow.com/questions/926437/is-there-a-way-to-view-relationships-in-oracle-sql-developer
• https://www.viralpatel.net/fast-data-copy-with-create-table-select-from-in-plsql/