Oracle / Apache webserver package modification question

From: Travis Paakki <trp9_at_MailAndNews.com>
Date: Mon, 12 Mar 2001 15:36:47 -0500
Message-ID: <3ABE6A69@MailAndNews.com>

I have the following Oracle web server package designed to let developers not
need to log into the instance to retrieve column and table structure information, just use a browser:
...package definition...
CREATE OR REPLACE PACKAGE BODY dmcoluq AS

 /* This procedure shows the SQL                                */

PROCEDURE showSQL

    (qnwhere IN VARCHAR2)
    IS
     qnfrom VARCHAR2(255);
  BEGIN

    htp.centerOpen;
    htp.hr;
    htp.tableOpen('0');

qnfrom := 'sys.dba_tab_columns';

Show the select statement showSelect(qnfrom, qnwhere);
Add the Order By htp.tableRowOpen; htp.tableData(htf.bold('ORDER BY'), 'RIGHT', cnowrap=>'NOWRAP'); htp.tableData('column_name, table_name, owner'); htp.tableRowClose;
End with a ; htp.tableRowOpen; htp.tableData(htf.bold(';'), ccolspan=>'2'); htp.tableRowClose;

    htp.tableClose;
    htp.br;
    htp.centerClose;
  END showSQL;
  /* This procedure shows the Select statement                   */
  /* *********************************************************** */

  PROCEDURE showSelect
    (qnfrom IN VARCHAR2,
     qnwhere IN VARCHAR2)
    IS
     qnw VARCHAR2(255);
  BEGIN
    htp.tableRowOpen;

      htp.tableData(htf.bold('SELECT'), 'RIGHT', cnowrap=>'NOWRAP');
      htp.tableData('column_name');

htp.tableRowClose;

    dmobjs.makeColumn('','table_name');
    dmobjs.makeColumn('','owner');
    dmobjs.makeColumn('','dcl');

    htp.tableRowOpen;
      htp.tableData(htf.bold('FROM'), 'RIGHT', cnowrap=>'NOWRAP');
      htp.tableData(qnfrom);

htp.tableRowClose;
htp.tableRowOpen;

      htp.tableData(htf.bold('WHERE'), 'RIGHT', cnowrap=>'NOWRAP');
      -- Get rid of the word WHERE
      qnw := REPLACE(qnwhere, 'WHERE', '');
      htp.tableData(qnw);

    htp.tableRowClose;
  END showSelect;
  /* This procedure is the main procedure. It runs the query. */

  /* *********************************************************** */
  PROCEDURE runQuery
    (qName            IN VARCHAR2 DEFAULT NULL,
     matchType        IN VARCHAR2 DEFAULT 'BEGINSWITH',
     showSQLTop       IN BOOLEAN DEFAULT FALSE,
     showSQLBottom    IN BOOLEAN DEFAULT TRUE,
     showStatsTop     IN BOOLEAN DEFAULT TRUE,
     showStatsBottom  IN BOOLEAN DEFAULT FALSE)
    IS
      err_msg          VARCHAR2(200);
      qn               VARCHAR2(255);
      qnselect         VARCHAR2(255);
      qnfrom           VARCHAR2(255);
      qnwhere          VARCHAR2(255);
      qnob             VARCHAR2(255);
      qnsql            VARCHAR2(1000);
      HTITLE           VARCHAR2(100);
      hheader          VARCHAR2(100);
      glsql            VARCHAR2(500);

BEGIN
qn := UPPER(qName);

Create the SQL variables qnselect := 'SELECT column_name' || ',table_name' || ',owner' || ',get_real(table_name, owner, column_id) dcl' ; qnob := ' ORDER BY column_name, table_name, owner';
Create the where clause IF (matchType = 'EXACT') THEN qnwhere := ' WHERE UPPER(column_name) = '||''''||qn||''''; ELSIF (matchType = 'BEGINSWITH') THEN qnwhere := ' WHERE UPPER(column_name) LIKE '||''''||qn||'%'||''''; ELSIF (matchType = 'LIKE') THEN qnwhere := ' WHERE UPPER(column_name) LIKE '||''''||'%'||qn||'%'||''''; ELSIF (matchType = 'IN') THEN qnwhere := ' UPPER(column_name) IN ('||''''||qn||''''||')'; ELSIF (matchType = 'ALL') THEN qnwhere := ''; END IF; qnfrom := ' FROM sys.dba_tab_columns';
Combine the SQL statement qnsql := qnselect || qnfrom || qnwhere;
Add the Order By qnsql := qnsql || qnob;
Show the header htitle := 'Columns Where Used by Column, Table'; hheader := 'Columns Where Used by Column, Table'; dmobjs.makeHeader(htitle, hheader);
Retrieve the Glossary Definition, if any.
new, rbyron: glSQL := 'SELECT jx.jxterm.jx_term_nm' || ' ,jx.jxterm.jx_term_sdesc ' || ' ,jx.jxterm.jx_term_desc ' || 'FROM jx.jxterm ' || 'WHERE jx.jxterm.jx_term_nm = ' || '''' || qn || '''';
Show the rows selected htp.centerOpen; htp.hr; htp.tableOpen(0,cattributes=>'BORDER=1 WIDTH=95%');
Run the SQL exeSQL(glsql); htp.tableClose; htp.centerClose;
Show the stats at the top if that option is selected IF showStatsTop THEN dmobjs.showStats(qnsql); END IF;
Show the SQL at the top if that option is selected IF showSQLTop THEN showSQL(qnwhere); END IF;
Show the rows selected htp.centerOpen; htp.hr; htp.tableOpen(0,cattributes=>'BORDER=1 WIDTH=95%');
Run the SQL runSQL(qnsql); htp.tableClose; htp.centerClose;
Show the stats at the bottom if that option is selected IF (showStatsBottom = TRUE) THEN dmobjs.showStats(qnsql); END IF;
Show the SQL at the bottom if that option is selected IF (showSQLBottom = TRUE) THEN showSQL(qnwhere); END IF;
Show the footer dmobjs.makeFooter; EXCEPTION WHEN NO_DATA_FOUND THEN htp.centerOpen; htp.br; htp.print('Your query returned no results.'); htp.centerClose; htp.br; WHEN OTHERS THEN htp.centerOpen; htp.fontOpen('#FF0000', csize=>'+2'); htp.bold('Error'); htp.fontClose; htp.br; htp.br; err_msg := SQLERRM; htp.print(err_msg); htp.br; htp.br; htp.centerClose; htp.br; END runquery; /* ********************************************************* */ /* This procedure runs the SQL passed in and displays it in an HTML table */ PROCEDURE runSQL (theSQL IN VARCHAR2) IS cursor_name INTEGER; ret INTEGER; savSQL VARCHAR2(500); dtn VARCHAR2(20); ddt VARCHAR2(30); dow VARCHAR2(12); dcl VARCHAR2(30); newdcl VARCHAR2(30); BEGIN
Create and open the cursor cursor_name := DBMS_SQL.OPEN_CURSOR;
Parse the SQL DBMS_SQL.PARSE(cursor_name, theSQL, DBMS_SQL.V7);
Bind the column names to variables DBMS_SQL.DEFINE_COLUMN(cursor_name, 1, dtn, 20); DBMS_SQL.DEFINE_COLUMN(cursor_name, 2, ddt, 30); DBMS_SQL.DEFINE_COLUMN(cursor_name, 3, dow, 12); DBMS_SQL.DEFINE_COLUMN(cursor_name, 4, dcl, 30);
Run the cursor ret := DBMS_SQL.EXECUTE(cursor_name);
Print the header htp.tableRowOpen; htp.tableHeader('Column Name'); htp.tableHeader('Table/View Name'); htp.tableHeader('Owner'); htp.tableHeader('Source'); htp.tableRowClose;

LOOP

Fetch the rows one at a time IF DBMS_SQL.FETCH_ROWS(cursor_name) > 0 THEN
- Get the values into the variables DBMS_SQL.COLUMN_VALUE(cursor_name, 1, dtn); DBMS_SQL.COLUMN_VALUE(cursor_name, 2, ddt); DBMS_SQL.COLUMN_VALUE(cursor_name, 3, dow); DBMS_SQL.COLUMN_VALUE(cursor_name, 4, dcl);
- Add space characters if the column is empty IF ( (ASCII(SUBSTR(dtn, 1, 1)) <= 32) OR (dtn IS NULL) ) THEN dtn := '&'||'nbsp;'; END IF;

        IF ((ASCII(SUBSTR(ddt, 1, 1)) <= 32) OR (ddt IS NULL)) THEN
          ddt := '&'||'nbsp;';
        END IF;

        IF dcl IS NULL THEN
           newdcl := '&'||'nbsp;';
        ELSE
           dcl := UPPER(dcl);
           newdcl := '';
           FOR num_char IN 1..LENGTH(dcl)
           LOOP
              IF ((ASCII(SUBSTR(dcl,num_char,1)) >= 65) AND
                  (ASCII(SUBSTR(dcl,num_char,1)) <= 90)) OR
                 ((ASCII(SUBSTR(dcl,num_char,1)) >= 48) AND
                  (ASCII(SUBSTR(dcl,num_char,1)) <= 57)) OR
                 (ASCII(SUBSTR(dcl,num_char,1)) = 95) THEN
                newdcl := newdcl||SUBSTR(dcl,num_char,1);
              END IF;
           END LOOP;
           IF LENGTH(newdcl) < 1 THEN
              newdcl := '&'||'nbsp;';
           END IF;
        END IF;
        -- Print the row
        htp.tableRowOpen;
        htp.tableData('<A

href=http:/www_dm/plsql/dm.dmrunqb.runQuery?querytype=dmcoluq&' || 'qname=' || dtn || '>' || dtn || '</A>', cattributes=>'VALIGN=TOP');

htp.tableData('<A
href=http:/www_dm/plsql/dm.dmrunqb.runQuery?querytype=dmdictq&' || 'qname=' || ddt || '>' || ddt || '</A>', cattributes=>'VALIGN=TOP');

        htp.tableData(dow,  cattributes=>'VALIGN=TOP');
--      htp.tableData('<A

href=http:/www_dm/plsql/dm.dmrunqb.runQuery?querytype=dmcoluq&' || 'qname=' || dow || '>' || dow || '</A>', cattributes=>'VALIGN=TOP');

htp.tableData('<A
href=http:/www_dm/plsql/dm.dmrunqb.runQuery?querytype=dmcoluq&' || 'qname=' || newdcl || '>' || newdcl || '</A>', cattributes=>'VALIGN=TOP');

        htp.tableRowClose;
      ELSE
        -- No more rows
        EXIT;
      END IF;

END LOOP;

Close the cursor DBMS_SQL.CLOSE_CURSOR(cursor_name);

EXCEPTION
WHEN OTHERS THEN

      IF DBMS_SQL.IS_OPEN(cursor_name) THEN
        DBMS_SQL.CLOSE_CURSOR(cursor_name);
      END IF;
      RAISE;

END runSQL;

  /* ********************************************************* */

  /* This procedure runs the SQL to retrieve the glossary definition */   PROCEDURE exeSQL
    (theSQL IN VARCHAR2)
    IS

      cursor_name  INTEGER;
      ret          INTEGER;
      savSQL       VARCHAR2(500);
      dtn          VARCHAR2(100);
      dsh          VARCHAR2(100);
      ddt          VARCHAR2(5000);

BEGIN

Create and open the cursor cursor_name := DBMS_SQL.OPEN_CURSOR;
Parse the SQL DBMS_SQL.PARSE(cursor_name, theSQL, DBMS_SQL.V7);
Bind the column names to variables DBMS_SQL.DEFINE_COLUMN(cursor_name, 1, dtn, 100); DBMS_SQL.DEFINE_COLUMN(cursor_name, 2, dsh, 100); DBMS_SQL.DEFINE_COLUMN(cursor_name, 3, ddt, 5000);
Run the cursor ret := DBMS_SQL.EXECUTE(cursor_name);
Print the header htp.tableRowOpen; htp.tableHeader('Term '); htp.tableHeader('Cross-Reference'); htp.tableHeader('Glossary Description'); htp.tableRowClose; LOOP
- Fetch the rows one at a time IF DBMS_SQL.FETCH_ROWS(cursor_name) > 0 THEN
  - Get the values into the variables DBMS_SQL.COLUMN_VALUE(cursor_name, 1, dtn); DBMS_SQL.COLUMN_VALUE(cursor_name, 2, dsh); DBMS_SQL.COLUMN_VALUE(cursor_name, 3, ddt);
  - Add space characters if the column is empty IF ( (ASCII(SUBSTR(dtn, 1, 1)) <= 32) OR (dtn IS NULL) ) THEN dtn := '&'||'nbsp;'; END IF; IF ((ASCII(SUBSTR(ddt, 1, 1)) <= 32) OR (ddt IS NULL)) THEN ddt := '&'||'nbsp;'; END IF;
  - Print the row htp.tableRowOpen; htp.tableData(dtn, cattributes=>'VALIGN=TOP'); htp.tableData(dsh, cattributes=>'VALIGN=TOP'); htp.tableData(ddt, cattributes=>'VALIGN=TOP'); htp.tableRowClose; ELSE
  - No more rows EXIT; END IF; END LOOP;
Close the cursor DBMS_SQL.CLOSE_CURSOR(cursor_name);

EXCEPTION
WHEN OTHERS THEN

      IF DBMS_SQL.IS_OPEN(cursor_name) THEN
        DBMS_SQL.CLOSE_CURSOR(cursor_name);
      END IF;
      RAISE;

END exeSQL;
END dmcoluq;
/
GRANT EXECUTE ON dmcoluq TO www_blah;
/

We have grown into a dev/qa/prod environment, with instances for each. The package is homed in dev and I am establishing links to the other instances. The submitting web page allows the user to request definitions for prod, qa, or dev, all three, and the requirements state that I indicate the row's home instance. Origionally I thought of using an outter join but I still didn't see how I would get the instance indicators present. Has anyone tried something like this? Is it really as many new lines of code to the from statements as I'm thinking? Any suggestions?

Get your FREE web-based e-mail and newsgroup access at:

                http://MailAndNews.com

Create a new mailbox, or access your existing IMAP4 or POP3 mailbox from anywhere with just a web browser.

Received on Mon Mar 12 2001 - 14:36:47 CST