RE: anyone use pipelined functions?

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Wed, 31 Dec 2003 07:09:27 -0800
Message-ID: <F001.005DB5CA.20031231070927@fatcity.com>

Ryan,

I use it extensively ... for some of the utilities I wrote for application support ... here is one sample ...

This utility shows errors in the pl/sql code, what's different is not only it shows the errors, but also shows the source lines and exact location of the error.

To test, install on test server under an account that has select privs on

sys.error$, sys.source$, sys.obj$, sys.user$ tables ...

Usage is visible in the output section ... please NOTE the SET commands, they are very useful in this situation

----------------------------   start of script ----------------------------------

DROP TYPE UTIL$STRINGS_TBL
/
DROP TYPE UTIL$STRINGS
/
CREATE OR REPLACE TYPE UTIL$STRINGS AS OBJECT (string_text VARCHAR2(4000)) /
CREATE OR REPLACE TYPE UTIL$STRINGS_TBL AS TABLE OF UTIL$STRINGS /
FUNCTION show_all_errors

   RETURN UTIL$STRINGS_TBL pipelined AS
--
--

  CURSOR cur_01 IS
    WITH
    o$ AS (SELECT o.obj# obj#, DECODE (o.TYPE#,

                  2,'TABLE ',4,'VIEW ',7,'PROCEDURE ',8,'FUNCTION ', 9,'PACKAGE ',
                  11,'PACKAGE ',12,'TRIGGER ',13,'TYPE ',14,'TYPE ',29,'JAVA CLASS ',
                  32, 'INDEXTYPE ',33,'OPERATOR ',' ') ||
                  '"' || u.NAME || '"."' || o.NAME || '"' now_compiling
             FROM sys.OBJ$ o, sys.USER$ u WHERE status > 1 AND u.USER# = o.owner#),
    s$ AS (SELECT e.obj#, e.SEQUENCE# err_seq, '[#' ||trim(TO_CHAR(e.line,'09999')) ||
                  '] ' || REPLACE(s.SOURCE,CHR(10),' ') src_text,
                  LPAD('_.', (e.position#-s.ltlen)+7, '_.') || '-^ ' || e.text err_text
             FROM (SELECT * FROM sys.ERROR$ WHERE text NOT LIKE 'PL/SQL% ignored%'
                    ORDER BY obj#, SEQUENCE#, line, position#) e,
          (SELECT obj#, line, (LENGTH(REPLACE(SOURCE,CHR(9),' ')) -
                  LENGTH(LTRIM(REPLACE(SOURCE,CHR(9),' ')))) ltlen,
                  LTRIM(SOURCE) SOURCE FROM sys.SOURCE$) s
            WHERE s.obj# = e.obj# AND s.line = e.line)
    SELECT o$.now_compiling col1, s$.src_text col2, s$.err_text col3
      FROM o$, s$
     WHERE o$.obj# = s$.obj#
     ORDER BY o$.now_compiling, s$.err_seq;

--

  szLStr VARCHAR2(100);
--

  PRAGMA AUTONOMOUS_TRANSACTION;
--
--

BEGIN
--

  EXECUTE IMMEDIATE 'alter session set cursor_sharing=exact';

  pipe ROW (UTIL$STRINGS('*****'));
  pipe ROW (UTIL$STRINGS('***** Displaying all errors in the database..'));
  pipe ROW (UTIL$STRINGS('*****'));

--

  FOR cErr IN cur_01
  LOOP
    EXIT WHEN cur_01%NOTFOUND;
    IF szLStr IS NULL THEN
      szLStr := cErr.col1;
    END IF;
    IF (szLStr <> cErr.col1) OR (cur_01%rowcount = 1) THEN

      pipe ROW (UTIL$STRINGS(''));
      pipe ROW (UTIL$STRINGS('*****'));
      pipe ROW (UTIL$STRINGS('***** Listing Errors For "' || cErr.col1 || '"'));
      pipe ROW (UTIL$STRINGS('*****'));

    END IF;
    pipe ROW (UTIL$STRINGS(cErr.col2));
    pipe ROW (UTIL$STRINGS(cErr.col3));
    szLStr := cErr.col1;
  END LOOP;
--

  IF cur_01%isopen THEN
    CLOSE cur_01;
  END IF;
--

  pipe ROW (UTIL$STRINGS('*****'));
  pipe ROW (UTIL$STRINGS('***** End Of Error Listing ...'));
  pipe ROW (UTIL$STRINGS('*****'));

--

pipe ROW (UTIL$STRINGS('Show All Errors, ESPN Oracle Utilities, © ESPN 2003')); RETURN;
--

END show_all_errors;
/

----------------------------   end of script ----------------------------------

Output looks like this ...

<output>
09:56:42 SQL> set line 200 trimspool on heading off pagesize 0 09:56:53 SQL> select * from table(show_all_errors()); -- this is how you call it ...

Displaying all errors in the database..

Listing Errors For "PACKAGE "SQLAB"."QUEST_SL_ERRORS_PKG""
[#00011] INSERT INTO quest_sl_errors (collector,proc_name,timestamp, _._._._._._._._._._.-^ PL/SQL: ORA-00942: table or view does not exist [#00022] INSERT INTO quest_sl_errors (collector,proc_name,timestamp, _._._._._._._._._._.-^ PL/SQL: ORA-00942: table or view does not exist

Listing Errors For "PACKAGE "SQLAB"."QUEST_SL_SCHEDULED_COLLECTOR""
[#00035] DELETE from quest_sl_errors WHERE collector=collector_name; _._._._._._._._._._.-^ PL/SQL: ORA-00942: table or view does not exist

Listing Errors For "PACKAGE "SQLAB"."QUEST_SL_USER_MANAGER""
[#00299] quest_sl_errors where proc_name = user_p; _._._._.-^ PL/SQL: ORA-00942: table or view does not exist
End Of Error Listing ...
Show All Errors, ESPN Oracle Utilities, © ESPN 2003

27 rows selected.

09:57:15 SQL>
</output>

In the output [#00035] is the actual source line number in the code

I have many more utilities like this, e.g.

1. a utility that compiles all invalid objects (in multiple passes)
2. a utility that locates given user in our RAC
3. utility that shows active locks in the system
4. script that displays stats for the table and its indexes in a hierarchy ...

and many more ... most of these use pipelining so data is visible instead of having to wait.

another Plus is there are no pesky limits of dbms_output ...

Special note: preserve the cursor_sharing line ... there is a bug in 9202 wieh using CS=FORCE and if you use pipelined function consecutively in the session they fail with some error that confuses the heck out of you. This alter session line will avoid that issue. Let me know if you need any clarification ...

Happy New Year everyone ...
HTHs
Raj

Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !

-----Original Message-----
Sent: Wednesday, December 31, 2003 9:24 AM To: Multiple recipients of list ORACLE-L

I read the little blurb in the 9i new features on it. The example there doesnt seem very useful. What have people used it for?

any good articles with good examples on this?

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jamadagni, Rajendra
  INET: Rajendra.Jamadagni_at_espn.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Dec 31 2003 - 09:09:27 CST