rank

RANK and its mates can be useful functions to use when you need to order a list of records.

For example, I needed to transform a database from OMF (oracle managed files) to OSF (oracle standard files). As you may know, OMF bear no resemblance to the datafile standard most corporates seem to run, a typical omf data file might look like o1_mf_ts_ofsaa_2gt2g1hr_.dbf, while a osf might look like user_01.dbf

Being a good dba I wanted to script and automate the CREATE CONTROLFILE statement. My soltion to the above problem was to use the rank function.

So something like -

select tablespace_name, file_name, rank() over (PARTITION BY tablespace_name order by file_id) "id"

solved my problem and produced an output something like -

tablespace1 somefile1 1
tablespace1 somefile2 2
tablespace2 somefile1 1
...

I was then able to use the above statement to generate the datafile section of the create controlfile file.

EXIT from PL/SQL procedure block

A simple method to EXIT from a PL/SQL block is to use the RETURN keyword. You should also be able to return an EXIT status.

So if we run something like -

1 BEGIN
2 dbms_output.put_line('one.');
3 return;
4 dbms_output.put_line('two.');
5* end;
n990538@edrtst> /
one.

PL/SQL procedure successfully completed.