Solved

Return a null cursor.

Posted on 2008-06-23
22
785 Views
Last Modified: 2013-12-18
I have the following :

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 grade_code_dtl_id
             FROM price_list_dtl
             WHERE price_list_hdr_id = p_old_price_list_id
      MINUS
        SELECT grade_code_dtl_id
               FROM price_list_dtl
               WHERE price_list_hdr_id = p_new_price_list_id;

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

END price_list_deletions;


I need to also return a null cursor on the stack.
if possible.
0
Comment
Question by:mathieu_cupryk
  • 10
  • 6
  • 4
  • +1
22 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 21846432
Is this what you mean?


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,
                                p_null_cursor  IN OUT t_cursor)
                               

IS

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

    p_null_cursor := null;


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

END price_list_deletions;

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 21846483
or do you mean a cursor that returns NULL?


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,
                                p_null_cursor  IN OUT t_cursor)
                               

IS

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

 
       -- using "max" on a false where clause ensures return NULL, and the data type of NULL
       -- will be the same as the cursor type you are using.  If t_cursor is strongly typed this
       -- is necessary.  If it's weakly type  then select NULL from dual;  would be sufficient.
       OPEN p_null_cursor
    FOR
           select max(grade_code_dtl_id) from price_list_dtl where 1=0;



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

END price_list_deletions;

0
 
LVL 47

Expert Comment

by:schwertner
ID: 21847179
I think when the cursor is empty you can avoid to open it using the other OUT parameter p_status.


IF p_status <> 'No Data' THEN
   -- open the cursor and use the data
END IF;

Does returning the cursor empty causes you errors?
0
 
LVL 17

Expert Comment

by:ram_0218
ID: 21847264
hello mathieu. first of all your code should not have any exception block! the exception block doesnt do what its intended.

  OPEN p_deleted_cursor
    FOR
      SELECT grade_code_dtl_id
             FROM price_list_dtl
             WHERE price_list_hdr_id = p_old_price_list_id
      MINUS
        SELECT grade_code_dtl_id
               FROM price_list_dtl
               WHERE price_list_hdr_id = p_new_price_list_id;


-- This line never going to throw NO_DATA_FOUND error. When there are no matches, its by default going to return you an empty cursor which you can check in your calling program as !=null && .count!=0

then based on that you can decide the status. if the status you want to returned from oracle, you may have to add additional checks like checking count and all. like :

select count(*) into V_count from (select * from source
minus
selec * from target)

if v_count ==0 then
 status = 'no_data_found';
end if;

something on this lines

Not sure about the other part of returning null cursor.. your cursor by default returns null records when no match.. if you want to return additional null cursor add output parameter and return
open new_out_param for null;

Thank you.
0
 

Author Comment

by:mathieu_cupryk
ID: 21847267
My first proc causes problems I have not tried this way.
0
 

Author Comment

by:mathieu_cupryk
ID: 21847346
does anyone have the correct solution for this?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 21847377
mathieu_cupryk, what is it you're trying to do.

When you say you want to "also return a null cursor "  what is it you're looking for?

And what is the problem with your existing code?  As noted above your exception block isn't going to capture no_data_found errors because you aren't fetching from it, at least not in the snippet you posted.

As you can probably tell from the random answers above, we're allo sort of just guessing as to what you want.  :)
0
 

Author Comment

by:mathieu_cupryk
ID: 21847466
System.Data.DataSet DSNew = new DataSet();
           try
           {
               Database db = DatabaseFactory.CreateDatabase("InitialPrices.Properties.Settings.ConnectionString");

               DbCommand dbCommand = db.GetStoredProcCommand(procedureName);
               db.AddInParameter(dbCommand, "p_old_price_list_id", DbType.String, OldPriceList);
               db.AddInParameter(dbCommand, "p_new_price_list_id", DbType.String, NewPriceList);
               db.AddOutParameter(dbCommand, "p_status", DbType.String, 255);


               DSNew = db.ExecuteDataSet(dbCommand); ===> it crashes here.
               Status = dbCommand.Parameters[2].Value.ToString();


               // Reset the variables
               return (DSNew);
           }
0
 
LVL 17

Accepted Solution

by:
ram_0218 earned 500 total points
ID: 21847585
I see that you'r trying to pass a "list" of values which you'r comparing in DB with just '=" for multiple values you need to be using 'IN'

though it may be the problem, your exception block OTHERS should catch the issue and would return empty cursor already. so i would guess the caller can not even get into the functional block inside. so i think your calling part is the issue here.

where is the output parameter registered for the cursor you'r looking?
db.AddOutParameter(dbCommand, "p_deleted_cursor", DbType.Cursor, 255);

finally "crashes" means what? isnt the "exception" block capturing it? is it c#? Check the documentation for DbType.Cursor not sure what it is. find the appropriate datatype and put it there!
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 21847619
where is your cursor parameter?  Or is that what you're trying ask?

How to call your procedure when you don't want the cursor?  Is that  what you meant by NULL cursor?

You can't.  

Your procedure has an OUT parameter, so your code MUST receive that parameter.

0
 
LVL 47

Expert Comment

by:schwertner
ID: 21847639
RAM said a very important thing!


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 grade_code_dtl_id
             FROM price_list_dtl
             WHERE price_list_hdr_id = p_old_price_list_id
      MINUS
        SELECT grade_code_dtl_id
               FROM price_list_dtl
               WHERE price_list_hdr_id = p_new_price_list_id;

       IF  p_deleted_cursor%ROWCOUNT = 0 THEN
         p_status := 'No Data';
       END IF;


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

END price_list_deletions;


If it cause errors next step will be somehow to put a row into cursor.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:mathieu_cupryk
ID: 21847688
there is no type db.cursor?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 21847759
use OracleType.Cursor  for the type of the ref cursor out parameter
0
 
LVL 17

Expert Comment

by:ram_0218
ID: 21847775
ok.. since you seem so tired :-) i did google for you..
http://forums.asp.net/t/883771.aspx

fyi. oracletype.cursor also have issues looks like :-)
0
 

Author Comment

by:mathieu_cupryk
ID: 21847924

Ram it is still failing on the execute

public DataSet ComparePriceListDelete(string OldPriceList, string NewPriceList, ref string Status)
        {

          string procedureName = "init_price.PRICE_LIST_REPORTING.price_list_deletions"; //schema.stored procedure
           
           System.Data.DataSet DSNew = new DataSet();
           try
           {
               Database db = DatabaseFactory.CreateDatabase("InitialPrices.Properties.Settings.ConnectionString");

               DbCommand dbCommand = db.GetStoredProcCommand(procedureName);
               db.AddInParameter(dbCommand, "p_old_price_list_id", DbType.String, OldPriceList);
               db.AddInParameter(dbCommand, "p_new_price_list_id", DbType.String, NewPriceList);
               db.AddOutParameter(dbCommand, "p_status", DbType.String, 255);
               db.AddOutParameter(dbCommand, "p_deleted_cursor", DbType.Object, 2000);
               

                 DSNew = db.ExecuteDataSet(dbCommand);
                 Status = dbCommand.Parameters[2].Value.ToString();
              // db.ExecuteNonQuery(dbCommand);
              // string results = strng.for (int ( = 0; ( < length; (++)
                  {
                  
                  }
               // Reset the variables
               return (DSNew);
           }
           catch (Exception ex)
           {
               throw (ex);
           }
           finally
           {
        //      ((IDisposable)db).Dispose();
           }

        }


I tested the stored proc in sql editor it returns nothing.

0
 
LVL 17

Expert Comment

by:ram_0218
ID: 21848032
ok then try with OracleType.Cursor  and if that also doesnt work, you may want to post a link in C# for this question.. this seems not db related.
0
 

Author Comment

by:mathieu_cupryk
ID: 21848080
RA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'PRICE_LIST_DELETIONS'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 21848405
     db.AddOutParameter(dbCommand, "p_deleted_cursor", DbType.Object, 2000);

change that to


      db.AddOutParameter(dbCommand, "p_deleted_cursor", OracleType.Cursor, 2000);
0
 

Author Comment

by:mathieu_cupryk
ID: 21848786
sd

it  does not see oracletype?
0
 

Author Closing Comment

by:mathieu_cupryk
ID: 31469750
Excellent job.
0
 

Author Comment

by:mathieu_cupryk
ID: 21851092
guys i need your help I created the stored procedures:

public DataSet ComparePriceListDelete(string OldPriceList, string NewPriceList, ref string Status)
        {

           string procedureName = "init_price.PRICE_LIST_REPORTING.price_list_deletions"; //schema.stored procedure
           
           System.Data.DataSet DSNew = new DataSet();
         
           Database db = DatabaseFactory.CreateDatabase("InitialPrices.Properties.Settings.ConnectionString");

           DbCommand dbCommand = db.GetStoredProcCommand(procedureName);
           db.AddInParameter(dbCommand, "p_old_price_list_id", DbType.String, OldPriceList);
           db.AddInParameter(dbCommand, "p_new_price_list_id", DbType.String, NewPriceList);
           db.AddOutParameter(dbCommand, "p_status", DbType.String, 255);
           Status = dbCommand.Parameters[2].Value.ToString();
           db.ExecuteNonQuery(dbCommand);


}

           
     It is failing.

           
         

        }
0
 

Author Comment

by:mathieu_cupryk
ID: 21851169
here is the stored proc:

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

rec_count number := 0;

BEGIN
  p_status := 'Success';
  SELECT count(*) into rec_count
        FROM (
        SELECT grade_code_dtl_id
          FROM price_list_dtl
          WHERE price_list_hdr_id = p_old_price_list_id
        MINUS
          SELECT grade_code_dtl_id
            FROM price_list_dtl
            WHERE price_list_hdr_id = p_new_price_list_id);
   If rec_count = 0 then
     p_status := 'No Data';
     OPEN p_deleted_cursor
       FOR
          SELECT count(*)
                 FROM price_list_dtl
                 WHERE price_list_hdr_id = p_old_price_list_id
          MINUS
            SELECT count(*)
                   FROM price_list_dtl
                   WHERE price_list_hdr_id = p_new_price_list_id;
   else
       OPEN p_deleted_cursor
         FOR
          SELECT grade_code_dtl_id
                 FROM price_list_dtl
                 WHERE price_list_hdr_id = p_old_price_list_id
          MINUS
            SELECT grade_code_dtl_id
                   FROM price_list_dtl
                   WHERE price_list_hdr_id = p_new_price_list_id;
   end if;

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

END price_list_deletions;
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle SQL Query Syntax 6 85
Oracle Finace 3 47
Oracle PL/SQL syntax 4 52
JDeveloper 12c for 32 bit 4 35
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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…

760 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

17 Experts available now in Live!

Get 1:1 Help Now