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


 

HTML Text

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