Solved

test a stored proc in oracle sql developer.

Posted on 2008-06-20
4
1,182 Views
Last Modified: 2013-12-18
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
0
Comment
Question by:mathieu_cupryk
  • 3
4 Comments
 
LVL 14

Expert Comment

by:ajexpert
ID: 21836073
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
 

Author Comment

by:mathieu_cupryk
ID: 21837811
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
 
LVL 14

Expert Comment

by:ajexpert
ID: 21838369
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
 
LVL 14

Accepted Solution

by:
ajexpert earned 500 total points
ID: 21838549
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

863 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

27 Experts available now in Live!

Get 1:1 Help Now