problem with execute immediate

33 views
Skip to first unread message

Verónica Pérez

unread,
Apr 27, 2021, 5:36:09 AMApr 27
to
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

Kay Kanekowski

unread,
Apr 29, 2021, 2:18:49 AMApr 29
to
Hi Verónica,
do use an oracle database ?
kind regards
Kay

Mladen Gogala

unread,
May 4, 2021, 11:08:13 PMMay 4
to
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
Reply all
Reply to author
Forward
0 new messages