Re: how to change dynamically field name when using recordtype

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Sun, 22 Feb 2004 23:15:28 -0800
Message-ID: <1077520490.272671@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;
/

-- 
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 Mon Feb 23 2004 - 01:15:28 CST