Oracle/Apache pl/sql package modification

From: <travisp_at_bestnet.com>
Date: 13 Mar 2001 23:21:51 GMT
Message-ID: <98ma2f$ip$1@news.netmar.com>

Hi oracle misc's. I could use a little advice.

I have a package (please see below) that is run to generate html based on table contents. The submitting page allows the user to select the a table (different package for each table), a search term, a search type ( =, like, etc). We would like to now add instance to that as well. Links have been created in the home instance to point to the same tables in like instances (instances reflecting the dev/qa/prod model). The task is to modify these packages to be able to incorporate a from and where so that the result will now indicate an instance as well as the origional information.

I was thinking of using an IN and possibily a temporary table. Is there anyway
a Boolean can be set during the duplicate toss of a join?

This all seems like it's getting far more complicated than what it will return
for us, is there a better all-around way to do this (package calling a sp, maybe?)

Thanks

CREATE OR REPLACE PACKAGE dmcoluq AS
PROCEDURE showSQL
(qnwhere IN VARCHAR2);

  PROCEDURE showSelect
    (qnfrom IN VARCHAR2,
     qnwhere IN VARCHAR2);

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);

PROCEDURE runSQL
(theSQL IN VARCHAR2);

PROCEDURE exeSQL
(theSQL IN VARCHAR2);
END dmcoluq;
/
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;

end new, rbyron:
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.print('Technical Difficulty executing the query--please note the');
      htp.br;
      htp.print('error message and contact the Help Desk at X2525.');
      htp.centerClose;
      htp.br;
      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.                                               */
  /* new: rbyron                                               */
  /* ********************************************************* */

  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_dm;
/

Posted via NewsOne.Net: Free (anonymous) Usenet News via the Web ----- http://newsone.net/ -- Free reading and anonymous posting to 60,000+ groups NewsOne.Net prohibits users from posting spam. If this or other posts made through NewsOne.Net violate posting guidelines, email abuse_at_newsone.net

Received on Tue Mar 13 2001 - 17:21:51 CST