oracleaid

CREATE OR REPLACE PACKAGE     data_browser AUTHID current_user AS
  FUNCTION escape(iUrl IN VARCHAR2) RETURN VARCHAR2 deterministic;
  PROCEDURE browse(iSchema IN VARCHAR2, iTableName IN VARCHAR2, iPage PLS_INTEGER, iSize PLS_INTEGER, iSearch IN VARCHAR2 DEFAULT NULL);
  PROCEDURE edit(iSchema IN VARCHAR2, iTableName IN VARCHAR2, iRowId VARCHAR2);
  PROCEDURE save(iSchema IN VARCHAR2, iTableName IN VARCHAR2, iRowData IN VARCHAR2);
  PROCEDURE DELETE(iSchema IN VARCHAR2, iTableName IN VARCHAR2, iRowId IN VARCHAR2);
END data_browser;
/
CREATE OR REPLACE PACKAGE BODY     data_browser  AS
  -- const
  c_page CONSTANT PLS_INTEGER := 20;
  c_buffer_len CONSTANT PLS_INTEGER := 4000;
  c_field_max_size CONSTANT PLS_INTEGER := 200;

  c_app_call CONSTANT VARCHAR2(300) := 'f?p=' || NV('APP_ID');
  c_page_next CONSTANT VARCHAR2(300) := c_app_call || ':2:' || V('SESSION') || '::NO::p2_page,p2_size,p2_search:';
  c_page_edit CONSTANT VARCHAR2(300) := c_app_call || ':3:' || V('SESSION') || '::NO::p3_rowid:';

  -- global vars
  v_table all_tables.table_name%TYPE := 'none';
  v_schema all_tables.owner%TYPE := 'none';
  v_page_no PLS_INTEGER;
  v_page_size PLS_INTEGER;

  v_sql VARCHAR2(32767);
  v_search VARCHAR2(4000);

  -- collections
  v_cols_pk DBMS_UTILITY.lname_array;
  v_cols DBMS_UTILITY.lname_array;
  v_col_types DBMS_UTILITY.lname_array;
  v_col_lens DBMS_UTILITY.lname_array;
  v_vals apex_application_global.vc_arr2;

  -- strings parallel to collections
  v_col_list VARCHAR2(4000) := NULL;
  v_col_pk_list VARCHAR2(4000) := NULL;

  -- lobs
  v_xslt clob;
  v_html clob;

  -- handles
  v_queryCtx dbms_xmlquery.ctxType;
  v_cur_hdl  INTEGER;

--------------------------------------------------------------------------------
  FUNCTION escape(iUrl IN VARCHAR2)
    RETURN VARCHAR2 deterministic
  AS
  BEGIN
    RETURN UTL_URL.escape(iUrl, TRUE);
  END escape;

--------------------------------------------------------------------------------
  PROCEDURE set_pk_col_list
  AS
    i PLS_INTEGER;
  BEGIN
    v_cols_pk.DELETE;
    SELECT column_name
      BULK COLLECT INTO v_cols_pk
      FROM all_constraints aa, all_cons_columns cc
      WHERE aa.table_name = v_table AND
        aa.owner = v_schema AND
        constraint_type = 'P' AND
        aa.constraint_name = cc.constraint_name AND
        aa.table_name = cc.table_name AND
        aa.owner = cc.owner
      ORDER BY cc.position;
    IF v_cols_pk.COUNT = 0 THEN
      raise_application_error(-20667, 'No primary key columns found.');
    END IF;
    DBMS_UTILITY.table_to_comma(v_cols_pk, i, v_col_pk_list);
  END set_pk_col_list;

--------------------------------------------------------------------------------
  PROCEDURE set_col_list
  AS
    i PLS_INTEGER;
  BEGIN
    v_cols.DELETE;
    v_col_types.DELETE;
    v_col_lens.DELETE;
    SELECT column_name, data_type, data_length
      BULK COLLECT INTO v_cols, v_col_types, v_col_lens
      FROM all_tab_columns
      WHERE table_name = v_table AND
        owner = v_schema
      ORDER BY column_id;
    IF v_cols.COUNT = 0 THEN
      raise_application_error(-20667, 'No columns found.');
    END IF;
    DBMS_UTILITY.table_to_comma(v_cols, i, v_col_list);
  END set_col_list;

--------------------------------------------------------------------------------
  FUNCTION get_where_clause
    RETURN VARCHAR2
  AS
    s VARCHAR2(4000) := ' where rownum <= :v_r_next_page ';
  BEGIN
    IF v_search IS NOT NULL THEN
      s := s || ' and  instr(upper(' || REPLACE(v_col_list, ',', ' || ') || '), :v_search) > 0 ';
    END IF;
    RETURN s;
  END;

--------------------------------------------------------------------------------
  PROCEDURE build_sql_browse
  AS
  BEGIN
    -- construct sql
   /* XSU does not like bind variables outside of the where clause (in "select case" columns  or complex "where" conditions).
      It throws ORA-29532: Java call terminated by uncaught Java exception: java.sql.SQLException: Missing IN or OUT parameter at index:: 1
      So - simplify SQL */

    v_sql :=
      'select /*+ first_rows(' || TO_CHAR(v_page_size + 1) || ') */ a.*, ' ||
      '    case when min(r_num) over () = 1 then 1 else 0 end first_page, ' ||
      '    case when max(r_num) over () = r_max then 1 else 0 end last_page ' ||
      '  from (select ' ||
      v_col_list || ', data_browser.escape(rowid) r_id, rownum r_num, max(rownum) over () r_max from ' ||
      v_schema|| '.' || v_table;

    -- add search
    v_sql := v_sql || get_where_clause;

    -- order by
    v_sql := v_sql || ' order by ' || v_col_pk_list;

    v_sql := v_sql || ') a where r_num between :v_r_min and :v_r_max';

  END build_sql_browse;

--------------------------------------------------------------------------------
  PROCEDURE make_xslt_browse
  AS
    i PLS_INTEGER;
    s VARCHAR2(32767);
    v_link_next VARCHAR2(100);
    v_link_prev VARCHAR2(100);
  BEGIN
    -- main template
    s := '<?xml version="1.0" encoding="ISO-8859-1"?>
      <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
      <xsl:template match="/">
        <table class="t3standard" summary="Report">
        <xsl:apply-templates/>
        <xsl:apply-templates select="//ROW[1]" mode="pagination"/>
        </table>
      </xsl:template>';

    -- rowset
    s := s ||
      '<xsl:template match="ROWSET"> ' ||
      '<h2 class="t3RegionHeader">' || v_schema || '.' || v_table || '</h2>' ||
      '<tr> ';

    -- column headers
    s := s || '<th class="t3header" >&nbsp;</th>';
    FOR i IN v_cols.FIRST..v_cols.LAST
    LOOP
      s := s || '<th class="t3header" >' || v_cols(i) || '</th>';
    END LOOP;
    s := s || '</tr> <xsl:apply-templates></xsl:apply-templates> </xsl:template>';

    -- row template
    s := s || '<xsl:template match="ROW"> <xsl:for-each select="."> <tr>' ;

    -- column values
    s := s || '<td class="t3data" ><a href="' || c_page_edit || '{R_ID}" ><img src="/i/themes/theme_3/ed-item.gif" alt="Edit" /></a></td>';
    FOR i IN v_cols.FIRST..v_cols.LAST
    LOOP
      s := s || '<td class="t3data"> <xsl:value-of select="' || v_cols(i) || '"/></td> ';
    END LOOP;

    -- close the rows
    s := s || ' </tr> </xsl:for-each> </xsl:template>';

    -- pagination template
    v_link_next := c_page_next || TO_CHAR(v_page_no + 1) || ',' || v_page_size || ',' || v_search;
    v_link_prev := c_page_next || TO_CHAR(v_page_no - 1) || ',' || v_page_size || ',' || v_search;

    -- an after rows table with controls
    s := s || '<xsl:template match="ROW" mode="pagination">
      <tr> <td colspan="99" class="t3afterrows">
        <table style="float:left;text-align:right;" summary="pagination">
      <xsl:if test="FIRST_PAGE=0">
              <td>
                <table style="float:left;" border="0" cellspacing="0" cellpadding="0" summary="">
                  <tr>
                    <td> <a href="' || v_link_prev || '"> <img src="/i/jtfupree.gif" width="16" height="16" alt="Previous" /> </a> </td>' ||
                   '<td> <a href="' || v_link_prev || '" class="fielddata"> Previous</a> </td>
                  </tr>
                </table>
              </td>
      </xsl:if>
      <xsl:if test="LAST_PAGE=0">
              <td>
                <table style="float:left;" border="0" cellspacing="0" cellpadding="0" summary="">
                  <tr>
                    <td> <a href="' || v_link_next || '"> <img src="/i/jtfunexe.gif" width="16" height="16" alt="Next" /> </a> </td>' ||
                   '<td> <a href="' || v_link_next || '" class="fielddata"> Next</a> </td>
                  </tr>
                </table>
              </td>
      </xsl:if>
        </table>
        </td>
      </tr>
      </xsl:template>';

    -- close the xslt
    s := s || '</xsl:stylesheet>';
    DBMS_LOB.writeappend(v_xslt, LENGTH(s), s);
  END make_xslt_browse;

--------------------------------------------------------------------------------
  PROCEDURE make_xslt_edit
  AS
    i PLS_INTEGER;
    s VARCHAR2(32767);
  BEGIN
    -- static part
    s := '<?xml version="1.0" encoding="ISO-8859-1"?>
      <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
        <xsl:template match="/">
            <table class="t3PageBody" summary="Edit">
              <xsl:for-each select="/ROWSET/ROW">';

    -- column labels and edit boxes
    FOR i IN v_cols.FIRST..v_cols.LAST
    LOOP
      s := s || '<tr class="t3Body"><td align="right"><a class="t3optionalwithhelp">' || v_cols(i) || '</a></td>' ||
        '<td> <input type="text" class = "ub_input" name="F01" id="ub_' || v_cols(i) ||
        '" size="' || CASE WHEN v_col_lens(i) < c_field_max_size THEN  v_col_lens(i) ELSE c_field_max_size END ||
        '" maxlength="' || v_col_lens(i) ||'" value="{'|| v_cols(i) || '}" /> </td></tr>';
    END LOOP;

    -- hidden inputs - scn and rowid
    s := s || '<tr class="t3Body">
      <td> <input type="hidden" class = "ub_input" name="F01" id="ub_row_id" size="30" maxlength="100" value="{R_ID}" /> </td>
      <td> <input type="hidden" class = "ub_input" name="F01" id="ub_row_scn" size="30" maxlength="100" value="{R_SCN}" /> </td>
    </tr>';

    -- close the stylesheet
    s := s || '</xsl:for-each></table></xsl:template></xsl:stylesheet>';

    DBMS_LOB.writeappend(v_xslt, LENGTH(s), s);
  END make_xslt_edit;

--------------------------------------------------------------------------------
  PROCEDURE set_table(iSchema IN VARCHAR2, iTableName IN VARCHAR2)
  AS
  BEGIN
    v_table := UPPER(iTableName);
    v_schema := UPPER(iSchema);
  END;

--------------------------------------------------------------------------------
  PROCEDURE prepare_select(iPage IN NUMBER, iSize IN NUMBER, iSearch IN VARCHAR2)
  AS
  BEGIN
    -- save arguments
    v_page_no := iPage;
    v_page_size := iSize;
    v_search := UPPER(TRIM(iSearch));

    -- analyze the table
    set_col_list;
    set_pk_col_list;

    -- prepare the sql-to-xml query
    build_sql_browse;
    make_xslt_browse;
  END prepare_select;

--------------------------------------------------------------------------------
  PROCEDURE init_lobs
  AS
  BEGIN
    DBMS_LOB.createtemporary(v_xslt, FALSE);
    DBMS_LOB.createtemporary(v_html, FALSE);
  END init_lobs;

--------------------------------------------------------------------------------
  PROCEDURE cleanup_lobs
  AS
  BEGIN
    DBMS_LOB.freetemporary(v_xslt);
    DBMS_LOB.freetemporary(v_html);
  END cleanup_lobs;

--------------------------------------------------------------------------------
  PROCEDURE display_html
  AS
  BEGIN
    IF LENGTH(v_html) > 32767 THEN
      raise_application_error(-20767, 'Cannot display a page larger than 32K.');
    END IF;
    --dbms_output.put_line(v_html);
    htp.p(v_html);
  END display_html;

--------------------------------------------------------------------------------
  PROCEDURE display(iMsg IN VARCHAR2)
  AS
  BEGIN
    --dbms_output.put_line(iMsg);
    htp.p(iMsg);
  END display;

--------------------------------------------------------------------------------
  PROCEDURE display_error
  AS
  BEGIN
    display('<br>Error stack: ' || DBMS_UTILITY.format_error_stack || '.<br>');
    display('<br>Call stack: ' || REPLACE(DBMS_UTILITY.format_call_stack, CHR(10), '<br>') || '.<br>');
    display('<br>Error backtrace: ' || DBMS_UTILITY.format_error_backtrace || '.<br>');
  END display_error;

--------------------------------------------------------------------------------
  PROCEDURE browse(iSchema IN VARCHAR2, iTableName IN VARCHAR2, iPage PLS_INTEGER, iSize PLS_INTEGER, iSearch VARCHAR2 DEFAULT NULL)
  AS
  BEGIN
    init_lobs;
    set_table(iSchema, iTableName);
    prepare_select(iPage, iSize, iSearch);

    -- create a new query context
    v_queryCtx := dbms_xmlquery.newContext(v_sql);
    dbms_xmlquery.setxslt(v_queryCtx, v_xslt);

    dbms_xmlquery.setraiseexception(v_queryCtx, TRUE);
    dbms_xmlquery.propagateoriginalexception(v_queryCtx, TRUE);

    dbms_xmlquery.setBindValue(v_queryCtx, 'v_r_min', (iPage - 1) * iSize + 1);
    dbms_xmlquery.setBindValue(v_queryCtx, 'v_r_max', iPage * iSize);
    dbms_xmlquery.setBindValue(v_queryCtx, 'v_r_next_page', iPage * iSize + 1);
    IF v_search IS NOT NULL THEN
      dbms_xmlquery.setBindValue(v_queryCtx, 'v_search', v_search);
    END IF;
    dbms_xmlquery.getxml(v_queryCtx, v_html);
    dbms_xmlquery.closeContext(v_queryCtx);
    display_html;
    cleanup_lobs;
  EXCEPTION
    WHEN OTHERS THEN
      display_error;
      cleanup_lobs;
  END browse;

--------------------------------------------------------------------------------
  PROCEDURE edit(iSchema IN VARCHAR2, iTableName IN VARCHAR2, iRowId VARCHAR2)
  AS
  BEGIN
    init_lobs;
    set_table(iSchema, iTableName);
    set_col_list;
    make_xslt_edit;
    -- if rowid is null - we are creating a new record
    IF iRowId IS NULL THEN
      v_sql := 'select ' || LPAD('null', v_cols.COUNT * 4 + 1 * (v_cols.COUNT - 1), 'null,') || ', null R_SCN, null R_ID from ' ||
        iSchema || '.' || iTableName || ' a  where rownum = 1';
    -- if rowid is NOT null - we are editing a record
    ELSE
      v_sql := 'select ' || v_col_list || ', ora_rowscn R_SCN, rowid R_ID from ' ||
        iSchema || '.' || iTableName || ' a  where rowid = :r_id';
    END IF;
    v_queryCtx := dbms_xmlquery.newContext(v_sql);
    dbms_xmlquery.setxslt(v_queryCtx, v_xslt);

    IF iRowId IS NOT NULL THEN
      dbms_xmlquery.setBindValue(v_queryCtx, 'r_id', UTL_URL.unescape(iRowId));
    END IF;

    dbms_xmlquery.getxml(v_queryCtx, v_html);
    dbms_xmlquery.closeContext(v_queryCtx);
    display_html;
    cleanup_lobs;
  EXCEPTION
    WHEN OTHERS THEN
      display_error;
      cleanup_lobs;
  END edit;

--------------------------------------------------------------------------------
  PROCEDURE run_sql_save(iRowId IN ROWID, iScn IN VARCHAR2)
  AS
    i PLS_INTEGER;
    r PLS_INTEGER;
  BEGIN
    -- build an insert statement
    IF iRowId IS NULL THEN
      v_sql := 'insert into ' || v_schema || '.' || v_table || ' (' || v_col_list ||
        ') values (';
      -- add bind variables
      FOR i IN 1..v_cols.COUNT
      LOOP
        v_sql := v_sql || ':v_' || i || ',';
      END LOOP;
      -- remove the last comma
      v_sql := SUBSTR(v_sql, 1, LENGTH(v_sql) - 1);
      -- close parenthesis
      v_sql := v_sql || ')';

    -- or an update statement
    ELSE
      v_sql := 'update ' || v_schema || '.' || v_table || ' set ';
      -- add column_names and bind variables
      FOR i IN 1..v_cols.COUNT
      LOOP
        v_sql := v_sql || v_cols(i) || '=:v_' || i || ',';
      END LOOP;
      -- remove the last comma
      v_sql := SUBSTR(v_sql, 1, LENGTH(v_sql) - 1);
      -- add the where clause
      v_sql := v_sql || ' where rowid = :v_r_id and ora_rowscn = :v_r_scn';
    END IF;

    -- parse the statement
    v_cur_hdl := DBMS_SQL.open_cursor;
    DBMS_SQL.parse(v_cur_hdl, v_sql, DBMS_SQL.native);

    -- supply binds
    -- update, fixed vars: rowid and row scn are the penultimate and the last entries
    IF iRowId IS NOT NULL THEN
      DBMS_SQL.bind_variable(v_cur_hdl, ':v_r_id', iRowId);
      DBMS_SQL.bind_variable(v_cur_hdl, ':v_r_scn', iScn);
    END IF;

    -- transient vars: one per column
    -- indexes of column names and value elements must match
    FOR i IN 1..v_cols.COUNT
    LOOP
      DBMS_SQL.bind_variable(v_cur_hdl, ':v_' || i, v_vals(i));
    END LOOP;

    -- execute
    r := DBMS_SQL.EXECUTE(v_cur_hdl);
    DBMS_SQL.close_cursor(v_cur_hdl);

    -- check results
    IF (r != 1) THEN
      raise_application_error(-20778, 'Expected to process 1 row. Instead, processed "' || r || '".');
    END IF;

    -- finish
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      display_error;
      ROLLBACK;
      RAISE;
  END run_sql_save;

--------------------------------------------------------------------------------
  PROCEDURE save(iSchema IN VARCHAR2, iTableName IN VARCHAR2, iRowData IN VARCHAR2)
  AS
     vRowId ROWID;
  BEGIN
    -- prepare collections
    set_table(iSchema, iTableName);
    set_col_list;

    -- extract data
    v_vals := apex_util.string_to_table(iRowData);

    -- check: the number of values should match the number of columns + 2 (rowid and rowscn)
    IF (v_vals.COUNT != v_cols.COUNT + 2) THEN
      raise_application_error(-20667, 'Expected "' || v_cols.COUNT + 2 ||
        '" elements in the array of values. Instead, got "' || v_vals.COUNT ||
        '". Data="' || iRowData || '". Columns="' || v_col_list || '".');
    END IF;

    -- unescape rowid
    vRowId := UTL_URL.unescape(v_vals(v_vals.LAST - 1));

    -- run the statement
    run_sql_save(vRowId, v_vals(v_vals.LAST));
  EXCEPTION
    WHEN OTHERS THEN
      display_error;
      display(iRowData);
      RAISE;
  END save;

--------------------------------------------------------------------------------
  PROCEDURE DELETE(iSchema IN VARCHAR2, iTableName IN VARCHAR2, iRowId IN VARCHAR2)
  AS
  BEGIN
    EXECUTE IMMEDIATE 'delete from ' || iSchema || '.' || iTableName ||
      ' where rowid = :v_r_id' using UTL_URL.unescape(iRowId);
    COMMIT;
  END DELETE;
END data_browser;
/