Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 831
  • Last Modified:

Return a null cursor.

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
mathieu_cupryk
Asked:
mathieu_cupryk
  • 10
  • 6
  • 4
  • +1
1 Solution
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
schwertnerCommented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
ram_0218Commented:
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
 
mathieu_cuprykAuthor Commented:
My first proc causes problems I have not tried this way.
0
 
mathieu_cuprykAuthor Commented:
does anyone have the correct solution for this?
0
 
sdstuberCommented:
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
 
mathieu_cuprykAuthor Commented:
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
 
ram_0218Commented:
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
 
sdstuberCommented:
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
 
schwertnerCommented:
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
 
mathieu_cuprykAuthor Commented:
there is no type db.cursor?
0
 
sdstuberCommented:
use OracleType.Cursor  for the type of the ref cursor out parameter
0
 
ram_0218Commented:
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
 
mathieu_cuprykAuthor Commented:

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
 
ram_0218Commented:
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
 
mathieu_cuprykAuthor Commented:
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
 
sdstuberCommented:
     db.AddOutParameter(dbCommand, "p_deleted_cursor", DbType.Object, 2000);

change that to


      db.AddOutParameter(dbCommand, "p_deleted_cursor", OracleType.Cursor, 2000);
0
 
mathieu_cuprykAuthor Commented:
sd

it  does not see oracletype?
0
 
mathieu_cuprykAuthor Commented:
Excellent job.
0
 
mathieu_cuprykAuthor Commented:
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
 
mathieu_cuprykAuthor Commented:
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
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.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 10
  • 6
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now