Tuesday, September 08, 2009

Online Training for the New Economy

When the going gets tough and budgets get tight, money for training is often cut first. You still, however, need to learn how to best leverage the PL/SQL language and write high quality code. To help you, Quest is offering a three-part, webcast training series that I will be teaching. Each session consists of two hours of in-depth coverage of a specific functionality area or best practice.

The cost is far less than you would be for an onsite or public training - and I don't have to travel (and neither do you)!

More information about session logistics and registration are available through these links below. For much more detail on each session, please check out the descriptions at the end of this posting.

Webcast Details
I hope that you can take advantage of this opportunity. Click here toget more details and to register.

Optimize PL/SQL Execution with Bulk Processing

This session focuses on how to turbo-charge the performance of multi-row SQL statements executed from within a PL/SQL using FORALL and BULK COLLECT. These features have been around since Oracle8i, but many developers still don't use them or only scratch the surface of what is possible.

The Bulk Processing webinar will talk about why these features were needed and how they work. Steven will start with simple examples and syntax and drill down into all nuances, including the LIMIT clause for BULK COLLECT and the SAVE EXCEPTIONS, INDICES OF and VALUES OF clauses for FORALL.

After attending this webinar, you will be able to both create new programs built around these features and also redesign existing programs safely to improve performance.

Writing Maintainable PL/SQL Code

Software managers (and, therefore, their developers) are very focused on delivering applications "on time" (that is, to meet the current production deadline). Sure, that's important. But as (or maybe even more) important is to build applications so that they can be maintained and enhanced without consuming all the resources of the development team (thereby interfering with new development).

This webinar offers a wide-ranging set of techniques to make your code easy to understand and maintain over time. These techniques include: the use of subtypes and local modules; how to activate and check compliance with standards; and encapsulation (information hiding).

After attending this webinar, you will be able to write code that minimizes the amount of development resources needed to maintain that code.

Error Management Features of Oracle PL/SQL

Consistent, robust error management is a critical feature of any successful application. Developers need to know all that is possible in PL/SQL regarding the raising, handling and logging of errors, and that standardize the way in which those tasks are performed.

This webinar takes you beyond the basics of exception handling in PL/SQL to explore the wide range of specialized error management features in Oracle, plus recommendations for best practices for consistent, robust application construction.

We will cover FORALL's SAVE EXCEPTIONS, DML error logging with the DBMS_ERRLOG package, the AFTERSERVERERROR trigger, the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function, and more. In addition, we will talk about hiding error management functionality behind a PL/SQL packaged API and then deploying it as a set of services for application developers.

After attending this webinar, you will be able to fully leverage PL/SQL error management features and follow best practices for consistent error management across your entire development team.


Charles Schultz said...

Steven, love your work and contributions to the field on pl/sql. And I think you are offering an excellent value with your webcasts.

We have a developement group that is transitioning from a database-agnostic methodology to something that embraces Oracle a little more. As such, our java background is pretty strong, but pl/sql is... well, growing. *grin* Is there a comprehensive online training resource that addresses this kind of audience? I myself am a DBA and would love to bolster my own pl/sql skills, but it is more important to our developer group to learn how to optimize and maximize coding practices that take advantage of Oracle's quirkiness. =) From a quick glance, it seems that your recent offering may be a little beyond what our average developer (and average DBA) is ready for. Perhaps.

Thanks in advance for your thoughts.

Steven Feuerstein said...


Thanks for your kind comments. Honestly, while I plan to go into depth on these topic areas, I am certain that even relative newbies will benefit greatly from these trainings. You might want to sign up for the first one, evaluate, and then decide for the others.

Also, I strongly encourage your group to send all/some to the Oracle PL/SQL Programming conference in Atlanta on Nov 10-11:


It has lots of fundamentals courses, plus many other training sessions.

Regards, SF

prichard said...



I am currently facing an issue with the usage of the FETCH ... BULK COLLECT INTO ... LIMIT statement. This is the code snippet that is being executed.

Note - TYPE t_extract_tab IS TABLE OF VARCHAR2(4001);

PROCEDURE output_extract
p_extract_refcur IN SYS_REFCURSOR,
p_header IN VARCHAR2,
x_return_code OUT NUMBER,
x_return_msg OUT VARCHAR2

l_proc_name VARCHAR2(240):='output_extract';

rExtracts_tab t_extract_tab:=t_extract_tab();


/* Output Header */
fnd_file.put_line(fnd_file.output, p_header);

/* Output Detail Records */

/* Fetch from balance temp table */
FETCH p_extract_refcur BULK COLLECT INTO rExtracts_tab LIMIT g_limit_rows;

EXIT WHEN rExtracts_tab.COUNT=0;

/* Write to Concurrent Output file */
FOR lv_index IN rExtracts_tab.FIRST..rExtracts_tab.LAST
fnd_file.put_line(fnd_file.output, rExtracts_tab(lv_index));


CLOSE p_extract_refcur;


x_return_msg:=SUBSTR('Error Message: '||dbms_utility.format_error_stack||CHR(10)||' Error Trace: '||dbms_utility.format_error_backtrace,1,4000);
END output_extract;

This procedure was executing fine till now. In one of our newly cloned instances, the usage is temp space is in excess of 48 GB. And the program fails with the below error message -

Error Message: ORA-01652: unable to extend temp segment by 640 in tablespace TEMP

Error Trace: ORA-06512: at "APPS.XXX_XXX_PKG", line 668

Any help in this regard, will be very much appreciated. I even tried initializing the collection within the loop using -


But still the usage of the temp space increases with time. The program in some instances would be required to spool out in excess of 500,000 records.


Prince Richard

Steven Feuerstein said...

PR -

Have you tried reducing the limit value so you work with a smaller number of rows with each fetch?

That should certainly reduce the memory consumed.