Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Stored Procedure - Must Be Delcared?
Oracle Stored Procedure - Must Be Delcared? [message #199874] Thu, 26 October 2006 09:32 Go to next message
quantass
Messages: 20
Registered: April 2006
Junior Member
I dont understand why i cant access my newly created stored procedure from within Oracle 10g. The procedure was created with:

create or replace PROCEDURE "Maps_Update" (mid IN number, x IN number, y IN number) AS
iexist pls_integer;
maxid number;
BEGIN

SELECT count(*) INTO iexist FROM Maps WHERE refid=mid AND refid_type='Orgs';

IF iexist = 0 THEN
select (nvl(max(MapID),0)+1) into maxid from Maps;
INSERT INTO Maps (MAPID,REFID,REFID_TYPE,MAP_DATA_SOURCE,MAP_SERVICE,X_COORD,Y_COORD,PUBLISH,GEOCODING_STATUS) VALUES (maxid, mid, 'Orgs', 'ArcIMS', 'maps', x,y,'Y', 10);
ELSE
UPDATE Maps SET X_COORD=x, Y_COORD=y WHERE REFID=mid AND REFID_TYPE='Orgs';
END IF;
END;

The procedure appears under the Procedure category and compiled fine. It seems like Oracle has properly created it without a problem. I then try to call it with EXECUTE Maps_Update(1,10,11) from within Oracle Sql Developer script window, SQL Plus, and .NET ODP Application. I get the same results:

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

The login Im using to create the procedure does have rigts to create, drop procedures, and execute. It is basically admin. I've never had a problem with it before.

Is there something im missing with stored procedures in oracle. I dont undertsnad why if it is listed under the Procedures branch implying the procedure is declared why it is having troubles locating it. I've also tried using the <schema name>.Maps_Update

[Updated on: Thu, 26 October 2006 09:38]

Report message to a moderator

Re: Oracle Stored Procedure - Must Be Delcared? [message #199883 is a reply to message #199874] Thu, 26 October 2006 09:57 Go to previous messageGo to next message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member

Problem due to "" used for Maps_Update
So, try with quotes

or rename the procedure

SQL> rename "Maps_Update" to Maps_Update
Re: Oracle Stored Procedure - Must Be Delcared? [message #199885 is a reply to message #199874] Thu, 26 October 2006 10:05 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Yep, that is all it is, an upper/lower case thing.

Why may I ask did you create the object in anything but upper case? This has been a problem for Oracle DBAs for a decade. As a general rule, never use special characters or lower case in the name of any Oracle Object.

Good luck, Kevin
Re: Oracle Stored Procedure - Must Be Delcared? [message #199902 is a reply to message #199885] Thu, 26 October 2006 11:43 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
I think it's because a lot of these people are coming from a Microsoft environment where everything is put in quotes. If you notice, you'll see a lot of double-quotes around things like procedure names and even in schema and tablenames as such:

"SCOTT"."EMP"
Previous Topic: procedure call from SQL*Plus in Korn Shell script
Next Topic: Occupied space from Data Dictionary
Goto Forum:
  


Current Time: Sat Dec 03 09:56:03 CST 2016

Total time taken to generate the page: 0.04854 seconds