?
Solved

Build a text file of Oracle Stored Procedure Return Columns

Posted on 2008-10-20
5
Medium Priority
?
712 Views
Last Modified: 2012-05-05
I am trying to use ado ext and ado to access an Oracle database and retrieve the output columns that the procedure for a fairly large set of stored procedures.

I can get the list of procs using the following:
        ADOXCatalog.ActiveConnection = adoConn
        procs = ADOXCatalog.Procedures

and look for a proc name using the following:
            For Each proc As ADOX.Procedure In procs

                If proc.Name = "Some ProcName Then
                    '....
                End If
            Next

But I do not see how to get back the returned columns.

Any and all help on this would be greatly appreciated.

Thanks,
Howard

0
Comment
Question by:Howard Bash
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 18

Expert Comment

by:Priest04
ID: 22765682
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?
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 1500 total points
ID: 22766065
Here is a CodeProject solution that might help you:

Schema Compare Tool for Oracle
http://www.codeproject.com/KB/database/OracleSchemaCompare.aspx

Important method:


  Public Function BuildSchemaInfo(ByVal ConnString As String, ByVal ConnUser As String) As DataSet
        mConn = New OracleConnection(ConnString)
        mConn.Open()
 
        Dim ds As New DataSet("SchemaInfo")
        ds.Clear()
        AddDSInfo(ds, ConnUser)
 
        AddSchemaItem(ConnString, "TABLE", "SELECT TABLE_NAME FROM USER_TABLES ORDER BY TABLE_NAME", ds)
        AddSchemaItem(ConnString, "VIEW", "SELECT VIEW_NAME,TEXT_LENGTH,VIEW_NAME TABLE_NAME FROM user_views", ds)
        AddSchemaItem(ConnString, "COLUMN", "SELECT TABLE_NAME||'.'||COLUMN_NAME COLUMN_NAME,DATA_TYPE,DATA_LENGTH,NULLABLE,TABLE_NAME FROM USER_TAB_COLUMNS ORDER BY TABLE_NAME,COLUMN_ID", ds)
        AddSchemaItem(ConnString, "SEQUENCE", "SELECT SEQUENCE_NAME,MIN_VALUE,MAX_VALUE,INCREMENT_BY,CYCLE_FLAG FROM USER_SEQUENCES ORDER BY 1", ds)
        AddSchemaItem(ConnString, "TRIGGER", "SELECT TABLE_NAME||'.'||TRIGGER_NAME TRIGGER_NAME,TRIGGER_TYPE,TRIGGERING_EVENT,TABLE_NAME FROM USER_TRIGGERS ORDER BY TABLE_NAME", ds)
        AddSchemaItem(ConnString, "CONSTRAINT", "SELECT TABLE_NAME||'.'||CONSTRAINT_NAME CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME, NVL(R_CONSTRAINT_NAME,'NULL') R_CONSTRAINT_NAME,NVL(DELETE_RULE,'NULL') DELETE_RULE FROM USER_CONSTRAINTS WHERE GENERATED = 'USER NAME' UNION SELECT C.TABLE_NAME||'.'||DECODE(C.CONSTRAINT_TYPE,'C','CHECK','?')||'_'||CLM.COLUMN_NAME,CONSTRAINT_TYPE,C.TABLE_NAME, NVL(R_CONSTRAINT_NAME,'NULL') R_CONSTRAINT_NAME,NVL(DELETE_RULE,'NULL') DELETE_RULE FROM USER_CONSTRAINTS C, USER_CONS_COLUMNS CLM WHERE C.CONSTRAINT_NAME=CLM.CONSTRAINT_NAME AND C.GENERATED='GENERATED NAME' ORDER BY 3", ds)
        AddSchemaItem(ConnString, "INDEX", "SELECT TABLE_NAME||'.'||INDEX_NAME INDEX_NAME,TABLE_NAME,UNIQUENESS FROM USER_INDEXES", ds)
        AddSchemaItem(ConnString, "JOB", "SELECT WHAT,INTERVAL FROM USER_JOBS", ds)
        AddSchemaItem(ConnString, "PROCEDURE", "SELECT OBJECT_NAME NAME FROM USER_OBJECTS WHERE OBJECT_TYPE IN ('FUNCTION','PROCEDURE') ORDER BY 1", ds)
        AddSchemaItem(ConnString, "PACKAGE", "SELECT OBJECT_NAME||' '||OBJECT_TYPE NAME, T.LINE_COUNT FROM USER_OBJECTS O, (SELECT NAME,COUNT(*) LINE_COUNT FROM USER_SOURCE GROUP BY NAME) T WHERE O.OBJECT_TYPE IN ('PACKAGE','PACKAGE BODY') AND (T.NAME = O.OBJECT_NAME) ORDER BY 1", ds)
 
        mConn.Close()
        Return ds
 
    End Function

Open in new window

0
 
LVL 1

Author Comment

by:Howard Bash
ID: 22768465
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)

Open in new window

0
 
LVL 1

Author Comment

by:Howard Bash
ID: 22768539
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.
0
 
LVL 1

Author Comment

by:Howard Bash
ID: 22773238
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For a while now I'v been searching for a circular progress control, much like the one you get when first starting your Silverlight application. I found a couple that were written in WPF and there were a few written in Silverlight, but all appeared o…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question