Let's assume we have the following TYPEs:
CREATE TYPE customer_rec_tp AS OBJECT
( customer_id NUMBER(10)
, first_name VARCHAR2(20)
, last_name VARCHAR2(20)
, store VARCHAR2(50)
, err_msg VARCHAR2(15)
) ;
/
CREATE TYPE student_rec_tp AS OBJECT
( student_ID NUMBER(8)
, first_name VARCHAR2(20)
, last_name VARCHAR2(20)
, school VARCHAR2(20)
, grade VARCHAR2(10)
, phone_number VARCHAR2(15)
, email VARCHAR2(30)
, err_msg VARCHAR2(15)
) ;
/Now, we declare two variables based on the aforementioned types:
v_cstmr customer_rec_tp;
v_stdnt student_rec_tp;
The question is whether we can somehow link the value of
v_cstmr.err_msg
to
v_stdnt.err_msg
?
The actual problem that I have come across is a little more complex than the scenario explained above. I have created overloading procedure which can take different inputs [object types] as IN OUT parameter. It processes all objects, which all have an entity named err_msg. The procedure appends a text message to err_msg if it comes across an error and sends the object back. What I hope to find is a way that any changes in the err_msg entity in an objects updates the others as well automatically. I understand that I can use a global variable to do this, but I would like to avoid it as much as possible and directly link the object entities from different types together.
I understand that it is an object oriented concept which is not the strongest feature of PL/SQL, but still there might be a way to use object type inheritance from a parent type to pass err_msg. Any ideas?
You have separate variables. If you want to copy the error message from one to another you need to assign it yourself. Type inheritance won't solve that:
create or replace type super_t as object (
err_msg varchar2(10)
) not final;
/
create or replace type sub_t under super_t (
sub int
) ;
/
declare
vsuper super_t := super_t(null);
vsub sub_t := sub_t(null, 1);
begin
vsub.err_msg := 'message';
dbms_output.put_line('SUB: ' || vsub.err_msg);
dbms_output.put_line('SUPER: ' || vsuper.err_msg);
vsuper.err_msg := vsub.err_msg;
dbms_output.put_line('SUB: ' || vsub.err_msg);
dbms_output.put_line('SUPER: ' || vsuper.err_msg);
end;
/
SUB: message
SUPER:
SUB: message
SUPER: messageI feel like you're approaching this the wrong way though. If you have a property which is global, you should use a global variable.
If you want to "link" the instances of types, you could:
- Remove err_msg from the types
- Create a record or object type which has the error message and the other objects as attributes
This allows you to create a single variable which holds all the data. For example:
CREATE TYPE customer_rec_tp AS OBJECT
( customer_id NUMBER(10)
, first_name VARCHAR2(20)
, last_name VARCHAR2(20)
, store VARCHAR2(50)
) ;
/
CREATE TYPE student_rec_tp AS OBJECT
( student_ID NUMBER(8)
, first_name VARCHAR2(20)
, last_name VARCHAR2(20)
, school VARCHAR2(20)
, grade VARCHAR2(10)
, phone_number VARCHAR2(15)
, email VARCHAR2(30)
) ;
/
declare
type trec is record (
err_msg varchar2(15),
customer customer_rec_tp,
student student_rec_tp
);
rec1 trec ;
rec2 trec ;
begin
rec1.err_msg := 'error';
dbms_output.put_line('REC1: ' || rec1.err_msg);
dbms_output.put_line('REC2: ' || rec2.err_msg);
end;
/
REC1: error
REC2: But as shown, you can still have separate instances of the "master" variable...