CODEWITHSUNDEEP
plsqloracle10g
Amit
Amit

Reputation: 623

PLS-00323 error in oracle

I am creating a procedure in a package. I have updated the specification of the package and then when i am updating the body of the package it shows me the following error.

[Error] PLS-00323 (314: 13): PLS-00323: subprogram or cursor 'INSERT_CUSTOMER_ADDRESS' is declared in a package specification and must be defined in the package body

N.B.: INSERT_CUSTOMER_ADDRESS is my procedure name.

Upvotes: 9

Views: 31156

Answers (4)

user3773778
user3773778

Reputation: 21

Always the declaration on the pkg spec and the pkg body should be same.

Which means the procedure / function declared in pkg spec ( includes name of the procedure / function , Parameter type ) should be exactly same.

Note : when oracle compiles the pkg for above error case, it won't give you the exact line no.

Upvotes: 2

Marc
Marc

Reputation: 778

Because somebody's old errors are always news to me, I'll add my 3 cents:

Someone deleted their spec, but still had the body (No, I don't know how) and they "didn't want to have to type all that back in".

So I showed them how to generate the spec from the body:

  1. Copy the body into a worksheet.
  2. Insert an empty header above the body with the name of the package.
  3. Compile.
  4. [Whether you get an error or not,] Right click on the package body in the Packages tree, and select "Synchronize Specification and Body".
  5. Select which objects to expose in the spec and click OK.
  6. "magic happens"

Only magic didn't happen -- and everything went red... with PLS-00323 errors.

SQL Developer populates the package spec by pulling the procedure and function headers from the code itself, so you shouldn't have to worry about things liek pselling. It presents a list of objects to add to the package/spec, and you choose which ones to add.

We selected the correct procedures and functions, but it compiled with errors. Again. And, again.

It turns out that the functions that threw errors were defined in the package body with DETERMINISTIC -- when SQL Dev's editor did the synchronization, it left out the word. That's pretty significant.

Anyway, reviewed, pasted DETERMINISTIC in the specific functions before the semi-colon, saved (recompiled), and then it was magic again.

You would expect SQL Developer to do the full job, instead of a half-... job.

Upvotes: 1

user2723258
user2723258

Reputation: 31

Specification : FUNCTION ITEM_ACTIVE(SKU_NUM IN NUMBER) RETURN BOOLEAN;

Body : FUNCTION ITEM_ACTIVE(P_SKU_NUM IN NUMBER) RETURN BOOLEAN;

Change either specification or body to match exact.

HTH!

Upvotes: 1

Maxim Shevtsov
Maxim Shevtsov

Reputation: 212

If you create a procedure in the package specification it must be created\implemented in the package body. Consider your package specification as an interface and the package body as its implementation.

Upvotes: 2

Related Questions