Solved

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

Posted on 2008-06-16
6
349 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

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…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

772 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