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 |
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
|
|
|
Re: ORA-00984: column not allowed here [message #9713 is a reply to message #9712] |
Tue, 02 December 2003 05:55 |
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)
|
|
|
Re: ORA-00984: column not allowed here [message #9714 is a reply to message #9712] |
Tue, 02 December 2003 06:08 |
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
|
|
|
|
Re: ORA-00984: column not allowed here [message #9718 is a reply to message #9715] |
Tue, 02 December 2003 06:59 |
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").
|
|
|
Re: ORA-00984: column not allowed here [message #9719 is a reply to message #9718] |
Tue, 02 December 2003 07:08 |
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
|
|
|
Re: ORA-00984: column not allowed here [message #9733 is a reply to message #9719] |
Tue, 02 December 2003 11:25 |
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?
|
|
|
|
ORA-00984: column not allowed here [message #14308 is a reply to message #11028] |
Thu, 19 August 2004 19:48 |
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.
|
|
|
Goto Forum:
Current Time: Thu Apr 18 03:49:40 CDT 2024
|