Home » Developer & Programmer » JDeveloper, Java & XML » PLS-00201 errot was displayed when i call a pl/sql function from java program (oracle 10g express edition)
PLS-00201 errot was displayed when i call a pl/sql function from java program [message #513990] Thu, 30 June 2011 10:02 Go to next message
ndhanashekar
Messages: 3
Registered: June 2011
Location: india
Junior Member
hi in my java program i am executing a pl/sql function using callable statement i checked everything in the function and the function is created without compilation errors in sql*plus environment, and i gave all DBA permissions to the user, but i got the error like below

java.sql.SQLException: ORA-06550: line 1, column 28: PLS-00201: identifier 'NAME' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored

and i wrote the function like below for the below table

create table student(regno number(6),name varchar2(15),dob date,phone number(10),
address varchar2(30),cat varchar2(2),password varchar2(12),hallno number(6),
fee_paid varchar2(3),constraint student_pk primary key(regno))


create table fee(regno number(6), branch varchar2(15),amount number(4), ddno number(12), fee_paid varchar2(3),
dddate date, dd_received_date date default sysdate, constraint fee_pk primary key(regno))



create sequence student_regno_seq


create or replace function student_insert(vname in varchar2, vdob in varchar2, vphone in number,vaddress in varchar2,
vcat in varchar2, vpassword in varchar2, vbranch in varchar2,vamount in number, vddno in number, vdddate in varchar2)
return number
is
vregno number;
vdob1 date;
vdddate1 date;
begin
select student_regno_seq.nextval into vregno from dual;
select to_date(vdob,'dd/mm/yyyy') into vdob1 from dual;
select to_date(vdddate,'dd/mm/yyyy') into vdddate1 from dual;
insert into student values(vregno, vname, vdob1, vphone, vaddress, vcat, vpassword, null,'no');
insert into fee values(vregno,vbranch,vamount,vddno,'no',vdddate1,null);

return vregno;
end student_insert;
/

and the java program is like below

try{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection connection=DriverManager.getConnection(dbURL,dbUserName,dbPassword);
CallableStatement st=connection.prepareCall("{?=call student_insert(name,dob,phone,address,category,passowrd,branch,amount,ddno,ddDate)}");
st.registerOutParameter(1,Types.INTEGER);
st.execute();
regNo=st.getInt(1);
connection.commit();
connection.close();
}
catch(Exception e)
{
out.println( e.toString());
}
can any one of you please help me to solve this problem

Thank You in Advance!
Re: PLS-00201 errot was displayed when i call a pl/sql function from java program [message #513991 is a reply to message #513990] Thu, 30 June 2011 10:04 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

PLS error is syntax error

[Updated on: Thu, 30 June 2011 10:04]

Report message to a moderator

Re: PLS-00201 errot was displayed when i call a pl/sql function from java program [message #513995 is a reply to message #513990] Thu, 30 June 2011 10:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
CallableStatement st=connection.prepareCall("{?=call student_insert(name,dob,phone,address,category,passowrd,branch,amount,ddno,ddDate)}");

This is not how you call a PL/SQL function, try:
"begin ?=student_insert(?,?,?,?,?,?,?,?,?,?); end;"


You have to give a value/variable for each function parameter.

Forgot to mention: NAME is a reserved word, do not use it.

Regards
Michel

[Updated on: Thu, 30 June 2011 10:31]

Report message to a moderator

Re: PLS-00201 errot was displayed when i call a pl/sql function from java program [message #513996 is a reply to message #513990] Thu, 30 June 2011 10:29 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
Most probably, you do not want to call STUDENT_INSERT with identifiers NAME, DOB, PHONE etc. (they do not represent anything here), but with values of equally named parameters in Java (there are none in the snippet you posted) or literals taken from wherever.

In any case the correct call should look like this:
"{?=call student_insert(?,?,?,?,?,?,?,?,?,?)}"
with additional binding of function parameters. Probably with other method than REGISTEROUTPARAMETER, but as I do not know Java, I cannot tell. Maybe you should explore Java abilities yourself or search in Java forum(s) for getting the correct one(s).
Re: PLS-00201 errot was displayed when i call a pl/sql function from java program [message #514043 is a reply to message #513996] Thu, 30 June 2011 21:08 Go to previous messageGo to next message
ndhanashekar
Messages: 3
Registered: June 2011
Location: india
Junior Member
i tried it works well.....
Thank You!
Re: PLS-00201 errot was displayed when i call a pl/sql function from java program [message #514044 is a reply to message #513995] Thu, 30 June 2011 21:10 Go to previous message
ndhanashekar
Messages: 3
Registered: June 2011
Location: india
Junior Member
yes i changed the "name" variable to uName it works well...

Thank you!
Previous Topic: Read XML Files!
Next Topic: query a column containing xml formatted data
Goto Forum:
  


Current Time: Thu Mar 28 09:31:21 CDT 2024