Amazon Redshift JDBC stored procedure support in oracle sql developer

Question

I'm trying to use an Amazon Redshift JDBC drivers version 1.2.27.1051 in Oracle SQL Developer because it supposedly supports Amazon Redshift stored procedures. I need stored procedures to systematically generate and transform some data prior to inserting into a table. However, using the JDBC driver in Oracle SQL Developer 18.4.0.376 keeps running into the 'ERROR: syntax error at or near "PROCEDURE"' signaling that it does not support stored procedures. Is there some configuration I am missing? Or is something wrong with the versions of JDBC and Oracle SQL Developer I am using?

JDBC Driver I installed: https://s3.amazonaws.com/redshift-downloads/drivers/jdbc/1.2.27.1051/RedshiftJDBC42-no-awssdk-1.2.27.1051.jar

Oracle SQL Developer version: https://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/sqldev-downloads-184-5458710.html

I followed most of the steps in this guide to setup SQL Developer to connect to Redshift, but used the Redshift JDBC driver instead of Postgres Driver: https://blog.openbridge.com/definitive-guide-for-connecting-oracle-sql-developer-to-amazon-redshift-e204fd76e334

Stored procedure I am attempting to test this:

CREATE OR REPLACE PROCEDURE test_sp1(f1 int, f2 varchar)
    AS $$
    BEGIN
        RAISE INFO 'f1 = %, f2 = %', f1, f2
    END;
    $$ LANGUAGE plpgsql;

    call test_sp1(5, 'abc');
    INFO: f1 = 5, f2 = abc
    CALL

I learned that Amazon Redshift cluster version 1.0.7287 or later supports stored procedures. So while the Redshift driver I am using supports SP, the current version of Redshift I am using is old and does not. Going to try to update the cluster version which should address my issues. — Shawn Fakhari

thatjeffsmith thatjeffsmith · Accepted Answer · 2019-05-14T16:50:07

that blog post for using postgresql drivers to connect to RedShift is completely out-dated, you can find current docs here

Even so, I'm pretty sure working with your SP there isn't going to work, and it's not designed to work.

The only use case we support for connecting to AWS Redshift is to move that database to an Oracle Autonomous Data Warehouse Cloud Service.

Amazon Redshift JDBC stored procedure support in oracle sql developer

1 Answers