• problem with execute immediate

    From =?UTF-8?B?VmVyw7NuaWNhIFDDqXJleg==?@21:1/5 to All on Tue Apr 27 02:36:05 2021
    Hello,

    I want to create a dynamic procedure that calls all the routines inside a dataset

    The query I am using is

    DECLARE routine_list ARRAY<STRING>;
    DECLARE iter INT64 DEFAULT 0;
    DECLARE query_string STRING;

    SET routine_list = (


    SELECT
    array_agg(concat(@@project_id,'.',specific_schema,'.',specific_name))
    FROM Mydataset.INFORMATION_SCHEMA.ROUTINES;

    );

    WHILE
    iter < ARRAY_LENGTH(routine_list) DO
    SET query_string = "CALL `" || routine_list[OFFSET(iter)] || "` ()";
    EXECUTE IMMEDIATE query_string;
    SET iter = iter + 1;
    END WHILE;

    the error
    Errore durante l'esecuzione della query
    SQL created by EXECUTE IMMEDIATE contains unsupported statement type: CallStatement at [18:23]

    do you know a better way to do this?? i don't want to list all the procedures Thanks in advanced
    Veronica

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Kay Kanekowski@21:1/5 to All on Wed Apr 28 23:18:46 2021
    Hi Verónica,
    do use an oracle database ?
    kind regards
    Kay

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Mladen Gogala@21:1/5 to All on Wed May 5 03:08:05 2021
    On Tue, 27 Apr 2021 02:36:05 -0700, Verónica Pérez wrote:


    DECLARE routine_list ARRAY<STRING>;
    DECLARE iter INT64 DEFAULT 0; DECLARE query_string STRING;


    These types look mighty strange for an Oracle database. They are not even
    DB2 types. DB2 has "BIGINT" data type and supports PL/SQL but it doesn't
    have int64. I know that there is an extension to PostgreSQL providing
    uint64 data type, but I don't know of anything supporting those types.


    --
    Mladen Gogala
    Database Consultant
    https://dbwhisperer.wordpress.com

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)