CODEWITHSUNDEEP
asp.netoracle-databasestored-procedures
Frank
Frank

Reputation: 67

Get User Collection Type Object (Table) as Out Parameter from Oracle stored procedure

I need to get back from an Oracle stored procedure (inside a package) an output parameter defined as user collection type object (a table).

Procedure GetCon (DataRif IN VARCHAR2,Results OUT my_collectiontype)
   IS
      BEGIN
          select my_ObjectType(Col1,Col2)
          bulk collect into Results
          FROM
          TABLE(PMS.myfunc(DataRif ,'31-12-2019');
      END;
....

The Function PMS.myfunc returns a my_collectiontype object

In my code (Asp.net 4.0 VB.NET with Oracle ManagedDataAccess Drivers), I've tried

        strConn = "Data Source=XE.WORK;User Id=PMS;Password=xxxx"
        Dim con As New OracleConnection(strConn)

        Dim dt As DataTable = New DataTable()
        Dim da As OracleDataAdapter = New OracleDataAdapter()

        con.Open()

        Dim cmd As New OracleCommand()
        cmd.Connection = con
        cmd.CommandText = "MySchema.MyPackage.GetCon"
        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.Add("DataRif", OracleDbType.Varchar2,"23-07-2019", ParameterDirection.Input)
        cmd.Parameters.Add("Results", OracleDbType.RefCursor).Direction = ParameterDirection.Output

        da.SelectCommand = cmd
        cmd.ExecuteNonQuery()
        da.Fill(dt)
        .....

I get this error

ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'GETCONT'
ORA-06550: line 1, column 7: PL/SQL: Statement ignored"

Sure the out parameter is not a ref cursor but I can't find the right object.

I need to use a stored procedure because I know it's possible execute something like this as simple view

SELECT * FROM TABLE (PMS.myfunc('01-01-2019', '31-12-2019')

but this solution is not the best at the moment.

Thanks in advance.

Upvotes: 0

Views: 293

Answers (1)

Frank
Frank

Reputation: 67

Solved in this following way that permits to receive a cursor and put it in a dataset.

Create Stored Procedure in Oracle like this

CREATE OR REPLACE PROCEDURE get_emp_rs (p_recordset OUT SYS_REFCURSOR) AS 
BEGIN 
  OPEN p_recordset FOR
    SELECT DIP_ID,DIP_NAME
    FROM  TABLE(fn_GetDataFunction('01-01-2019','31-12-2019','15-09-2019'));
END get_emp_rs;
/

In Asp.net 

Public Function TestCursor() As DataSet

        Dim strConn As String = "Data Source=MYSOURCE;User Id=MYID;Password=xxxxx"
        Dim dsReturn As DataSet = Nothing

        Try
            Using con As New OracleConnection(strConn)
                Using sda As New OracleDataAdapter()
                    Dim cmd As New OracleCommand()
                    cmd.Connection = con
                    cmd.CommandType = CommandType.StoredProcedure
                    cmd.CommandText = "get_emp_rs"

                    cmd.Parameters.Add("p_recordset", OracleDbType.RefCursor).Direction = ParameterDirection.Output

                    sda.SelectCommand = cmd
                    Using ds As New System.Data.DataSet("results")
                        sda.Fill(ds, "MyTableName")
                        Return ds
                    End Using
                End Using
            End Using
        Catch ex As Exception
           '---------
        End Try
    End Function

Upvotes: 0

Related Questions