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