Build a text file of Oracle Stored Procedure Return Columns

Are you trying to get the code from the stored procedure, or you have a particular stored procedure in mind, that efter you have executed it, you want retrieved data from it to be saved in text file?

I downloaded the above project and while it is interesting,  I am not sure how it will help with the problem.

However,  here is how far I have progressed since the initial post:
I can get the Oracle Stored Procedure's input parameters and also I can call the stored procedure and by looking at the returned datatable,  I can capture all the column names and types.

So far so good. But,  there are problems.  I can manually build a parameter collection and use it to obtain my return columns,  but want to obtain the parameters using code to avoid hardcoding stored proc params and letting code query the database.  I am using ado ext and once I get back my ado ext params (which are missing the return cursor references),  I need to convert these ado db types to Oracle data type.

Attached are the two routines in question.  Again the issues are,  how to convert the ado datatype to oracle and where is my output cursor.

    Public Shared Function OracleGetStoredProcedureInputParams(ByVal sProcName As String) As List(Of ColumnRecord)
        Dim oCat As New ADOX.Catalog
        Dim oCmd As New ADODB.Command
        Dim strConn As String
        Dim oProc As ADOX.Procedure
        Dim oPar As ADODB.Parameter
 
        Dim lProcParams As List(Of ColumnRecord) = New List(Of ColumnRecord)
 
        Dim adoconn As ADODB.Connection = New ADODB.Connection
 
        strConn = "my connection info for my oracle server"
        adoconn.ConnectionString = strConn
        adoconn.Open()
 
        oCmd.ActiveConnection = adoconn
        oCmd.CommandType = adCmdStoredProc
        oCmd.CommandText = sProcName
 
        ' this will retrieve information about parameters  <--- no output cursor though
        oCmd.Parameters.Refresh()
 
        For Each oPar In oCmd.Parameters
            Dim nc As New ColumnRecord
            nc.ColumnName = oPar.Name
 
            ' converting parameter's direction constatn which is integer to
            ' "spelled out" version
 
            nc.ColumnType = oPar.Type
            nc.ColumnDirection = oPar.Direction
 
            Select Case oPar.Direction
                Case adParamUnknown
                    nc.ColumnDirectionFriendly = "adParamUnknown"
                Case adParamInput
                    nc.ColumnDirectionFriendly = "adParamInput"
                Case adParamOutput
                    nc.ColumnDirectionFriendly = "adParamOutput"
                Case adParamInputOutput
                    nc.ColumnDirectionFriendly = "adParamInputOutput"
                Case adParamReturnValue
                    nc.ColumnDirectionFriendly = "adParamReturnValue"
            End Select
            ' converting the type of the parameter
            Select Case oPar.Type
                Case adBigInt
                    nc.ColumnTypeFriendly = "adBigInt"
                Case adBinary
                    nc.ColumnTypeFriendly = "adBinary"
                Case adBoolean
                    nc.ColumnTypeFriendly = "adBoolean"
                Case adBSTR
                    nc.ColumnTypeFriendly = "adBSTR"
                Case adChapter
                    nc.ColumnTypeFriendly = "adChapter"
                Case adChar
                    nc.ColumnTypeFriendly = "adChar"
                Case adCurrency
                    nc.ColumnTypeFriendly = "adCurrency"
                Case adDate
                    nc.ColumnTypeFriendly = "adDate"
                Case adDBDate
                    nc.ColumnTypeFriendly = "adDBDate"
                    '''''''''''''''''''''''''''''''''             Case adDBFileTime
                    '''''''''''''''''''''''''''''''''''''''''''''''''Debug.Print("adDBFileTime")
                Case adDBTime
                    nc.ColumnTypeFriendly = "adDBTime"
                Case adDBTimeStamp
                    nc.ColumnTypeFriendly = "adDBTimeStamp"
                Case adDecimal
                    nc.ColumnTypeFriendly = "adDecimal"
                Case adDouble
                    nc.ColumnTypeFriendly = "adDouble"
                Case adEmpty
                    nc.ColumnTypeFriendly = "adEmpty"
                Case adError
                    nc.ColumnTypeFriendly = "adError"
                Case adFileTime
                    nc.ColumnTypeFriendly = "adFileTime"
                Case adGUID
                    nc.ColumnTypeFriendly = "adGUID"
                Case adIDispatch
                    nc.ColumnTypeFriendly = "adIDispatch"
                Case adInteger
                    nc.ColumnTypeFriendly = "adInteger"
                Case adIUnknown
                    nc.ColumnTypeFriendly = "adIUnknown"
                Case adLongVarBinary
                    nc.ColumnTypeFriendly = "adLongVarBinary"
                Case adLongVarChar
                    nc.ColumnTypeFriendly = "adLongVarChar"
                Case adLongVarWChar
                    nc.ColumnTypeFriendly = "adLongVarWChar"
                Case adNumeric
                    nc.ColumnTypeFriendly = "adNumeric"
                Case adPropVariant
                    nc.ColumnTypeFriendly = "adPropVariant"
                Case adSingle
                    nc.ColumnTypeFriendly = "adSingle"
                Case adSmallInt
                    nc.ColumnTypeFriendly = "adSmallInt"
                Case adTinyInt
                    nc.ColumnTypeFriendly = "adTinyInt"
                Case adUnsignedBigInt
                    nc.ColumnTypeFriendly = "adUnsignedBigInt"
                Case adUnsignedInt
                    nc.ColumnTypeFriendly = "adUnsignedInt"
                Case adUnsignedSmallInt
                    nc.ColumnTypeFriendly = "adUnsignedSmallInt"
                Case adUnsignedTinyInt
                    nc.ColumnTypeFriendly = "adUnsignedTinyInt"
                Case adUserDefined
                    nc.ColumnTypeFriendly = "adUserDefined"
                Case adVarBinary
                    nc.ColumnTypeFriendly = "adVarBinary"
                Case adVarChar
                    nc.ColumnTypeFriendly = "adVarChar"
                Case adVariant
                    nc.ColumnTypeFriendly = "adVariant"
                Case adVarNumeric
                    nc.ColumnTypeFriendly = "adVarNumeric"
                Case adVarWChar
                    nc.ColumnTypeFriendly = "adVarWChar"
                Case adWChar
                    nc.ColumnTypeFriendly = "adWChar"
                Case Else
                    nc.ColumnTypeFriendly = "other"
            End Select
 
            lProcParams.Add(nc)
 
        Next oPar
 
        oCat = Nothing
        oCmd = Nothing
 
        Return lProcParams  'returns a list of Column Records for the Stored Proc
 
    End Function
 
 
    Public Shared Function OracleGetStoredProcResultset(ByVal conn As OracleClient.OracleConnection, ByVal sProcName As String, ByVal cmd As OracleClient.OracleCommand) As List(Of String)
        Dim dt As DataTable = New DataTable
        Dim adapt As OracleClient.OracleDataAdapter = New OracleClient.OracleDataAdapter
 
        Try
            If conn.State = ConnectionState.Open Then
                cmd.Connection = conn
                cmd.CommandType = CommandType.StoredProcedure
                cmd.CommandText = sProcName
                adapt.SelectCommand = cmd
                adapt.Fill(dt)
            End If
 
            Dim l As List(Of String) = New List(Of String)
            Dim nCnt As Integer, nlp As Integer
 
            nCnt = dt.Columns.Count
            For nlp = 0 To nCnt - 1
                l.Add(dt.Columns(nlp).ColumnName)
            Next
 
            Return l
 
        Catch ex As Exception
            MsgBox(ex.Message)
            Throw New Exception
 
        Finally
            If Not conn Is Nothing Then
                If conn.State = ConnectionState.Open Then
                    conn.Close()
                End If
                conn.Dispose()
            End If
 
        End Try
 
    End Function
 
 
'' Somewhere at a higher level:
        'Oracle
        Dim sConnOracle As String = Constants.HRDC_ConnString
        Dim connOracle As OracleClient.OracleConnection = New OracleClient.OracleConnection(sConnOracle)
        Dim cmdOracle As OracleClient.OracleCommand = New OracleClient.OracleCommand
        connOracle.Open()
 
        Dim lstOracleParameterRecords As List(Of ColumnRecord) = dbAnalyzer.dbAnalyzer.OracleGetStoredProcedureInputParams("USP_DF_GET_ALL_EVENTS")
 
        'Add the parameters to the cmdOracle object
        cmdOracle.Parameters.Clear()
        cmdOracle.Connection = connOracle
 
        For Each p As ColumnRecord In lstOracleParameterRecords
            With cmdOracle
                .Parameters.Add(p.ColumnName, OracleType.Cursor, 0)
            End With
        Next
        ' Get the resultset columns
        Dim lOracleReturnColumns As List(Of String) = dbAnalyzer.dbAnalyzer.OracleGetStoredProcResultset(connOracle, "USP_DF_GET_ALL_EVENTS", cmdOracle)

Select allOpen in new window

The last call on line 202 above fails due to not using the oracle datatype and that the output cursor is not defined in the parameter collection.

OK.  Here it is.  It seems that by changing drivers/connection string,  I am able to get the output cursor along with the other parameters.

Thanks for the help.