We help IT Professionals succeed at work.
Get Started

Oracle Stored Procedure Question

jmpatton
jmpatton asked
on
393 Views
Last Modified: 2012-08-13
When trying to compile this oracle stored procedure I keep getting an error stating "Error(69,1): PLS-00103: Encountered the symbol "END" "  Can someone help me out as to why?  I thought I was supposed to put an end there because I had a corresponding BEGIN at the top.
create or replace
PROCEDURE GET_CONFLICT_VOLUME(pJunction       IN VARCHAR2,
                              pPipeline       IN VARCHAR2,
                              pBatchCode      IN VARCHAR2,
                              pStartDateTime  IN VARCHAR2,
                              pFinishDateTime IN VARCHAR2,
                              pConflictVolume OUT VARCHAR2) IS
                                
                              pStartToCheck DATE;
                              pEndToCheck DATE;
                              pVolume NUMBER;
                              
  CURSOR C1 IS
  SELECT START_DATE_TIME, FINISH_DATE_TIME
    FROM sch_fie_events
   WHERE rate > 0
     AND shipper || '-' || product || '-' || cycle || phase = pBatchCode
     AND junction = pJunction
     AND pipeline = pPipeline
     AND STATION = 'S00'
     AND (to_date(pStartDateTime, 'mm/dd/yyyy hh:mi:ss am') between START_DATE_TIME and FINISH_DATE_TIME
     OR to_date(pFinishDateTime, 'mm/dd/yyyy hh:mi:ss am') between START_DATE_TIME and FINISH_DATE_TIME);

  BEGIN     

     pVolume := 0;

     FOR rec IN C1.count
     
       LOOP
        
        IF START_DATE_TIME < to_date(pStartDateTime, 'mm/dd/yyyy hh:mi:ss am') THEN
          pStartToCheck := to_date(pStartDateTime, 'mm/dd/yyyy hh:mi:ss am');
        ELSE
          pStartToCheck := START_DATE_TIME;
        END IF;
        
        IF END_DATE_TIME > to_date(pEndDateTime, 'mm/dd/yyyy hh:mi:ss am') THEN
          pEndToCheck := to_date(pEndDateTime, 'mm/dd/yyyy hh:mi:ss am');
        ELSE
          pEndToCheck := END_DATE_TIME;
        END IF;
        
        BEGIN
        
         SELECT rate * ((to_date(pEndToCheck, 'mm/dd/yyyy hh:mi:ss am') - to_date(pStartToCheck, 'mm/dd/yyyy hh:mi:ss am')) * 24)
          INTO pVolume
          FROM sch_fie_events
          WHERE rate > 0
           AND shipper || '-' || product || '-' || cycle || phase = pBatchCode
           AND junction = pJunction
           AND pipeline = pPipeline
           AND STATION = 'S00'
           AND (to_date(pStartDateTime, 'mm/dd/yyyy hh:mi:ss am') between START_DATE_TIME and FINISH_DATE_TIME
           OR to_date(pFinishDateTime, 'mm/dd/yyyy hh:mi:ss am') between START_DATE_TIME and FINISH_DATE_TIME);
        
        END; 
        
        pVolume := pVolume + pVolume;       
       
      END LOOP;
      
      pConflictVolume := to_char(pVolume);
       
END; <----- Error Here

  

END GET_CONFLICT_VOLUME;

Open in new window

Comment
Watch Question
Commented:
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE