Perhaps this is a poor use case for what I'm trying to accomplish, but I've read through dozens of pages and I can't figure out if this is possible in Oracle or not.
I would like to accomplish something similar to another stackoverflow question: How to chain calls in a pl/sql object type of functions returning SELF
In my code, I have a type named parseName with a constructor function and 3 member functions: getFirstName, getMiddleName, getLastName. I would like to have a fourth member function called: cleanString which removes all characters not between A-Z or a-z.
Example calls:
SELECT parseName('John123 Doe').getFirstName().cleanString()
FROM dual;
SELECT parseName('John123 Doe').getFirstName()
FROM dual;
Here's the type as I have it written so far.
CREATE OR REPLACE TYPE parseName AS OBJECT
(
g_name VARCHAR2(255),
g_parts NUMBER,
g_first_name VARCHAR2(255),
g_middle_name VARCHAR2(1),
g_last_name VARCHAR2(255),
-- constructor function
CONSTRUCTOR FUNCTION parseName
(p_name IN VARCHAR2)
RETURN self AS result,
-- member functions
MEMBER FUNCTION getFirstName RETURN VARCHAR2,
MEMBER FUNCTION getMiddleName RETURN VARCHAR2,
MEMBER FUNCTION getLastName RETURN VARCHAR2
);
/
SHOW ERRORS
/
CREATE OR REPLACE TYPE BODY parseName IS
-- populateValues
CONSTRUCTOR FUNCTION parseName
(p_name IN VARCHAR2)
RETURN self AS result
IS
-- other variables
v_name VARCHAR2(255);
v_length NUMBER;
v_parts NUMBER;
v_instr NUMBER;
BEGIN
-- save off input
v_name := TRIM(p_name);
-- check
IF v_name IS NULL THEN
self.g_first_name := 'Unknown';
self.g_middle_name := ' ';
self.g_last_name := 'Unknown';
RETURN;
END IF;
-- otherwise, fill our global
self.g_name := v_name;
-- exit
RETURN;
END;
/* getFirstName */
/* --------------------------------------- */
MEMBER FUNCTION getFirstName
RETURN VARCHAR2
IS
v_parts NUMBER;
BEGIN
-- did we get a null on construct?
IF self.g_first_name IS NOT NULL THEN
RETURN self.g_first_name;
END IF;
-- how many spaces do we have?
v_parts := LENGTH(self.g_name) - LENGTH(REPLACE(self.g_name,' ',''));
-- if 0 spaces, return the name
IF v_parts = 0 THEN
RETURN self.g_name;
-- else, return everything up to the space
ELSE
RETURN TRIM(SUBSTR(self.g_name,1, INSTR(self.g_name,' ',1) ));
END IF;
END getFirstName;
/* getMiddleName */
/* --------------------------------------- */
MEMBER FUNCTION getMiddleName
RETURN VARCHAR2
IS
v_parts NUMBER;
v_instr2 NUMBER;
v_instr1 NUMBER;
BEGIN
-- did we get a null on construct?
IF self.g_middle_name IS NOT NULL THEN
RETURN NULL;
END IF;
-- how many spaces do we have?
v_parts := LENGTH(self.g_name) - LENGTH(REPLACE(self.g_name,' ',''));
-- if we have zero spaces, we only have a first name, return null
IF v_parts = 0 THEN
RETURN NULL;
-- don't do middle if we only have 1 space
ELSIF v_parts = 1 THEN
RETURN NULL;
-- else, we've got more than one, so grab between space 1 and 2
ELSE
v_instr2 := INSTR(self.g_name,' ',1,2);
v_instr1 := INSTR(self.g_name,' ',1,1);
RETURN TRIM( SUBSTR(self.g_name, v_instr1, (v_instr2-v_instr1) ));
END IF;
END getMiddleName;
/* getLastName */
/* --------------------------------------- */
MEMBER FUNCTION getLastName
RETURN VARCHAR2
IS
v_parts NUMBER;
BEGIN
-- did we get a null on construct?
IF self.g_last_name IS NOT NULL THEN
RETURN self.g_last_name;
END IF;
-- how many spaces do we have?
v_parts := LENGTH(self.g_name) - LENGTH(REPLACE(self.g_name,' ',''));
-- if we have zero spaces, we only have a first name, return 'Unknown'
IF v_parts = 0 THEN
RETURN 'Unknown';
-- if have 1 space, the space on is the last name
ELSIF v_parts = 1 THEN
RETURN TRIM( SUBSTR(self.g_name, INSTR(self.g_name,' ',1,1), LENGTH(self.g_name)) );
-- else, we've got more than one, go from 2 to end
ELSE
RETURN TRIM( SUBSTR(self.g_name, INSTR(self.g_name,' ',1,2), LENGTH(self.g_name)) );
END IF;
END getLastName;
END;
/
SHOW ERRORS
/
.
Thanks for any advice that you can provide.
I would like to have a fourth member function called: cleanString which removes all characters not between A-Z or a-z.
Example calls:
SELECT parseName('John123 Doe').getFirstName().cleanString() FROM dual;
When you call .getFirstName() the expectation is that it returns the person's first name (after all that is what the member function's name says) and the name is a VARCHAR2 data type. VARCHAR2 is a primitive data type and is not an object that you could (somehow) extend to have a .cleanString() member function.
You can either define a cleanString() function:
CREATE FUNCTION cleanString( value VARCHAR2 ) RETURN VARCHAR2
IS
BEGIN
RETURN REGEXP_REPLACE( value, '[^[:alpha:]]+' );
END cleanString;
/
And then call:
SELECT cleanString( parseName('John123 Doe').getFirstName() )
FROM DUAL;
Or you could make a member function that cleans the names before you return them:
Oracle 11g R2 Schema Setup:
CREATE OR REPLACE TYPE parseName AS OBJECT
(
g_name VARCHAR2(255),
g_first_name VARCHAR2(255),
g_middle_name VARCHAR2(1),
g_last_name VARCHAR2(255),
-- constructor function
CONSTRUCTOR FUNCTION parseName(p_name IN VARCHAR2) RETURN self AS result,
-- member functions
MEMBER FUNCTION cleanNames RETURN parseName,
MEMBER FUNCTION getFirstName RETURN VARCHAR2,
MEMBER FUNCTION getMiddleName RETURN VARCHAR2,
MEMBER FUNCTION getLastName RETURN VARCHAR2
);
/
CREATE OR REPLACE TYPE BODY parseName IS
-- populateValues
CONSTRUCTOR FUNCTION parseName(p_name IN VARCHAR2) RETURN self AS result
IS
BEGIN
g_name := TRIM(p_name);
g_first_name := REGEXP_SUBSTR( g_name, '^(\S+)\s+((\S*?)\s+)?(.*)$', 1, 1, NULL, 1 );
g_middle_name := REGEXP_SUBSTR( g_name, '^(\S+)\s+((\S*?)\s+)?(.*)$', 1, 1, NULL, 3 );
g_last_name := REGEXP_SUBSTR( g_name, '^(\S+)\s+((\S*?)\s+)?(.*)$', 1, 1, NULL, 4 );
RETURN;
END;
MEMBER FUNCTION cleanNames RETURN parseName
IS
v_name parseName := SELF;
BEGIN
v_name.g_first_name := cleanString( SELF.g_first_name );
v_name.g_middle_name := cleanString( SELF.g_middle_name );
v_name.g_last_name := cleanString( SELF.g_last_name );
RETURN v_name;
END;
MEMBER FUNCTION getFirstName RETURN VARCHAR2
IS
BEGIN
RETURN g_first_name;
END getFirstName;
MEMBER FUNCTION getMiddleName RETURN VARCHAR2
IS
BEGIN
RETURN g_middle_name;
END getMiddleName;
MEMBER FUNCTION getLastName RETURN VARCHAR2
IS
BEGIN
RETURN g_last_name;
END getLastName;
END;
/
Query 1:
SELECT parseName('John123 Doe').getFirstName(),
parseName('John123 Doe').cleanNames().getFirstName()
FROM DUAL
| PARSENAME('JOHN123DOE').GETFIRSTNAME() | PARSENAME('JOHN123DOE').CLEANNAMES().GETFIRSTNAME() |
|----------------------------------------|-----------------------------------------------------|
| John123 | John |