Share

Database, SQL and PL/SQL

Just Say No, Unless You Can’t

Listing 1: Package body for exec_any_sql 
PACKAGE BODY exec_any_sql
IS
   PROCEDURE delete_from (schema_in      IN     VARCHAR2 DEFAULT USER,
                          table_in       IN     VARCHAR2,
                          where_in       IN     VARCHAR2 DEFAULT NULL,
                          rowcount_out      OUT INTEGER)
   IS
   BEGIN
      EXECUTE IMMEDIATE
         'DELETE FROM ' || schema_in || '.' || table_in || ' ' || where_in;

      rowcount_out := SQL%ROWCOUNT;
   END;

   FUNCTION columns_list (columns_in IN DBMS_SQL.varchar2_table)
      RETURN VARCHAR2
   IS
      l_return   VARCHAR2 (32767);
   BEGIN
      FOR indx IN 1 .. columns_in.COUNT
      LOOP
         l_return := l_return || ',' || columns_in (indx);
      END LOOP;

      RETURN LTRIM (l_return, ',');
   END;

   FUNCTION values_list (values_in IN DBMS_SQL.varchar2_table)
      RETURN VARCHAR2
   IS
      l_return   VARCHAR2 (32767);
   BEGIN
      FOR indx IN 1 .. values_in.COUNT
      LOOP
         l_return := l_return || ',''' || values_in (indx) || '''';
      END LOOP;

      RETURN LTRIM (l_return, ',');
   END;

   PROCEDURE insert_into (schema_in      IN     VARCHAR2 DEFAULT USER,
                          table_in       IN     VARCHAR2,
                          columns_in     IN     DBMS_SQL.varchar2_table,
                          values_in      IN     DBMS_SQL.varchar2_table,
                          rowcount_out      OUT INTEGER)
   IS
   BEGIN
      EXECUTE IMMEDIATE
            'INSERT INTO '
         || schema_in
         || '.'
         || table_in
         || ' ('
         || columns_list (columns_in)
         || ') VALUES ('
         || values_list (columns_in)
         || ')';

      rowcount_out := SQL%ROWCOUNT;
   END;

   FUNCTION set_pairs (columns_in   IN DBMS_SQL.varchar2_table,
                       values_in    IN DBMS_SQL.varchar2_table)
      RETURN VARCHAR2
   IS
      l_return   VARCHAR2 (32767);
   BEGIN
      FOR indx IN 1 .. columns_in.COUNT
      LOOP
         l_return :=
               l_return
            || columns_in (indx)
            || ' = '''
            || values_in (indx)
            || ''',';
      END LOOP;

      RETURN RTRIM (l_return, ',');
   END;

   PROCEDURE update_in (schema_in      IN     VARCHAR2 DEFAULT USER,
                        table_in       IN     VARCHAR2,
                        where_in       IN     VARCHAR2 DEFAULT NULL,
                        columns_in     IN     DBMS_SQL.varchar2_table,
                        values_in      IN     DBMS_SQL.varchar2_table,
                        rowcount_out      OUT INTEGER)
   IS
   BEGIN
      EXECUTE IMMEDIATE
            'UPDATE '
         || schema_in
         || '.'
         || table_in
         || ' SET '
         || set_pairs (columns_in, values_in)
         || ' WHERE '
         || where_in;

      rowcount_out := SQL%ROWCOUNT;
   END;
END;
Return to Steven Feuerstein’s “Just Say No, Unless You Can’t” article
Steven Feuerstein

Steven Feuerstein is Oracle Corporation's Developer Advocate for PL/SQL, and an expert on the Oracle PL/SQL language, having written ten books on PL/SQL, including Oracle PL/SQL Programming and Oracle PL/SQL Best Practices (all published by O'Reilly Media). Steven has been developing software since 1980, spent five years with Oracle back in the "old days" (1987-1992), and was PL/SQL Evangelist for Quest Software (and then Dell) from January 2001 to February 2014 - at which point he returned joyfully to Oracle Corporation. He was one of the original Oracle ACE Directors and writes regularly for Oracle Magazine, which named him the PL/SQL Developer of the Year in both 2002 and 2006. He is also the first recipient of ODTUG's Lifetime Achievement Award (2009).

DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.