General SQL Scripts

Convert LONG data types to LOBs

create table old_long_table(c1 number, c2 long); insert into old_long_table values (1, ‘LONG data to convert to CLOB’); create table new_lob_table(c1 number, c2 clob); — Use TO_LOB function to convert LONG to LOB… insert into new_lob_table select c1, to_lob(c2) from old_long_table;

Delete duplicate values from a table

DELETE FROM my_table WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM my_table GROUP BY delete_col_name);

Demonstrate Oracle temporary tables

drop table x / create global temporary table x (a date) on commit delete rows — Delete rows after commit — on commit preserve rows — Delete rows after exit session / select table_name, temporary, duration from user_tables where table_name = ‘X’ / insert into x values (sysdate); select * from x; commit; — Inserted…

Demonstrate VARRAY database types

CREATE OR REPLACE TYPE vcarray AS VARRAY(10) OF VARCHAR2(128); / CREATE TABLE varray_table (id number, col1 vcarray); INSERT INTO varray_table VALUES (1, vcarray(‘A’)); INSERT INTO varray_table VALUES (2, vcarray(‘B’, ‘C’)); INSERT INTO varray_table VALUES (3, vcarray(‘D’, ‘E’, ‘F’)); SELECT * FROM varray_table; SELECT * FROM USER_VARRAYS; — SELECT * FROM USER_SEGMENTS; — Unnesting the collection:…

Demonstrate Oracle database types and object tables

drop type employee_typ; create type employee_typ as object ( empno NUMBER, emp_name varchar2(30), hiredate date, member function days_at_company return NUMBER, pragma restrict_references(days_at_company, WNDS) ) / create type body employee_tye is begin member function days_at_company return number is begin return (SYSDATE-hiredate); end; end; / show errors drop type department_typ; create type department_typ as object ( deptno…

Count the number of rows for ALL tables in current schema

set termout off echo off feed off trimspool on head off pages 0 spool countall.tmp select ‘SELECT count(*), ”’||table_name||”’ from ‘||table_name||’;’ from user_tables / spool off set termout on @@countall.tmp set head on feed on

Demonstrate simple encoding and decoding of messages

SELECT TRANSLATE( ‘HELLO WORLD’, — Message to encode ‘ABCDEFGHIJKLMNOPQRSTUVWXYZ ‘, ‘1234567890!@#$%^&*()-=_+;,.’) ENCODED_MESSAGE FROM DUAL / SELECT TRANSLATE( ’85@@%._%*@4′, — Message to decode ‘1234567890!@#$%^&*()-=_+;,.’, ‘ABCDEFGHIJKLMNOPQRSTUVWXYZ ‘) DECODED_MESSAGE FROM DUAL /