Force using Overloaded Package version

[zephan]

Hi All,
I try to use owa_opt_lock.checksum oracle SYS's function. My problem is that Oracle dont want to use the second version.
The first version is : owa_opt_lock.checksum(p_buff VARCHAR2) RETURN NUMBER;

and the second one : owa_opt_lock.checksum(
p_owner VARCHAR2,
p_tname VARCHAR2,
p_rowid ROWID) RETURN NUMBER;

If I use the second version, it returns "wrong number or type of arguments".

I tried to view the package declaration on the database, if ound this function declared with both headers, however, my SQL assistant software shows only the list of arguments for the first version. I suppose this list is retreived somewhere from the datadictionnary.

Anyone having an Idea on this problem.

Best Regards,
Zeph

 

[zephan]

Hi,
I dont know why, but the problem was with the assignment. If I used n:= owa_opt_lock.checksum(bla,blabla,blablabla); it would work.

Now, I tried to use this function in a SELECT statment. I got another error : "Cannot use a DML operation inside a query".

How can I get the checksum of a row in a select statment.

 

[carp]

It sounds like you have DML in your function. Look for INSERT, UPDATE, or DELETE statements in the function. If you find any, this is where your problem is. To verify this is the problem, you could comment out the DML and then try running it as a query.

 

[zephan]

Thank you carp. This function is a SYS function. I have noway to changeit so I have to use a function giving the same result but written by my own.

 

[carp]

As I understand your post, the problem you are having is with the one that you have written. So why can you not experiment with it to determine what the source of the problem is? And if you have no way to change anything, how are you adding a function to a SYS package?

 

[zephan]

Sorry it is a misunderstanding. This SYS package comes with the two versions of the function. The overloading is not from me. I beleive Checksum functionnality is not too complexe to realize, someone have certainly thought about and tested it before me, I just want a checksum I can use in a SELECT statment.

 

[carp]

Aha! Got it! Thanks for that.
Well, the problem is liable to be the same - there is DML in the function (which Oracle will tell you is a really bad idea!). If the code is not wrapped and you can create your own objects, I might suggest you do a cut/paste to create your own package with a different name, take the DML out of the function(s) and use that.
If DML IS in the function, I would be very circumspect about using it for something other than its intended purpose - at least until you figure out what that DML is doing and whether or not that is going to cause a problem for you.

 

[zephan]

Thank you Carp, I'll close this isssue and open another one for checksum.