Solved

Oracle Function

Posted on 2011-02-13
34
359 Views
Last Modified: 2012-05-11
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
Comment
Question by:pinkuray
  • 17
  • 17
34 Comments
 
LVL 4

Author Comment

by:pinkuray
Comment Utility
Experts please help me to solve my issue.

0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
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
 
LVL 4

Author Comment

by:pinkuray
Comment Utility
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
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
is :SUCCESSDATE is of date data type or varchar2 ?
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
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
 
LVL 4

Author Comment

by:pinkuray
Comment Utility
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
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
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
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
is :SUCCESSDATE is of date data type or varchar2 ?   --> can you confirm this ?
0
 
LVL 4

Author Comment

by:pinkuray
Comment Utility
sucessdate is  date..
0
 
LVL 4

Author Comment

by:pinkuray
Comment Utility
I removed " :" as you said but still i am getting this below error:

PLS-00201: identifier 'SUCCESSDATE' must be declared


0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
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
 
LVL 4

Author Comment

by:pinkuray
Comment Utility
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
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
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
 
LVL 4

Author Comment

by:pinkuray
Comment Utility
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
 
LVL 4

Author Comment

by:pinkuray
Comment Utility
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
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
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
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
you try the code given in 34886084 and let me know if that works for you.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 4

Author Comment

by:pinkuray
Comment Utility
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
 
LVL 4

Author Comment

by:pinkuray
Comment Utility
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
 
LVL 4

Author Comment

by:pinkuray
Comment Utility
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
 
LVL 4

Author Comment

by:pinkuray
Comment Utility
In TEST PACKAGE the SUCCESSDATE is varchar2  OUT parameter.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
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
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
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
 
LVL 4

Author Comment

by:pinkuray
Comment Utility
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
 
LVL 4

Author Comment

by:pinkuray
Comment Utility
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
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
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
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
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
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
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
 
LVL 4

Author Comment

by:pinkuray
Comment Utility
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
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
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
 
LVL 4

Author Comment

by:pinkuray
Comment Utility
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
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 500 total points
Comment Utility
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
 
LVL 4

Author Closing Comment

by:pinkuray
Comment Utility
Thanks a lot :-)
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
Good.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now