[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 384
  • Last Modified:

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
0
pinkuray
Asked:
pinkuray
  • 17
  • 17
1 Solution
 
pinkurayAuthor Commented:
Experts please help me to solve my issue.

0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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 ?

0
 
pinkurayAuthor Commented:
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
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Naveen KumarProduction Manager / Application Support ManagerCommented:
is :SUCCESSDATE is of date data type or varchar2 ?
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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;
/
0
 
pinkurayAuthor Commented:
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

0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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;
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
is :SUCCESSDATE is of date data type or varchar2 ?   --> can you confirm this ?
0
 
pinkurayAuthor Commented:
sucessdate is  date..
0
 
pinkurayAuthor Commented:
I removed " :" as you said but still i am getting this below error:

PLS-00201: identifier 'SUCCESSDATE' must be declared


0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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;
0
 
pinkurayAuthor Commented:
I think we also need to declare input date as now i am getting error as :

PLS-00201: identifier 'INPUT_DATE' must be declared


0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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;
0
 
pinkurayAuthor Commented:
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?
0
 
pinkurayAuthor Commented:
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

0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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;

0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
you try the code given in 34886084 and let me know if that works for you.
0
 
pinkurayAuthor Commented:
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

0
 
pinkurayAuthor Commented:
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

0
 
pinkurayAuthor Commented:
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
0
 
pinkurayAuthor Commented:
In TEST PACKAGE the SUCCESSDATE is varchar2  OUT parameter.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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.

0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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.
0
 
pinkurayAuthor Commented:
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;
0
 
pinkurayAuthor Commented:
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
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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;
/
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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;
/
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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;
/
0
 
pinkurayAuthor Commented:
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

0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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;
/
0
 
pinkurayAuthor Commented:
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;
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
yes, why not.  use the below :

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);
--> no SS because your procedure does not give it. only hh24:mi should be used in to_char(..)
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;

0
 
pinkurayAuthor Commented:
Thanks a lot :-)
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Good.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 17
  • 17
Tackle projects and never again get stuck behind a technical roadblock.
Join Now