Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Pseudocode for Next_Word() Function
Oracle Tips by Burleson

So far, we’ve identified two subroutines to be included in the package: the function Next_Word() and the procedure Build_Error(). We also know that the Build_Error() procedure must be public and therefore must be declared within the package spec.

Listing 6.10 shows the logic for the Next_Word() function.

Listing 6.10 Logic for the function Next_Word().

find the location of the specified delimiter in the string;
if the specified delimiter isn't the first delimiter then
  find the previous delimiter's position
end if;
return the portion of the string between the two delimiters;

This pseudocode looks a little odd and will look complicated when translated into PL/SQL. It would be better if Next_Word() were a procedure and used OUT and IN OUT parameters to return multiple values. Let’s rewrite the pseudocode to accommodate this change. The revised logic is shown in Listing 6.11.

Listing 6.11 Revised pseudocode for the Next_Word() function.

find the first delimiter in the data string;
extract the portion of the data string prior to the delimiter;
shorten the data string;
return the modified data string and the next data portion to
  the calling routine;

Listing 6.12 shows the logic for the Build_Error() procedure.

Listing 6.12 Logic for the Build_Error() procedure.

if the specified module or error number doesn't exist then
  write an "unknown module" error message;
  exit the procedure normally;
end if;
get the first portion of the error message for the module;
while there is more data to be included in the message loop
   stick the next piece of data onto the string;
   stick the next portion of the message onto the string;
end loop;
write the message to the system errors table;
if any unhandled errors occur during processing then
   place a recursive call to this procedure to log an "unhandled
     error" error message (watch for infinite loops!);
end if;

Looking at the highlighted lines of this pseudocode, we can identify at least one other module that might be usable within the package—a function that retrieves the next portion of the error message, as needed. Let’s call this function Next_String(). The pseudocode for the Next_String() function is shown in Listing 6.13.

Listing 6.13 Logic for the Next_String() function.

query the next part of the error message from the ERROR_MESSAGES
increment the message part counter;
return the next part of the error message;

This seems pretty straightforward. We’re now ready to code.

This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter