I was asked a question today by someone who emailed me. I like getting the occasional question as sometimes it either makes me learn new things or sharpens my focus on something I already know. I often don’t have time to answer questions but I try as often as I can.

Today’s question didn’t teach me anything new but it was a fun 15 minute distraction. I like SQL and coming up with solutions.

Here’s the question (names removed to protect the innocent):

Subject: Postgres query

Hello,

My name is xxxxxxx and I saw your link on the web and wonder if you have any example that can do the following view.

Someone recently posted a question in the Postgres Forums about how to call a package in Advanced Server. He also had a question about how to call a packaged procedure in a specific schema.

Here is my reply to the question:

You can’t actually execute packages. You execute the procedures or functions in them. Here is some code to execute a procedure in a package. I am using public as the schema name. If you want to execute a procedure in a user’s schema, just replace public with the user’s name.

using System;using System.Data;using EnterpriseDB.EDBClient;

namespace ConsoleApplication2{    class Program    {        static void Main(string[] args)        {            EDBConnection conn =               new EDBConnection(                     "Server=localhost;" +                     "Port=5444;" +                     "User Id=enterprisedb;" +                     "Password=enterprisedb;" +                     "Database=edb");

            conn.Open();            EDBCommand cmd = new EDBCommand();            cmd.Connection = conn;

            int vEmpId = 7876;            String query = "public.emp_admin.fire_emp(:e_id)";            cmd.CommandText = query;            cmd.CommandType = CommandType.StoredProcedure;

            EDBParameter prm = new EDBParameter(                             "e_id",                              EDBTypes.EDBDbType.Integer);

            cmd.Parameters.Add(prm);

            cmd.Parameters[0].Value = vEmpId;    

            cmd.Prepare();

            cmd.ExecuteNonQuery();

            conn.Close();        }    }}

.Net enterprisedb

I come from an Oracle background so I am used to a few rules when it comes to DDL.

• Don’t perform DDL in a production database until you’ve done it in a test instance.

• DDL is permanent unless you have flashback or want to restore.

That might sound like a limitation but I don’t see it that way. You *SHOULD* test everything before running it in production. And flashback is much more powerful than simply having commit and rollback for DDL.
Today I was trying out some changes to my code (that runs outside of the database) and needed to do a quick test that would delete some rows.

LewisC’s An Expert’s Guide To Oracle Technology

Have you ever needed to bring data from a non-Oracle databases into your Oracle database? This is very common in a warehouse and is even somewhat common in ERP solutions. For a price, Oracle offers Transparent Gateways to many popular databases. What do you do if you need to load some data from a data source but there is no Transparent Gateway (or if you have no budget for one)? If that data source happens to support ODBC, you’re in luck and it won’t cost you anything above and beyond what you are already paying for your database.

The solution is called Heterogeneous Services Generic Connectivity.

LewisC’s An Expert’s Guide To Oracle Technology

This tip relates to the latest version of EnterpriseDB’s Advanced Server (AS) product. This is the commercially licensed database that provides Oracle compatibility. I have spoken quite a bit about what that compatibility entails but the gist of it is that the compatibility is code compatibility. The latest version of AS offers quite a few new features that I will be writing about over time. Today I am writing about the Oracle compatible db links.

To understand database links in Oracle, read my entry here on ittoolbox, Database Links: A Definition in Plain English.

In the past, your basic support for postgres has been mail groups. I’ve never been a big fan of mail groups. You have to subscribe to the group and usually you get tons of stuff you don’t really want. Worst of all, you either have to sort through it or filter it to a folder. I never seem to be able to keep up with the stuff I am interested in due to the clutter.

EnterpriseDB has had forums for a while but recently combined the postgres and advanced server forums into a single location. GridSQL also has it’s own forum.