Solved

Initialization of an oracle type

Posted on 2012-03-13
4
314 Views
Last Modified: 2012-09-14
I have created an oracle type like this:

Create or replace Rec_Comm as object
(
    field_1  varchar2(10),
    field_2  varchar2(10),
     .....
    field_50  varchar2(10),
)
/

in a PL/SQL I then want to initliaze all fields of this type to NULL  without specify 50 fields to null, how do i do this ?
0
Comment
Question by:rogaut1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
4 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 37714761
assuming you already have a variable declared and instantiated, then setting the variable to NULL will effectively clear all of it's member attributes

BUT...you can't use it until you reinstantiate it with the constructor again
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37714769
if you're trying to instantiate with the default constructor method of an object then you can't do it.
You'll have to do...

declare
 v_your_object rec_comm := rec_comm(null,null,null,null.....null,null);  -- 50 nulls
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37714816
you could create your own constructor function for the object type that does the mass initialization for you.

CREATE OR REPLACE TYPE rec_comm AS OBJECT
(
    field1 VARCHAR2(10),
    field2 VARCHAR2(10),
    field3 VARCHAR2(10),
    field4 VARCHAR2(10),
    field5 VARCHAR2(10),
    field6 VARCHAR2(10),
    field7 VARCHAR2(10),
    field8 VARCHAR2(10),
    field9 VARCHAR2(10),
    field10 VARCHAR2(10),
    field11 VARCHAR2(10),
    field12 VARCHAR2(10),
    field13 VARCHAR2(10),
    field14 VARCHAR2(10),
    field15 VARCHAR2(10),
    field16 VARCHAR2(10),
    field17 VARCHAR2(10),
    field18 VARCHAR2(10),
    field19 VARCHAR2(10),
    field20 VARCHAR2(10),
    field21 VARCHAR2(10),
    field22 VARCHAR2(10),
    field23 VARCHAR2(10),
    field24 VARCHAR2(10),
    field25 VARCHAR2(10),
    field26 VARCHAR2(10),
    field27 VARCHAR2(10),
    field28 VARCHAR2(10),
    field29 VARCHAR2(10),
    field30 VARCHAR2(10),
    field31 VARCHAR2(10),
    field32 VARCHAR2(10),
    field33 VARCHAR2(10),
    field34 VARCHAR2(10),
    field35 VARCHAR2(10),
    field36 VARCHAR2(10),
    field37 VARCHAR2(10),
    field38 VARCHAR2(10),
    field39 VARCHAR2(10),
    field40 VARCHAR2(10),
    field41 VARCHAR2(10),
    field42 VARCHAR2(10),
    field43 VARCHAR2(10),
    field44 VARCHAR2(10),
    field45 VARCHAR2(10),
    field46 VARCHAR2(10),
    field47 VARCHAR2(10),
    field48 VARCHAR2(10),
    field49 VARCHAR2(10),
    field50 VARCHAR2(10),
    CONSTRUCTOR FUNCTION rec_comm(self IN OUT NOCOPY rec_comm)
        RETURN SELF AS RESULT
)

Open in new window


CREATE OR REPLACE TYPE BODY rec_comm
AS
    CONSTRUCTOR FUNCTION rec_comm(self IN OUT NOCOPY rec_comm)
        RETURN SELF AS RESULT
    IS
    BEGIN
        field1   := NULL;
        field2   := NULL;
        field3   := NULL;
        field4   := NULL;
        field5   := NULL;
        field6   := NULL;
        field7   := NULL;
        field8   := NULL;
        field9   := NULL;
        field10  := NULL;
        field11  := NULL;
        field12  := NULL;
        field13  := NULL;
        field14  := NULL;
        field15  := NULL;
        field16  := NULL;
        field17  := NULL;
        field18  := NULL;
        field19  := NULL;
        field20  := NULL;
        field21  := NULL;
        field22  := NULL;
        field23  := NULL;
        field24  := NULL;
        field25  := NULL;
        field26  := NULL;
        field27  := NULL;
        field28  := NULL;
        field29  := NULL;
        field30  := NULL;
        field31  := NULL;
        field32  := NULL;
        field33  := NULL;
        field34  := NULL;
        field35  := NULL;
        field36  := NULL;
        field37  := NULL;
        field38  := NULL;
        field39  := NULL;
        field40  := NULL;
        field41  := NULL;
        field42  := NULL;
        field43  := NULL;
        field44  := NULL;
        field45  := NULL;
        field46  := NULL;
        field47  := NULL;
        field48  := NULL;
        field49  := NULL;
        field50  := NULL;
        RETURN;
    END;
END;

Open in new window


DECLARE
    v_my_object   rec_comm := rec_comm();
BEGIN
    IF v_my_object.field30 IS NULL
    THEN
        v_my_object.field30 := 'Thirty';
        DBMS_OUTPUT.put_line('It worked!');
    END IF;
END;

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37714900
actually your user-defined constructor doesn't even need to specify the assignments.
All member attributes are NULLed as soon as the constructor is called
So, this is sufficient...

CREATE OR REPLACE TYPE BODY rec_comm
AS
    CONSTRUCTOR FUNCTION rec_comm(self IN OUT NOCOPY rec_comm)
        RETURN SELF AS RESULT
    IS
    BEGIN
        RETURN;
    END;
END;
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question