Home » SQL & PL/SQL » SQL & PL/SQL » Calling Function within a package
Calling Function within a package [message #220908] Thu, 22 February 2007 12:48 Go to next message
bobby_smitty99
Messages: 12
Registered: February 2007
Junior Member
Hi,

I am trying to get the following PL/SQL statement to work in a package, but have not had any success.

I call a function called ISDATE - The ddl is below:

create or replace function isdate
( p_string in varchar2,
p_fmt in varchar2 := null)
return boolean
as
l_date date;
begin
l_date :=
to_date(p_string,p_fmt);
return TRUE;
exception
when others then
return FALSE;
end;


I perform the following select statement:

SELECT TRANSACTIONPROCESSDT INTO VTRANPRODTCHAR FROM TABLE1
WHERE .....

VTRANPRODCHAR is defined as a BOOLEAN;

I perform my test


IF NOT ISDATE(VTRANPRODTCHAR,'DD-Mon-YYYY')
THEN
INSERT INTO TEMP_ERR_CHK ( CLAIMNO , RESERVENO, TRANSACTIONPROCESSDT,ERROR_MESSAGE_CD,REPORTING_DATE,LOAD_PROCESS_DATE_TIME )
VALUES(VCLAIMNO , VRESERVENO , VTIME_DATE,310,VREPORTING_DATE,CURRENT_TIMESTAMP);
END IF;


But I get the following error

PLS-00306: wrong number or types of arguments in call to 'ISDATE'


How do I call the ISDATE function?

Any help would be appreciated,

Thanks

Bob
Re: Calling Function within a package [message #220914 is a reply to message #220908] Thu, 22 February 2007 13:32 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
My guess is that the 1st parameter you are passing to the function is defined as type DATE in the database, the funciton is expectiong a varchar.

Re: Calling Function within a package [message #220924 is a reply to message #220914] Thu, 22 February 2007 14:54 Go to previous messageGo to next message
bobby_smitty99
Messages: 12
Registered: February 2007
Junior Member
Actually, the field is a varchar field.
Re: Calling Function within a package [message #220925 is a reply to message #220908] Thu, 22 February 2007 14:58 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Actually, the field is a varchar field.
If the above was true, you would NOT be getting the error.
What about
>VTRANPRODCHAR is defined as a BOOLEAN;
???????????????????????????????????????
Re: Calling Function within a package [message #220931 is a reply to message #220925] Thu, 22 February 2007 15:16 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Actually, I think everyone is confused about the varaibles and their types.

From above, I see
Quote:

SELECT TRANSACTIONPROCESSDT INTO VTRANPRODTCHAR FROM TABLE1
WHERE .....

VTRANPRODCHAR is defined as a BOOLEAN;

I perform my test


IF NOT ISDATE(VTRANPRODTCHAR,'DD-Mon-YYYY')
THEN


Your variables are too similar. Why not use more relevant and easy to read variables?

VTRANPRODTCHAR and VTRANPRODCHAR are too similar. Easy to get confused, and now you confused us too. Use better names.
Re: Calling Function within a package [message #221437 is a reply to message #220908] Mon, 26 February 2007 14:13 Go to previous messageGo to next message
bobby_smitty99
Messages: 12
Registered: February 2007
Junior Member
you're right - i should be using better variable names.

But, after checking my code, the value of

SELECT TRANSACTIONPROCESSDT INTO VTRANPRODTCHAR FROM TABLE1
WHERE .....

VTRANPRODTCHAR is defined as a BOOLEAN;

My function still returns an error.




Maybe, I'm going about this the wrong way -

how would I call the ISDATE function I have defined in my package?

Thanks,

Re: Calling Function within a package [message #221485 is a reply to message #221437] Tue, 27 February 2007 02:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think you've tripped over your own variable names:
SELECT TRANSACTIONPROCESSDT INTO VTRANPRODTCHAR FROM TABLE1
WHERE .....

VTRANPRODTCHAR is defined as a BOOLEAN;

This code will never work - you cannot use BOOLEAN data types in SQL. If this is actually the code that you've got in your package, then this is where the problem lies. Replace VTRANPRODTCHAR with a variable whos type matches that of the column you are trying to retrieve.
Also - what particular type of madness caused you to name a boolean variable xxxxxCHAR - surely that's a name for a character datatype, if you've got any sort of coding standards.
Re: Calling Function within a package [message #221554 is a reply to message #221485] Tue, 27 February 2007 07:37 Go to previous messageGo to next message
bobby_smitty99
Messages: 12
Registered: February 2007
Junior Member
I used a boolean datatype since the function returned a boolean value. Is this not correct?


Point taken about the variable names - but I'm at a point were I want to get my code working - which i'm still striving for.


I will rephrase the question.

SELECT TRANSACTIONPROCESSDT INTO VTRANPRODTCHAR FROM TABLE1
WHERE .....





VTRANPRODTCHAR is a character field (varchar(22)) which contains a date. The aim to verify that the date is this field is a valid date (hence the ISDATE function).

I would like to test if VTRANPRODTCHAR is a valid date field,
so i attempt to use the ISDATE function


IF NOT ISDATE(VTRANPRODTCHAR,'DD-Mon-YYYY')
THEN
.....


So my question remains, How do I call this function?

Re: Calling Function within a package [message #221557 is a reply to message #221554] Tue, 27 February 2007 07:47 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
I give up. First you say VTRANPRODTCHAR is a BOOLEAN variable, then you say it's a VARCHAR2 column? Why don't you spend some time and tell us the truth rather than spewing out information that is not even accurate and expecting us to give you an answer

[Updated on: Tue, 27 February 2007 07:49]

Report message to a moderator

Re: Calling Function within a package [message #221563 is a reply to message #221554] Tue, 27 February 2007 08:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Any decent text editor or coding tool will give you a Search/Replace function.
Just use that to change the variable names - after all, it's taken 5 days so far, and it;s still not working - at this point, I'd be trying anything that would make things easier.

I don't understand why you used a boolean. You say:
I used a boolean datatype since the function returned a boolean value. Is this not correct?
but the point where you were using this variable isn't where you are calling the function. You were using a Boolean in a SELECT statement.

You would call that ISDATE function exactly like you did in your OP - I suspect that the error it was giving you was about the Boolean in the SQL rather than the call to ISDATE.

If it still doesn't work, then cut and paste this into SQL and show us the results:
DECLARE
  v_string   varchar2(22);
  v_boolean  boolean;
BEGIN
  SELECT TRANSACTIONPROCESSDT 
  INTO   v_string
  FROM   TABLE1
  WHERE  rownum=1;

  v_boolean  := ISDATE(v_string,'dd-mon-yyyy');

  IF v_boolean THEN
    raise_application_error(-20001,'ISDATE returned TRUE');
  ELSE
    raise_application_error(-20001,'ISDATE returned FALSE');
  END IF;
END;


And of course, you wouldn't be having these problems if you'd remembered the important rule:

Put Numbers in NUMBER datatypes,
put Dates in DATE datatypes and
put strings in VARCHAR2 datatypes.

Putting dates in strings only ever causes problems. It never makes things easier.
Re: Calling Function within a package [message #221588 is a reply to message #221563] Tue, 27 February 2007 09:53 Go to previous messageGo to next message
bobby_smitty99
Messages: 12
Registered: February 2007
Junior Member
I altered the code a bit to make it more like what I need.

But this is what I was looking for - a way to call the function

Now, I have to incorporate it into the package I am writing.



DECLARE
v_string varchar2(10);
v_boolean boolean;
BEGIN
SELECT substr(TRANSACTIONPROCESSDT,1,10)
INTO v_string
FROM irf_claims_staging1
WHERE rownum=1;
v_boolean := ISDATE(v_string,'yyyy-mm-dd');
IF v_boolean THEN
raise_application_error(-20001,'ISDATE returned TRUE');
ELSE
raise_application_error(-20001,'ISDATE returned FALSE');
END IF;
END;
/

The result from this block is

ORA-20001: ISDATE returned TRUE



with respect to you comments
"And of course, you wouldn't be having these problems if you'd remembered the important rule:

Put Numbers in NUMBER datatypes,
put Dates in DATE datatypes and
put strings in VARCHAR2 datatypes.

Putting dates in strings only ever causes problems. It never makes things easier. "

I am working on converting an existing application from SQL Serve to Oracle. Specifically, I am working on an error processing routine. All the fields that I am validating are in character format, and I have to determine if they are eligible to be put in their proper data types (i.e. Numbers in NUMBER datatypes, Dates in Date, etc).

Thanks to all for their assistance and patience.
Re: Calling Function within a package [message #221589 is a reply to message #221588] Tue, 27 February 2007 09:59 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your original code of
IF NOT ISDATE(VTRANPRODTCHAR,'DD-Mon-YYYY')
was fine, if VTRANPRODTCHAR was a Varchar data type.

Previous Topic: spool table script
Next Topic: Refresh time for Materialized View
Goto Forum:
  


Current Time: Fri Dec 09 00:07:26 CST 2016

Total time taken to generate the page: 0.31379 seconds