Home » SQL & PL/SQL » SQL & PL/SQL » problem with procedure not using defaults when called via dbms job or manually
icon5.gif  problem with procedure not using defaults when called via dbms job or manually [message #271472] Mon, 01 October 2007 13:36 Go to next message
tazsorrell
Messages: 1
Registered: October 2007
Junior Member
Hi all
I'm having a problem with a call to a procedure via dbms job.

The procedure is designed to be called with or without parameters and is coded with default values for those parameters when none are set.

i.e.
CREATE OR REPLACE PACKAGE BODY nnltomos is
-- Global private constants.
gcVSSVersion constant varchar2(255) := '$Revision: 35 $';
gcPACKAGE_NAME constant varchar2(30) := 'NNLToMos';

procedure populateVwNNLLimits (pDay in date,
pLoadMinSensLimits in integer default 1,
pLoadConfigurableSensLimits in integer default 1)

When called via the following the procedure is not recognizing the default values (pLoadMinSensLimits and pLoadConfigurableSensLimits and we receive an oracle error
NNLToMos.populateVwNNLLimits(TimePac.GetGMTDate());

Oracle error received:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "SPPPROD.NNLTOMOS", line 59


We have tried running manually both by not passing any parameters and also via NNLToMos.populateVwNNLLimits(TimePac.GetGMTDate(),1,1); Sometimes the process works in manual mode and sometimes it doesn't.

Looking for any input on why the defaults are not being used.
Re: problem with procedure not using defaults when called via dbms job or manually [message #271482 is a reply to message #271472] Mon, 01 October 2007 13:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: problem with procedure not using defaults when called via dbms job or manually [message #271554 is a reply to message #271472] Tue, 02 October 2007 00:51 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Whatever you do, don't post your code. We LOVE to guess what line 59 of your package might be.
I get to guess first: Date-format.

[Updated on: Tue, 02 October 2007 00:52]

Report message to a moderator

Previous Topic: PL SQL procedure optimization
Next Topic: UTL_FILE.FOPEN
Goto Forum:
  


Current Time: Sat Dec 10 14:38:40 CST 2016

Total time taken to generate the page: 0.10986 seconds