test a stored proc in oracle sql developer.

I would like to test the following:
p_old_price_list_id = '200701'
p_new_price_list_id = '200720'

PROCEDURE price_list_deletions (p_old_price_list_id       IN     price_list_dtl.price_list_hdr_id%TYPE,
                                p_new_price_list_id       IN     price_list_dtl.price_list_hdr_id%TYPE,
                                p_status                  OUT NOCOPY varchar2,
                                p_deleted_cursor          IN OUT t_cursor)
                               

IS

BEGIN
  p_status := 'Success';
  OPEN p_deleted_cursor
    FOR
      SELECT A.grade_code_dtl_id
             FROM price_list_dtl A
             WHERE A.price_list_hdr_id = p_old_price_list_id
      MINUS
        SELECT B.grade_code_dtl_id
               FROM price_list_dtl B
               WHERE B.price_list_hdr_id = p_new_price_list_id;

  EXCEPTION
    WHEN others THEN
         p_status := 'Failure: ' || SQLERRM;

END price_list_deletions;


See the output
mathieu_cuprykAsked:
Who is Participating?
 
ajexpertConnect With a Mentor Commented:
Here is the check you can do in database

Hope it helps
PROCEDURE price_list_deletions (p_old_price_list_id       IN     price_list_dtl.price_list_hdr_id%TYPE,
                                p_new_price_list_id       IN     price_list_dtl.price_list_hdr_id%TYPE,
                                p_status                  OUT NOCOPY varchar2,
                                p_deleted_cursor          IN OUT t_cursor)
                                
 
IS
lv_count                        NUMBER :=0;
 
BEGIN
  p_status := 'Success';
 
  SELECT COUNT(*) INTO lv_count
  FROM
  (
        SELECT A.grade_code_dtl_id 
             FROM price_list_dtl A 
             WHERE A.price_list_hdr_id = p_old_price_list_id
      MINUS
        SELECT B.grade_code_dtl_id
               FROM price_list_dtl B
               WHERE B.price_list_hdr_id = p_new_price_list_id;
);
 
  IF lv_count = 0 THEN
    p_status := 'Failure: No records returned';
    RETURN;
  END IF;
 
  OPEN p_deleted_cursor
    FOR
      SELECT A.grade_code_dtl_id 
             FROM price_list_dtl A 
             WHERE A.price_list_hdr_id = p_old_price_list_id
      MINUS
        SELECT B.grade_code_dtl_id
               FROM price_list_dtl B
               WHERE B.price_list_hdr_id = p_new_price_list_id;
 
  EXCEPTION
    WHEN others THEN
         p_status := 'Failure: ' || SQLERRM;
 
END price_list_deletions

Open in new window

0
 
ajexpertCommented:
We cannot test it as we do not have tables and data

What is your requirement.  If you can tell us, probably we can tell you the logic
0
 
mathieu_cuprykAuthor Commented:
ajex, i need to do more error checking because there are no rows being returned and it fails when it does the execute dataset.
0
 
ajexpertCommented:
I believe you can do error checking in the front end.  You have the property of dataset.rowcount
If this is 0 you can take appropriate action
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.