PLSQL functions help

Question

I have this SELECT below which i have to run it in a LOOP for every activity_id(record) that i get from the join.

Also,this SELECT has to be run in multiple places in a procedure to get details and for that records-->I run the assign_ course or assign_test as below.

Can anyone please help me write a function which would have this select .

inputs are strtplanid,strPersonid, p_ objective_id

FOR activity_id IN
               (SELECT objact.activity_id, objact.activity_type,objact.IS_REQUIRED
                  FROM test_training_plan tp,
                       test_tp_objective tp_obj,
                       test_train_obj_activity objact
                 WHERE tp.tplan_id = tp_obj.tplan_id
                   AND tp.tplan_id = strtplanid
                   AND tp_obj.t_objective_id = p_objective_id
                   AND tp_obj.t_objective_id = objact.t_objective_id
                   AND objact.activity_id NOT IN (
                          SELECT tplplr.activity_id
                            FROM test_learning_record lr,
                                 test_learning_record lr1,
                                 test_tp_learning_activity tplplr
                           WHERE lr.lr_catalog_history_id = tplplr.activity_id
                             AND lr.learning_record_id =
                                                      tplplr.activity_lp_lr_id
                             AND tplplr.tp_lp_lr_id = lr1.learning_record_id
                             AND lr1.lr_catalog_history_id =
                                               strtplanid
                             AND lr.lr_person_id = strPersonid
                             AND lr1.lr_person_id = strPersonid
                             AND lr.status IN
                                           ('PASSED', 'WAIVED', 'TESTED_OUT'))
                   AND objact.activity_id NOT IN (
                          SELECT event_id
                            FROM test_train_obj_activity toa,
                                 test_event_sessions sessions,
                                 test_learning_record lr1,
                                 test_tp_learning_activity tplearnact,
                                 test_learning_record tplr
                           WHERE toa.activity_id = sessions.event_id
                             AND sessions.event_session_id =
                                                     lr1.lr_catalog_history_id
                             AND lr1.learning_record_id =
                                                  tplearnact.activity_lp_lr_id
                             AND tplearnact.tp_lp_lr_id =
                                                       tplr.learning_record_id
                             AND tplr.lr_catalog_history_id =
                                               strtplanid
                             --AND toa.is_required = 1
                             AND toa.t_objective_id = obj.t_objective_id
                             AND tplr.lr_person_id = strPersonid
                             AND lr1.lr_person_id = strPersonid
                             AND lr1.status IN
                                           ('PASSED', 'WAIVED', 'TESTED_OUT')))

            LOOP

               IF (activity.activity_type = 'Course')
               THEN

                   ASSIGN_COURSETP(strPersonid,activity.activity_id,strPersonid,activity.activity_type,
                  activity.IS_REQUIRED,strtplanid,v_straccreditingorg); 

                ELSif (activity.activity_type ='Test')
                THEN
                  ASSIGN_TESTTP(strPersonid,activity.activity_id,strPersonid,activity.activity_type,
                  activity.IS_REQUIRED,strtplanid,v_straccreditingorg); 

                END IF;

Can someone please help me with this?

rohit
– rohit

2009-05-30 16:10:36 +00:00
Commented May 30, 2009 at 16:10 — rohit
– rohit, Commented May 30, 2009 at 16:10

Jeff Meatball Yang · Accepted Answer · 2009-05-31 08:14:01Z

2

Does this need to return anything?

Why can't you wrap your code in the boilerplate function code?

CREATE OR REPLACE Function ACTIVITY_ASSIGN_TESTTP
(
  strtplanid IN number
, strPersonid IN number
, p_objective_id IN number
)
RETURN number
IS
  retVal number;
BEGIN

  -- YOUR CODE

  RETURN retVal
END ACTIVITY_ASSIGN_TESTTP;

answered May 31, 2009 at 8:14

Jeff Meatball Yang

39.2k27 gold badges94 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PLSQL functions help

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related