PL/SQL default date in procedure

[SteveCulshaw]

I'm trying to get to grips with using stored procedures, and already I'm baffled :-(
I thought it'd be sensible to set a default date
....
PROCEDURE GetValidBCApp(
o_CaseNo OUT tblCASENO,
o_RefVal OUT tblREFVAL,
o_Address OUT tblADDRESS,
i_From IN tblDECIDD DEFAULT '01-JAN-00',
i_Until IN tblDECIDD)
IS
....
but I get a PLS-00323 error.
Just what is the syntax for setting default ??

Thanks, Cheers,
Steve C.

 

[jee]

If the variable is an Oracle date variable you have to use something like:

to_date ('01-JAN-00', 'DD-MMM-YY')

 

[SteveCulshaw]

Thanks for the response, but that doesn't seem to work :-(

CREATE OR REPLACE
PACKAGE BC_Pkg
AS
  TYPE tblCASENO 	IS TABLE OF VARCHAR2(13) INDEX BY BINARY_INTEGER;
  TYPE tblREFVAL 	IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
  TYPE tblADDRESS 	IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
  TYPE tblBCSTAT 	IS TABLE OF VARCHAR2(6) INDEX BY BINARY_INTEGER;
  TYPE tblDECIDD	IS TABLE OF DATE INDEX BY BINARY_INTEGER;

  PROCEDURE GetValidBCApp(
  	o_CaseNo	OUT tblCASENO,
	o_RefVal   	OUT tblREFVAL,
	o_Address	OUT tblADDRESS,
  	i_From 		IN tblDECIDD,
  	i_Until 	IN tblDECIDD)
  	;

END BC_Pkg;

and the body

CREATE OR REPLACE
PACKAGE BODY BC_Pkg
AS
  PROCEDURE GetValidBCApp(
  	o_CaseNo	OUT tblCASENO,
	o_RefVal  	OUT tblREFVAL,
	o_Address   OUT tblADDRESS,
  	i_From 		IN tblDECIDD DEFAULT to_date('01-JAN-00','DD-MMM-YY'),
  	i_Until 	IN tblDECIDD)
  IS
 .... etc.

If I try to compile this I get PLS-00382: Expression is of wrong type, but I thought the header defined it as DATE ??
Cheers,
Steve C.

 

[Naith]

You're nearly there, Steve.

The correct date format syntax is:

to_date('01-JAN-00','DD-MON-YY')

not 'DD-MMM-YY'.

All the best,

Naith

 

[SteveCulshaw]

Thanks for the response Naith
Oh durr, showing my Access roots here aren't I
Still no luck
- does the default appear in the package and/or package body definition ?
- I only have it in the body

  PROCEDURE GetValidBCApp(
  	o_CaseNo	OUT tblCASENO,
	o_RefVal   	OUT tblREFVAL,
	o_Address	OUT tblADDRESS,
  	i_From 		IN tblDECIDD DEFAULT to_date('01-JAN-00','DD-MON-YY'),
  	i_Until 	IN tblDECIDD DEFAULT to_date('31-JUN-00','DD-MON-YY'))
  IS

Cheers,
Steve C.