Oracle Error ORA-06553

look at the package specification for pk_jobRevenue


select * from dba_source where name = 'PK_JOBREVENUE';

f_contract is overloaded,  check all versions that may take your parameters.

note, you have strings that look like dates and numbers,  so you may be getting implicit conversion that maps your inputs to incorrect interfaces.

Not having any luck with it, but it's probably due to not knowing what the "dba_source" format should be in the select statement.
The database name is NOR_PROD.

The select statement reads: select * from dba_NOR_PROD where name = 'PK_JOBREVENUE';

I cannot get it to run as: select * from NOR_PROD where name - 'PK_JOBREVENUE';  either.  Error message is: ORA-00942: table or view does not exist when I run either statement.

try to remove quetes... maybe it helps to find the correct call

SELECT pk_jobRevenue.f_contract (JOB.JOB_ID,1,'2009/01/01')
 FROM   NOR_PROD.JOB as JOB
where job_id = '1009463'

Select allOpen in new window

no DBA_SOURCE is the literal name of the view that will show you the package source code

don't subsitute anything for the word "source"

if you don't have access to dba_source

try all_source instead

Removing all the quote marks returns the same original error message.

Using all_source
select * from all_source where name = 'PK_JOBREVENUE';

I found the code for the package.  It is in the attached worksheet. Can you determine anything from looking at that code?


Package-Code.xls

I believe your issue is probably that the date.  You need to do the explicit conversion to date because Oracle cannot figure out which overloaded function to match it to.

SELECT pk_jobRevenue.f_contract ("JOB"."JOB_ID",1,to_date('2009/01/01', 'YYYY/MM/DD'))
 FROM   "NOR_PROD"."JOB" "JOB"
where job_id = '1009463';

johnsone...
I think that might have worked. It returned no error messages when it ran. I'll have to test it with more variables tomorrow. Then, I'll post an update here if it is returning the proper data with different variables in the select statement.

yes, the problem is overloading, but it doesn't appear to be a problem with dates.
There are only two interfaces with 3 parameters for f_contract



    function f_contract        ( as_jobId            in string,
                                 an_lineItemNum      in number,
                                 an_requestType      in number) return number;

    function f_contract        ( as_jobId            in string,
                                 an_lineItemNum      in number,
                                 as_coStatCd         in string) return number;

using to_date sort of tricks Oracle into picking the right one.  Since a date is wrong for both interfaces, Oracle must attempt an implicit conversion to string which matches the second form.

I have to admit, I didn't really look at the package spec first, I just guessed it had a problem picking between date and string.

Not sure why it would have an issue, since the call that is being made clearly is specifying the string, number, string combination.

In theory, this would best match the call, but I don't see why the TO_CHAR should be necessary.

SELECT pk_jobRevenue.f_contract ("JOB"."JOB_ID",1,TO_CHAR('2009/01/01'))
 FROM   "NOR_PROD"."JOB" "JOB"
where job_id = '1009463'

hmm,  TO_CHAR('2009/01/01'))  will do an implicit conversion from string to date, then an explicit but unformatted conversion from date back to string.

CAST might be a better option

I agree though that it looks like Oracle "should" be able to figure out the right version of the overload on its own without tricking it.

TO_CHAR('2009/01/01') should do a conversion from string to string.  There is no way it knows that 2009/01/01 is a valid date format.

TO_CHAR is overloaded, so it does have to determine which version to use.

There is an implicit conversion going on but you're right,  it's not to a date.  I forgot about the overloading to the other character types.
so, in this case it would most likely be an implicit conversion to clob and then back to varchar2

what is job_id?


from your example it appears it could be numeric.

where job_id = '1009463'

if so, this would be another implicit conversion.  Which might be what is throwing off the parser.  Since there would be no exact match it would have to rely on conversions,
and, if Oracle must do conversions then the types are irrelevant and it's only the number of parameters that count and there are 2 forms with 3 parameters.

Of course, this ONLY applies if job_id is, in fact, a numeric type and is simply being used inefficiently with a string conversion.

This is fascinating commentary, folks...

For what it's worth, I've attached a listing of the field descriptions, etc., showing what the various functions in PK_jobRevenue.contract are. Perhaps this will help to better present the data types in this package function.

Thanks for the ongoing help with this. There are several other elements to the PK_jobRevenue package other than .contract. I'm not seeing this problem with any of them. It's just the .contract function that is throwing me this curve.  If you can think of any other info from the Oracle database that I can provide to you, please ask and I'll post it.
Package-Fields-Descriptions.pdf

I've also attached a copy of the page from the Stored Procedures manual for the Oracle database relevant to the PK_jobRevenue.f_conrtract package function showing how the function is structured and used. Maybe this will also help you guys.
Package-Function-Usage-Info.pdf

Attached is a result set for job_id '1009463' from the LINE_ITEM_HIST table.  This is the table referred to on the documentation page shown in the previous attachment "Package Function Usage Info.pdf.


LINE-ITEM-HIST-Data.xls

Can you run this command and post the output:

DESC NOR_PROD.JOB

We think there are more implicit conversions going on here than need to.

Result set for DESC NOR_PROD.JOB attached.
DESC-NOR-PROD.JOB.xls

That worked, sdstuber...

Thanks so much for your expert help with this!

I sincerely appreciate your great help!

glad I could help,  I suggest a split with johnsone though.

sdstuber...

Glad to split the points...Tell me how I go back and do that.

It isn't really about the points.  It's about helping people.  I'm sure we should have split points on other questions together and didn't.

johnsone,
I agree points collection shouldn't be the primary motivation but they are the mechanism by which EE runs
however, giving credit also notes in the PAQ the pieces of the thread that were important.

I think the key points are these....

24475763 - instructions to look at the package
24482588 - instructions to look at the table
24482682 - usable answer derived from the previous steps.

glennes,
if you want to split (it is your choice after all) then you can click the "Request Attention" link above and ask the admins to reopen the
question and then you can accept multiple posts and split points however you see fit.