Return Single Row In Oracle Function

Question

I'm attempting to return a single row of user data from a function based on the input of a user. I cannot access the values of the object columns. I have tried the procedure solution here as well as pretty much copying the solution here (Using ORACLE and SQLDeveloper)

CREATE OR REPLACE FUNCTION F_FINDBYSSN (SSN VARCHAR2)
   RETURN T_USEROBJECT
AS
   THISUSER   T_USEROBJECT;
BEGIN
   SELECT T_USEROBJECT (A.USER_ID,
                        A.FIRSTNAME,
                        A.LASTNAME,
                        A.SOCIAL,
                        A.BIRTHDATE,
                        A.PHONE,
                        A.USER_ROLE_TYPE_ID,
                        A.USER_LOGIN_ID,
                        A.USER_LAST_LOGIN,
                        B.LOGIN_USERNAME,
                        B.LOGIN_PASSWORD)
     INTO THISUSER
     FROM BANK_USERS A
          INNER JOIN LOGININFO B
             ON     A.USER_LOGIN_ID = B.LOGIN_ID
                AND A.SOCIAL = SSN;

   RETURN THISUSER;
END;
/

The Object Is A User

CREATE OR REPLACE TYPE T_USEROBJECT IS OBJECT
(
   USERID INTEGER,
   FNAME VARCHAR2 (50),
   LNAME VARCHAR2 (50),
   SSN VARCHAR2 (15),
   BIRTHDATE DATE,
   USERPHONE VARCHAR2 (15),
   ROLEID INT,
   LOGINID INT,
   LASTLOGIN DATE,
   UNAME VARCHAR2 (50),
   UPASS VARCHAR (50)
);
/

This outputs [T_USEROBJECT]

SELECT *
FROM ( SELECT F_FINDBYSSN('123-45-6789') AS THISUSER FROM DUAL );

Using any column names in the select statement causes an "invalid identifier" error

Answer 1

I found one way to do, but is it overkill for retrieving a single row?

CREATE OR REPLACE TYPE t_singleuserset IS TABLE OF t_userobject;
/

CREATE OR REPLACE FUNCTION select_userrow (ssn IN VARCHAR)
   RETURN t_singleuserset
AS
   l_user   t_singleuserset := t_singleuserset ();
   n        INTEGER := 0;
BEGIN
   FOR i IN (SELECT a.user_id,
                    a.firstname,
                    a.lastname,
                    a.social,
                    a.birthdate,
                    a.phone,
                    a.user_role_type_id,
                    a.user_login_id,
                    a.user_last_login,
                    b.login_username,
                    b.login_password
               FROM bank_users a
                    INNER JOIN logininfo b
                       ON     a.user_login_id = b.login_id
                          AND a.social = ssn)
   LOOP
      l_user.EXTEND ();
      n := n + 1;
      l_user (n) :=
         t_userobject (i.user_id,
                       i.firstname,
                       i.lastname,
                       i.social,
                       i.birthdate,
                       i.phone,
                       i.user_role_type_id,
                       i.user_login_id,
                       i.user_last_login,
                       i.login_username,
                       i.login_password);
   END LOOP;

   RETURN l_user;
END;
/

SELECT * FROM TABLE (select_userrow ('123-45-6789'));

Answer 2

Found Answer here. Needed to Use the Treat Function like so

SELECT TREAT(THISUSER AS T_USEROBJECT).USERID, TREAT(THISUSER AS T_USEROBJECT).FNAME
 FROM ( SELECT * FROM (SELECT F_FINDBYSSN('123-45-6789') AS THISUSER FROM DUAL ));

How to SELECT from object type column in Oracle 11g?