CODEWITHSUNDEEP
oracle-databaseplsqloracle11goracle-sqldeveloper
Ian Carpenter
Ian Carpenter

Reputation: 8626

Including the schema name when calling a PL/SQL procedure doesn't work in one schema but does in another

This problem came up when trying out the Unit Testing capabilities of SQLDeveloper.

When running a test for a procedure that is created within my schema I am seeing an error however when the same procedure is run in one of the oracle supplied schemas it works without an issue.

SQL Developer generates the following calls:

1) This one doesn't work (error is shown below):

BEGIN
 "IANC"."SIMPLE_PARAMETER"(P_X => 123);
END;

2) This one does:

BEGIN
 "HR"."SIMPLE_PARAMETER"(P_X => 123);
END;

This is the procedure:

CREATE OR REPLACE PROCEDURE SIMPLE_PARAMETER 
(
  P_X IN NUMBER  
)
IS
BEGIN

  null;

END SIMPLE_PARAMETER;

The following is the output from SQLPLUS, where you can see when the procedure is run in my schema I see an error whilst when running the same procedure in another schema the procedure works as expected:

enter image description here

In case of need my I am using Oracle Enterprise Edition 11.2.0.1.0

Update

Screen shot showing procedure signatures

enter image description here

I should also mention that if I remove the schema name from the procedure call then the procedure runs and completes as expected.

Thanks in advance for any help received.

Upvotes: 1

Views: 8419

Answers (1)

Dave Costa
Dave Costa

Reputation: 48111

Are you sure that the SIMPLE_PARAMETER procedure in IANC is the same (or at least has the same signature) as the one in HR? What do you get from `DESCRIBE "IANC"."SIMPLE_PARAMETER".

(P.S. since your identifiers are all upper-case, you shouldn't need the double quotes at all.)

Added: Another possibility is that you have a package called IANC in the IANC schema, so Oracle is looking for a procedure in that package called SIMPLE_PARAMETER that does not exist. Example:

SQL> exec bigdecimaltest

PL/SQL procedure successfully completed.

SQL> exec dcosta.bigdecimaltest

PL/SQL procedure successfully completed.

SQL> create or replace package dcosta as
  2  end;
  3  /

Package created.

SQL> exec dcosta.bigdecimaltest
BEGIN dcosta.bigdecimaltest; END;

             *
ERROR at line 1:
ORA-06550: line 1, column 14:
PLS-00302: component 'BIGDECIMALTEST' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

This seems like buggy behavior -- if the attempt to resolve the name as package.member doesn't succeed, I think Oracle ought to then try it as schema.object, but it looks like once it has found a match on the package name it won't reconsider that.

Upvotes: 2

Related Questions