Using user defined function inside package
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Using user defined function inside package

  1. #1
    Join Date
    May 2006
    Posts
    4

    Using user defined function inside package

    Hi,

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

    I have created 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 ISDATE(VTRANPRODTCHAR,'DD-Mon-YYYY') = 'FALSE'
    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'

    Why is this returning an error - who do i fix it?

    thanks

  2. #2
    Join Date
    Apr 2006
    Posts
    50
    you have defined your function to accept to in parameters of type varchar2, but then you pass a boolean variable 'VTRANPRODTCHAR' into it as the first parameter.

    I think you need to call it something like

    date_str VARCHAR2 := '01-Jan-2007';

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

    .....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width