Subscribe

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