Solved

Oracle Function

Posted on 2011-02-13
34
367 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
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
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 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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Shredding xml into an oracle 11g Database 2 59
Repeat query 13 61
run sql script from putty 4 67
DB migration from Mssql to 12c oracle , data not loading. 3 39
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

839 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