Solved

Need to use AddOut Parameter. Help on setting up the variables.

Posted on 2008-06-16
6
348 Views
Last Modified: 2013-12-07
Need help below on how to get the gradecodedetailId and the status asw well.

[WebMethod(Description = "Get Grade Code Detail based on the grade name")]
        public void GetGradeCodeDetailID(string GradeName, ref int GradeCodeDetailID, ref string Status)
        {
            string procedureName = "init_price.PRICE_LIST_REPORTING.get_grade_code_dtl_id";
            try
            {
                Database db = DatabaseFactory.CreateDatabase("InitialPrices.Properties.Settings.ConnectionString");
                DbCommand dbCommand = db.GetStoredProcCommand(procedureName);

                db.AddInParameter(dbCommand, "p_grade_name", DbType.String, GradeNameGradeName);
                db.AddOutParameter(dbCommand, "p_grade_code_dtl_id", DbType.Int32, 4);
                db.AddOutParameter(dbCommand, "p_status", DbType.String, 7);
                db.ExecuteNonQuery(dbCommand);
               

                GradeCodeDetailID = Convert.ToInt32( dbCommand.Parameters[1].Value, CultureInfo.InvariantCulture);
                Status = dbCommand.Parameters[2].Value;
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return;
        }
PROCEDURE get_grade_code_dtl_id (p_grade_name            IN  grade_code_dtl.grade_name%TYPE,

                                 p_grade_code_dtl_id     OUT grade_code_dtl.grade_code_dtl_id%TYPE,

                                 p_status                OUT varchar2)

IS
 

p_temp_id grade_code_dtl.grade_code_dtl_id%TYPE;
 

BEGIN

     p_status := 'Success';

     SELECT GRADE_NAME

     INTO p_temp_id

     FROM INIT_PRICE.GRADE_CODE_DTL A

     WHERE A.GRADE_NAME = p_grade_name;
 

     EXCEPTION

        WHEN no_data_found THEN p_grade_code_dtl_id := -1;

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

END get_grade_code_dtl_id;

Open in new window

0
Comment
Question by:mathieu_cupryk
  • 4
  • 2
6 Comments
 
LVL 14

Expert Comment

by:ajexpert
ID: 21798027
Some thougs
You are not assigning any value to p_status incase of NO_DATA_FOUND EXCEPTION and p_grade_code_dtl_id in case of OTHERS EXCEPTION

In short consider assigning values to both OUT variables in case of each exception

Hope this helps
0
 

Author Comment

by:mathieu_cupryk
ID: 21798163
I am not sure what u mean I need to try see if this can work,
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 21798210
This is what I mean to convey
PROCEDURE get_grade_code_dtl_id (p_grade_name            IN  grade_code_dtl.grade_name%TYPE,

                                 p_grade_code_dtl_id     OUT grade_code_dtl.grade_code_dtl_id%TYPE,

                                 p_status                OUT varchar2)

IS

 

p_temp_id grade_code_dtl.grade_code_dtl_id%TYPE;

 

BEGIN

     p_status := 'Success';

     SELECT GRADE_NAME

     INTO p_temp_id

     FROM INIT_PRICE.GRADE_CODE_DTL A

     WHERE A.GRADE_NAME = p_grade_name;

 

     EXCEPTION

        WHEN no_data_found THEN 

        p_grade_code_dtl_id := -1;

        p_status := 'Failure : No Data Found ';

        WHEN others THEN 

        p_status := 'Failure: ' || SQLERRM;

        p_grade_code_dtl_id := -1;

END get_grade_code_dtl_id;

Open in new window

0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 14

Expert Comment

by:ajexpert
ID: 21798247
One more point
In case of successful execution you are assigning
p_status := 'Success';
but p_grade_code_dtl_id     is null.


is p_temp_id = p_grade_code_dtl_id ?    
0
 
LVL 14

Accepted Solution

by:
ajexpert earned 500 total points
ID: 21798263
I believe p_temp_id = p_grade_code_dtl_id looking at the type declaration

See the modified SP here
PROCEDURE get_grade_code_dtl_id (p_grade_name            IN  grade_code_dtl.grade_name%TYPE,

                                 p_grade_code_dtl_id     OUT grade_code_dtl.grade_code_dtl_id%TYPE,

                                 p_status                OUT varchar2)

IS

 

p_temp_id grade_code_dtl.grade_code_dtl_id%TYPE;

 

BEGIN

     p_status := 'Success';

     SELECT GRADE_NAME

     INTO p_temp_id

     FROM INIT_PRICE.GRADE_CODE_DTL A

     WHERE A.GRADE_NAME = p_grade_name;

 

     p_grade_code_dtl_id     := p_temp_id;

     EXCEPTION

        WHEN no_data_found THEN 

        p_grade_code_dtl_id := -1;

        p_status := 'Failure : No Data Found ';

        WHEN others THEN 

        p_status := 'Failure: ' || SQLERRM;

        p_grade_code_dtl_id := -1;

END get_grade_code_dtl_id;

Open in new window

0
 

Author Comment

by:mathieu_cupryk
ID: 21798523
i need help on the parameter passing though.
I really appreciate your help.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

914 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

20 Experts available now in Live!

Get 1:1 Help Now