Generating SQL Scripts on SQL Workbench/J to Create Amazon Redshift Stored Procedures

Amazon Redshift logo

The task of generating a SQL script for a stored procedure or function is fairly simple in Microsoft SQL Server using SQL Server Management Studio, or in Oracle Database, using SQL Developer.

However, if your database is Amazon Redshift, you’re probably using SQL Workbench/J to interact with your database, and unfortunately, its ability to script out stored procs is limited.

I recently discovered that, when selecting the stored procedure from a list in Database Explorer, the SQL script that is supposed to be able to re-create the stored proc does not work. It looks something like this:

DROP PROCEDURE schemaname.procname()

CREATE FUNCTION procname (null)
AS $$


.... << stored proc code here >> ...

$$ LANGUAGE plpgsql

You might notice a few things about this code. First of all, this stored procedure is being called a function here rather than a procedure. Since it is, in reality, a stored procedure, it returns no value, but this script doesn’t seem to know how to do that. Also there happen to be no parameters for this stored proc, but the scripting engine puts “null” as the parameter data type rather than leaving empty parentheses. The code for this stored proc should be this:

CREATE OR REPLACE PROCEDURE schemaname.procname ()
AS $$


.... << stored proc code here >> ...

$$ LANGUAGE plpgsql

I have, with a little help from the Amazon Redshift documentation and the PostgreSQL documentation, been able to write a script that will correctly script out your stored proc, with the exception of the datatypes for the parameters, if there are any. If there are any you will need to look at the pg_type table and the proargtype column in the pg_proc_info table.

  CASE WHEN p.prokind='p' THEN 'PROCEDURE ' 
       WHEN p.prokind='f' THEN 'FUNCTION ' 
       ELSE '???? ' END ||
  n.nspname || '.' || proname || ' (' || 
  CASE WHEN p.proargtypes IS NULL OR p.proargtypes='' THEN ''
  ELSE ' ???? '
  END || 
  ')' ||  CHR(13) ||
  CASE WHEN p.prorettype=0 THEN ''  
       ELSE 'RETURNS ' || (SELECT TOP 1 typname FROM pg_type WHERE oid=p.prorettype) || CHR(13) END || 
  CASE WHEN p.prokind='p' THEN ''
       WHEN p.provolatile='i' THEN 'IMMUTABLE' 
       WHEN p.provolatile='s' THEN 'STABLE'  
       WHEN p.provolatile='v' THEN 'VOLATILE' END || 
  CHR(13) || 'AS $$' || CHR(13) || CHR(13) ||
  p.prosrc ||
  '$$ LANGUAGE plpgsql' || CHR(13) || ';' || CHR(13) || '/'
    pg_catalog.pg_namespace n
JOIN pg_catalog.pg_proc_info p ON
    p.pronamespace = n.oid
JOIN pg_user b ON
    b.usesysid = p.proowner
    n.nspname not in ('information_schema','pg_catalog') 
    and p.proname like '%whatever%;  --put a part or all of the function/proc name here with percent wildcards and remember that like is case-sensitive. Use ilike for case-insensitive searching.

The result should be a single column with the text for a script to re-create your stored procedure or function!

Blockchain: Where to Begin?

Blockchain symbolic representation

Blockchain is a technology that I hear about almost every day now. 

And yet I haven’t found the best place to learn about it – both its concepts and its implementation. With so many courses and books, where to begin?

Those of you who have learned about it and use it regularly – what resources did you use? What, if any, certifications are legitimate?

You may leave your thoughts in the comments, or tweet me: @deepinthecode.

Connecting to Oracle 12g with the Instant Client and Excel VBA

Oracle Database logo

One rather old application I’ve supported for several years loads data from Excel spreadsheets into a reporting database. These Excel spreadsheets have always been manually updated by several users. However, because the data that the users are entering into these spreadsheets are first being entered into another separate application database, these users have been doing double-entry – a redundant process which can be easily remedied by various means.

Ideally, the solution for this problem would be to extract the data from the application database and load it into the reporting database using an SSIS package. Unfortunately, that would require some redevelopment of the application which loads data into the reporting database, and we (and the customers) have no bandwidth for that. So I came up with a quick workaround that made everyone happy – using a VBA macro to automatically populate the spreadsheets with data when the users open them.

The tricky part here was getting Excel to connect to Oracle with the least amount of work having to be done on the users’ PCs, which are remote from my location.

First of all, since these users don’t require SQL Plus or any development tools for Oracle, the full client software was unnecessary. Also, the Oracle Instant Client could be pushed to the users with Altiris Deployment Solution.

I had the Instant Client software installed on the PCs, then I added the requisite database connection information to the tnsnames.ora file.

Nota bene: In the Instant Client (or at least in our setup, using version the tnsnames file is in
C:\oracle\instantclient_11_2_0_4 rather than in C:\oracle\product\\client_1\NETWORK\ADMIN as it often would be in the full Oracle client.

The connection in VBA was simple enough, but not immediately obvious – notice that the connection string includes “Microsoft ODBC Driver for Oracle” rather than an Oracle driver; even though this is used, no ODBC connection needs to be set up in the ODBC Data Source Administrator. It is only imperative that the proper entries exist in the tnsnames.ora file, and that the Microsoft ActiveX Data Object Library is installed and referenced in Excel. (Add References by navigating to Tools –> References in the VBA editor in Excel.)

Excel References

In a subroutine, this code was used to connect to the database and pull data.

    Dim SQL_String As String
    Dim dbConnectStr As String
    Dim con As New ADODB.Connection
    Dim recset As New ADODB.Recordset
    Dim strUid As String
    Dim strPwd As String
    Dim strEnv As String
    Dim strDSN As String
    Dim iRow As Integer    

    strEnv = "prod"
    strUid = "username"
    strPwd = "password"

    If strEnv = "prod" Then
        strDSN = "(prod database net_service_name* from tnsnames)"
        strDSN = "(dev database net_service_name* from tnsnames)"
    End If

    dbConnectStr = "Driver={Microsoft ODBC for Oracle}; " & _
            "Server=" & strDSN & ";" & _
            "uid=" & strUid & ";pwd=" & strPwd & ";"

    con.ConnectionString = dbConnectStr

    SQL_String = "(insert SQL query here)"

    recset.Open SQL_String, con

    iRow = 0
    Do While Not recset.EOF
         'Have a loop here to go through all the fields
        Sheet1.Range("A" & iRow).Value = recset.Fields("colname") ' colname = Column Name from SQL query

        ' &c. ...

        iRow = iRow + 1


* net_service_name

%d bloggers like this: