Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00984: column not allowed here
ORA-00984: column not allowed here [message #9712] Tue, 02 December 2003 05:44 Go to next message
omer naj
Messages: 9
Registered: December 2003
Junior Member
hi
with help from william i have created the following procedure but when i go to insert
i get the following error i cannot understand why?

create or replace procedure add_student
(fname varchar2,
lname varchar2,
astreet varchar2,
acity varchar2,
apostcode varchar2,
cphone varchar2,
dob varchar2)
AS
stu_no number; num number; u_id varchar2(5);
flet varchar2(1); llet varchar2(1); em varchar2(16);
BEGIN
select studno.nextval into stu_no from dual;
flet:=SUBSTR(fname,1,1);
llet:=SUBSTR(lname,1,1);
num:=trunc(abs(dbms_random.random)/power(2,31)*899)+100;
dbms_random.terminate;
u_id:=llet || flet || to_char(num);
em:=u_id || '@gre.ac.uk';
END;

insert into student values (student_type(fname, lname, address_type
(astreet, acity, apostcode, 'UK'), contact_type(cphone, null, em),
to_date(dob),'British','Full',to_char(stu_no),u_id,'Pending'));
commit;
END;

to_date(dob),'British','Full',to_char(stu_no),to_char(u_id),'Pending'))
*
ERROR at line 3:
ORA-00984: column not allowed here

thanks

Report message to a moderator

Re: ORA-00984: column not allowed here [message #9713 is a reply to message #9712] Tue, 02 December 2003 05:55 Go to previous messageGo to next message
omer naj
Messages: 9
Registered: December 2003
Junior Member
my student table is as follows

SQL> desc student
Name Null? Type
--------------------------- -------- ------------
FIRST_NAME VARCHAR2(10)
LAST_NAME VARCHAR2(10)
ADDRESS ADDRESS_TYPE
CONTACT CONTACT_TYPE
DOB DATE
NATIONALITY VARCHAR2(15)
ATTENDANCE_MODE VARCHAR2(15)
STUDENT_NO VARCHAR2(10)
USER_ID VARCHAR2(5)
FEE_STATUS VARCHAR2(25)

SQL> desc address_type
Name Null? Type
--------------------------- -------- ------------
STREET VARCHAR2(25)
CITY VARCHAR2(25)
POST_CODE VARCHAR2(10)
COUNTRY VARCHAR2(20)

SQL> desc contact_type
Name Null? Type
-------------------------- -------- ------------
DAYTIME_TEL NUMBER(12)
EVENING_TEL NUMBER(12)
EMAIL VARCHAR2(25)

Report message to a moderator

Re: ORA-00984: column not allowed here [message #9714 is a reply to message #9712] Tue, 02 December 2003 06:08 Go to previous messageGo to next message
Adrian Billington
Messages: 139
Registered: December 2002
Senior Member
Looks like you have a variable named the same as a column and sure enough, when we look at your table definition in the other post, there is a column called DOB. Which is the same name as your DOB parameter.

I cannot express enough how seriously and utterly bad this practice is. In your case it has generated an error because Oracle is trying to reference the column DOB in the TO_DATE on INSERT and not the parameter. In other cases, mainly UPDATEs, it can cause unexpected results. If your statement was "UPDATE table SET dob = dob", then Oracle would have updated the DOB column with the DOB column and ignored the parameter altogether - so you'd have updated the column with itself.

Get yourself some naming conventions and always use them. For parameters, people tend to use "p_" or "_in" / "_out" / "_inout". For variables, people tend to prefix "v_", "l_" or "g_" ( depending on local or global).

First things first - change your parameter names...

Regards
Adrian

Report message to a moderator

Re: ORA-00984: column not allowed here [message #9715 is a reply to message #9714] Tue, 02 December 2003 06:25 Go to previous messageGo to next message
omer naj
Messages: 9
Registered: December 2003
Junior Member
i have made the changes you have have said
but how do i insert in to the add_student procedure so that it generates the u_id and the stu_no

thanks

Report message to a moderator

Re: ORA-00984: column not allowed here [message #9718 is a reply to message #9715] Tue, 02 December 2003 06:59 Go to previous messageGo to next message
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
Senior Member
Looks like your procedure has two ENDs. When I try it I get a different error message (PLS-00103: Encountered the symbol "INSERT") but you could try taking out the first one (when I do that I get "ORA-02289: sequence does not exist", which makes sense because I don't have a sequence named "studno").

Report message to a moderator

Re: ORA-00984: column not allowed here [message #9719 is a reply to message #9718] Tue, 02 December 2003 07:08 Go to previous messageGo to next message
omer naj
Messages: 9
Registered: December 2003
Junior Member
i have made the changes you have said,
the procedure has created successfully
now how do i run the procedure (check it works)
so that the em, studno and u_id have been generated

create sequence studno start with 0000001;
drop sequence studno;

create or replace procedure add_student
(fname varchar2,
lname varchar2,
astreet varchar2,
acity varchar2,
apostcode varchar2,
cphone varchar2,
db varchar2)
AS
stu_no number; num number; u_id varchar2(5);
flet varchar2(1); llet varchar2(1); em varchar2(16);
BEGIN
select studno.nextval into stu_no from dual;
flet:=SUBSTR(fname,1,1);
llet:=SUBSTR(lname,1,1);
num:=trunc(abs(dbms_random.random)/power(2,31)*899)+100;
dbms_random.terminate;
u_id:=llet || flet || to_char(num);
em:=u_id || '@gre.ac.uk';

insert into student values (student_type(fname, lname, address_type
(astreet, acity, apostcode, 'UK'), contact_type(cphone, null, em),
to_date(db),'British','Full',to_char(stu_no),u_id,'Pending'));
commit;
END;

thanks

Report message to a moderator

Re: ORA-00984: column not allowed here [message #9733 is a reply to message #9719] Tue, 02 December 2003 11:25 Go to previous messageGo to next message
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
Senior Member
In SQL*Plus, either

exec add_student('William','Robertson','Fake St','London','WC2','09758123456','17-OCT-1789')

or

<pre style="color: navy;">BEGIN
ADD_STUDENT
( 'William'
, 'Robertson'
, 'Fake St'
, 'London'
, 'WC2'
, '09758123456'
, '17-OCT-1789' );
END;
/</pre>

Alternativly TOAD, PL/SQL Developer etc have test and debugging tools.

btw shouldn't DB be a date, not a varchar2?

Report message to a moderator

Re: ORA-00984: column not allowed here [message #11028 is a reply to message #9718] Mon, 01 March 2004 20:07 Go to previous messageGo to next message
bhuvana
Messages: 3
Registered: September 2002
Junior Member
i insert the value to the oracle database. it display this message "column not allowed here". i can't understood what is the error.

Report message to a moderator

ORA-00984: column not allowed here [message #14308 is a reply to message #11028] Thu, 19 August 2004 19:48 Go to previous message
suneetha
Messages: 4
Registered: April 2002
Junior Member
LOAD DATA
INFILE G3056V00.txt
APPEND
INTO TABLE GMEX_TRAFFIC_STATS_DLY
( ACTION_DATE POSITION(1:8) CHAR,
ACTION_TIME POSITION(9:20) CHAR,
DLR POSITION(21:26) CHAR,
APPLDLR POSITION(27:32) CHAR,
APPL POSITION(33:38) CHAR,
STATUS POSITION(39:43) CHAR,
CTRY POSITION(44:45) CHAR,
TOTAL_DOC POSITION(46:51) CHAR,
TOTAL_APPL POSITION(52:59) CHAR,
REC_TYPE CHAR "NVL(:REC_TYPE,' ') ",
FUN_GROUP CHAR "NVL(:FUN_GROUP,' ')",
CREATED_BY CHAR "GMExchangeReports",
CREATED_DT "to_date('08/19/2004','mm/dd/yyyy')"
)

This is the control file iam trying to run to load data into a table.But it is giving error
ORA-00984: column not allowed here

Please tell me the solution why it is giving error.It is urgent.

Report message to a moderator

Previous Topic: Beginner
Next Topic: I want to replace the "REPLACE" function with an aggregate function
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 18 03:49:40 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.