OraFAQ Forum: JDeveloper, Java & XML » PLS-00201 errot was displayed when i call a pl/sql function from java program

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)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

PLS-00201 errot was displayed when i call a pl/sql function from java program [message #513990]

Thu, 30 June 2011 10:02

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!

Report message to a moderator

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

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

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

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

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).

Report message to a moderator