DSQL BODY

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;


  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;
--dbms_output.put_line('temp(' || counter || ')= ' || temp(counter));
--     counter := counter + 1;

   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;