Re: how to change dynamically field name when using recordtype

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Tue, 24 Feb 2004 07:48:40 -0800
Message-ID: <1077637682.102960@yasure>

Basant wrote:

> 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,

To do what you are attempting I'd suggest native dynamic SQL rather than functions. Pass parameters to a procedure and use NDS to cobble together the statements.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Tue Feb 24 2004 - 09:48:40 CST