Solved

Oracle Function

Posted on 2011-02-13
34
371 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 17
  • 17
34 Comments
 
LVL 4

Author Comment

by:pinkuray
ID: 34885915
Experts please help me to solve my issue.

0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34885922
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
ID: 34885931
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 28

Expert Comment

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

Expert Comment

by:Naveen Kumar
ID: 34885961
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
ID: 34886015
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
ID: 34886021
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
ID: 34886023
is :SUCCESSDATE is of date data type or varchar2 ?   --> can you confirm this ?
0
 
LVL 4

Author Comment

by:pinkuray
ID: 34886029
sucessdate is  date..
0
 
LVL 4

Author Comment

by:pinkuray
ID: 34886034
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
ID: 34886047
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
ID: 34886055
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
ID: 34886065
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
ID: 34886078
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
ID: 34886082
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
ID: 34886084
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
ID: 34886087
you try the code given in 34886084 and let me know if that works for you.
0
 
LVL 4

Author Comment

by:pinkuray
ID: 34886098
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
ID: 34886102
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
ID: 34886116
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
ID: 34886122
In TEST PACKAGE the SUCCESSDATE is varchar2  OUT parameter.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34886131
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
ID: 34886135
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
ID: 34886141
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
ID: 34886144
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
ID: 34886198
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
ID: 34886200
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
ID: 34886224
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
ID: 34886576
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
ID: 34886584
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
ID: 34886616
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
ID: 34886624
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
ID: 34887461
Thanks a lot :-)
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34893207
Good.
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

707 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