PL/SQL: Trapping NO_DATA_FOUND

Doing a lookup in a procedure and don't want it to die with a NO_DATA_FOUND error? You could trick it into returning a null by using an aggregation function, but that may add I/O's. You could write your own function to handle it, but isn't that a bit kludgey? Wrap the offending statement in its own block with an exception handler:

if(iLOGIN_UID = 0 or iLOGIN_UID is null) then
begin
   select
       LOGIN_UID
   into
       oReturn
   from
       LOGIN
   where
       GUID = iGUID;

   exception when NO_DATA_FOUND then oReturn := null;
end;
else
   oReturn := iLOGIN_UID;
end if;

Tags: plsql, oracle

Cardinality

Need to know how many rows are in your tables?

• T-SQL - The Transact-SQL statement uses sysindexes

• PL/SQL - The PL/SQL block requires a schema name and does an actual count against the table. If you have current statistics (or permissions to update them), you can just do 'select table_name, num_rows from all_tables'

PL/SQL: Random Data Generation

I recently created a script to generate random data. I did not want random strings for names, so I downloaded name lists from the U.S. Census names file. The data is all uppercase, I preferred to go with sentence case. An easy update statement later and I had names.

In order to randomly retrieve a row:

select
        NameLast
into
        xNameLast
from
        (
                select
                        NameLast
                from
                        NameLast
                order by
                        dbms_random.value
        )
where
        rownum = 1;

Why does this work? We are limiting the resultset to one row by using 'where rownum = 1'. That row is being randomized by the derived statement's ORDER BY clause referencing a random value... Which I do not understand. ORDER BY needs to reference columns, which in this case would always be one. Yet it does randomly select a row. So it must be sorting it based on a surrogate column or some such. I would ask Tom, but he is busy now.

Random names add some authenticity to the data set and just makes for good reading; Charity Certain and Refugio Cheeseman are two of my favorites.

I thought about loading a dictionary file so that I could randomly generate phrases... Selecting a random number of random words would be an easy enhancement.

In any event, feeding your procedures with random data is easy to do with PL/SQL using the DBMS_RANDOM package. The following statements outline how to generate most values.

-- Retrieve a whole number between x and y
select trunc(dbms_random.value( x, y)) from dual;

-- Retrieve a string of x characters
select dbms_random.string('A', x) from dual;

-- Retrieve a date between now and (now – x)
select sysdate - (trunc(dbms_random.value(0, x))) from dual;