Extract return value from ORACLE function

Question

I have to extract a retrun value from Oracle function, ok here goes

This is a function:

FUNCTION set_idoc
    (Pidoctyp       in varchar2,    -- tip idoc-a
     Pdirect        in varchar2,    -- smjer idoc-a
     Psgln01        in varchar2,    -- identifikator pošiljatelja
     Prgln01        in varchar2,    -- identifikator primatelja
     Pdtdc01        in varchar2,    -- datum i vrijeme kreiranja poruke
     Pbodi01        in varchar2)    -- identifikator
return number is

Lidoc_num       NUMBER;
Lind_obr        AGR3PL_IDOC.ind_obr%TYPE;
BEGIN

    SELECT SEQ_IDOC.nextval, DECODE(Pdirect, '1', 'U', 'P')
      INTO Lidoc_num, Lind_obr
      FROM DUAL;

    INSERT INTO AGR3PL_IDOC (idoc_num, direct, idoctyp, sgln01, rgln01, dtdc01, bodi01, ind_obr)
    VALUES (Lidoc_num, Pdirect, Pidoctyp, Psgln01, Prgln01, Pdtdc01, Pbodi01, Lind_obr);

    return Lidoc_num;
EXCEPTION
    when others then raise;
END;

So i nedd to extract a Lidoc_num value

I tried like this:

System.Data.OracleClient.OracleParameter Lidoc_num = new  System.Data.OracleClient.OracleParameter();
Lidoc_num.Direction = ParameterDirection.ReturnValue;
  Lidoc_num.Size = 16;
                        com1.Parameters.Add(Lidoc_num);

                        //Predaj parametre funkciji i vrati broj dokumneta iz baze
                        com1.CommandText = "TRB01.set_idoc('DOC','1','"
                                                           + sender + "','"
                                                           + reciver + "','"
                                                           + cre_date + "','"
                                                           + bod_ID + "')";
                        com1.ExecuteNonQuery();

                        Lidoc_num = com1.Parameters[0].Value;

But nothing, so how can I do this

Thanks infront!!

CrBruno · Accepted Answer · 2012-05-28 08:47:13Z

3

Solve it!! It has to be like this:

System.Data.OracleClient.OracleParameter prm = new System.Data.OracleClient.OracleParameter();
                prm.Direction = ParameterDirection.ReturnValue;
                prm.DbType = DbType.AnsiString;
                prm.Size = 16;
                com1.Parameters.Add(prm);


                com1.CommandText = "TRB01.set_idoc('DOC','1','"
                                                   + sender + "','"
                                                   + reciver + "','"
                                                   + cre_date + "','"
                                                   + bod_ID + "')";
                com1.ExecuteNonQuery();

                Lidoc_num = com1.Parameters[0].Value.ToString();

answered May 28, 2012 at 8:47

CrBruno

1,0138 gold badges20 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Extract return value from ORACLE function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related