OraFAQ Forum: JDeveloper, Java & XML » errors in running the stored procedure

Home » Developer & Programmer » JDeveloper, Java & XML » errors in running the stored procedure

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

errors in running the stored procedure [message #91546]

Tue, 05 November 2002 01:40

Irene
Messages: 11
Registered: February 2002

Junior Member

hello all.. can someone help me out??

I had this errors when running my stored procedure in the sqlplus.

My stored procedure goes like this :

CREATE OR REPLACE PROCEDURE sp_procedure (yearVar IN NUMBER, etyVar IN varChar, optVar IN varChar, valVar IN NUMBER)
AS
cp integer;
ety char(3);
BEGIN
SELECT DISTINCT costpool_id, entity INTO cp, ety FROM Table
WHERE year = yearVar and entity = etyVar and optVar = valVar;
END sp_procedure;

My error goes liike this :
SQL> exec sp_procedure (2002,'SG','abc7',214);
begin sp_crossCostPool1 (2002,'SG','abc7',214); end;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SP_PROCEDURE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Report message to a moderator

Re: errors in running the stored procedure [message #91549 is a reply to message #91546]

Tue, 05 November 2002 10:52

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

did you create the procedure??
it is absolutely fine.

SQL> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE PROCEDURE sp_procedure (yearVar IN NUMBER, etyVar IN varChar, optVar IN varCh
  2  AS
  3  cp integer;
  4  ety char(3);
  5  BEGIN
  6  null;
  7  --SELECT DISTINCT costpool_id, entity INTO cp, ety FROM Table
  8  --WHERE year = yearVar and entity = etyVar and optVar = valVar;
  9* END sp_procedure;
SQL> /

Procedure created.

SQL> exec sp_procedure (2002,'SG','abc7',214);

PL/SQL procedure successfully completed.

SQL>

Report message to a moderator

Re: errors in running the stored procedure [message #91551 is a reply to message #91546]

Tue, 05 November 2002 23:15

Irene
Messages: 11
Registered: February 2002

Junior Member

hi, I do not know why the things i post is similar on what I am having but it gave me errors.. I try to re-run with yours..

CREATE OR REPLACE PROCEDURE sp_procedure(yearVar IN NUMBER, etyVar IN varchar, optVar IN varchar, valVar IN NUMBER)
AS
cp integer;
ety char(5);
BEGIN
SELECT DISTINCT costpool_id, entity INTO cp, ety FROM Table
WHERE year = yearVar and entity = etyVar and optVar = valVar;
END sp_procedure;

SQL> exec sp_procedure(2002,'CSG','ubr6',2482)
begin sp_costpool1 (2002,'CSG','ubr6',2482); end;

*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at "APPINF.SP_PROCEDURE", line 6
ORA-06512: at line 1

By the way, I had created another function.. It is running fine.. I can upload successfully but it seems to have problems with my sql statement inside..

when my sql statement is like this
SELECT DISTINCT costpool_id,entity FROM Inventory
WHERE year = yearVar;

it can return me results.

when my sql statement is like this
SELECT DISTINCT costpool_id,entity FROM Inventory
WHERE year = yearVar and entity = 'etyVar';

it gave me no records is found... i think its ignoring my 'and' what should I do??

Thanks alot

Report message to a moderator

Re: errors in running the stored procedure [message #91553 is a reply to message #91551]

Wed, 06 November 2002 02:53

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

there must some mistmatch in datatype in the DML inside the procedure. ( I have commented it).
and
as per your code

CREATE OR REPLACE PROCEDURE sp_procedure(yearVar IN NUMBER, etyVar IN varchar, optVar IN varchar, valVar IN NUMBER)

you are using varchar.
change it to varchar2

Report message to a moderator

Re: errors in running the stored procedure [message #91558 is a reply to message #91551]

Fri, 08 November 2002 04:14

F. Tollenaar
Messages: 64
Registered: November 2002

Member

Either you didn't copy-paste from sql-plus or there's something fishy going on:


>SQL> exec sp_procedure(2002,'CSG','ubr6',2482)
>begin sp_costpool1 (2002,'CSG','ubr6',2482); end;
           ^^^^^^^^

Report message to a moderator

Re: errors in running the stored procedure [message #91563 is a reply to message #91551]

Sat, 09 November 2002 10:19

Irene
Messages: 11
Registered: February 2002

Junior Member

oh.. i didnt copy and paste.. typo error.. hehe

Report message to a moderator

Previous Topic:	Re: Orcale + JAVA
Next Topic:	What's The best and mos usefull book for learning Java ?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 19:36:47 CST 2024