Start Free Trial

asked on

Oracle Function

I have a procedure which gives me the latest sucessdate if I pass few parameter to it:

EXEC TEST.SUCCESSDATE(VMA,NAME,INST,:SUCCESSDATE);

where IN parameter are VMA,NAME,INST in varchar2 and sucessdate is yyyy-mm-dd hh24:mi
now I want to write another function where my input will be any date like 2011-02-14
so my function should get the SUCESSDATE from TEST.SUCCESSDATE procedre and then verify if it falls in between 2011-02-14 18:00 and 2011-02-15 11:00.

means I am adding +1 to my input date, so if it falls under this date then return true else false
and I am only going to pass the date but the times for checking is going to be fixed like :

<<FUNCTION input date>> 18:00
<<FUNCTION input DATE + 1>> 11:00

ASKER

Experts please help me to solve my issue.

is this procedure or function ? TEST.SUCCESSDATE(VMA,NAME,INST,:SUCCESSDATE); --> i think this is a procedure.

Can you confirm :SUCCESSDATE is an OUT variable ?

ASKER

TEST is my package and under it I have a procedure called SUCCESSDATE

>> Can you confirm :SUCCESSDATE is an OUT variable ?

Yes it is a OUT variable

is :SUCCESSDATE is of date data type or varchar2 ?

if successdate variable is of DATE data type, then try this :

create or replace function fun_2 return number is
begin
-- your procedure gets called here so that successdate variable has the value in it.
if ( successdate between to_date(input_date || ' 18:00:00' ,'yyyy-mm-dd hh24:mi:ss') and
to_date(input_date,'yyyy-mm-dd')+1 + (11/24) ) then
return 1; --> means true
else
return 0; --> means false
end if;
end;
/

ASKER

I am getting error while creating the function for ":SUCCESSDATE "

ERROR : Warning: Function created with compilation errors.

"PLS-00049: bad bind variable 'SUCCESSDATE' "

Can you please help me in this
:

create or replace function fun_2 return number is
begin
TEST.SUCCESSDATE('VMA','NAME','INST',:SUCCESSDATE);
if ( successdate between to_date(input_date || ' 18:00:00' ,'yyyy-mm-dd hh24:mi:ss') and
to_date(input_date,'yyyy-mm-dd')+1 + (11/24) ) then
  return 1; --> means true
else
return 0; --> means false
end if;
end;

Open in new window

try this : ( no need of : there )

create or replace function fun_2 return number is
begin
TEST.SUCCESSDATE('VMA','NAME','INST',SUCCESSDATE);
if ( successdate between to_date(input_date || ' 18:00:00' ,'yyyy-mm-dd hh24:mi:ss') and
to_date(input_date,'yyyy-mm-dd')+1 + (11/24) ) then
return 1; --> means true
else
return 0; --> means false
end if;
end;

is :SUCCESSDATE is of date data type or varchar2 ? --> can you confirm this ?

ASKER

sucessdate is date..

ASKER

I removed " :" as you said but still i am getting this below error:

PLS-00201: identifier 'SUCCESSDATE' must be declared

you need to declare it right ? try the below :

create or replace function fun_2 return number is
successdate date;
begin
TEST.SUCCESSDATE('VMA','NAME','INST',SUCCESSDATE);
if ( successdate between to_date(input_date || ' 18:00:00' ,'yyyy-mm-dd hh24:mi:ss') and
to_date(input_date,'yyyy-mm-dd')+1 + (11/24) ) then
return 1; --> means true
else
return 0; --> means false
end if;
end;

ASKER

I think we also need to declare input date as now i am getting error as :

PLS-00201: identifier 'INPUT_DATE' must be declared

yes, you are right. i have assigned the value you wanted to.

create or replace function fun_2 return number is
successdate date;
intput_date date := '2011-02-14';
begin
TEST.SUCCESSDATE('VMA','NAME','INST',SUCCESSDATE);
if ( successdate between to_date(input_date || ' 18:00:00' ,'yyyy-mm-dd hh24:mi:ss') and
to_date(input_date,'yyyy-mm-dd')+1 + (11/24) ) then
return 1; --> means true
else
return 0; --> means false
end if;
end;

ASKER

I dont want the input to be fixed,

I would like to change the input date every time:

select fun_2('2010-07-09') from dual;

So do I need to have a IN param in the function?

ASKER

Still gettiing error as below after declaring it:

CREATE OR REPLACE
  FUNCTION fun_2
    RETURN NUMBER
  IS
  SUCCESSDATE DATE;
  intput_date date := '2010-07-09';
  BEGIN
    TEST.SUCCESSDATE('VMA','NAME','INST',SUCCESSDATE);
    IF ( successdate BETWEEN to_date(input_date || ' 18:00:00' ,'yyyy-mm-dd hh24:mi:ss') AND to_date(input_date,'yyyy-mm-dd')+1 + (11/24) ) THEN
      RETURN 1; --> means true
    ELSE
      RETURN 0; --> means false
    END IF;
  END;
  
  
  ERROR: PLS-00201: IDENTIFIER 'INPUT_DATE' MUST BE DECLARED

Open in new window

ok. create this :

create or replace function fun_2(input_date varchar2) return number is
successdate date;
begin
TEST.SUCCESSDATE('VMA','NAME','INST',SUCCESSDATE);
if ( successdate between to_date(input_date || ' 18:00:00' ,'yyyy-mm-dd hh24:mi:ss') and
to_date(input_date,'yyyy-mm-dd')+1 + (11/24) ) then
return 1; --> means true
else
return 0; --> means false
end if;
end;
/

Then call it and verify :

select fun_2('2010-07-09') from dual;

you try the code given in 34886084 and let me know if that works for you.

ASKER

got error :
SQL> select fun_2('2010-07-09') from dual;
select fun_2('2010-07-09') from dual
*
ERROR at line 1:
ORA-01861: literal does not match format string

ASKER

In the TEST package SUCCESSDATE is varchar2 as out param.

So I changed the function like below and it was created successfully:

create or REPLACE
function fun_2(input_date varchar2) return number is
successdate VARCHAR2(50);
begin
TEST.SUCCESSDATE('VMA','NAME','INST',SUCCESSDATE);
if ( successdate between to_date(input_date || ' 18:00:00' ,'yyyy-mm-dd hh24:mi:ss') and
to_date(input_date,'yyyy-mm-dd')+1 + (11/24) ) then
  return 1; --> means true
else
return 0; --> means false
end if;
end;

Open in new window

ASKER

these error are only for the datatype that we are passing.

but if i execute the test package then I get like below:

SQL> EXEC TEST.SUCCESSDATE('VMA','NAME','INST',:SUCCESSDATE);

PL/SQL procedure successfully completed.

SQL> PRINT SUCCESSDATE

SUCCESSDATE
--------------------------------------------------------------------------------
2010-07-09 04:54

ASKER

In TEST PACKAGE the SUCCESSDATE is varchar2 OUT parameter.

can you show me your SUCCESSDATE procedure code as i am not sure whether successdate out variable data type is DATE.

The code given above works fine for me.

but you told me earlier that is of DATE data type and hence all this confusion.

Hold on until i modify it again and give you.

ASKER

Here is my test package:

create or replace package TEST as

PROCEDURE SUCCESSDATE(
SCHEMA IN VARCHAR2,
HOSTNAME IN VARCHAR2,
INSTANCE IN VARCHAR2,
SUCCESSDATE OUT VARCHAR2
);

END TEST;

ASKER

it is varchar2 , so when I run this test package I get the result as below:

SQL> EXEC TEST.SUCCESSDATE('VMA','NAME','INST',:SUCCESSDATE);

PL/SQL procedure successfully completed.

SQL> PRINT SUCCESSDATE

SUCCESSDATE
--------------------------------------------------------------------------------
2010-07-09 04:54

can you run this and give me the output first :

declare
aaa date;
begin
TEST.SUCCESSDATE('VMA','NAME','INST',aaa);
dbms_output.put_line('value is:' || aaa );
end;
/

typo there. run this :

can you run this and give me the output first :

declare
aaa varchar2(100);
begin
TEST.SUCCESSDATE('VMA','NAME','INST',aaa);
dbms_output.put_line('value is:' || aaa );
end;
/

assuming it displays something like this --> value is : 2010-07-09 04:54:30

try the attached code :

create or replace function fun_2(input_date varchar2) return number is
successdate date;
begin
TEST.SUCCESSDATE('VMA','NAME','INST',SUCCESSDATE);
if ( to_date(successdate,'yyyy-mm-dd hh24:mi:ss') between to_date(input_date || ' 18:00:00' ,'yyyy-mm-dd hh24:mi:ss') and
to_date(input_date,'yyyy-mm-dd')+1 + (11/24) ) then
return 1; --> means true
else
return 0; --> means false
end if;
end;
/

ASKER

Please find my result as below :
I am getting the correct result after changing it to varchar2

declare
aaa date;
begin
TEST.SUCCESSDATE('VMA','NAME','INST',aaa);
dbms_output.put_line('value is:' || aaa);
end;
/

Error: ORA-01861: literal does not match format string



declare
aaa varchar2(50);
begin
TEST.SUCCESSDATE('VMA','NAME','INST',aaa);
dbms_output.put_line('value is:' || aaa);
end;


value is:2010-07-09 04:54

Open in new window

ok. so it means there is no seconds in it and only date along with hours and minutes.

Try this :

create or replace function fun_2(input_date varchar2) return number is
successdate date;
begin
TEST.SUCCESSDATE('VMA','NAME','INST',SUCCESSDATE);
if ( to_date(successdate,'yyyy-mm-dd hh24:mi') between to_date(input_date || ' 18:00:00' ,'yyyy-mm-dd hh24:mi:ss') and
to_date(input_date,'yyyy-mm-dd')+1 + (11/24) ) then
return 1; --> means true
else
return 0; --> means false
end if;
end;
/

ASKER

This works for me after modifying a little :

create or replace function fun_2(input_date VARCHAR2) return number is
successdate VARCHAR2(50);
begin
TEST.SUCCESSDATE('VMA','NAME','INST',SUCCESSDATE);
if ( to_date(successdate,'yyyy-mm-dd hh24:mi:ss') between to_date(input_date || ' 18:00:00' ,'yyyy-mm-dd hh24:mi:ss') and
to_date(input_date,'yyyy-mm-dd')+1 + (11/24) ) then
  return 1; --> means true
else
return 0; --> means false
end if;
end;

Open in new window

Now one more thing can we add or more input parameter to pass the parameter to the procedure like :

create or replace function fun_2(input_date VARCHAR2, SCHEMA VARCHAR2, HOSTNAME VARCHAR2,INSTANCE VARCHAR2) return number is
successdate VARCHAR2(50);
begin
TEST.SUCCESSDATE(SCHEMA,HOSTNAME,INSTANCE,SUCCESSDATE);
if ( to_date(successdate,'yyyy-mm-dd hh24:mi:ss') between to_date(input_date || ' 18:00:00' ,'yyyy-mm-dd hh24:mi:ss') and
to_date(input_date,'yyyy-mm-dd')+1 + (11/24) ) then
return 1; --> means true
else
return 0; --> means false
end if;
end;

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Thanks a lot :-)

Good.