ReturnValue in a Procedure...

Hello there... i'm working with Oracle for the 1st time (i'm used to SQL
Server)! I've researched but found no answer: is there anyway to retrieve a
value from a Oracle Procedure? 

I know this is possible (and easy) to execute in SQL. I'm devolping a Windows
App and the returnValue is kind of important to manage bugs and possible errors!


Thanks,
SuperJB

U can use Out or Inout parameter to get the value from a stored procedure.

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped
you.

I've tried using Output param to get a result, but since i created this param in
my Procedure, i can't even execute the code in VisualStudio:

/ / / / / / / / / / / / / / / / /  O R A C L E / / / / / / / / / / / / / / / / /



    CREATE OR REPLACE PROCEDURE PersonAdd
    (
     pName IN person.FULLNAME%TYPE,
     pDate IN person.BIRTHDATE%TYPE,
     pOutput OUT boolean
    )
    IS      
    BEGIN
    
        INSERT INTO Person VALUES(seq_IncBy1.nextval, pName, pDate);
        pOutput := true;
        
        EXCEPTION
         WHEN OTHERS THEN
           pOutput := false;
           RAISE;
        
    END PersonAdd;

/ / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / 

 

/ / / / / / / / / / / / / / / / /  V I S U A L   S T U D I O / / / / / / / / / /
/ / / / / / /  


        public static bool Insert(Person p)
        {
            DbConnection oConn = DBConnections.ScottConnection();
            DbCommand InsertCommand = oConn.CreateCommand();

            #region Parameters
            DbParameter NameParam = InsertCommand.CreateParameter();
            NameParam.ParameterName = "pName";
            NameParam.Direction = ParameterDirection.Input;
            NameParam.Value = p.Name;
            InsertCommand.Parameters.Add(NameParam);

            DbParameter DateParam = InsertCommand.CreateParameter();
            DateParam.ParameterName = "pDate";
            NameParam.Direction = ParameterDirection.Input;
            DateParam.Value = p.BirthDate;
            InsertCommand.Parameters.Add(DateParam);

            DbParameter returnValue = InsertCommand.CreateParameter();
            returnValue.ParameterName = "pOutput";
            returnValue.Direction = ParameterDirection.Output;
            returnValue.DbType = DbType.Boolean;
            InsertCommand.Parameters.Add(returnValue);


            #endregion

            try
            {
                oConn.Open();
                InsertCommand.CommandType = CommandType.StoredProcedure;
                InsertCommand.CommandText = "PersonAdd";
                InsertCommand.ExecuteNonQuery();
                oConn.Close();
                bool a = (bool)returnValue.Value;
                return a;
            }
            catch (DbException ex)
            {
                ex.Data.Clear();
                if (oConn.State == System.Data.ConnectionState.Open)
                    oConn.Close();
                return false;
            }
        } 


 / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / 

I got it working fine without the outputParam, but now i always get an error
when VS executes the query (InsertCommand.ExecuteNonQuery()):

ex = {"ORA-06550: line 1, column 7:
PLS-00306: number or types of wrong arguments calling 'PERSONADD'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored"}

 

Can you take a look at my code and tell me what i'm doing wrong?

 

Thanks once again,
SuperJB

Longback i worked on oracle. ok first change the    pName IN
person.FULLNAME%TYPE, pDate IN person.BIRTHDATE%TYPE, to varchar and date.

Next 

returnValue.Direction = ParameterDirection.Output;

if option consists the returnvalue u set this option and check  this one sure u
will get it. Plz chek the options for Direction


i m not having oracle in my system thats wy i cant check this code here. 


These links may help u plz check it

http://www.15seconds.com/issue/031229.htm

http://weblogs.asp.net/jdanforth/archive/2005/09/06/424513.aspx

http://www.netnewsgroups.net/group/microsoft.public.dotnet.framework.aspnet/topi
c19038.aspx

I understand how the DIRECTIONs work, if i run my Procedure in my PL/SQL
application (Toad version 8.6), it works fine! The problem is running this in
VS2005... i changed the Param Types in the SP, my returnValue Direction was
already set to OUTPUT and the others as INPUT! But the error still persists!

I'll check out he links u sent me.... 

Thanks,
SuperJB