cptkirkh wrote:
>
> On Jan 29, 2:28 pm, DA Morgan <damor..._at_psoug.org> wrote:
>> cptkirkhwrote:
>>
>>> On Jan 29, 1:13 pm, DA Morgan <damor..._at_psoug.org> wrote:
>>>> Mark D Powell wrote:
>>>>> On Jan 29, 12:35 pm, "cptkirkh" <k..._at_scic.com> wrote:
>>>>>> I want to fill a table from a triggered event on another table. The
>>>>>> field that will be changed in the destination table will be determined
>>>>>> by what is changed on the source. I think I have set this up right
>>>>>> but I get an ORA-1733 error on the word "field" in the update
>>>>>> statement. It says virtual column not allowed here. How can I
>>>>>> resolve this or what does anybody suggest is a better way to work
>>>>>> aorund this problem. Or of course is my PL/SQL wrong? Thnaks for
>>>>>> your help. Here is the PL.SQL
>>>>>> FIELD VARCHAR2(20);
>>>>>> x number;
>>>>>> y number;
>>>>>> old date;
>>>>>> new date;
>>>>>> id varchar2(10);
>>>>>> BEGIN
>>>>>> FIELD := CASE
>>>>>> WHEN x = 1 AND y = 1 THEN 'CLI_CIC_NPDATE'
>>>>>> ELSE 'NOT SURE'
>>>>>> END;
>>>>>> DBMS_OUTPUT.PUT_LINE(FIELD);
>>>>>> if nvl(old,'01-JAN-1901') <> nvl(new,'01-JAN-1901') and field is not
>>>>>> null then
>>>>>> UPDATE DESIGNATION_INFO_TEST SET FIELD = sysdate WHERE
>>>>>> CLI_RID = id;
>>>>>> ELSE
>>>>>> INSERT INTO DESIGNATION_INFO_TEST(CLI_RID,FIELD)
>>>>>> VALUES (id,sysdate);
>>>>>> end if;
>>>>>> end;
>>>>> You have a pl/sql variable named FIELD and a table column FIELD. It
>>>>> would appear that the parser is having difficulty telling which one
>>>>> you are trying to reference. You can find the variable scope rules
>>>>> defined in the PL/SQL manual for your version.
>>>>> I suggest you prefix or suffix your pl/sql variable names so that the
>>>>> pl/sql variable names and table column names are not identical.
>>>>> HTH -- Mark D Powell --It is worse than that:
>>>> The following are reserved words:
>>>> FIELD
>>>> ID
>>>> NEW
>>>> OLD
>>>> and '01-JAN-1901' is not a date ... it is a string and the OP is relying
>>>> on implicit conversion which is best practice either.
>>>> --
>>>> Daniel A. Morgan
>>>> University of Washington
>>>> damor..._at_x.washington.edu
>>>> (replace x with u to respond)
>>>> Puget Sound Oracle Users Groupwww.psoug.org-Hide quoted text -- Show quoted text -
>>> Ok so I went in and changed the names to non-reserved words. still
>>> get the same error. In answer to Mark's statement I don't have two
>>> field columns. the column in the table that needs to be updated is
>>> determined on what item is changed in the source table for example if
>>> x and y both equal one then change the column cli_cic_npdate at the
>>> row equal to the cli_id. the column could change names. I just left
>>> out the other possiblities toi shorten what I posted.Now that you've changed it ... repost it along with your version number
>> and the DDL for any tables.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damor..._at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -- Show quoted text -
>
>
> DECLARE
> stuff VARCHAR2 (20);
> x NUMBER;
> y NUMBER;
> chl_old DATE;
> chl_new DATE;
> chl_id VARCHAR2 (10);
> BEGIN
> stuff := CASE
> WHEN x = 1 AND y = 1
> THEN 'CLI_CIC_NPDATE'
> ELSE 'NOT SURE'
> END;
> DBMS_OUTPUT.put_line (stuff);
>
> IF NVL (chl_old, '01-JAN-1901') <> NVL (chl_new, '01-JAN-1901')
> AND stuff IS NOT NULL
> THEN
> UPDATE designation_info_test
> SET stuff = SYSDATE
> WHERE cli_rid = chl_id;
> ELSE
> INSERT INTO designation_info_test
> (cli_rid, stuff
> )
> VALUES (ID, SYSDATE
> );
> END IF;
> END;
As Mark indicates you need dynamic SQL. You can do it either with Native
Dynamic SQL as in Mark's example or using the DBMS_SQL built-in package.
Demos of both of these technologies can be found in Morgan's Library at
www.psoug.org.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Mon Jan 29 2007 - 19:11:31 CST
