Oracle Collection of Useful Stuff

General pitfalls

• Every statements should be ended with ;
• Create Procedure statements shall be ended with a ; and a / on the next line like:

  create or replace
  PROCEDURE ExampleProc
  IS
  BEGIN
    NULL;
  END;
  /

• Identifiers shall have a maximum of 30 characters, otherwise the code using them fails

Output text from within a script

PROMPT "text"

Execute content of another file

@script.sql

Loads the content of the file script.sql and executes it.

Alter a table in a procedure

EXECUTE IMMEDIATE 'alter table PERSON drop column FIRST_NAME';

If you want to use DDL in a procedure you need to wirte it as Dynamic SQL not Static SQL.

Drop index if exists

Sometimes you don’t want an SQL-Script to raise an exception if you try to delete an index which does not exist. But unfortunately there is no if exists option for the drop index statement. You can however define a procedure for that case:

create or replace
PROCEDURE DROP_INDEX_IF_EXISTS
	(INDEX_NAME IN VARCHAR2)
IS
  index_does_not_exist EXCEPTION;
  PRAGMA EXCEPTION_INIT(index_does_not_exist, -1418);
begin
	execute immediate 'drop index '||INDEX_NAME;
	exception when index_does_not_exist then null;
end;
/

Drop materialized view if exists

Sometimes you don’t want an SQL-Script to raise an exception if you try to delete a materialized view which does not exist. But unfortunately there is no if exists option for the drop materialized view statement. You can however define a procedure for that case:

create or replace
create or replace
PROCEDURE DROP_MAT_VIEW_IF_EXISTS
	(MV_NAME IN VARCHAR2)
IS
  mat_view_does_not_exist EXCEPTION;
  PRAGMA EXCEPTION_INIT(mat_view_does_not_exist, -12003);
begin
	execute immediate 'drop materialized view '||MV_NAME;
	exception when mat_view_does_not_exist then null;
end;
/

Drop view if exists

Sometimes you don’t want an SQL-Script to raise an exception if you try to delete a view which does not exist. But unfortunately there is no if exists option for the drop view statement. You can however define a procedure for that case:

create or replace
PROCEDURE DROP_VIEW_IF_EXISTS
	(VIEW_NAME IN VARCHAR2)
IS
  index_does_not_exist EXCEPTION;
  PRAGMA EXCEPTION_INIT(view_does_not_exist, -942);
begin
	execute immediate 'drop view '||VIEW_NAME;
	exception when view_does_not_exist then null;
end;
/

Fixing ORA-29861:
domain index is marked LOADING/FAILED/UNUSABLE

First of all you need to identify the indices. This can be done with the following statement:

select index_name, domidx_opstatus, parameters from user_indexes where domidx_opstatus='LOADING' OR domidx_opstatus='FAILED' OR domidx_opstatus='UNUSABLE'

The easiest solution to get rid of the error is to drop and recreate those indices

Batch-Script to Startup sqlplus with a connection string

The following script asks for a username and password and does then startup sqlplus logged on with the specified user.

set ORACLE_SID=mysid

set user=
set /p user=Please enter user: 
set pass=
set /p pass=Please enter password: 

sqlplus %user%/%pass%@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.0)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(service_name=sid.domain)))

If you want to login as sysdba using the batchscript you need to change the sqlplus command to:

sqlplus sys/%user%/%pass%@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.0)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(service_name=sid.domain))) as sysdba

Combining the rows of multiple queries into one resultset

select count(*) from (
  select translation from german
  UNION
  select translation from french
  UNION
  select translation from english
);

The above query would count all translations in the tables german, french and english

Set a new password for a user

alter user user_name identified by new_password