Looping in oracle sql

Last Reply 10 days ago By dharmendr

Posted 17 days ago

Hi I am trying to get sessionids from select statement and looping by passing the sessionids in where condition but it is is faling to fetch the data dont know whether the value is passing inside the loop or not 

Below is my  procedure 

The link which i provided below is the screenshot of the  sessionids which i am tring to get in intial stage i mean before the loop

 

https://ibb.co/XXHP83K

Where i am going wrong?

Could you please help me in that?

  PROCEDURE Assigned_Tutor_details(
        iTutorusername         IN VARCHAR,
        iStartDate             IN VARCHAR,
        iendDate               IN VARCHAR,
        itime                  IN VARCHAR,
        ocursor                OUT t_cursor
     
     )
     
     IS
    
     BEGIN
      
 FOR x IN (select olt.course_id As course_id,olt.session_id AS session_id  from fev_oltsession olt
  INNER JOIN fev_session_workpool_routing wrkpl on olt.session_id = wrkpl.session_id)

  LOOP
  
  IF x.course_id IS NOT NULL then

     IF(itime is not null) then
     OPEN ocursor FOR
     
     SELECT wrkpl.session_id,wrkpl.student_id,wrkpl.student_name,wrkpl.preferred_tutor_id,(tut.first_name ||' '||tut.last_name)As Assigned_TUTOR_NAME,lo.user_name,wrkpl.grade_name,sub.name AS subject_name,wrkpl.school_name,dst.district_name,crs.name AS course_name,(scl.title)As School_level,bckt.bucket_id,bckt.title,wrkpl.scheduled_starttime FROM FEV_SESSION_WORKPOOL_ROUTING wrkpl
     
     LEFT OUTER JOIN fev_oltsession olt on wrkpl.session_id = olt.session_id
     
     RIGHT OUTER JOIN FEV_TUTOR tut ON wrkpl.preferred_tutor_id = tut.tutor_id
     
     LEFT OUTER JOIN FEV_LOGIN lo ON tut.login_id = lo.login_id
     
     LEFT OUTER JOIN fev_district dst ON wrkpl.district_id = dst.district_id
     
     LEFT OUTER JOIN fev_course_bucket crb ON olt.course_id  = crb.course_Id 
     
     LEFT OUTER JOIN fev_course crs ON crb.course_id = crs.course_id
     
     LEFT OUTER JOIN fev_tutorbucket bckt ON crb.bucket_id = bckt.bucket_id AND olt.subject_id = bckt.subject_id
     
     LEFT OUTER JOIN fev_subject sub ON bckt.subject_id = sub.subject_id
     
     LEFT OUTER JOIN fev_school_level scl ON bckt.SCHOOL_LEVEL_ID = scl.SCHOOL_LEVEL_ID
     
  WHERE  
      nvl(lower(lo.USER_NAME),0) LIKE (nvl(lower(iTutorusername)|| '%','%'))
      
  
    AND
    wrkpl.scheduled_starttime = to_date(iStartDate,
                       'mm/dd/yyyy hh:mi:ss am'
                  )
                  AND
                  olt.session_id = x.session_id;
  Else
     OPEN ocursor FOR
     
  SELECT wrkpl.session_id,wrkpl.student_id,wrkpl.student_name,wrkpl.preferred_tutor_id,(tut.first_name ||' '||tut.last_name)As Assigned_TUTOR_NAME,lo.user_name,wrkpl.grade_name,sub.name AS subject_name,wrkpl.school_name,dst.district_name,crs.name AS course_name,(scl.title)As School_level,bckt.bucket_id,bckt.title,wrkpl.scheduled_starttime FROM FEV_SESSION_WORKPOOL_ROUTING wrkpl
     
     LEFT OUTER JOIN fev_oltsession olt on wrkpl.session_id = olt.session_id
     
     RIGHT OUTER JOIN FEV_TUTOR tut ON wrkpl.preferred_tutor_id = tut.tutor_id
     
     LEFT OUTER JOIN FEV_LOGIN lo ON tut.login_id = lo.login_id
     
     LEFT OUTER JOIN fev_district dst ON wrkpl.district_id = dst.district_id
     
     LEFT OUTER JOIN fev_course_bucket crb ON olt.course_id  = crb.course_Id 
     
     LEFT OUTER JOIN fev_course crs ON crb.course_id = crs.course_id
     
     LEFT OUTER JOIN fev_tutorbucket bckt ON crb.bucket_id = bckt.bucket_id AND olt.subject_id = bckt.subject_id
     
     LEFT OUTER JOIN fev_subject sub ON bckt.subject_id = sub.subject_id
     
     LEFT OUTER JOIN fev_school_level scl ON bckt.SCHOOL_LEVEL_ID = scl.SCHOOL_LEVEL_ID
  
     
  WHERE  
                 nvl(lower(lo.USER_NAME),0) LIKE (nvl(lower(iTutorusername)|| '%','%'))
                     AND
          wrkpl.scheduled_starttime >= TO_DATE(
                       istartdate,
                        'mm/dd/yyyy hh:mi:ss am'
                    )
                AND
                    wrkpl.scheduled_starttime < TO_DATE(
                        ienddate,
                        'mm/dd/yyyy hh:mi:ss am'
                    )
                    
                    AND 
                         olt.session_id = x.session_id;
                  
     
   END IF;
   END IF;
   
   IF x.course_id IS NULL then
   
       IF(itime is not null) then
     OPEN ocursor FOR
     
     SELECT wrkpl.session_id,wrkpl.student_id,wrkpl.student_name,wrkpl.preferred_tutor_id,(tut.first_name ||' '||tut.last_name)As Assigned_TUTOR_NAME,lo.user_name,wrkpl.grade_name,sub.name AS subject_name,wrkpl.school_name,dst.district_name,wrkpl.course_name,(scl.title)As School_level,bckt.bucket_id,bckt.title,wrkpl.scheduled_starttime FROM FEV_SESSION_WORKPOOL_ROUTING wrkpl
     
     LEFT OUTER JOIN fev_oltsession olt on wrkpl.session_id = olt.session_id
     
     RIGHT OUTER JOIN FEV_TUTOR tut ON wrkpl.preferred_tutor_id = tut.tutor_id
     
     LEFT OUTER JOIN FEV_LOGIN lo ON tut.login_id = lo.login_id
     
     LEFT OUTER JOIN fev_district dst ON wrkpl.district_id = dst.district_id
     
     LEFT OUTER JOIN fev_grade grd ON olt.GRADE_ID = grd.GRADE_ID
     
     LEFT OUTER JOIN fev_tutorbucket bckt ON grd.school_level_id = bckt.school_level_id AND olt.subject_id = bckt.subject_id
     
     LEFT OUTER JOIN fev_subject sub ON bckt.subject_id = sub.subject_id
     
     LEFT OUTER JOIN fev_school_level scl ON bckt.SCHOOL_LEVEL_ID = scl.SCHOOL_LEVEL_ID
     
  WHERE  
      nvl(lower(lo.USER_NAME),0) LIKE (nvl(lower(iTutorusername)|| '%','%'))
      
  
    AND
    wrkpl.scheduled_starttime = to_date(iStartDate,
                       'mm/dd/yyyy hh:mi:ss am'
                  )
                  AND
                       olt.session_id = x.session_id;
  Else
     OPEN ocursor FOR
     
     SELECT wrkpl.session_id,wrkpl.student_id,wrkpl.student_name,wrkpl.preferred_tutor_id,(tut.first_name ||' '||tut.last_name)As Assigned_TUTOR_NAME,lo.user_name,wrkpl.grade_name,sub.name AS subject_name,wrkpl.school_name,dst.district_name,wrkpl.course_name,(scl.title)As School_level,bckt.bucket_id,bckt.title,wrkpl.scheduled_starttime FROM FEV_SESSION_WORKPOOL_ROUTING wrkpl
     
     LEFT OUTER JOIN fev_oltsession olt on wrkpl.session_id = olt.session_id
     
     RIGHT OUTER JOIN FEV_TUTOR tut ON wrkpl.preferred_tutor_id = tut.tutor_id
     
     LEFT OUTER JOIN FEV_LOGIN lo ON tut.login_id = lo.login_id
     
     LEFT OUTER JOIN fev_district dst ON wrkpl.district_id = dst.district_id
     
    
     LEFT OUTER JOIN fev_grade grd ON olt.GRADE_ID = grd.GRADE_ID
     
     LEFT OUTER JOIN fev_tutorbucket bckt ON grd.school_level_id = bckt.school_level_id AND olt.subject_id = bckt.subject_id
     
     LEFT OUTER JOIN fev_subject sub ON bckt.subject_id = sub.subject_id
     
     LEFT OUTER JOIN fev_school_level scl ON bckt.SCHOOL_LEVEL_ID = scl.SCHOOL_LEVEL_ID
  WHERE  
      
                     nvl(lower(lo.USER_NAME),0) LIKE (nvl(lower(iTutorusername)|| '%','%'))
                     AND
          wrkpl.scheduled_starttime >= TO_DATE(
                       istartdate,
                        'mm/dd/yyyy hh:mi:ss am'
                    )
                AND
                    wrkpl.scheduled_starttime < TO_DATE(
                        ienddate,
                        'mm/dd/yyyy hh:mi:ss am'
                    )
                    
                    AND
                         olt.session_id = x.session_id;
                  
                 
                    END IF;
  END IF;
    END LOOP;
  
     END;

 

You are viewing reply posted by: zeeshanpas 13 days ago.
Posted 13 days ago Modified on 13 days ago

Basically I am trying to loop in procedure based on checking course null condition

Before I was using only one cursor out parameter

There  I can able to loop I checked data in DBMS.output.line but not able bind data to cursor 

But after refering your example I considered two cursors you can go through my query which I posted in last query

After putting two cursors 

Not able to loop itself 

User requested cancelled it's showing 

Don't know what execution timeout error 

How to check

 

Please try to check once my procedure 

What I am looping and binding to cursor is correct or not?