Solved

test a stored proc in oracle sql developer.

Posted on 2008-06-20
4
1,186 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

724 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