PL SQL Temporary Table

Question

I need to create a key-value pair in PL SQL.

I know that Oracle doesn't have the same concept of temporary tables as SQL Server does but it'd help me to convert a split string into a hash, array or similar to process it. My problem is that, every time I try to use an associative array (index-by table), I get the following error:

PLS-00103: Encountered the symbol "REPORTTABTYP" when expecting one of the following: := . ( @ % ;

This is the declaration of my associative array:

create or replace procedure "POST_TX"
(p_report IN VARCHAR2)
is
begin
    TYPE ReportTabTyp IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
    ReportTab ReportTabTyp := ReportTabTyp();

Similarly, I've tried to use EXECUTE IMMEDIATELY in the procedure to create a temporary table but, since it doesn't exist, the procedure fails to compile.

Here's my full procedure:

create or replace procedure "POST_TX"
(p_report IN VARCHAR2)
is
begin
    TYPE ReportTabTyp IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
    ReportTab ReportTabTyp := ReportTabTyp();

FOR loop_words IN
        (select level as ID, regexp_substr(p_report,'[^ ]+', 1, level) as report
        from dual
        connect by regexp_substr(p_report, '[^ ]+', 1, level) is not null)
    LOOP
        ReportTab(loop_words.ID) := loop_words.report;
    END LOOP loop_words;

    IF (LENGTH(TRIM(TRANSLATE(ReportTabTyp(2).report, ' +-.0123456789', ' '))) IS NULL) THEN
        -- Do stuff
    ELSE
        -- Output error
    END IF;
end;

Answer 1

Your type and variable should be declared before the begin. You also seem to have the datatypes swapped over.

create or replace procedure "POST_TX"
(p_report IN VARCHAR2)
is
    TYPE ReportTabTyp IS TABLE OF VARCHAR2(64) INDEX BY NUMBER;
    ReportTab ReportTabTyp := ReportTabTyp();
begin
...

Answer 2

You can create global temporary table in oracle and use in your session - GTT