Solved

PLSQL Procedure

Posted on 2011-02-28
19
329 Views
Last Modified: 2012-05-11
Hi I have a procedure which gives me the successdate like below:

 2011-02-25 02:24

Now I want to implement a simple logic like

 
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
ERROR_MSG :='THE LAST SUCESSDATE WHICH IS ON'||SUCCESSDATE;
end if ;
end;

Open in new window



This is not working but if I remove the if condition then i am able to get the ERROR_MSG .

Need you help experts..
0
Comment
Question by:pinkuray
  • 8
  • 8
  • 2
  • +1
19 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34995050
what is the data type of this "successdate" ? Can you confirm please.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34995051
what is the value you are passing for the input_date ?
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34995084
i tested this with the below and is working perfectly fine unless you can tell me what values are being given for the input_Date and successdate variables ?

i used the below to test this...

select 'THE LAST SUCESSDATE WHICH IS ON' from dual
where to_date('2011-02-28 19:10','yyyy-mm-dd hh24:mi') BETWEEN to_date('2011-02-28' || ' 18:00:00' ,'yyyy-mm-dd hh24:mi:ss') AND to_date('2011-02-28','yyyy-mm-dd')+1 + (11/24)
0
 
LVL 4

Author Comment

by:pinkuray
ID: 34995086
Sorry a typo mistake while posting the question:

It should be like below:

 IF SUCCESSDATE IS NOT NULL THEN ERROR_MSG    :='THE LAST SUCESSDATE WHICH IS ON '||SUCCESSDATE;
         ELSE IF ( TO_DATE(SUCCESSDATE,'YYYY-MM-DD HH24:MI') BETWEEN TO_DATE(SUCCESSDATE || ' 18:00:00' ,'YYYY-MM-DD HH24:MI:SS')
AND TO_DATE(SUCCESSDATE,'YYYY-MM-DD')+1 + (11/24) ) THEN
ERROR_MSG    :='THE LAST SUCESSDATE NEW WHICH IS ON '||to_date(SUCCESSDATE,'yyyy-mm-dd');
          END IF;


The SUCESSDATE  is of type SUCCESSDATE OUT VARCHAR2.

I am calling a procedure before this statement.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34995097
ok.
 
1) what is the output you are getting for the below code currently and what is the output you are expecting ?
2) can you provide the values of  successdate ?

 IF SUCCESSDATE IS NOT NULL THEN ERROR_MSG    :='THE LAST SUCESSDATE WHICH IS ON '||SUCCESSDATE;
         ELSE IF ( TO_DATE(SUCCESSDATE,'YYYY-MM-DD HH24:MI') BETWEEN TO_DATE(SUCCESSDATE || ' 18:00:00' ,'YYYY-MM-DD HH24:MI:SS')
AND TO_DATE(SUCCESSDATE,'YYYY-MM-DD')+1 + (11/24) ) THEN
ERROR_MSG    :='THE LAST SUCESSDATE NEW WHICH IS ON '||to_date(SUCCESSDATE,'yyyy-mm-dd');
          END IF;
0
 
LVL 4

Author Comment

by:pinkuray
ID: 34995310
Values of SUCESSDATE IS : 2011-02-25 02:24  

As an output.

I want to implement the if the time is between 18:00 to 11:59  then it should be the previous day
0
 
LVL 4

Author Comment

by:pinkuray
ID: 34995325
It is going to the exception as when other then part..
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34995721
i am sorry i did not understand what your requirement is.

first give your full code otherwise i cannot understand this "It is going to the exception as when other then part "
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 34996438
is success_date already a date?


if so  don't use to_date on it.


use to_char to convert a date to a string
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 3

Expert Comment

by:LFLFM
ID: 34996470
Your code seems to be all wrong... you need to post the whole thing...
First you check that SUCCESSDATE is not null, ELSE you check it's value!?? the value in that condition will always be NULL...

Also, doesnt SUCCESSDATE need to be an IN parameter?
Here's what I think your code may have to be:
First, declare the variable dtRealDate as DATE.
/*
IF SUCCESSDATE IS NOT NULL THEN
    ERROR_MSG    :='THE LAST SUCESSDATE WHICH IS ON '||SUCCESSDATE;
ELSE --... ELSE NOTHING! By this time SUCCESSDATE IS NULL, so there is no point in checking its value again*/
IF SUCCESSDATE IS NULL THEN
    ERROR_MSG    :='THERE IS NO SUCCESSDATE!';
ELSE
    dtRealDate = to_date(SUCCESSDATE,'YYYY-MM-DD HH24:MI');
    IF ( dtRealDate BETWEEN TO_DATE(substr(SUCCESSDATE,1,11) || ' 18:00:00' ,'YYYY-MM-DD HH24:MI:SS')
                        AND trunc(dtRealDate)+1-(1/24/60/60)) THEN
        ERROR_MSG    :='THE LAST SUCESSDATE NEW WHICH IS ON ' || trunc(dtRealDate);
    END IF;
/*END IF;*/

Open in new window



Pseudocode:
If there is no value in SUCCESSDATE then
   error
else
   assign a real date variable dtRealDate with the actual date value 
   if datevariable between (date at 18:00) and (date at 23:59:59) then
        Error message informing (date)
    END IF;
END IF;

Open in new window

0
 
LVL 4

Author Comment

by:pinkuray
ID: 35003132
successdate is a data with time stored in my DB and also used as a out parameter for a procedure but the datatype is in varchar2(200) ..

Please find my attached code:

 
DECLARE
  INSTANCE SR.TARGET_LIST_T.INSTANCENAME%TYPE;
  SCHEMA SR.TARGET_LIST_T.SCHEMANAME%TYPE;
  HOSTNAME SR.TARGET_LIST_T.HOSTNAME%TYPE;
  SUCCESSDATE VARCHAR2(100);
  dtRealDate date;
  VSTATUS     NUMBER := 0;
  VPRODUCT    VARCHAR2(15);
  ERROR_MSG   VARCHAR2(2000):='NO ERRORS';
BEGIN
  BEGIN
    IF :P1_TARGET IS NOT NULL THEN
      EA.SCHEMA_CHECK ( :P1_TARGET ,TO_NUMBER(:P1_PRODUCT),VSTATUS);
      BEGIN
        SELECT PRODUCTNAME INTO VPRODUCT FROM PRODUCT_M WHERE PRODUCTID = :P1_PRODUCT;
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
        ERROR_MSG:= 'DATA DOESN NOT FOUND WITH THE GIVEN INPUT OF PRODUCT/REFRESH THE PAGE';
      WHEN OTHERS THEN
        ERROR_MSG:= 'ERROR FOUND IN THE INPUT CONTACT ADMINISTRATOR';
      END;
      IF VSTATUS    = 0 THEN
        ERROR_MSG  := 'IN ' || VPRODUCT || ' PRODUCT TARGET SCHEMA DOES NOT FOUND. PLEASE CHECK THE EXISTANCE OF ' || :P1_TARGET || '  TARGET ON SS';
      ELSIF VSTATUS = 8 THEN
        --NEED TO CHECK THE BELOW LINE
        ERROR_MSG   := 'PLEASE SPECITY THE PRODUCT OR FOR THIS PRODUCT IT IS STILL IN PROGRESS';
      ELSIF VSTATUS  = 9 THEN
        ERROR_MSG   := 'ERROR FOUND IN THE INPUT CONTACT ADMINISTRATOR AND PROVIDE NECESSARY DETAILS';
      ELSIF VSTATUS != 1 THEN
        ERROR_MSG   := 'MORE THAN ONE VALUE EXISTS IN SS FOR TARGET ' || :P1_TARGET || 'NOT ABLE TO SCHEDULE AND DISPLAY THE BACKUP CONTACT ADMINISTRATOR';
      ELSE
        EA.GET_INSTANCE ( :P1_TARGET ,TO_NUMBER(:P1_PRODUCT),INSTANCE,HOSTNAME);
        IF TO_NUMBER(:P1_PRODUCT) != 4 AND TO_NUMBER(:P1_PRODUCT) != 6 AND TO_NUMBER(:P1_PRODUCT) != 5 THEN
          SCHEMA                  := 'ALL';
        ELSE
          SCHEMA :=:P1_TARGET;
        END IF;
        EA.SUCCESSDATE(SCHEMA,HOSTNAME,INSTANCE,SUCCESSDATE);
        IF SUCCESSDATE IS NULL THEN
    ERROR_MSG    :='THERE IS NO SUCCESSDATE!';
ELSE  
DTREALDATE = TO_CHAR(SUCCESSDATE,'YYYY-MM-DD HH24:MI'); 
IF ( to_date(DTREALDATE,'yyyy-mm-dd hh24:mi') BETWEEN to_date(DTREALDATE || ' 18:00:00' ,'yyyy-mm-dd hh24:mi:ss') 
    AND to_date(DTREALDATE,'yyyy-mm-dd')+1 + (11/24) ) THEN
        ERROR_MSG    :='THE LAST SUCESSDATE NEW WHICH IS ON ' || TRUNC(DTREALDATE);
    END IF;
END IF;
END IF;
END IF;

  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    ERROR_MSG:= SUCCESSDATE||'NOT FOUND PLEASE CHECK THE SUCCESSDATE';
  WHEN OTHERS THEN
    ERROR_MSG:='NO DATA FOUND PLEASE CHECK ON OTHERS ';
  END;
  HTP.PRN('<td>&nbsp;'||INSTANCE||'</td>');
  HTP.PRN('<td>&nbsp;'||HOSTNAME||'</td>');
  HTP.PRN('<td>&nbsp;'||SUCCESSDATE||'</td>');
  HTP.PRN('<td>&nbsp;'||ERROR_MSG||'</td>');
END;

Open in new window


Now this gives me an error.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35004018
what is the error you are getting with the code given in the update : 35003132  ?

Also can you provide what output you are expecting from that code ? Also good to know the value of succcessdate variable returned by the procedure. You can add a debug message to see the value immediately after the procedure call line.

Thanks
0
 
LVL 4

Author Comment

by:pinkuray
ID: 35004461
The return value from the procedure  in line 38 i.e. :

 EA.SUCCESSDATE(SCHEMA,HOSTNAME,INSTANCE,SUCCESSDATE);

is 2011-02-25 02:24  and after which I have added the lines for checking if it is in between time.

The error is converting a varchar2 to date ....

where as after this it is getting SUCESSDATE  as varchar2(200).
and then I am trying to check the dates and time in between which is conflicting it to get the result.

When it is error it is going to :

WHEN OTHERS THEN
    ERROR_MSG:='NO DATA FOUND PLEASE CHECK ON OTHERS ';

0
 
LVL 4

Author Comment

by:pinkuray
ID: 35004519
this can be easily solved by using regular expression but I know to do it without using this one.

0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 500 total points
ID: 35004549
ok. understood now. You should be at least this much explanative when you create a question otherwise it is just something which no one will have a clue on.

your existing code :
DTREALDATE = TO_CHAR(SUCCESSDATE,'YYYY-MM-DD HH24:MI');
IF ( to_date(DTREALDATE,'yyyy-mm-dd hh24:mi') BETWEEN to_date(DTREALDATE || ' 18:00:00' ,'yyyy-mm-dd hh24:mi:ss')
    AND to_date(DTREALDATE,'yyyy-mm-dd')+1 + (11/24) ) THEN
        ERROR_MSG    :='THE LAST SUCESSDATE NEW WHICH IS ON ' || TRUNC(DTREALDATE);
    END IF;
END IF;
END IF;
END IF;

modify it as below :
DTREALDATE = TO_DATE(SUCCESSDATE,'YYYY-MM-DD HH24:MI');  
-- you don't need to use to_char because successdate is already a varchar variable.
IF ( DTREALDATE BETWEEN to_date(to_char(DTREALDATE,'YYYY-MM-DD') || ' 18:00' ,'yyyy-mm-dd hh24:mi') AND trunc(DTREALDATE+1)+(11/24) ) THEN
        ERROR_MSG    :='THE LAST SUCESSDATE NEW WHICH IS ON ' || TRUNC(DTREALDATE);
    END IF;
END IF;
END IF;
END IF;

Test it out.
0
 
LVL 4

Author Comment

by:pinkuray
ID: 35006474
I will test this and update you accordingly.
0
 
LVL 3

Expert Comment

by:LFLFM
ID: 35006750
you should not have put to_date() on the DTREALDATE... when I told you to use this variable, it was so you didn't have to do exactly that :-)
Try the code nav_kum_v posted, its just like my original code (except for the final time).

onde thing, your SUCCESSDATE doesn't store seconds?
If it does, you really want:
dtRealDate = to_date(SUCCESSDATE,'YYYY-MM-DD HH24:MI:SS');
0
 
LVL 4

Author Closing Comment

by:pinkuray
ID: 35013743
Thanks this solve my problem
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35013748
Good & Thanks,
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…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

757 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

18 Experts available now in Live!

Get 1:1 Help Now