PL/SQL function to check for invalid characters in a string

Question

I was trying to create a function in pl/sql that will accept a String and to split the String into an array then compare the array of the split string into the invalid characters stored in the database.

I am still experimenting, haven't tested the apex_string.split since I encountered these errors: Error(7,1): PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following: begin function pragma procedure subtype type current cursor delete exists prior The symbol "begin" was substituted for "DECLARE" to continue.

Error(38): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge

I'm kinda new to PL/SQL and I am open to any comments

thanks

create or replace FUNCTION invalid_checker(inword IN varchar2)
RETURN boolean
IS 
    hasInvalid boolean;


declare
    CURSOR invalids IS select invalidChar from InvalidCharacter;  --table that contains the invalid characters
    Type wordsplit IS VARRAY(100) OF VARCHAR2(2);
    splitcount INTEGER;
    invalidscount INTEGER ;
    ctr INTEGER := 0;
    invalidctr INTEGER :=0;
    wordarray wordsplit;    
    
begin

open invalids;

wordarray := wordsplit(apex_string.split(inword, null));--split inserted word into array
--splitcount := wordsplit.count
--invalidsctr := invalids.count

FOR splitcount IN wordarray LOOP
    ctr := ctr + 1;
    FOR invalidscount IN invalids LOOP
        invalidctr := invalidctr + 1;
        IF (wordarray(ctr) = invalids(invalidctr)) THEN
            hasInvalid := true;
        END IF;
    END LOOP;
END LOOP;

close invalids;

RETURN hasInvalid;
end;
/

Simply delete declare, it's not needed.

Wernfried Domscheit
– Wernfried Domscheit

2021-04-27 08:37:43 +00:00
Commented Apr 27, 2021 at 8:37 — Wernfried Domscheit
– Wernfried Domscheit, Commented Apr 27, 2021 at 8:37

MT0 · Accepted Answer · 2021-04-27 09:08:20Z

Delete the DECLARE keyword as it is not needed.
You want to iterate the array using:
```
FOR splitcount IN 1 .. wordarray.COUNT LOOP
```
and you do not need to declare the splitcount varaible.
You cannot read a CURSOR multiple times. Instead of a CURSOR use SELECT ... BULK COLLECT INTO ... and collect the invalid values into a collection (and that you can read multiple times).

However, it looks like you could simplify it a lot:

CREATE FUNCTION invalid_checker(inword IN varchar2)
RETURN boolean
IS 
  hasinvalid INTEGER ;    
BEGIN
  SELECT 1
  INTO   hasInvalid
  FROM   (
           SELECT SUBSTR(inword, LEVEL, 1) AS ch
           FROM   DUAL
           CONNECT BY LEVEL <= LENGTH(inword)
         ) c
         INNER JOIN InvalidCharacter i
         ON ( c.ch = i.invalidchar )
  FETCH FIRST ROW ONLY;

  RETURN TRUE;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN FALSE;
END;
/

Then, if you have:

CREATE TABLE InvalidCharacter ( invalidchar ) AS
SELECT 'A' FROM DUAL UNION ALL
SELECT 'C' FROM DUAL UNION ALL
SELECT 'E' FROM DUAL;

And run:

DECLARE
  words SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(
    'BDF',
    'ACE',
    'BDE',
    'ace'
  );
BEGIN
  FOR i IN 1 .. words.COUNT LOOP
    IF invalid_checker( words(i) ) THEN
      DBMS_OUTPUT.PUT_LINE( words(i) || ' invalid' );
    ELSE
      DBMS_OUTPUT.PUT_LINE( words(i) || ' valid' );
    END IF;
  END LOOP;
END;
/

The output is:

BDF valid
ACE invalid
BDE invalid
ace valid

db<>fiddle here

Scott · Accepted Answer · 2021-04-27 23:21:28Z

1

I saw this question and thought, why invoke PL/SQL at all? This can be answered with just SQL.

CREATE TABLE InvalidCharacter ( invalidchar ) AS
SELECT 'A' FROM DUAL UNION ALL
SELECT 'C' FROM DUAL UNION ALL
SELECT 'E' FROM DUAL;

select * from apex_string.split('ORACLE','')
where column_value not in (select invalidchar from invalidcharacter);

Result Sequence
---------------
O
R
L

answered Apr 27, 2021 at 23:21

Scott

5,1303 gold badges26 silver badges38 bronze badges

1 Comment

Scott Over a year ago

The exact deployment of this SQL would depend on the specific needs/inputs you have, but it demonstrates that it can be bundled without PL/SQL.

Collectives™ on Stack Overflow

PL/SQL function to check for invalid characters in a string

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related