CODEWITHSUNDEEP
databaseoracle-databasestored-procedures
Rodrigo
Rodrigo

Reputation: 11

How to add a stored procedure to Oracle database?

Through SQL Developer, I added a new stored procedure to an Oracle database. Not only it is listed on the stored procedures list (along with every other SP) but I can easily execute it on SQL Developer.

The problem is I cannot seem to execute it from an external application. The database is linked to it, I can call every other stored procedure, but not the one I just created. The error message I get is:

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

I have copied access and permissions configs from other SPs that work, but it didn't really change anything.

Upvotes: 0

Views: 269

Answers (1)

Sodved
Sodved

Reputation: 8598

A good thing when debugging this sort of thing is:

SELECT owner, object_name, object_type
FROM   all_objects
WHERE  object_name IN( 'PROCEDURE_WHICH_WORKS', 'PROCEDURE_WHICH_DOESNT' )

This should make it obvious if SQL*Developer created it in the wrong schema or there is a missing synonym or something like that. If its all the same then you need to look at possible missing GRANT EXECUTE permissions.

Are you sure SQL*Developer and your external program are connecting as exactly the same user? Just to be sure you can

SELECT user FROM dual

Upvotes: 1

Related Questions