Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Initialization of an oracle type

Posted on 2012-03-13
4
Medium Priority
?
316 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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

604 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