Solved

Initialization of an oracle type

Posted on 2012-03-13
4
302 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
  • 4
4 Comments
 
LVL 73

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 73

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 73

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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now