Solved

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

Posted on 2008-06-16
6
350 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
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.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to read text with RegEx... 2 26
Finding the IIS version 5 22
oracle collections 2 22
PL/SQl Expanding the WHERE statement in query 3 20
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…
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.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup

828 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