Designing a PL/SQL app.

From: Danielle Engel <dogbert_at_myremarq.com>
Date: Sun, 23 May 1999 00:57:36 -0400
Message-ID: <37478AC0.47614B33@myremarq.com>

Help! I need a clue!

I'm fairly new to Oracle and PL/SQL, but I'm catching on pretty quickly, I think. I am designing an application which is very large to replace a legacy app from a database with a very different design which currently exists on an Adabas platform. Somehow, I am expected to create a bridge between the new database and existing mainframe applications designed for output produced from the Adabas db. It's very complex, and very critical that I do it the right way.

As it takes shape, I'm seeing that there are going to be dozens and dozens of functions with the same basic structure:

FUNCTION myfunc1 ( x NUMBER, y NUMBER, z VARCHAR2 )

   RETURN VARCHAR2; Mostly these functions are to fetch and return a specific piece of data from the database, applying field specific processing.

I have chosen to write so many separate functions, rather than combining into a few functions, because the data requires a lot of specific massaging, with many different rules and exceptions to those rules. I feel that in the long run it will be easier to maintain many --possibly over 100-- small, simple functions as opposed to fewer but more complicated functions. Also, looking forward, I expect to be able to re-use at least 50% of these functions - perhaps more - in future applications.

As a result of this flattened-out design, it looks like I will have a main section of code that will contain line after line of code that looks something like this:
--EXAMPLE ONE --

field_out( output_area, "TAG1", myfunc1( a, b, c ));
field_out( output_area, "TAG2", myfunc2( a, b, c ));
field_out( output_area, "TAG3", myfunc3( a, b, c ));

IF( some_value < 3 ) THEN
  field_out( output_area, "TAG4", myfunc4( a, b, c )); END IF; etc. etc...(where field_out puts out a tag followed by the value returned by myfuncX, and concatenates it to output_area. Output to look something like this:

<TAG1>stuff<TAG2>99999<TAG3>some text

So it occurred to me that what I would *really* like to have is an external file:
--EXAMPLE TWO--
'TAG1','myfunc1'
'TAG2','myfunc2'
'TAG3','myfunc3'
'TAG4','myfunc4','somevalue < 3'

Then I could have a procedure to read this external file, loop through and parse each line, which I can handle using DBMS_FILE. I would have a tag name, which I can pass to field_out, a function name, and (optionally) a condition. I would like to take a file, like the one in EXAMPLE TWO, and use it to build and execute statements as in EXAMPLE ONE. Dynamic PL/SQL, in other words!

If these were SQL statements, I could use DBMS_SQL procedures to parse and execute statements dynamically. But how would I go about accomplishing this feat for PL/SQL? Received on Sat May 22 1999 - 23:57:36 CDT