Collection or In-memory table for algorithm?

[Custom24]

Hi
I am not completely fluent in PL/SQL. But then, I'm not 100% sure how I'd do this in any other language anyway.

I want to write a function which standardises a string which is sent to it. Basically, the string is a chemical molecular formula. So for example,

C2H6BrC -> BrC3H6

because I was planning to just alphabeticise the output, and duplicate elements should be added together.

I was planning to use owa_pattern to extract each element, evaluate its number and add this to a running total for each element. At the end, I would order by the element type alphabetically and construct the output.

There are two problems - first, owa_pattern is not like any reg exp I have come across before, but this is a separate issue and I think I can cope.

The second is what do I use to maintain the running total? My latest and craziest idea is to use a helper table, which I fill up during the function, and execute regular SQL on to manage the collection of elements and order by at the end. Then I delete from this table. I was thinking to have an ID from a sequence in the table, and use this to avoid deleting any rows belonging to other calls of the function.

Is this a good idea? How do I persuade Oracle that this table is purely a memory structure, while still being able to issue SQL to it? Will this perform well? Have I lost the plot?

Thanks for any advice.

Mark

 

[SantaMufasa]

Mark,

As you noted, you have several structural options available to you, including a PL/SQL memory table (which should execute VERY quickly) and a standard Oracle database table. It sounds as though you will benefit from "persistence" of variable values over the life of the session for which you will be gathering these "chemical formulae". Obviously, a standard database table offers persistence. But PL/SQL memory structures (from simple variables to memory tables [i.e., arrays]) are persistent when you define them in the variable section of a PL/SQL Package header.

Please post any other specific question you may have in resolving your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:26 (08Sep04) UTC (aka "GMT" and "Zulu"), 13:26 (08Sep04) Mountain Time)

 

[Custom24]

Mufasa
Thank you for your post. I was not clear. The memory structure only needs to persist for the duration of the function call, so that I can use it to put stuff in and then pull stuff out of at the end. I don't think I should need to use a package, but maybe I am wrong.

This is the kind of thing I am trying to do - this code does not compile - I get 'Identifier CAST is not valid'

Does this clarify my question?

CREATE OR REPLACE FUNCTION Standardise(MolForm IN VARCHAR2) RETURN VARCHAR2 AS

TYPE AnELEMENT IS RECORD (ElemName VARCHAR2(2), ElemCount INTEGER);
TYPE TheElements IS TABLE OF AnELEMENT INDEX BY BINARY_INTEGER;
MyElements TheElements;
ReturnValue VARCHAR2(200);
BEGIN
--these assignments will come from the regular expression algorithm, which I've not done yet - for now, I am just placing values in the table manually
MyElements(1).ElemName := 'H'; MyElements(1).ElemCount := '2';
MyElements(2).ElemName := 'He'; MyElements(2).ElemCount := '4';
MyElements(3).ElemName := 'Li'; MyElements(3).ElemCount := '8';
MyElements(4).ElemName := 'Be'; MyElements(4).ElemCount := '16';
MyElements(5).ElemName := 'B'; MyElements(5).ElemCount := '32';
FOR rec IN (SELECT ElemName, ElemCount FROM TABLE (CAST (MyElements)) ORDER BY ElemName)
LOOP
ReturnValue := ReturnValue || rec.ElemName || rec.ElemCount;
END LOOP;
RETURN returnvalue;
--in this case, I would expect B32Be16H2He4Li8
END;

Mark

 

[Custom24]

I figured it out. I was almost there. There are some syntactical things and you have to declare types outside of the function, but this web page explains it.

http://www.oracle-base.com/articles/8i/ComplexRecordsets.php#PLSQLTables

Most of the other examples I've seen only talk about a 1D table, which was of no use to me.

Mark

 

[SantaMufasa]

Mark,

For those who may search Tek-Tips for solutions to problems similar to yours, it would be very helpful for you to post the code set that finally worked for you.

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA @ 22:27 (14Sep04) UTC (aka "GMT" and "Zulu"), 15:27 (14Sep04) Mountain Time)

 

[Custom24]

Yes of course - it is pretty much what is on the web page I referenced above. It is not yet a working function - it only shows how to order from an in memory table. I wanted to do it this way in order to avoid touching the disk.

CREATE OR REPLACE
TYPE AnELEMENT as object (ElemName VARCHAR2(2), ElemCount INTEGER);
/

CREATE OR REPLACE
TYPE TheElements IS TABLE OF AnELEMENT;
/

CREATE OR REPLACE FUNCTION CanonicaliseMolForm(MolForm IN VARCHAR2) RETURN VARCHAR2 AS
MyElements TheElements := TheElements();
BEGIN
--there is probably a neater way to add records to the table, but...
MyElements.extend;
MyElements(1) := AnElement('H','2');
MyElements.extend;
MyElements(2):= AnElement('He','4');
MyElements.extend;
MyElements(3):= AnElement('Li','8');
MyElements.extend;
MyElements(4):= AnElement('Be','16');
MyElements.extend;
MyElements(5):= AnElement('B','32');
FOR rec IN (SELECT ElemName, ElemCount FROM TABLE (CAST (MyElements as TheElements)) ORDER BY ElemName)
LOOP
DBMS_OUTPUT.PUT_LINE(rec.ElemName || ' ' || rec.ElemCount);
END LOOP;
END;
/

Mark