Does Oracle Allow you to simply supply the expected data types for a RECORD without Declaring the Record within a Procedure Call?

user_6026385 0
Viewed 32

Similar to the work done in this post I am curious to know if it is possible to do something similar with composite data types.

Example of what I'm attempting but have never seen it work.

/* Is it possible somehow to instead do THIS? */
tst_proc_rec_type(p_rec    =>  (sys.odcivarchar2list('dan','bob')
                              , sys.odcivarchar2list('anderson','bebop'))
);

For some reason I keep returning to this concept and over the years I've never seen a way to get this to work... most likely due to the fact that it's simply NOT possible but I decided to reach out to the community in hopes that perhaps it IS possible and maybe I'm simply doing it wrong. (Wouldn't be the first time).

Example of working code AND altered code showing what I'm trying to do.

DECLARE

  TYPE t_tst_rec IS RECORD (
    fname   sys.odcivarchar2list,
    lname   sys.odcivarchar2list
  );

  iamrecord   t_tst_rec; /* Declare record of type t_tst_rec */

PROCEDURE tst_proc_rec_type(p_rec t_tst_rec)
IS

BEGIN

  FOR i IN 1..p_rec.lname.COUNT
  LOOP

    dbms_output.put_line(p_rec.lname(i) ||', ' || p_rec.fname(i));

  END LOOP;

END;

BEGIN

  iamrecord.fname :=  sys.odcivarchar2list('dan','bob');
  iamrecord.lname :=  sys.odcivarchar2list('anderson','bebop');

  tst_proc_rec_type(p_rec    =>  iamrecord
  );

  /* Is it possible somehow to instead do THIS? */
--  tst_proc_rec_type(p_rec    =>  (sys.odcivarchar2list('dan','bob')
--                                , sys.odcivarchar2list('anderson','bebop'))
--  );

END;

See it in action.

enter image description here

record oracle11g plsql collections oracle
1 Answers

You need to be dealing with an object type in order to have a constructor of this kind. Simple record types have no such ability. For example, the following would work.

CREATE OR REPLACE TYPE t_tst_rec AS OBJECT
(
   fname sys.odcivarchar2list,
   lname sys.odcivarchar2list,
   CONSTRUCTOR FUNCTION t_tst_rec
   (
      fname sys.odcivarchar2list,
      lname sys.odcivarchar2list
   ) RETURN SELF AS RESULT
);

CREATE OR REPLACE TYPE BODY t_tst_rec AS

   CONSTRUCTOR FUNCTION t_tst_rec
   (
      fname sys.odcivarchar2list,
      lname sys.odcivarchar2list
   ) RETURN SELF AS RESULT IS
   BEGIN
      self.fname := fname;
      self.lname := lname;
      RETURN;
   END t_tst_rec;

END;
/

DECLARE
   iamrecord t_tst_rec; /* Declare record of type t_tst_rec */

   PROCEDURE tst_proc_rec_type(p_rec t_tst_rec) IS
   BEGIN
      FOR i IN 1 .. p_rec.lname.count LOOP
         dbms_output.put_line(p_rec.lname(i) || ', ' || p_rec.fname(i));
      END LOOP;
   END;

BEGIN
   tst_proc_rec_type(t_tst_rec(sys.odcivarchar2list('dan', 'bob')
                              ,sys.odcivarchar2list('anderson', 'bebop')));

END;
/

Reference for the lack of a RECORD type constructor syntax.

user_32681280
Related