Sociable PL/SQL – associative arrays indexed by VARCHAR2

PL/SQL tables are a really good way of array processing in PL/SQL. In 99% of cases, using a table indexed by PLS_INTEGER works a treat, but what about those times when life would be that bit easier if you could index by VARCHAR2 ?

I was recently presented with one such instance, courtesy of some unconventional table design by now long-departed wise-men.
These gurus had decided to use a synthetic key on a reference table…except the natural key was still in the table….but the actual primary key was half of the natural key plus the synthetic key.

Oh how I would’ve loved to meet these ancients and received the benefit of their wisdom, with the aid of a spot-light, some gaffer tape…and a very sharp stick.

This is a theme I will return to in more detail in a future post ( yep, the 10 things that really wind me up post, coming after the quick ‘n’ dirty tuning post I promised a couple of months ago).

Anyway, back to the PL/SQL table madness.

In this example, we have a table called COUNTRIES – not very original, but hey, the pub’s open so we haven’t got time to hang around.

CREATE TABLE countries(
code VARCHAR2(3),
country VARCHAR2(50))
/

The COUNTRY table has the following records :-


SQL> select * from countries;
CODE COUNTRY
---- -----------------------
AUS AUSTRALIA
BEL BELGIUM
FRA FRANCE
GBR UNITED KINGDOM
GER GERMANY
JPN JAPAN
ITA ITALY
USA UNITED STATES
NZL NEW ZEALAND


9 rows selected.


Now, we have a situation where we want to use the country code for three of those countries, we know which three we want to get. But we then want to use them programatically elsewhere without having to faff about working out which order they are in in our array….

--
-- associate.sql
-- Script to demonstrate a PL/SQL associative array indexed by something other
-- than a number.
-- Mike 5/10/09
--
set serveroutput on
DECLARE
  TYPE typ_country_codes IS TABLE OF countries.code%TYPE INDEX BY countries.country%TYPE;
  tbl_country_codes typ_country_codes;
  --
  -- In this instance, we want all the English speaking countries...except for the USA
  -- To paraphrase the colonel in Apocalypse Now - "Yankee don't play cricket !"
  --
  CURSOR c_countries IS
    SELECT code, country
    FROM countries
    WHERE country in ('AUSTRALIA', 'NEW ZEALAND', 'UNITED KINGDOM');
BEGIN
  FOR r_countries IN c_countries LOOP
    --
    -- assign the code to the table using the value of the country column as the index
    --
    tbl_country_codes(r_countries.country) := r_countries.code;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE( 'Country code for Australia is '||tbl_country_codes('AUSTRALIA'));
  DBMS_OUTPUT.PUT_LINE( 'Country code for New Zealand is '||tbl_country_codes('NEW ZEALAND'));
  DBMS_OUTPUT.PUT_LINE( 'Country code for United Kingdom is '||tbl_country_codes('UNITED KINGDOM'));
END;
 /

And that’s stumps.
Please let me know if you find a less tortuous circumstance in which you find this useful.

Des Cartes Must Die – a Killer App for PL/SQL

Text books, manuals, experts and assorted gurus are fairly insistent on the point that it’s probably a good idea to keep your OLTP systems and Data Warehouses in separate instances ( ideally on separate servers).
Meanwhile, back in the real world, you’ve got a bit of a problem.
Not only do you have a Data Warehouse competing with your OLTP App for resources on the same instance, the DW users have access to the database via SQL and are extremely reluctant to give it up.
Your reasoned explanation as to why this is a bad idea and that you can’t guarantee performance of any application on the database as a result has fallen on deaf ears.
The application often grinds to a halt when one of the DW users generates a cartesian product, or just runs a humping great query at a peak time.

The process for killing such rogue sessions has been complicated somewhat by your company’s enthusiastic embracing of outsourcing. Indeed, getting this done involves the user raising a call with the help desk, who pass it to you. You have to raise another call to the DBA’s via a call-management system and hope that someone is up as it’s early/late/the middle of the night wherever they are.
Even if all runs smoothly, it still takes around half-an-hour to get the runaway session terminated and all the while, users are complaining about performance and that clammy feeling on the back of your neck is the laboured breathing of a boss who JUST WANTS IT FIXED.

Having gone through this particular loop several times it becomes apparent that (a) no-one is going to sign-off on the new hardware/Oracle license/time and effort to split out these warring applications and (b) the fact that you can’t guarantee performance for anything running on this setup is accepted without question right up to the time things grind to a halt – means that desperate measures are called for.

Yep, the users running those rogue queries are going to get the facility to kill their own sessions.

At this point, it’s probably worth outlining a couple of requirements ( if only to stop your DBA hyperventilating after reading that last sentence) :-

1. Users need to be able to terminate their own sessions WITHOUT being granted the ALTER SYSTEM privilege
2. Users will only be allowed to kill their own sessions, not anyone elses
3. Users won’t be able to kill the current session as that will obviously be the result of a user error
4. Users need to see enough information about their own current sessions to work out which one they want to kill.

Now Baldrick, the raw materials for this cunning plan are :-

• A Global Temporary Table
• A stored procedure to populate the GTT
• A stored procedure to kill the session

Our target audience is proficient enough in SQL that you can point them at the DBMS_APPLICATION_INFO package and get them to add identifiers to their sessions using this package.

Now for our GTT :-

CREATE GLOBAL TEMPORARY TABLE tmp_user_sessions (
audsid NUMBER,
status VARCHAR2(8),
program VARCHAR2(64),
session_name VARCHAR2(48),
action VARCHAR2(32),
client_info VARCHAR2(64) )
ON COMMIT DELETE ROWS
/

Simple enough, create a Global Temporary Table where all the rows are cleared down at the end of a transaction.
Granting Select, Insert, and Delete on this table will enable a user to see their own rows in this table, but only their own.
The GTT consists of columns taken from V$SESSION. You may have noticed that these do not include the sid and serial# that you need to kill the session. Well, you can get this from V$SESSION by including AUDSID in the predicate, so we’ll keep things simple by allowing the user to work off one piece of information rather than two.
NOTE – the module column in V$SESSION has been changed to SESSION_NAME in this table so it’s obvious to users where the session name they set using DBMS_APPLICATION_INFO can be found.
Next step – give the users a mechanism to populate the table.

CREATE OR REPLACE PROCEDURE get_session_info AS
BEGIN
--
-- First cleardown the table without creating a transaction boundary
-- i.e. can't do a TRUNCATE here
--
DELETE FROM tmp_user_sessions;
--
-- Now populate the table with details of the current sessions
--   INSERT INTO tmp_user_sessions
SELECT audsid, status, program, module, action, client_info
FROM v$session
WHERE username = USER;
END;
/

Nice and simple to call this, no parameters required, simply exec get_session_info or, if they’re TOAD ( or SQLDeveloper) users – begin get_session_info; end;

Now they should have some data to look at in the table.


SQL> exec get_session_info


PL/SQL procedure successfully completed.


SQL> select audsid, session_name
2 from tmp_user_sessions;


AUDSID SESSION_NAME
---------- --------------------------------
1771 SQL*Plus
1765 SQL Developer
1770 huge query

SQL>

Now all they need to do is pass the relevant audsid value to the killer procedure, which looks like this :-

CREATE OR REPLACE PROCEDURE kill_session( p_audsid IN NUMBER) IS
l_sid v$session.sid%TYPE;
l_serial v$session.serial#%TYPE;
l_uname v$session.username%TYPE;

CURSOR c_target( cp_audsid NUMBER) IS
SELECT sid, serial#, username
FROM v$session
WHERE audsid = cp_audsid;

l_command VARCHAR2(200);
e_current_session EXCEPTION;
e_invalid_session EXCEPTION;
e_session_owner EXCEPTION;

BEGIN
--
-- First, make sure that we're not trying to kill the current session
--
IF p_audsid = USERENV('sessionid') THEN
RAISE e_current_session;
END IF;
--
-- Now get the session details
--
OPEN c_target( p_audsid);
FETCH c_target INTO l_sid, l_serial, l_uname;
IF c_target%NOTFOUND THEN
CLOSE c_target;
RAISE e_invalid_session;
END IF;
CLOSE c_target;
--
-- Check that the user actually owns the session we're going to kill
--
IF l_uname != USER THEN
RAISE e_session_owner;
END IF;
--
-- If we're still here, then kill the session
--
l_command := 'ALTER SYSTEM KILL SESSION '
||''''||l_sid||', '||l_serial||''''||' IMMEDIATE';
EXECUTE IMMEDIATE( l_command);
EXCEPTION
WHEN e_current_session THEN
RAISE_APPLICATION_ERROR(-20000, 'You cannot kill the current session.');
WHEN e_invalid_session THEN
RAISE_APPLICATION_ERROR(-20001, 'Could not find this session.');
WHEN e_session_owner THEN
RAISE_APPLICATION_ERROR(-20002, 'Cannot Kill a session you do not own.');
END;
/

NOTE – this procedure MUST be created under a schema which has ALTER SESSION granted to it directly – i.e. not via a role.

You can then grant execute on this procedure to the relevant users.

So, now the user can kill their own “huge query” by simply entering :-

SQL> exec kill_session(1770)

PL/SQL procedure successfully completed.

SQL>

The target session will then be terminated and the next time the user interacts with it they should get an ORA-03113 : end-of-file on communication channel.

Whilst this utility isn’t a panacea, it does make the time lag between recognising the approach of Mr Des Cartes, and his ultimate demise, somewhat shorter.

The computer has forgotten my password !

The quote in the title of this entry is from my all-time favourite support call, made to be by a very irate and hassled user.
Taking pity on their plight, I dealt with the call and refrained from pointing out the flaws in this assertion.

Several years later, I had cause to remember this incident, although the circumstances were very different.

The place I was working at had gone in for outsourcing…in a big way. Whilst we still had control of the application owner account, our DBAs were in Madrid; Unix admins in Switzerland and other development teams in Poland.
As the application owners, we were in the process of rolling out a major enhancement to the app.
All was going well, we’d rehearsed the rollout several times previously and most of the 300-odd scripts had been completed without a hitch.
We then came to a small change on one of the other applications on the machine, required to account for the changes we were making to the main application.
At this point, several things became apparent :-
the password we had for the user we needed to run these scripts as wasn’t correct
the person or persons who had this information were definitely unavailable ( it was Saturday afternoon)
changing the password on the account could potentially cause issues with batch jobs connecting as this user ( although we had no idea what these could be as we didn’t know the application in question).
Faced with the prospect of having to rollback the release and come back the following Saturday ( F.A. Cup Final day, no less), we opted to “cheat”.

Utilizing that useful little dodge known as “identified by values” we were able to change the relevant password, connect as the user and run the scripts and then change the password back to it’s original value, without ever having to know what that value was.

For an illustration of this technique, I used an account which had CREATE USER, ALTER USER, and SELECT ANY DICTIONARY privileges.

Let’s start by creating a user :-

SQL> grant connect to mystery identified by secret;
Grant succeeded.

Now, we can look at DBA_USERS to find a hash value for the password :-

SQL> select password
2 from dba_users
3 where username = 'MYSTERY';
PASSWORD
------------------------------
33CF630B57549B54

To change the password for this user, it’s simply a matter of :-

SQL> alter user mystery identified by iknowurpwd;
User altered.

Once you’ve done what you need to as this user and need to reset the password to it’s original value :-

SQL> alter user mystery identified by values '33CF630B57549B54';
User altered.

And hey presto, off to the pub ( and next weekend is still free to watch the footie !)

Oerr Missus – Carry On find that error

Oracle on linux has quite a useful little utility. By simply typing “oerr” then an oracle error number, you’ll get back the text of the message and (occasionally) some useful info about the error.
For those of you who aren’t lucky enough to have your database running on a proper OS, there is a way of getting something almost as good from the database itself.

This sort of thing comes in handy in circumstances such as when Forms spits an error at you like :-
“FRM-40735: PRE-QUERY trigger raised unhandled exception ORA-1403″

You want to find out what ORA-1403 is so you can get on with working out what the problem is.
Well, you can create this simple function on the database :-

CREATE OR REPLACE FUNCTION oerr ( p_errno IN PLS_INTEGER)
   RETURN VARCHAR2
AS
   BEGIN
      IF p_errno IN (1, 100) THEN
         --
         -- All Oracle errors are negative numbers
         --...except these two
         --
         RETURN sqlerrm(p_errno);
      ELSE
         RETURN sqlerrm(p_errno * -1);
      END IF;
   END;
/


Then, at the SQL prompt, simply issue the following query :-

SQL> SELECT oerr(1403) FROM dual;


ORA-1403 No Data Found


As for working out WHY forms is not behaving, I’m afraid you’re on your own there !

Clock watching – Instrumenting PL/SQL with timing information

You know what it’s like, that piece of code you wrote last week and simply whizzed along in the Development environment is now running like a dog in test. You want to get a quick pointer as to where the bottle-neck is before you get up to your elbows in trace files.

In my experience, there’s a better than even chance that your problem is caused by some issue with an index – i.e. it’s missing / disabled/ stats are out of date. Checking the index is a bit like starting to work out why you can’t switch on the TV by checking that it’s plugged in. But I digress.
If it’s a bit of straight SQL, the easiest way to check how fast it is, is to SET TIMING ON in SQL*Plus and just run it. But what if your problem is in a PL/SQL block containing multiple SQL statement ?
It’s at this point, you congratulate yourself for listening to that nice Mr Kyte when he was going on about “instrumentation”.
You know that Oracle now has this nice little function that returns the time in 100ths of a second ( DBMS_UTILITY.GET_TIME) and that you can include conditional compiler directives to induce your carefully crafted masterpiece to spit out loads of useful info.
Furthermore, you’ve written a function to return the difference between two DBMS_UTILITY.GET_TIME values, and looks something like this :-
CREATE OR REPLACE FUNCTION pls_timing( p_start IN PLS_INTEGER, p_end IN PLS_INTEGER) RETURN VARCHAR2 IS
    l_elapsed PLS_INTEGER;
    l_huns PLS_INTEGER;
    l_secs PLS_INTEGER;
    l_mins PLS_INTEGER;
    l_hrs PLS_INTEGER;
    l_formatted_time VARCHAR2(20);
BEGIN
    l_elapsed := p_end - p_start;
    l_huns := MOD( l_elapsed, 100);
    l_secs := FLOOR(MOD( l_elapsed, 6000)/ 100);
    l_mins := FLOOR(FLOOR( MOD( l_elapsed , 360000))/6000);
    l_hrs := FLOOR(l_elapsed / 360000);
    l_formatted_time := LPAD(l_hrs, 2, '0')||':'||LPAD( l_mins, 2, '0')||':'||LPAD( l_secs, 2, '0')||'.'||LPAD( l_huns,2,'0');
    RETURN l_formatted_time;
END;


All in all, you’re a bit of a clever-clogs really.
Now all you need to do is fire up SQL*Plus and away you go.

Your code will probably look something like this :-

set serveroutput on size 1000000
--
-- slow.sql
-- PL/SQL script to demonstrate usage of timing package in conjunction
-- with conditional compilation
--
ALTER SESSION SET PLSQL_CCFLAGS = 'debug:TRUE'
/

DECLARE
    l_start PLS_INTEGER;
    l_end PLS_INTEGER;
    l_elapsed VARCHAR2(100);
BEGIN
    $IF $$debug $THEN
        l_start := DBMS_UTILITY.GET_TIME;
    $END
--
-- This is where your SQL would normally go.
-- If you want to run this example as is, make sure you have
-- execute privileges on DBMS_LOCK
--
    DBMS_LOCK.SLEEP(5);
    $IF $$debug $THEN
        l_end := DBMS_UTILITY.GET_TIME;
        l_elapsed := pls_timing( l_start, l_end);
        DBMS_OUTPUT.PUT_LINE(l_elapsed);
    $END
    DBMS_OUTPUT.PUT_LINE('Yawn! is it that time already ?');
END;
/

The output will probably be something like :-

SQL> @slow.sql

Session altered.

00:00:05.01

Yawn! is it that time already ?

PL/SQL procedure successfully completed.

SQL>

Note the ALTER SESSION SET PL_CC_FLAGS='debug:TRUE'.
This effectively declares a variable called debug to be used at compile time and sets it’s value to TRUE.
So, when the compiler comes across the line
$IF $$debug $THEN it evaluates this to TRUE and processes the commands up to the
$END
If the problem is code related ( and it usually is), you should get a fair idea of where to start looking.
Which reminds me, at some point I will be writing something about factors affecting performance…don’t hold your breath, it’s not earth-shattering.

Lazy Insert

Do you ever get that annoying colleague quoting Kyte at you … “if you can do it SQL then do it in SQL” ?
One of the more tedious aspects of development is those repetitive insert scripts – usually for adding reference data.
Consider a table like this :-


CREATE TABLE my_ref_data_tab(
    id NUMBER(3),
    ref_val VARCHAR2(30),
    active_ind VARCHAR2(1),
    created_by VARCHAR2(30) NOT NULL,
    creation_date DATE NOT NULL,
    modified_by VARCHAR2(30),
    modified_date DATE,
    CONSTRAINT mrdt_pk PRIMARY KEY ( id),
    CONSTRAINT mrdt_uk UNIQUE( ref_val))
/


The synthetic key is populated from a sequence.

Now, I need to create 5 records in this table ( it could be any number, but I got bored typing bloody insert statements).
Usually, I’d have to do something like :-

spool myinsert.log
INSERT INTO my_ref_data_tab( id, ref_val, active_ind, created_by, creation_date)
VALUES( mrdt_id.NEXTVAL, 'MIKE', 'Y', USER, SYSDATE)
/
INSERT INTO my_ref_data_tab( id, ref_val, active_ind, created_by, creation_date)
VALUES( mrdt_id.NEXTVAL, 'IS', 'Y', USER, SYSDATE)
/
INSERT INTO my_ref_data_tab( id, ref_val, active_ind, created_by, creation_date)
VALUES( mrdt_id.NEXTVAL, 'A', 'Y', USER, SYSDATE)
/
INSERT INTO my_ref_data_tab( id, ref_val, active_ind, created_by, creation_date)
VALUES( mrdt_id.NEXTVAL, 'LAZY', 'Y', USER, SYSDATE)
/
INSERT INTO my_ref_data_tab( id, ref_val, active_ind, created_by, creation_date)
VALUES( mrdt_id.NEXTVAL, 'SOD', 'Y', USER, SYSDATE)
/
spool off

Loads of unnecessary typing, lots of cut-and-paste…and more often than not, lots of debugging when you realise you’ve made a typo, or cut and paste an insert without changing the value you’re inserting. And what happens when someone decides to change the structure of the table ?

All in all, life is far too short. So….

set serveroutput on size 1000000
spool lazy.log
DECLARE
    l_message VARCHAR2(15) := '1 row inserted.';
    PROCEDURE ins( p_ref_val VARCHAR2) IS
        BEGIN
            INSERT INTO my_ref_data_tab( id, ref_val, active_ind, created_by, creation_date)
            VALUES( mrdt_id.NEXTVAL, p_ref_val, 'Y', USER, SYSDATE);
        END ins;
BEGIN
    ins( 'MIKE');
    DBMS_OUTPUT.PUT_LINE(l_message);
    ins('IS');
    DBMS_OUTPUT.PUT_LINE(l_message);
    ins('A');
    DBMS_OUTPUT.PUT_LINE(l_message);
    ins('LAZY');
    DBMS_OUTPUT.PUT_LINE(l_message);
    ins('SOD');
    DBMS_OUTPUT.PUT_LINE(l_message);
END;
/
spool off

Note there’s no exception handling here – if anything fails, you’ll want to rollback the lot and figure out what’s happening. On the plus side however, you’ve only got one insert statement to puzzle over rather than five.

You Don’t Have to be Posh to be Privileged

Working in multiple environments, I sometimes get tripped up by the RDBMS telling me a program unit doesn’t exist when I know it does.
What with system privileges either being granted directly, or via roles, I find the following query really useful in working out just what privileges I’ve got on an environment.

SELECT distinct privilege
FROM dba_sys_privs
WHERE grantee = USER
OR grantee IN (
    SELECT granted_role
    FROM dba_role_privs
    START WITH grantee = USER
    CONNECT BY PRIOR granted_role = grantee)
ORDER BY 1

As a SQL Developer user, I drop this into my User Defined Reports folder so it’s always handy.

CONTINUE statement in PL/SQL – or how GOTO can be a useful member of society

Coding Standards, Best Practices, generally accepted truth – these almost always have something to say on the subject of the evil “GOTO” statement.
Invariably, what’s said is don’t use it. Ever. On pain of pain.
It’s the absolute and unequivocal nature of these strictures that I find annoying.
It may be the case that, in the primordial times of Computer Science, before young Larry Ellison dropped out of College, grew a beard and started getting ideas about applying this Relational Theory stuff to a working database, GOTO was abused to create the dreaded “spaghetti code”.
Since that time, any tentative mention of this structure outside of a Basic Program has been met with revulsion.
However, it’s worth noting that, even such luminaries as Kernighan and Ritchie, whilst describing the goto statement in C as “infinitely abusable” do concede that there are times when gotos may find a place.
As things stand right now (at least, if you’re in one of those shops still waiting for 11g, one of those places can be found in PL/SQL.
I think it’s reasonable to compare PL/SQL ( based on Ada, which in turn is based on Pascal) with C due to they’re shared 3-GL ancestry.
Prior to 11g, PL/SQL is missing one of the features common to many 3-GLs – including C – the continue keyword, which allows the programmer the option to skip a single iteration of a loop without all that mucking about with IF-THEN-ELSE.
For example – say we want to loop through an array of numbers but we only want to process the positive numbers in that array and ignore any negative numbers.
In ‘C’, this is fairly straightforward ….

for( i = 0; i < n; i++)
{
    if( a[i] < 0)
       continue;
    ... /* if we get here then the element is positive so do stuff to it */
}


Contrast this with PL/SQL


FOR i IN a.FIRST..a.LAST LOOP
   IF a(i) > 0 THEN
      -- do stuff to the positive element
   END IF;
   -- not immediately obvious that we do anything with
   -- the negative elements – you have to read through
   -- all of the code to work this out.
END LOOP;

So, no continue statement, a bit less clarity. However, now GOTO is out on parole, we have a job for it …


FOR i IN a.FIRST..a.LAST LOOP
   IF a(i) < 0 THEN
      GOTO continue;
   END IF;
   --
   -- If we get here then the element is positive so do stuff
   --
   
<<continue>> -- continue label
      NULL; -- need a statement here to
         -- stop the compiler choking
END LOOP;


You may consider this to be a somewhat artifical way of introducing CONTINUE into PL/SQL. All I’ll say to that is – how do you declare BOOLEANs in C ?

#define TRUE 1
#define FALSE 0


Simple is good – an ISNUMBER Function for PL/SQL

A while ago, I was presented with a requirement to read in a variable which could either be a character string or an integer, the subsequent data lookup being dependent on whether the variable was numeric or not.
My initial reaction was to think of writing a generic ISNUMBER function to do this. However, after brief consideration, I realised that, if it was to be truly generic, it would have to handle any numeric values, not just whole numbers. Furthermore, the value passed in maybe negative.
Initially, I came up with this :-

CREATE OR REPLACE FUNCTION isnumber( pa_string VARCHAR2)
    RETURN BOOLEAN IS
    l_ascii PLS_INTEGER;
    l_decimal BOOLEAN := FALSE;
    l_pos PLS_INTEGER := 1;
BEGIN
   --
   -- First character may be a "-"
   --
   IF SUBSTR( pa_string,1,1) = '-' THEN
      --
      -- start checking from the second character
      -- provided the string is more than one character
      IF LENGTH( pa_string) > 1 THEN
         l_pos := 2;
      ELSE
         RETURN FALSE;
      END IF;
   END IF;
   --
   -- Now loop through the input string looking for non-numeric characters
   -- NOTE - we need to be aware that there may be a decimal in here
   -- somewhere
   --
   FOR i IN l_pos..LENGTH(pa_string) LOOP
      l_ascii := ASCII(SUBSTR( pa_string, i, 1));
      IF l_ascii = 46 THEN
         -- decimal point. Is this the first one we've hit ?
         IF l_decimal THEN
            -- already encountered one             -- so this is not a number
            RETURN FALSE;
         ELSE
            -- need to remember that we've hit a decimal point
            -- as a number can contain             --only 1
            l_decimal := TRUE;
         END IF;
      ELSIF l_ascii NOT BETWEEN 48 AND 57 THEN
         --
         -- not a character between 0 and 9 therefore          -- this isn't a number
         --
         RETURN FALSE;
      END IF;
   END LOOP;
   --
   -- If we get here then we know it's a number
   --
   RETURN TRUE;
END;

I was feeling quite pleased with myself, until a colleague of mine showed me his version :-

CREATE OR REPLACE FUNCTION isnumber( pa_string VARCHAR2) RETURN BOOLEAN IS
   l_dummy NUMBER;
BEGIN
   l_dummy := TO_NUMBER( pa_string);
   RETURN TRUE;
EXCEPTION
   WHEN INVALID_NUMBER THEN
      RETURN FALSE;
END;

Note that there’s no WHEN-OTHERS exception. If something other than a VARCHAR2, NUMBER or DATE is passed in then it’s likely something’s gone horribly wrong so we want the function to complain…loudly.
No mucking about with ASCII; no walking through the entire input string; and basically ONE active line of code :-
   l_dummy := TO_NUMBER( pa_string);
I know which one I’d rather support.
In the words of Leonardo Da Vinci – “Simplicity is the ultimate sophistication.”
OK, so I was going to quote Ron Greenwood ( “Simplicity is genius”), but Da Vinci looks far more impressive !