I’ve been working with Oracle for the past 12 months or so and needed to export the code and SQL for my project, as a documentation tool (and a safeguard against a system crash). If you’re a DBA there are a number of tools at your disposal; I’m not, and I needed something that I could run from Excel or Access any time I needed to.
You will need to build yourself a couple of views based on the user tables, and then use VBA to loop through those views to build the text files. Although the concept is similar for both SQL and code, there are important differences in how the data is parsed so they will be treated separately.
SQL extraction
Oracle SQL is stored in the user_views table. I always include an identifier in views for a particular project, and you can use that identifier (shown hereafter as XX) to pull out all of your SQL for that project.
Create a view in Oracle
In your favourite editor (I use TOAD) create and commit the view.
CREATE OR REPLACE FORCE VIEW TABLESPACE.ZZ_V_XX_VIEWS AS SELECT view_name, text view_sql FROM user_views WHERE view_name LIKE '%XX%'; GRANT SELECT ON TABLESPACE.ZZ_V_XX_VIEWS TO READ_ROLE;
Where I work we assign permissions on a group basis. Make sure that you assign SELECT permission on the view to a role that (1) includes your user name and (2) can at least select data. You could also just grant the SELECT permission to your user name. You will also need to connect with the credentials of the user who created the views.
If you look at the view you will see that VIEW_NAME is the name of the view, and VIEW_SQL contains the formatted SQL. The multiple lines are separated by line feeds Chr(10), and we will use this to write the SQL into the text file.
Create the VBA
The VBA code is shown below. Copy and paste into the IDE in Excel or Access. Change paths, connection strings and suffixes as required.
'======================================================
'You will need to update this code to suit your setup,
'in the following places:
'sPATH, sFILE, database, user id, password, strSQL
'You will also need to set a reference to the
'Microsoft ActiveX Data Objects 2.x Library
'======================================================
Sub Harvest_SQL_from_view()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strProc As String
Dim sFILE As String
'include the trailing backslash
Const sPATH = "G:\some path\"
Dim strOpenFile As String
Dim strOutFile As String
Dim x
Dim FF As Integer
Dim i As Integer
'open the text file.
'If it doesn’t exist it will be created.
sFILE = "xx_sql"
strOpenFile = sPATH & sFILE & ".txt"
strOutFile = sPATH & sFILE & "_"
& Format(Now(), "yyyymmdd") & ".txt"
FF = FreeFile
Open strOpenFile For Output As #FF
'Create the connection
Set cnn = New ADODB.Connection
With cnn
.ConnectionString = "Provider=MSDAORA;" _
& "Data Source=YOURDATABASE.WORLD;" _
& "User Id=your_user_name;" _
& "password=your_password
.Open
End With
'SQL for the recordset
strSQL = "SELECT * FROM zz_v_xx_views"
'pull the data and write out to a text file
'first, create the recordset, then loop through it
'NOTE: For Oracle you MUST use the
'Execute method to set a recordset.
Set rst = cnn.Execute(strSQL)
With rst
.MoveFirst
Do
Print #FF, " "
Print #FF, "++++++++++++++++++++++"
Print #FF, rst!view_name
Print #FF, " "
'the SQL is split using Chr(10)
'as the delimiter
x = Split(rst!view_sql, Chr(10))
For i = LBound(x) To UBound(x)
Print #FF, x(i)
Next i
.MoveNext
Loop Until .EOF
End With
'close and save the file
Close #FF
'copy the generic file to one with a datestamped name
FileCopy strOpenFile, strOutFile
'clean up object references
rst.Close
Set rst = Nothing
Set cnn = Nothing
'open the output file
Call Shell("Notepad.exe" & " " & strOutFile, vbNormalFocus)
End Sub
The Split function does most of the work in extracting the SQL. If you haven’t encountered it before, Split creates an array by splitting a text string on a common delimiter (here, we used Chr(10)). Looping through the array and using the Print command puts each element onto a new line, thus rebuilding the query SQL with its original formatting.
Once the file is written, the Shell command displays the output. Each time you run the routine, a datestamped file is created so you can keep dated versions of your SQL.