0

I'm using a Commercial vendors API and need help following a section of their instructions:

QUOTE FROM API DOCUENTATION: 7) The next step is to locate the ELEMENT_ID using the following oracle function.

nElementID := PK_TMX_STORAGE_MANAGEMENT.F_FIND_ID_BY_ADDRESS(‘FF-S01-R01-T01-001’, nBankID) ;

I need to run this Function in a VB.Net webservice (C# code is fine also) and I'm not sure about the syntax. Do I run it as a Stored Procedure and if so how do I pass the two variable (‘FF-S01-R01-T01-001’, nBankID) into the function.

Or do I wrap it in a Select Statement somehow and if do I need a FROM TABLE?

I know that Stored Proc and Functions are close but I'm running into problems with systax.

I get back ORA-06550: line 1, column 7: PLS-00221: 'F_FIND_ID_BY_ADDRESS' is not a procedure or is undefined ORA-06550: line 1, column 7: PL/SQL: Statement ignored Error

Thanks

Bill
  • 1,423
  • 2
  • 27
  • 51
  • What Oracle program is this, exactly? Is this OracleDB? Or a different program? – Nzall Sep 20 '15 at 11:55
  • I'm using OracleDB 10.2 Using a connection string of Return "Data Source=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = xxxxx)(PORT = 1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=xx))); User Id=xxx;Password=xxx;" – Bill Sep 20 '15 at 12:27
  • 1
    Look here how to pass values to stored procedure http://stackoverflow.com/questions/3940587/calling-oracle-stored-procedure-from-c – Roman Dibikhin Sep 20 '15 at 13:04
  • Thanks for the link but I'm not having any trouble passing parameters to a Store Procudure just the Function and get a return value – Bill Sep 20 '15 at 14:20
  • Please edit your question and include the C# code which you're using to try to invoke the function. Thanks. – Bob Jarvis - Слава Україні Sep 20 '15 at 14:52
  • I got it to work-- Thanks to all – Bill Sep 20 '15 at 15:05

1 Answers1

0
     Dim str As String = "PK_TMX_STORAGE_MANAGEMENT.F_FIND_ID_BY_ADDRESS"
        Dim OracleCMD As OracleCommand = New OracleCommand(str, OracleConn)
        OracleCMD.CommandType = CommandType.StoredProcedure                 
        DIM ELEMENT_ID as  OracleParameter  = new OracleParameter("ELEMENT_ID",OracleDbType.Int32)
    ELEMENT_ID.Direction = ParameterDirection.ReturnValue
    OracleCMD.Parameters.Add(ELEMENT_ID)

         OracleCMD.Parameters.Add(New OracleParameter("ADDRESS", OracleDbType.VARCHAR2, ParameterDirection.Input)).Value = STORAGE_ADDRESS
         OracleCMD.Parameters.Add(New OracleParameter("BANK_ID", OracleDbType.Int32 , ParameterDirection.Input)).Value = BANK_ID
                    OracleConn.Open()   
        OracleCMD.ExecuteNonQuery()
                Return System.Convert.ToString(OracleCMD.Parameters("ELEMENT_ID").Value )
        OracleConn.Close()
Bill
  • 1,423
  • 2
  • 27
  • 51