Re: how to change dynamically field name when using recordtype

From: Basant <basantshanker_at_hotmail.com>
Date: 24 Feb 2004 01:01:13 -0800
Message-ID: <e3fbec7a.0402240005.3ad7729c@posting.google.com>

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1077520490.272671_at_yasure>...
> Basant wrote:
>
> > Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1077299808.209216_at_yasure>...
> >
> >>Basant wrote:
> >>
> >>
> >>>Hi,
> >>> I want to change the field name of a recordtype dynamically to
> >>>compare two values. For Example.
> >>>
> >>> TYPE Rec IS RECORD (name VARCHAR2(300),
> >>> Roll NUMBER);
> >>> Student Rec;
> >>>
> >>> FUNCTION isEqual (l_Student Rec,
> >>> ColumnName VARCHAR2,
> >>> Value VARCHAR2)
> >>> BEGIN
> >>> IF 'l_Student'||Columnname = Value THEN
> >>> RETURN TRUE;
> >>> ELSE
> >>> RETURN FALSE;
> >>> END IF;
> >>> END;
> >>>
> >>>Calling funciton as :
> >>> l_Student Rec;
> >>>BEGIN
> >>> l_Student.Name:='JOHN';
> >>> isEqual(l_student,'.Name','JOHN');
> >>>END;
> >>>
> >>>I have already tried with Dynamic SQL . But didn't got any option to
> >>>do like this.
> >>>
> >>>
> >>>Help me out...
> >>>
> >>>Thanks in Advance.
> >>>Basant
> >>
> >>Help you out with what? You provided no version or edition information
> >>and no error message or indication of what is wrong.
> >>
> >>But the first thing I'd do is:
> >>
> >>SELECT keyword FROM v$reserved_words
> >>WHERE keyword LIKE 'NA%';
> >>
> >>SELECT keyword FROM v$reserved_words
> >>WHERE keyword LIKE 'RO%';
> >
> >
> >
> >
> > Hi,
> > I am using client : " SQL*Plus: Release 9.0.1.3.0"
> > and server version is : "Oracle9i Enterprise Edition Release
> > 9.2.0.1.0".
> >
> > In above case i am getting wrong result i.e. function return
> > 'FALSE', whereas it should return 'TRUE'. I tried to trace and find
> > that the
> > IF condition " 'l_Student'||Columnname " of define FUNCTION
> > "isEqual" is treated as string . It is not assigning the passed value
> > "JOHN".
> > I want to know that , is there any way to assign the proper
> > value in this case?
> > Let me know if any addition info reqd.
>
> I think the first problem is that you seem to have mixed up how to
> use a function vs. how to use a stored procedure. A function must
> return something.
>
> FUNCTION isEqual (l_Student Rec,
> >>> ColumnName VARCHAR2,
> >>> Value VARCHAR2)
> >>> BEGIN
> >>> IF 'l_Student'||Columnname = Value THEN
> >>> RETURN TRUE;
> >>> ELSE
> >>> RETURN FALSE;
> >>> END IF;
> >>> END;
>
> is not a function even if you call it one. The syntax for a function
> would be:
>
> CREATE OR REPLACE isEqual (<parameters here>) RETURN BOOLEAN IS
>
> BEGIN
> ...
> END isEqual;
> /
>
> and I have no idea why you think l_Student of type Rec can be
> evaluated in the way you propose: It can not be and this does
> not seem to be a good use of a use defined data type. Why are
> you trying to do this?
>
> If you want a function you must run it as:
>
> DECLARE
> x BOOLEAN;
> BEGIN
> x := isEqual(<parameters>);
> END;
> /

Hi,

      Above code was a sample code. I wrote with correct syntax in my program. Here the scenario is as follows:

I have two table named: REC_TABLE and REC_RULE Structure and data is follows:

REC_TABLE:

Name                                      Null?    Type
----------------------------------------- -------- ----------
BR_IND                                             VARCHAR2(1)
PLAN_NUM                                           VARCHAR2(6)
PLAN_SEQ                                           VARCHAR2(3)
PART_NUM                                           VARCHAR2(9)
SUB_PLAN                                           VARCHAR2(6)
PART_EXT                                           VARCHAR2(2)
REC_TYPE                                           VARCHAR2(2)
TRAN_CODE                                          VARCHAR2(3)
ACTIVITY                                           VARCHAR2(3)

Data is as follows :
B PLAN_N PLA PART_NUM SUB_PL PA RE TRA ACT - ------ --- --------- ------ -- -- --- ---

A 007426 200 380565425           BR 381 021
A 007426 200 380565425           BR 381 007
A 007426 200 380565425           BR 381 007
A 007426 200 380565425           BR 381 007
A 150003 200 552808797           BR 381 007


REC_RULE:
Name                                      Null?    Type
----------------------------------------- -------- ----------
ACTIVITY                                           VARCHAR2(3)
RULE                                               VARCHAR2(500)

Data is as follows:
ACT RULE

---  ---------------------------------------------------------------

021 REC_TYPE='BR' AND TRAN_CODE=381
007 REC_TYPE <> 'BR' and TRAN_CODE=381

here according to logic each rows of 1st table should validate with rule of 2nd table with ACTIVITY as a reference Key considering that the column may be added in 1st table in future which may reflect in RULE column of 2nd table.

I think this explain you why I want to pass the item name of record Dynamically.

If you have any other method to do same then please let me know.

Thanks, Received on Tue Feb 24 2004 - 03:01:13 CST