Oracle SP use of global temporary tables

Question

Usually, my procedures (Oracle 12c) look like the below:

PROCEDURE xxx AS 
   declare variables
BEGIN
   save log
   create table 1
   save log
   ...
   create table 2
   save log
   ...
   create table n
   save log
END xxx

I create tables 1 to n-1 as auxiliary tables (performance and modularity) for obtaining my desired n-th table. Am I using normal tables when I should be using global temporary tables?

PD: My first question got closed for being opinion based, I've modified it to make it more concrete.

EDIT: adding example procedure

create or replace PACKAGE BODY balance_mismatch_dca AS

    idproceduregeneral VARCHAR2(50);

    PROCEDURE aux_tables AS
        balance_zufi_dca_exists     INTEGER;
        balance_dfkkop_dca_exists   INTEGER;
    BEGIN
        idproceduregeneral := utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(1));
        uxxxxxx_logs('Start Aux Tables', idproceduregeneral);
        BEGIN
            SELECT
                COUNT(*)
            INTO balance_zufi_dca_exists
            FROM
                sys.all_tables
            WHERE
                table_name = upper('balance_zufi_dca');

        EXCEPTION
            WHEN no_data_found THEN
                balance_zufi_dca_exists := 0;
        END;

        IF ( balance_zufi_dca_exists ) = 1 THEN
            EXECUTE IMMEDIATE q'[Drop Table balance_zufi_dca]';
        END IF;
        EXECUTE IMMEDIATE q'[
        
        CREATE TABLE balance_zufi_dca
            AS
            WITH aux AS (
                SELECT
                    vkont,
                    vtref,
                    MAX(zzmfvdat) AS lat
                FROM
                    cdc.uap_zufi_t_dca_place@rbip
                WHERE
                    zzextamount != 0
                GROUP BY
                    vkont,
                    vtref
            ), aux2 AS (
                SELECT
                    a.vkont,
                    a.vtref,
                    a.lat,
                    MAX(b.zzextamount) AS amount_zufi,
                    MAX(b.zzplacref) AS dca,
                    MAX(zzplace_typ) AS placement
                FROM
                    aux                             a
                    JOIN cdc.uap_zufi_t_dca_place@rbip   b ON ( b.vkont = a.vkont
                                                              AND b.vtref = a.vtref
                                                              AND b.zzmfvdat = a.lat )
                GROUP BY
                    a.vkont,
                    a.vtref,
                    a.lat
            )
            SELECT
                vkont,
                SUM(amount_zufi) AS amount_zufi,
                MAX(dca) AS dca,
                MAX(placement) AS placement
            FROM
                aux2
            GROUP BY
                vkont
        ]'
        ;
        uxxxxxx_logs('End Balance Zufi DCA', idproceduregeneral);
        BEGIN
            SELECT
                COUNT(*)
            INTO balance_dfkkop_dca_exists
            FROM
                sys.all_tables
            WHERE
                table_name = upper('balance_dfkkop_dca');

        EXCEPTION
            WHEN no_data_found THEN
                balance_dfkkop_dca_exists := 0;
        END;

        IF ( balance_dfkkop_dca_exists ) = 1 THEN
            EXECUTE IMMEDIATE q'[Drop Table balance_dfkkop_dca]';
        END IF;
        EXECUTE IMMEDIATE q'[
        
        Create Table balance_dfkkop_dca AS
                WITH aux AS (
                    SELECT /*+ FULL(a) */
                        vkont,
                        betrw as amount_dfkkop
                    FROM
                        cdc.uap_dfkkop@rbip a
                    WHERE
                        augrs IS NULL
                        AND augst IS NULL
                        AND stakz IS NULL
                        AND abwbl IS NULL
                        AND (studt <= to_char(sysdate, 'YYYYMMDD') or studt is null)
                        AND faedn <= to_char(sysdate, 'YYYYMMDD')
                )
                SELECT
                    *
                FROM
                    aux
        ]'
        ;
        uxxxxxx_logs('End Balance DFKKOP DCA', idproceduregeneral);
    END aux_tables;

    PROCEDURE gen_view AS
    BEGIN
        idproceduregeneral := utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(1));
        EXECUTE IMMEDIATE q'[
        CREATE OR REPLACE VIEW vw_bm_dca_volumes AS
        WITH aux AS (
            SELECT
                a.vkont,
                a.amount_zufi,
                SUM(nvl(b.amount_dfkkop, 0)) AS amount_dfkkop
            FROM
                balance_zufi_dca   a
                LEFT JOIN balance_dfkkop_dca    b ON ( b.vkont = a.vkont )
            GROUP BY
                a.vkont,
                a.amount_zufi
        )
        SELECT
            CASE
                WHEN amount_zufi = amount_dfkkop THEN
                    'Chased for the Correct Balance'
                WHEN amount_zufi > amount_dfkkop THEN
                    'Chased for Higher Balance'
                WHEN amount_zufi < amount_dfkkop THEN
                    'Chased for Lower Balance'
                ELSE
                    NULL
            END AS status,
            COUNT(1) AS ca_count,
            round(RATIO_TO_REPORT(COUNT(1)) OVER() * 100, 2) perc
        FROM
            aux
        GROUP BY
            CASE
                WHEN amount_zufi = amount_dfkkop THEN
                    'Chased for the Correct Balance'
                WHEN amount_zufi > amount_dfkkop THEN
                    'Chased for Higher Balance'
                WHEN amount_zufi < amount_dfkkop THEN
                    'Chased for Lower Balance'
                ELSE
                    NULL
            END
        ORDER BY
            COUNT(1) DESC
    
    ]'
        ;
        
       EXECUTE IMMEDIATE q'[
        CREATE OR REPLACE VIEW vw_bm_dca_data AS
        WITH aux AS (
            SELECT
                a.vkont,
                a.dca,
                a.placement,
                a.amount_zufi,
                SUM(nvl(b.amount_dfkkop, 0)) AS amount_dfkkop
            FROM
                balance_zufi_dca     a
                LEFT JOIN balance_dfkkop_dca   b ON ( b.vkont = a.vkont )
            GROUP BY
                a.vkont,
                a.dca,
                a.placement,
                a.amount_zufi
        )
        SELECT
            *
        FROM
            aux
        WHERE
            amount_zufi != amount_dfkkop
    
    ]'
        ;
        uxxxxxx_logs('End Views', idproceduregeneral);
    END gen_view;

END balance_mismatch_dca;

Answer 1

It depends. For example:

• if it is only "you" who use those tables, then it doesn't really matter.
• if there are many users who work with the same procedure, then
  • if you use "normal" tables, you have to have some identifier (ID, username, whatever) which will distinguish one user's data from another's, because - if you don't - they will modify all rows for all users and you'll get serious problems (from inconsistency to locking). Unless you manually delete rows from tables, they will remain populated
  • if you use (global) temporary tables, every user will see only their own data, and their (tables') contents will be lost when session (or transaction; depending on how you created tables) ends

So, as I said, it just depends.