Solved

Build a text file of Oracle Stored Procedure Return Columns

Posted on 2008-10-20
5
654 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:hbash
  • 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 500 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:hbash
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:hbash
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:hbash
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
This video discusses moving either the default database or any database to a new volume.

758 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now