Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

pl/sql package spec vs body variable declaration

2c2b5ef6-be8c-4227-b273-3eaf9b3f273fMay 25 2016 — edited May 25 2016

Hi,

I am starting to get into pl/sql programing (version 11.2.0.4.0). I currently just create/execute queries and small scripts to collect data for reporting. I have written a couple of packages, but, have a few questions about where to declare variables.

I am currently trying to modify an existing package and trying to figure out the parameters / variable declarations.

The spec is for this package is:

CREATE OR REPLACE PACKAGE COUPI_OM_OKS_WF_DETAILS_PKG AS

  PROCEDURE OM_OKS_DETAILS (

       P_ERRBUF      OUT VARCHAR2,

       P_RETCODE    OUT VARCHAR2,

       P_MODE           IN VARCHAR2);

  PROCEDURE UPDATE_EXT_TABLE (

       P_RET_CODE  OUT VARCHAR2);

END COUPI_OM_OKS_WF_DETAILS_PKG;

/

P_MODE comes from a parameter when you start the package manually. You can't modify its default value and its a date.

In the body, there are these procedures:

PROCEDURE OM_OKS_DETAILS (

  P_ERRBUF      OUT VARCHAR2,

  P_RETCODE   OUT VARCHAR2,

  P_MODE          IN VARCHAR2,

  P_DATE_FROM IN VARCHAR2)

IS...

and this one

PROCEDURE UPDATE_EXT_TABLE (

  P_DATE_FROM IN DATE,

  P_RET_CODE OUT VARCHAR2)

IS...

I see that P_MODE, P_RETCODE AND P_RET_CODE are in the spec and in the body. Also, P_MODE comes from the parameter screen (when you start the package and you can set the values to either FULL or UPDATE), which makes it look like the way to pass values from the parameter screen to the procedures is to define in the spec, right?

However, P_DATE_FROM is not in the spec, but, the value comes from a parameter (when you start the package). You can't change it.

- Why is P_MODE in the spec and not P_DATE_FROM?

- Don’t variables passed from the parameter screen need to be in the spec?

- Looks like it is ok to define the same variable (P_DATE_FROM) as both a varchar2 and then later as a date. This seems odd to me, why not leave it one way or the other?

thanks for your feedback and best regards

Alan

This post has been answered by unknown-7404 on May 25 2016
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 22 2016
Added on May 25 2016
4 comments
2,793 views