Example

A package, called dsql, is defined which contains two procedures:

• get_columns. This procedure takes as input startch, a character and returns information about all the columns of any database table in the database whose name starts with startch.
• get_query_results. This procedure takes as input
  • query_string, which has as its value an SQL select statement,
  • ncols, the number of select list items in the SQL select statement, and
  • column_types, a PL/SQL table that contains the data type of each of the select list items.
  It then executes the query and returns the results in the output parameter result along with the number of rows in the result in variable nrows.

  The result output parameter is a PL/SQL table of strings. The individual columns in a row of the result of the query are concatenated into a long string with the bar character | between two values.

-- p20.sql
CREATE OR REPLACE PACKAGE dsql AS
  TYPE dd_rec_type IS RECORD (
    table_name varchar2(30),
    column_name varchar2(30),
    data_type  varchar2(9));
  TYPE dd_table_type IS TABLE OF dd_rec_type 
       INDEX BY BINARY_INTEGER;
  TYPE string1024_table IS TABLE OF varchar2(1024) 
       INDEX BY BINARY_INTEGER;
  TYPE string9_table IS TABLE OF varchar2(9) 
       INDEX BY BINARY_INTEGER;
  TYPE number_table IS TABLE OF number
       INDEX BY BINARY_INTEGER;

PROCEDURE get_columns(startch IN char,
                      dd_table OUT dd_table_type,
                      n OUT number);

procedure get_query_results(query_string IN varchar2,
                            ncols IN number,
                            column_types IN string9_table,
                            result OUT string1024_table,
                            nrows OUT number); 

END dsql;
/
show errors

CREATE OR REPLACE PACKAGE BODY dsql AS

PROCEDURE get_columns(startch in char,
                      dd_table OUT dd_table_type,
                      n OUT number) AS
handle Integer;
dbms_return Integer;
tablename varchar(30);
colname varchar(30);
datatype varchar(9);
counter integer := 0;

begin

  handle := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(handle,
    'select distinct table_name,column_name,data_type ' ||
    'from   user_tab_columns '                          ||
    'where  table_name like '''                         || 
    startch                                        || 
    '%'''                                          ||
    'order by table_name,column_name', DBMS_SQL.V7);

  DBMS_SQL.DEFINE_COLUMN(handle, 1, tablename, 30);
  DBMS_SQL.DEFINE_COLUMN(handle, 2, colname, 30);
  DBMS_SQL.DEFINE_COLUMN(handle, 3, datatype, 9);

  dbms_return := DBMS_SQL.EXECUTE(handle);

  counter := 0;
  loop
    if DBMS_SQL.FETCH_ROWS(handle) = 0 then
      exit;
    else
      DBMS_SQL.COLUMN_VALUE(handle, 1, tablename);
      DBMS_SQL.COLUMN_VALUE(handle, 2, colname);
      DBMS_SQL.COLUMN_VALUE(handle, 3, datatype);
 
      counter := counter + 1;
      dd_table(counter).table_name := tablename;
      dd_table(counter).column_name := colname;
      dd_table(counter).data_type := datatype;
    end if;
  end loop;
  n := counter;
  DBMS_SQL.CLOSE_CURSOR(handle);

EXCEPTION
  WHEN OTHERS THEN
    DBMS_SQL.CLOSE_CURSOR(handle);
    n := -1;
END get_columns;

procedure get_query_results(query_string IN varchar2,
                            ncols IN number,
                            column_types IN string9_table,
                            result OUT string1024_table,
                            nrows OUT number) AS

y1 string1024_table;
y2 number_table;
counter Integer;
i Integer;
handle Integer;
dbms_return Integer;
temp string1024_table;

BEGIN

  for i in 1 .. ncols loop
    y1(i) := '';
    y2(i) := 0;
  end loop;

  counter := 0;
  handle := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(handle,query_string,DBMS_SQL.V7);

  for i in 1 .. ncols loop
    if ((column_types(i) = 'VARCHAR2') or
        (column_types(i) = 'CHAR')) then
      DBMS_SQL.DEFINE_COLUMN(handle, i, y1(i) , 300);
    else
      DBMS_SQL.DEFINE_COLUMN(handle, i, y2(i));
    end if;
  end loop;

  dbms_return := DBMS_SQL.EXECUTE(handle);

  loop
     if DBMS_SQL.FETCH_ROWS(handle) = 0 then
       exit;
     else
       for i in 1 .. ncols loop
         if ((column_types(i) = 'VARCHAR2') or
             (column_types(i) = 'CHAR')) then
           DBMS_SQL.COLUMN_VALUE(handle, i, y1(i));
         else
           DBMS_SQL.COLUMN_VALUE(handle, i, y2(i));
         end if;
       end loop; 
       counter := counter + 1;
       temp(counter) := '';

       for i in 1 .. ncols loop
         if ((column_types(i) = 'VARCHAR2') or
             (column_types(i) = 'CHAR')) then
           temp(counter) := temp(counter) || 
                            rtrim(y1(i)) || '|';
         else
           temp(counter) := temp(counter) || 
                            rtrim(y2(i)) || '|';
         end if;
       end loop;
   end if;
  end loop;

  nrows := counter;
  result := temp;
  DBMS_SQL.CLOSE_CURSOR(handle);

  EXCEPTION
    WHEN OTHERS THEN
      DBMS_SQL.CLOSE_CURSOR(handle);
      nrows := -1;
END get_query_results;
end dsql;
/
show errors

The following package, called dsql_driver contains two procedures to drive the two procedures in the dsql package.

-- p21.sql 
CREATE OR REPLACE PACKAGE dsql_driver AS
procedure drive_get_columns(startch IN char);
procedure drive_get_query_results(
    query_string IN varchar2,
    ncols IN number,
    column_types IN dsql.string9_table);
END dsql_driver;
/

CREATE OR REPLACE PACKAGE BODY dsql_driver AS
procedure drive_get_columns(startch IN char) AS
  dd_table dsql.dd_table_type;
  i binary_integer;
  n number;
  prev varchar2(30);
  newentry boolean;
BEGIN
  dsql.get_columns(startch,dd_table,n);
  dbms_output.put_line('N =  ' || n);
  
  dbms_output.put_line('Tables Starting with ' || 
                        startch);
  dbms_output.put_line('------------------------');

  prev := '';
  newentry := false;
  for i in 1 .. dd_table.count loop
    if (i = 1) then
      dbms_output.put(dd_table(i).table_name || '(');
      prev := dd_table(i).table_name;
    elsif (prev != dd_table(i).table_name) then
      dbms_output.put_line(');');
      dbms_output.put(dd_table(i).table_name || '(');
      prev := dd_table(i).table_name;
      newentry := false;
    end if;
    if (newentry) then
      dbms_output.put(', ');
    end if;
    newentry := true;
    dbms_output.put(dd_table(i).column_name || ':');
    dbms_output.put(dd_table(i).data_type);
  end loop;  
  dbms_output.put_line(');');
END;

procedure drive_get_query_results
    (query_string IN varchar2,
     ncols IN number,
     column_types IN dsql.string9_table) AS
result dsql.string1024_table;
nrows number;
i binary_integer;

BEGIN
  dsql.get_query_results(query_string,ncols,column_types,
                         result,nrows);
  for i in 1 .. nrows loop
    dbms_output.put_line(result(i));
  end loop;
END;

END dsql_driver;
/
show errors

The following anonymous PL/SQL block calls the driver procedure for the get_query_results procedure.

-- p22.sql
DECLARE
 query_string varchar2(256);
 ncols number;
 column_types dsql.string9_table;
BEGIN
  query_string := 'select cno,cname,city ' ||
                  'from customers,zipcodes ' ||
                  'where customers.zip = zipcodes.zip';
  ncols := 3;
  column_types(1) := 'NUMBER';
  column_types(2) := 'VARCHAR2';
  column_types(3) := 'VARCHAR2';

  dsql_driver.drive_get_query_results(query_string,
                                      ncols,
                                      column_types);
END;
/

The following is a sample SQL*PLUS session to illustrate the above example.

SQL> execute display_table('customers');
1111 Charles 123 Main St. 67226 316-636-5555
2222 Bertram 237 Ash Avenue 67226 316-689-5555
3333 Barbara 111 Inwood St. 60606 316-111-1234

PL/SQL procedure successfully completed.

SQL> execute display_table('abcd');
NOT A VALID TABLE NAME

PL/SQL procedure successfully completed.

SQL> start p22
1111|Charles|Wichita|
2222|Bertram|Wichita|
3333|Barbara|Fort Dodge|

PL/SQL procedure successfully completed.

SQL>