Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Keywords  () 1 Vote
Oracle Keywords [message #204071] Fri, 17 November 2006 11:59 Go to next message
sampathg2k
Messages: 8
Registered: November 2006
Junior Member
Hi Gurus,

My Requirement is i dont want to change the column names in the database. My requirement is i should use the Oracle Keywords BLOB and DATE and data types also same. How can i create the trigger witout changing the column names. IF i change the column name trigger is working fine. Can you give me the other soulution how can i achieve...

Here's the create table script:

CREATE TABLE RESULT2
(
ID NUMBER(20) NOT NULL,
"BLOB" BLOB,
"DATE" DATE
)

and the result of attempting to create the 'before insert' trigger to populate the ID column:

/* deb_trig2.sql */
CREATE OR REPLACE TRIGGER DEBBIE.RESULT2_TBI01
BEFORE INSERT ON DEBBIE.RESULT2
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
BEGIN
if INSERTING then
select DEBBIE.GLOBAL_SEQ.nextval into :new.ID from dual;
end if;

END;
/

dev379> @deb_trig2
BEFORE INSERT ON DEBBIE.RESULT2
*
ERROR at line 2:
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is
incomplete or malformed


dev379> describe result2
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(20)
BLOB BLOB
DATE DATE
Re: Oracle Keywords [message #204075 is a reply to message #204071] Fri, 17 November 2006 12:13 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
It is a bad idea to have objects and columns created within quotes.
Re: Oracle Keywords [message #204080 is a reply to message #204075] Fri, 17 November 2006 12:41 Go to previous messageGo to next message
sampathg2k
Messages: 8
Registered: November 2006
Junior Member
MAhesh,

Ur rt. But the thing is it has been developed a long back and the requirement is they are not willing to change the column name. so please let me know how can i acheive this...

Thanks
Sampth
Re: Oracle Keywords [message #204087 is a reply to message #204080] Fri, 17 November 2006 13:34 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
As you can see, this is one case where trying to trick Oracle into using reserved words in column names backfires.
Even though you are not referencing the column in your trigger, your trigger cannot be created. And it doesn't have to do with having to define the columns in quotes during table creation, it has to do with their being reserved words.

Now, there is a way around this, but I hesitate to mention it because I think it is such a bad practice that it gives me the willies.

[Updated on: Fri, 17 November 2006 13:36]

Report message to a moderator

Re: Oracle Keywords [message #204093 is a reply to message #204071] Fri, 17 November 2006 14:15 Go to previous messageGo to next message
sampathg2k
Messages: 8
Registered: November 2006
Junior Member
Hi

There are 3 columns in the table id,blob and date, I am assigning the sequence value into id. Trigger is working if i change the column names like dev_blob, create_date and id as it is. is there any way like creating one dummy table and insert into those records into original table where the column names as it is.

Thanks
Samapth
Re: Oracle Keywords [message #204100 is a reply to message #204071] Fri, 17 November 2006 16:24 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
I would do the following

create view RESULT2 as
select id,blob_dev "BLOB", date_dev "DATE"
from real_RESULT2;


CREATE OR REPLACE TRIGGER DEBBIE.RESULT2_TBI01
BEFORE INSERT ON DEBBIE.REAL_RESULT2
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
BEGIN
select DEBBIE.GLOBAL_SEQ.nextval into :new.ID from dual;
end;

Your current application will work through the view, but the actual table will have no problems.

[Updated on: Fri, 17 November 2006 16:24]

Report message to a moderator

Re: Oracle Keywords [message #204423 is a reply to message #204100] Mon, 20 November 2006 10:34 Go to previous messageGo to next message
sampathg2k
Messages: 8
Registered: November 2006
Junior Member
Hi Bill,

Even i am getting the same error. no change
Re: Oracle Keywords [message #204426 is a reply to message #204423] Mon, 20 November 2006 11:03 Go to previous messageGo to next message
sampathg2k
Messages: 8
Registered: November 2006
Junior Member

Hi Bill,

First of all i am not able to create the view with the alias name.

Thanks
Sampath
Re: Oracle Keywords [message #204436 is a reply to message #204071] Mon, 20 November 2006 14:06 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
show me what you are doing, please cut and paste.
Re: Oracle Keywords [message #204659 is a reply to message #204436] Tue, 21 November 2006 09:46 Go to previous messageGo to next message
sampathg2k
Messages: 8
Registered: November 2006
Junior Member
Hi Bill,

Step by Step procedure i did


1)table creation
CREATE TABLE RESULT2
(
ID NUMBER(20) NOT NULL,
"BLOB" BLOB,
"DATE" DATE
)

done

2)View creation
create view RESULT3 as
select id, "BLOB" blob_dev, "DATE" date_dev
from RESULT2;

done(View also created)

3) created one sequence with the name sam also
select * from all_Sequences where sequence_name='SAM'

4)Trigger creation
CREATE OR REPLACE TRIGGER RESULT2_UNDER
BEFORE INSERT ON RESULT2
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
BEGIN
select SAM.nextval into :new.ID from dual;
end;


Message while creating the trigger

ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed

Thanks
Sampat
Re: Oracle Keywords [message #204681 is a reply to message #204071] Tue, 21 November 2006 12:31 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
You have it backwards, do it like this

1)table creation
CREATE TABLE RESULT2
(
ID NUMBER(20) NOT NULL,
BLOB_DEV BLOB,
DATE_DEV DATE
)

done

2)View creation
create view RESULT3 as
select id, BLOB_DEV "BLOB", DATE_DEV "DATE"
from RESULT2;

done(View also created)

3) created one sequence with the name sam also
select * from all_Sequences where sequence_name='SAM'

4)Trigger creation
CREATE OR REPLACE TRIGGER RESULT2_UNDER
BEFORE INSERT ON RESULT2
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
BEGIN
select SAM.nextval into :new.ID from dual;
end;


Then

insert into results3 VALUES(NULL,MY_BLOB,SYSDATE);

[Updated on: Tue, 21 November 2006 12:33]

Report message to a moderator

Re: Oracle Keywords [message #204687 is a reply to message #204071] Tue, 21 November 2006 12:56 Go to previous messageGo to next message
sampathg2k
Messages: 8
Registered: November 2006
Junior Member
Thanks Bill,

That means we have to create one temporary table with the different column names as u mentioed here. But my requirement is i dont want to change the column names and more over i dont want to create any temporory table. As you said you are not using oracle keywords while creating the table called result2.

Thanks
Sampath
Re: Oracle Keywords [message #204688 is a reply to message #204071] Tue, 21 November 2006 12:58 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
It's not a temporary table, it replaces your existing table and your application will still access the data in the new table through the view which use the old column names.
Re: Oracle Keywords [message #204696 is a reply to message #204688] Tue, 21 November 2006 14:12 Go to previous message
sampathg2k
Messages: 8
Registered: November 2006
Junior Member
But i am not able to create the trigger. If i change the column name then i am able to create the trigger
Previous Topic: help with query
Next Topic: Create View in Oracle9.2.0.7.0 - Is this a bug?
Goto Forum:
  


Current Time: Fri Dec 09 00:01:19 CST 2016

Total time taken to generate the page: 0.23068 seconds