Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Initialization of an oracle type

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

564 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