Oracle Error ORA-06553

Can one of you help me modify this script to correct the error: ORA-06553: PLS-307: too many declarations of 'F_CONTRACT' match this call...

SELECT pk_jobRevenue.f_contract ("JOB"."JOB_ID",1,'2009/01/01')
 FROM   "NOR_PROD"."JOB" "JOB"
where job_id = '1009463'
Glenn StearnsAnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
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.





0
Glenn StearnsAnalystAuthor Commented:
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.
0
HainKurtSr. System AnalystCommented:
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'

Open in new window

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

sdstuberCommented:
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"

0
sdstuberCommented:
if you don't have access to dba_source

try all_source instead
0
Glenn StearnsAnalystAuthor Commented:
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
0
johnsoneSenior Oracle DBACommented:
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';
0
Glenn StearnsAnalystAuthor Commented:
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.

0
sdstuberCommented:
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;
0
sdstuberCommented:
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.
0
johnsoneSenior Oracle DBACommented:
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.
0
johnsoneSenior Oracle DBACommented:
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'
0
sdstuberCommented:
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.
0
johnsoneSenior Oracle DBACommented:
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.
0
sdstuberCommented:
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
0
sdstuberCommented:
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.
0
Glenn StearnsAnalystAuthor Commented:
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
0
Glenn StearnsAnalystAuthor Commented:
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
0
Glenn StearnsAnalystAuthor Commented:
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
0
johnsoneSenior Oracle DBACommented:
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.
0
Glenn StearnsAnalystAuthor Commented:
Result set for DESC NOR_PROD.JOB attached.
DESC-NOR-PROD.JOB.xls
0
sdstuberCommented:
yep,  like guessed above.

job_id is a number, not a string

try this...

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Glenn StearnsAnalystAuthor Commented:
That worked, sdstuber...

Thanks so much for your expert help with this!
0
Glenn StearnsAnalystAuthor Commented:
I sincerely appreciate your great help!
0
sdstuberCommented:
glad I could help,  I suggest a split with johnsone though.
0
Glenn StearnsAnalystAuthor Commented:
sdstuber...

Glad to split the points...Tell me how I go back and do that.
0
johnsoneSenior Oracle DBACommented:
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.
0
sdstuberCommented:
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.


0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.