[ Pobierz całość w formacie PDF ]
.These are overloaded procedures;overloading is discussed in the section "Module Overloading" in Chapter 15, Procedures and Functions.Here are some examples of these programs in use:1.Request activation of trace on the SFEUERST account for level 16:SQL> exec trace.filter_by ('sfeuerst', 16);2.Request activation of trace on the LELLISON account for levels 16-25:SQL> exec trace.filter_by ('lellsion', 16, 25);3.Request activation of trace on the WGATES account for levels 2, 6, 10, and 55:SQL> exec trace.filter_by ('wgates', '2,6,10,55');Gee, that looks easy enough to use! Of course, the devil is in the details.In all three cases, the proceduresmust convert the levels provided into a string with a consistent format so that the trace.startup program (andothers) can query the tracetab_activate table and properly interpret the request.26.4.1 From Idea to ImplementationSure, this section doesn't provide all of the code behind the interface.However, don't underestimate theimportance of getting that interface right.Too many of us spend too little time thinking about how ourprograms will be used and how the interface to our functionality would be best constructed.Instead, we areeager to dive into the implementation of the programs.Without question, package bodies will be needed for916 [Appendix A] What's on the Companion Disk?most every package specification you ever write, but if your specification is poorly designed, it will be hard tounderstand and use -- which means that most likely your code will not be used.26.3 Free Format Filtering 26.5 Quick-and-DirtyTracingCopyright (c) 2000 O'Reilly & Associates.All rights reserved.917 Chapter 26Tracing PL/SQL Execution26.5 Quick-and-Dirty TracingI have found that in many situations, PL/SQL developers don't have the time or the access to tools to performcomprehensive tracing.Instead, they just need to get more information out of a specific package or program,and they need it right away.Let's take a look at the options you have for some "quick-and-dirty" tracing.First of all, there isDBMS_OUTPUT.PUT_LINE, a built-in which generates output from within a PL/SQL program.Forexample, if I executed in SQL*Plus the following block:BEGINFOR emp_rec IN(SELECT ename, sal FROM emp ORDER BY sal DESC)LOOPDBMS_OUTPUT.PUT_LINE('Employee ' || emp_rec.ename || ' earns ' ||TO_CHAR (emp_rec.sal) || ' dollars.');END LOOP;END;/I would see the following output when the program terminated:Employee KING earns 5000 dollars.Employee SCOTT earns 3000 dollars.Employee JONES earns 2975 dollars.Employee ADAMS earns 1100 dollars.Employee JAMES earns 950 dollars.You will only see trace information from DBMS_OUTPUT in SQL*Plus if you issue the followingcommand:SQL> set serveroutput onThis will enable the package within SQL*Plus.You can also set the buffer which contains trace informationto its maximum size of 1MB as follows:SQL> set serveroutput on size 1000000Finally, if you are running Oracle Server 7.3 and above, you can also request that output fromDBMS_OUTPUT.PUT_LINE be "wrapped" so that leading blanks are not trimmed and long lines arewrapped within the SQL*Plus linesize:SQL> set serveroutput on size 1000000 format wrappedSo DBMS_OUTPUT does give you the flexibility of embedding trace calls inside your program, but onlyseeing the output when you have SET SERVEROUTPUT ON.It is, unfortunately, an all-or-nothingproposition with this package.You see no messages or you see all messages.UsingDBMS_OUTPUT.PUT_LINE "in the raw" as a trace mechanism therefore leaves much to be desired.(Well,to be honest, when talking about the inadequacies of DBMS_OUTPUT, one would also have to mention that918 [Appendix A] What's on the Companion Disk?it can only display a maximum of 255 bytes per call, that it does not display Booleans or combinations ofdata, and that it will not work in the Oracle Developer/2000 environment nor in Oracle WebServer.)[2][2] See Chapter 7 of my book on packages, Advanced Oracle PL/SQL Programming withPackages, for details about the usage of DBMS_OUTPUT.PUT_LINE.Ideally, you would like to be able to set up a trace mechanism so that you can see information about only thispackage or that procedure.The best way to do that is to set up a "toggle" within a package.Let's step througha simple example to make the technique clear.Suppose I have a package which assigns a value to a package variable (which must be defined in the packagespecification) using dynamic SQL execution.(This is similar to the indirect referencing available in OracleForms with COPY and NAME_IN.) The specification and body of such a package is shown below:/* filename on companion disk: dynvar.spp */CREATE OR REPLACE PACKAGE dynvarISPROCEDURE assign (var_in IN VARCHAR2, val_in IN VARCHAR2);FUNCTION val (var_in IN VARCHAR2) RETURN VARCHAR2;END dynvar;/CREATE OR REPLACE PACKAGE BODY dynvarISPROCEDURE assign (var_in IN VARCHAR2, val_in IN VARCHAR2)IScur INTEGER;fdbk INTEGER;BEGINcur := DBMS_SQL.OPEN_CURSOR;DBMS_SQL.PARSE (cur,'BEGIN ' || var_in || ' := :val; END;', DBMS_SQL.NATIVE);DBMS_SQL.BIND_VARIABLE (cur, 'val', val_in, 2000);fdbk := DBMS_SQL.EXECUTE (cur);DBMS_SQL.CLOSE_CURSOR (cur);END;FUNCTION val (var_in IN VARCHAR2) RETURN VARCHAR2IScur INTEGER;fdbk INTEGER;retval VARCHAR2(2000);BEGINcur := DBMS_SQL.OPEN_CURSOR;DBMS_SQL.PARSE(cur, 'BEGIN :val := ' || var_in || '; END;', DBMS_SQL.NATIVE);DBMS_SQL.BIND_VARIABLE (cur, 'val', var_in, 2000);fdbk := DBMS_SQL.EXECUTE (cur);DBMS_SQL.VARIABLE_VALUE (cur, 'val', retval);DBMS_SQL.CLOSE_CURSOR (cur);RETURN retval;END;END dynvar;/Here is a little test package and some program calls in SQL*Plus to give you a sense of how it would work:CREATE OR REPLACE PACKAGE TSTVARISstr1 varchar2(2000);str2 varchar2(2000);END;/919 [Appendix A] What's on the Companion Disk?SQL> exec dynvar.assign ('tstvar.str1', 'abc')SQL> exec dbms_output.put_line (tstvar.str1)abcSQL> exec dbms_output.put_line (dynvar.val ('tstvar.str1'))abcThis package seems to work just fine.When working with dynamic SQL and PL/SQL, however, the trickiestaspect of the package might not be building it, but using it [ Pobierz całość w formacie PDF ]
zanotowane.pl doc.pisz.pl pdf.pisz.pl odbijak.htw.pl
.These are overloaded procedures;overloading is discussed in the section "Module Overloading" in Chapter 15, Procedures and Functions.Here are some examples of these programs in use:1.Request activation of trace on the SFEUERST account for level 16:SQL> exec trace.filter_by ('sfeuerst', 16);2.Request activation of trace on the LELLISON account for levels 16-25:SQL> exec trace.filter_by ('lellsion', 16, 25);3.Request activation of trace on the WGATES account for levels 2, 6, 10, and 55:SQL> exec trace.filter_by ('wgates', '2,6,10,55');Gee, that looks easy enough to use! Of course, the devil is in the details.In all three cases, the proceduresmust convert the levels provided into a string with a consistent format so that the trace.startup program (andothers) can query the tracetab_activate table and properly interpret the request.26.4.1 From Idea to ImplementationSure, this section doesn't provide all of the code behind the interface.However, don't underestimate theimportance of getting that interface right.Too many of us spend too little time thinking about how ourprograms will be used and how the interface to our functionality would be best constructed.Instead, we areeager to dive into the implementation of the programs.Without question, package bodies will be needed for916 [Appendix A] What's on the Companion Disk?most every package specification you ever write, but if your specification is poorly designed, it will be hard tounderstand and use -- which means that most likely your code will not be used.26.3 Free Format Filtering 26.5 Quick-and-DirtyTracingCopyright (c) 2000 O'Reilly & Associates.All rights reserved.917 Chapter 26Tracing PL/SQL Execution26.5 Quick-and-Dirty TracingI have found that in many situations, PL/SQL developers don't have the time or the access to tools to performcomprehensive tracing.Instead, they just need to get more information out of a specific package or program,and they need it right away.Let's take a look at the options you have for some "quick-and-dirty" tracing.First of all, there isDBMS_OUTPUT.PUT_LINE, a built-in which generates output from within a PL/SQL program.Forexample, if I executed in SQL*Plus the following block:BEGINFOR emp_rec IN(SELECT ename, sal FROM emp ORDER BY sal DESC)LOOPDBMS_OUTPUT.PUT_LINE('Employee ' || emp_rec.ename || ' earns ' ||TO_CHAR (emp_rec.sal) || ' dollars.');END LOOP;END;/I would see the following output when the program terminated:Employee KING earns 5000 dollars.Employee SCOTT earns 3000 dollars.Employee JONES earns 2975 dollars.Employee ADAMS earns 1100 dollars.Employee JAMES earns 950 dollars.You will only see trace information from DBMS_OUTPUT in SQL*Plus if you issue the followingcommand:SQL> set serveroutput onThis will enable the package within SQL*Plus.You can also set the buffer which contains trace informationto its maximum size of 1MB as follows:SQL> set serveroutput on size 1000000Finally, if you are running Oracle Server 7.3 and above, you can also request that output fromDBMS_OUTPUT.PUT_LINE be "wrapped" so that leading blanks are not trimmed and long lines arewrapped within the SQL*Plus linesize:SQL> set serveroutput on size 1000000 format wrappedSo DBMS_OUTPUT does give you the flexibility of embedding trace calls inside your program, but onlyseeing the output when you have SET SERVEROUTPUT ON.It is, unfortunately, an all-or-nothingproposition with this package.You see no messages or you see all messages.UsingDBMS_OUTPUT.PUT_LINE "in the raw" as a trace mechanism therefore leaves much to be desired.(Well,to be honest, when talking about the inadequacies of DBMS_OUTPUT, one would also have to mention that918 [Appendix A] What's on the Companion Disk?it can only display a maximum of 255 bytes per call, that it does not display Booleans or combinations ofdata, and that it will not work in the Oracle Developer/2000 environment nor in Oracle WebServer.)[2][2] See Chapter 7 of my book on packages, Advanced Oracle PL/SQL Programming withPackages, for details about the usage of DBMS_OUTPUT.PUT_LINE.Ideally, you would like to be able to set up a trace mechanism so that you can see information about only thispackage or that procedure.The best way to do that is to set up a "toggle" within a package.Let's step througha simple example to make the technique clear.Suppose I have a package which assigns a value to a package variable (which must be defined in the packagespecification) using dynamic SQL execution.(This is similar to the indirect referencing available in OracleForms with COPY and NAME_IN.) The specification and body of such a package is shown below:/* filename on companion disk: dynvar.spp */CREATE OR REPLACE PACKAGE dynvarISPROCEDURE assign (var_in IN VARCHAR2, val_in IN VARCHAR2);FUNCTION val (var_in IN VARCHAR2) RETURN VARCHAR2;END dynvar;/CREATE OR REPLACE PACKAGE BODY dynvarISPROCEDURE assign (var_in IN VARCHAR2, val_in IN VARCHAR2)IScur INTEGER;fdbk INTEGER;BEGINcur := DBMS_SQL.OPEN_CURSOR;DBMS_SQL.PARSE (cur,'BEGIN ' || var_in || ' := :val; END;', DBMS_SQL.NATIVE);DBMS_SQL.BIND_VARIABLE (cur, 'val', val_in, 2000);fdbk := DBMS_SQL.EXECUTE (cur);DBMS_SQL.CLOSE_CURSOR (cur);END;FUNCTION val (var_in IN VARCHAR2) RETURN VARCHAR2IScur INTEGER;fdbk INTEGER;retval VARCHAR2(2000);BEGINcur := DBMS_SQL.OPEN_CURSOR;DBMS_SQL.PARSE(cur, 'BEGIN :val := ' || var_in || '; END;', DBMS_SQL.NATIVE);DBMS_SQL.BIND_VARIABLE (cur, 'val', var_in, 2000);fdbk := DBMS_SQL.EXECUTE (cur);DBMS_SQL.VARIABLE_VALUE (cur, 'val', retval);DBMS_SQL.CLOSE_CURSOR (cur);RETURN retval;END;END dynvar;/Here is a little test package and some program calls in SQL*Plus to give you a sense of how it would work:CREATE OR REPLACE PACKAGE TSTVARISstr1 varchar2(2000);str2 varchar2(2000);END;/919 [Appendix A] What's on the Companion Disk?SQL> exec dynvar.assign ('tstvar.str1', 'abc')SQL> exec dbms_output.put_line (tstvar.str1)abcSQL> exec dbms_output.put_line (dynvar.val ('tstvar.str1'))abcThis package seems to work just fine.When working with dynamic SQL and PL/SQL, however, the trickiestaspect of the package might not be building it, but using it [ Pobierz całość w formacie PDF ]