Home » Developer & Programmer » JDeveloper, Java & XML » errors in running the stored procedure
errors in running the stored procedure [message #91546] Tue, 05 November 2002 01:40 Go to next message
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
Re: errors in running the stored procedure [message #91549 is a reply to message #91546] Tue, 05 November 2002 10:52 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
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> 

Re: errors in running the stored procedure [message #91551 is a reply to message #91546] Tue, 05 November 2002 23:15 Go to previous messageGo to next message
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
Re: errors in running the stored procedure [message #91553 is a reply to message #91551] Wed, 06 November 2002 02:53 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
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
Re: errors in running the stored procedure [message #91558 is a reply to message #91551] Fri, 08 November 2002 04:14 Go to previous messageGo to next message
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;
           ^^^^^^^^
Re: errors in running the stored procedure [message #91563 is a reply to message #91551] Sat, 09 November 2002 10:19 Go to previous message
Irene
Messages: 11
Registered: February 2002
Junior Member
oh.. i didnt copy and paste.. typo error.. hehe
Previous Topic: Re: Orcale + JAVA
Next Topic: What's The best and mos usefull book for learning Java ?
Goto Forum:
  


Current Time: Fri Mar 29 07:38:20 CDT 2024