-
Hi,
I am working as a DBA in SPARC Solaris 7. Recently we upgraded to 8.1.7 from 8.1.5
One procedure which was running fine in 815 and now it is giving ORA-00900 problem in 817 while executing. The procedure is been created successfully in 817 and we face this problem only while execution.
We referred in the manual and it seems procedural option is missing in our database. Should I install it separately and if it is then how? Anyone there to help me. This is bit urgent.....
Here is my procedure...
create or replace procedure testrun
(oldname varchar,
newname person.loginname%type,pass radius_user.ru_password%type,
orgn person.person_def%type,
fname person.person_fname%type,
mname person.person_mname%type,
lname person.person_lname%type,
desgn person.person_title%type,
addr1 person.person_address1%type,
addr2 person.person_address2%type,
pager person.person_pager%type,
faxnum person.person_faxno%type,
altelnum person.person_altelnum%type,
city person.person_city%type,
state person.person_state%type,
pincode person.person_zip%type,
homeph person.person_homeph%type,
workph person.person_workph%type,
purchase person.person_fax%type,
dob date,
education person.person_change_by%type,
occupation person.person_template%type,
income person.person_country%type,
isp person.person_cnotes%type,
industry person.person_tnotes%type,
city_ip person.person_county%type,
netuser person.person_oem_id%type,
spdetail person.person_oem_str%type,
prodcode varchar,
stdcode tmpuser_tran.service_cnt%type) as
mail number;
sys_date date;
begin
select sysdate into sys_date from dual;
update logincount set loginname = newname where loginname = oldname;
update radius_user set ru_user = newname,ru_password = pass
where ru_user =oldname;
update unames_tran set u_name = newname,reg_time = sysdate
where u_name = oldname;
update custusage set custname =newname where custname =oldname;
update custinfo set loginname=newname where loginname=oldname;
update person set loginname=newname,person_def = orgn,person_fname= fname,
person_mname = mname,
person_lname =lname,person_title = desgn,
person_address1 = addr1,person_address2 = addr2,
person_pager=pager,person_faxno = faxnum,person_altelnum = altelnum,
person_city = city,
person_state = state,person_zip = pincode,person_homeph = homeph,
person_workph=workph,
person_fax=purchase,person_change = dob,person_change_by = education,
person_template = occupation,person_country = income,person_cnotes = isp,
person_tnotes = 'sify.com',person_county = city_ip,person_oem_id = netuser,
person_oem_str = spdetail,person_stdno = stdcode where loginname=oldname;
select mailquota into mail from prod_plan where prod_code=prodcode;
insert into isocor_tran values(prodcode,newname,pass,mail,sys_date,'sify.com');
end testrun;
/
SQL*Plus: Release 8.1.7.0.0 - Production on Wed Dec 5 13:50:16 2001
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
execute usrchgdf008('hp4209691','roydajoy','transport','','DR A K','','ROY', '','D 3 ANAND SREE FLATS 32', 'HINDI PRACHARSABHA STREET', '','','', 'Chennai', 'Tamil Nadu', '600017', '4337362', 'royjk_dr@yahoo.com','','2-jul-1965','graduate','administration/general','none','','','10.1.2.57','y','','DF008','044')
*
ERROR at line 1:
ORA-00900: invalid SQL statement
Regards,
Ravi
-
are you sure that usrchgdf008 exists ???
-
Solution Description:
====================
Use SQL*Plus 3.1.X or later to create stored database procedures and functions.
Solution Explanation:
=====================
SQL*Plus 3.0.X should be used with Oracle Version 6.0, and SQL*Plus 3.1.X and
above is used with the Oracle7 Server. Stored database procedures and
functions were not available until the Oracle7 Server.
Felix
DBA
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|