3316 lines
113 KiB
Plaintext
3316 lines
113 KiB
Plaintext
reset search_path;
|
|
create extension if not exists gms_utility;
|
|
-- start test db_version
|
|
declare
|
|
version varchar2(50);
|
|
compatibility varchar2(50);
|
|
begin
|
|
gms_utility.db_version(version, compatibility);
|
|
raise info 'version: %', version;
|
|
raise info 'compatibility: %', compatibility;
|
|
end;
|
|
/
|
|
--?INFO: version: openGauss.*
|
|
--?INFO: compatibility: openGuass.*
|
|
declare
|
|
version varchar2(50);
|
|
begin
|
|
gms_utility.db_version(version);
|
|
raise info 'version: %', version;
|
|
end; -- error
|
|
/
|
|
ERROR: function "db_version" with 1 parameters doesn't exist
|
|
CONTEXT: SQL statement "CALL gms_utility.db_version(version)"
|
|
PL/pgSQL function inline_code_block line 3 at SQL statement
|
|
call gms_utility.db_version(); -- error
|
|
ERROR: function "db_version" with 0 parameters doesn't exist
|
|
declare
|
|
version int;
|
|
compatibility int;
|
|
begin
|
|
gms_utility.db_version(version, compatibility);
|
|
raise info 'version: %', version;
|
|
raise info 'compatibility: %', compatibility;
|
|
end; -- error
|
|
/
|
|
--?ERROR: invalid input syntax for integer:.*
|
|
CONTEXT: PL/pgSQL function inline_code_block line 4 at SQL statement
|
|
-- end test db_version
|
|
-- start test get_cpu_time
|
|
select gms_utility.get_cpu_time();
|
|
get_cpu_time
|
|
--------------
|
|
--?.*
|
|
(1 row)
|
|
|
|
select gms_utility.get_cpu_time(100); -- error
|
|
ERROR: function gms_utility.get_cpu_time(integer) does not exist
|
|
LINE 1: select gms_utility.get_cpu_time(100);
|
|
^
|
|
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
|
|
CONTEXT: referenced column: get_cpu_time
|
|
declare
|
|
cputime char(1);
|
|
begin
|
|
cputime := gms_utility.get_cpu_time();
|
|
raise info 'cpuTimeDelta: %', cputime;
|
|
end;
|
|
/
|
|
ERROR: value too long for type character(1)
|
|
CONTEXT: PL/pgSQL function inline_code_block line 3 at assignment
|
|
declare
|
|
cputime json;
|
|
begin
|
|
cputime := gms_utility.get_cpu_time();
|
|
raise info 'cpuTimeDelta: %', cputime;
|
|
end;
|
|
/
|
|
--?INFO: cpuTimeDelta:.*
|
|
declare
|
|
t1 number := 0;
|
|
t2 number := 0;
|
|
timeDelta number;
|
|
i integer;
|
|
sum integer;
|
|
begin
|
|
t1 := gms_utility.get_cpu_time();
|
|
for i in 1..1000000 loop
|
|
sum := sum + i * 2 + i / 2;
|
|
end loop;
|
|
t2 := gms_utility.get_cpu_time();
|
|
timeDelta = t2 - t1;
|
|
raise info 'cpuTimeDelta: %', timeDelta;
|
|
end;
|
|
/
|
|
--?INFO: cpuTimeDelta:.*
|
|
declare
|
|
i number;
|
|
j number;
|
|
k number;
|
|
begin
|
|
i := gms_utility.get_cpu_time;
|
|
|
|
select count(*)
|
|
into j
|
|
from pg_class t, pg_index i
|
|
where t.Oid = i.indexrelid;
|
|
|
|
k := gms_utility.get_cpu_time;
|
|
raise info 'costtime: %', (k-i);
|
|
end;
|
|
/
|
|
--?INFO: costtime:.*
|
|
-- end test get_cpu_time
|
|
-- start test get_endianness
|
|
select gms_utility.get_endianness();
|
|
get_endianness
|
|
----------------
|
|
--?.*
|
|
(1 row)
|
|
|
|
select gms_utility.get_endianness(1000);
|
|
ERROR: function gms_utility.get_endianness(integer) does not exist
|
|
LINE 1: select gms_utility.get_endianness(1000);
|
|
^
|
|
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
|
|
CONTEXT: referenced column: get_endianness
|
|
-- end test get_endianness
|
|
-- start test get_sql_hash
|
|
declare
|
|
hash raw(50);
|
|
l4b number;
|
|
begin
|
|
gms_utility.get_sql_hash('Today is a good day!', hash, l4b);
|
|
raise info 'hash: %', hash;
|
|
raise info 'last4byte: %', l4b;
|
|
end;
|
|
/
|
|
INFO: hash: 834E5BE13C0240D4F1AEBB1BCE7205AF
|
|
INFO: last4byte: 2936369870
|
|
declare
|
|
hash raw(50);
|
|
l4b number;
|
|
begin
|
|
gms_utility.get_sql_hash(NULL, hash, l4b);
|
|
raise info 'hash: %', hash;
|
|
raise info 'last4byte: %', l4b;
|
|
end;
|
|
/
|
|
INFO: hash: <NULL>
|
|
INFO: last4byte: <NULL>
|
|
declare
|
|
hash raw(50);
|
|
l4b number;
|
|
begin
|
|
gms_utility.get_sql_hash('', hash, l4b);
|
|
raise info 'hash: %', hash;
|
|
raise info 'last4byte: %', l4b;
|
|
end;
|
|
/
|
|
INFO: hash: <NULL>
|
|
INFO: last4byte: <NULL>
|
|
call gms_utility.get_sql_hash();
|
|
ERROR: function "get_sql_hash" with 0 parameters doesn't exist
|
|
-- end test get_sql_hash
|
|
-- start test is_bit_set
|
|
declare
|
|
r raw(50) := '123456AF';
|
|
result NUMBER;
|
|
pos NUMBER;
|
|
begin
|
|
for pos in 1..32 loop
|
|
result := gms_utility.is_bit_set(r, pos);
|
|
raise info 'position = %, result = %', pos, result;
|
|
end loop;
|
|
end;
|
|
/
|
|
INFO: position = 1, result = 1
|
|
INFO: position = 2, result = 1
|
|
INFO: position = 3, result = 1
|
|
INFO: position = 4, result = 1
|
|
INFO: position = 5, result = 0
|
|
INFO: position = 6, result = 1
|
|
INFO: position = 7, result = 0
|
|
INFO: position = 8, result = 1
|
|
INFO: position = 9, result = 0
|
|
INFO: position = 10, result = 1
|
|
INFO: position = 11, result = 1
|
|
INFO: position = 12, result = 0
|
|
INFO: position = 13, result = 1
|
|
INFO: position = 14, result = 0
|
|
INFO: position = 15, result = 1
|
|
INFO: position = 16, result = 0
|
|
INFO: position = 17, result = 0
|
|
INFO: position = 18, result = 0
|
|
INFO: position = 19, result = 1
|
|
INFO: position = 20, result = 0
|
|
INFO: position = 21, result = 1
|
|
INFO: position = 22, result = 1
|
|
INFO: position = 23, result = 0
|
|
INFO: position = 24, result = 0
|
|
INFO: position = 25, result = 0
|
|
INFO: position = 26, result = 1
|
|
INFO: position = 27, result = 0
|
|
INFO: position = 28, result = 0
|
|
INFO: position = 29, result = 1
|
|
INFO: position = 30, result = 0
|
|
INFO: position = 31, result = 0
|
|
INFO: position = 32, result = 0
|
|
declare
|
|
r raw(50) := '123456AF';
|
|
result NUMBER;
|
|
pos NUMBER;
|
|
begin
|
|
for pos in 33..64 loop
|
|
result := gms_utility.is_bit_set(r, pos);
|
|
raise info 'position = %, result = %', pos, result;
|
|
end loop;
|
|
end;
|
|
/
|
|
INFO: position = 33, result = 1
|
|
INFO: position = 34, result = 1
|
|
INFO: position = 35, result = 1
|
|
INFO: position = 36, result = 1
|
|
INFO: position = 37, result = 0
|
|
INFO: position = 38, result = 1
|
|
INFO: position = 39, result = 0
|
|
INFO: position = 40, result = 1
|
|
INFO: position = 41, result = 0
|
|
INFO: position = 42, result = 1
|
|
INFO: position = 43, result = 1
|
|
INFO: position = 44, result = 0
|
|
INFO: position = 45, result = 1
|
|
INFO: position = 46, result = 0
|
|
INFO: position = 47, result = 1
|
|
INFO: position = 48, result = 0
|
|
INFO: position = 49, result = 0
|
|
INFO: position = 50, result = 0
|
|
INFO: position = 51, result = 1
|
|
INFO: position = 52, result = 0
|
|
INFO: position = 53, result = 1
|
|
INFO: position = 54, result = 1
|
|
INFO: position = 55, result = 0
|
|
INFO: position = 56, result = 0
|
|
INFO: position = 57, result = 0
|
|
INFO: position = 58, result = 1
|
|
INFO: position = 59, result = 0
|
|
INFO: position = 60, result = 0
|
|
INFO: position = 61, result = 1
|
|
INFO: position = 62, result = 0
|
|
INFO: position = 63, result = 0
|
|
INFO: position = 64, result = 0
|
|
select gms_utility.is_bit_set(); -- error
|
|
ERROR: function gms_utility.is_bit_set() does not exist
|
|
LINE 1: select gms_utility.is_bit_set();
|
|
^
|
|
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
|
|
CONTEXT: referenced column: is_bit_set
|
|
select gms_utility.is_bit_set(NULL, 10); -- error
|
|
ERROR: invalid input value
|
|
CONTEXT: referenced column: is_bit_set
|
|
select gms_utility.is_bit_set('', 10); -- error
|
|
ERROR: invalid input value
|
|
CONTEXT: referenced column: is_bit_set
|
|
select gms_utility.is_bit_set('123456'); -- error
|
|
ERROR: function gms_utility.is_bit_set(unknown) does not exist
|
|
LINE 1: select gms_utility.is_bit_set('123456');
|
|
^
|
|
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
|
|
CONTEXT: referenced column: is_bit_set
|
|
select gms_utility.is_bit_set('123456AF', 9);
|
|
is_bit_set
|
|
------------
|
|
0
|
|
(1 row)
|
|
|
|
select gms_utility.is_bit_set('12345678AF', 9);
|
|
is_bit_set
|
|
------------
|
|
0
|
|
(1 row)
|
|
|
|
select gms_utility.is_bit_set('123456TT', 9); -- error
|
|
ERROR: invalid hexadecimal digit: "T"
|
|
LINE 1: select gms_utility.is_bit_set('123456TT', 9);
|
|
^
|
|
CONTEXT: referenced column: is_bit_set
|
|
select gms_utility.is_bit_set('12345678TT', 9); -- error
|
|
ERROR: invalid hexadecimal digit: "T"
|
|
LINE 1: select gms_utility.is_bit_set('12345678TT', 9);
|
|
^
|
|
CONTEXT: referenced column: is_bit_set
|
|
select gms_utility.is_bit_set('1234', 9);
|
|
is_bit_set
|
|
------------
|
|
0
|
|
(1 row)
|
|
|
|
select gms_utility.is_bit_set('12345678', -10); -- error
|
|
ERROR: invalid second param value range
|
|
CONTEXT: referenced column: is_bit_set
|
|
select gms_utility.is_bit_set('12345678', 0); -- error
|
|
ERROR: invalid second param value range
|
|
CONTEXT: referenced column: is_bit_set
|
|
select gms_utility.is_bit_set('12345678', 100);
|
|
is_bit_set
|
|
------------
|
|
1
|
|
(1 row)
|
|
|
|
select gms_utility.is_bit_set('12345678', 3.14E100); -- error
|
|
ERROR: integer out of range
|
|
CONTEXT: referenced column: is_bit_set
|
|
select gms_utility.is_bit_set('团团又圆圆', 20); -- error
|
|
--?ERROR: invalid hexadecimal digit:.*
|
|
LINE 1: select gms_utility.is_bit_set('团团又圆圆', 20);
|
|
^
|
|
CONTEXT: referenced column: is_bit_set
|
|
-- end test is_bit_set
|
|
-- start test is_cluster_database
|
|
select gms_utility.is_cluster_database();
|
|
is_cluster_database
|
|
---------------------
|
|
f
|
|
(1 row)
|
|
|
|
select gms_utility.is_cluster_database(1000);
|
|
ERROR: function gms_utility.is_cluster_database(integer) does not exist
|
|
LINE 1: select gms_utility.is_cluster_database(1000);
|
|
^
|
|
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
|
|
CONTEXT: referenced column: is_cluster_database
|
|
begin
|
|
if gms_utility.is_cluster_database then
|
|
raise info 'true';
|
|
else
|
|
raise info 'false';
|
|
end if;
|
|
end;
|
|
/
|
|
INFO: false
|
|
-- end test is_cluster_database
|
|
-- start test old_current_schema
|
|
select gms_utility.old_current_schema();
|
|
old_current_schema
|
|
--------------------
|
|
--?.*
|
|
(1 row)
|
|
|
|
select gms_utility.old_current_schema(2000);
|
|
ERROR: function gms_utility.old_current_schema(integer) does not exist
|
|
LINE 1: select gms_utility.old_current_schema(2000);
|
|
^
|
|
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
|
|
CONTEXT: referenced column: old_current_schema
|
|
-- end test old_current_schema
|
|
-- start test old_current_user
|
|
select gms_utility.old_current_user();
|
|
old_current_user
|
|
------------------
|
|
--?.*
|
|
(1 row)
|
|
|
|
select gms_utility.old_current_user(2000);
|
|
ERROR: function gms_utility.old_current_user(integer) does not exist
|
|
LINE 1: select gms_utility.old_current_user(2000);
|
|
^
|
|
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
|
|
CONTEXT: referenced column: old_current_user
|
|
-- end test old_current_user
|
|
---------------------------
|
|
-- expand_sql_text
|
|
---------------------------
|
|
create schema test_utility_est;
|
|
set search_path to test_utility_est;
|
|
declare
|
|
sqlTxt CLOB;
|
|
begin
|
|
gms_utility.expand_sql_text(NULL, sqlTxt);
|
|
raise info 'output_sql_text: %', sqlTxt;
|
|
end;
|
|
/
|
|
INFO: output_sql_text: <NULL>
|
|
declare
|
|
sqlTxt CLOB;
|
|
begin
|
|
gms_utility.expand_sql_text(NULL);
|
|
raise info 'output_sql_text: %', sqlTxt;
|
|
end;
|
|
/
|
|
ERROR: function "expand_sql_text" with 1 parameters doesn't exist
|
|
CONTEXT: SQL statement "CALL gms_utility.expand_sql_text(NULL)"
|
|
PL/pgSQL function inline_code_block line 3 at SQL statement
|
|
create table test_dx(id int);
|
|
create view view1 as select * from test_dx;
|
|
create materialized view mv_dx as select * from test_dx;
|
|
declare
|
|
input_sql_text clob := 'select * from test_utility_est.view1';
|
|
output_sql_text clob;
|
|
begin
|
|
gms_utility.expand_sql_text(input_sql_text, output_sql_text);
|
|
raise info 'output_sql_text: %', output_sql_text;
|
|
end;
|
|
/
|
|
INFO: output_sql_text: SELECT id FROM (SELECT test_dx.id FROM test_utility_est.test_dx) view1
|
|
declare
|
|
input_sql_text clob := 'select * from test_utility_est.view1;';
|
|
output_sql_text clob;
|
|
begin
|
|
gms_utility.expand_sql_text(input_sql_text, output_sql_text);
|
|
raise info 'output_sql_text: %', output_sql_text;
|
|
end;
|
|
/
|
|
INFO: output_sql_text: SELECT id FROM (SELECT test_dx.id FROM test_utility_est.test_dx) view1
|
|
-- don't expand materialized view sql, just print
|
|
declare
|
|
input_sql_text clob := 'select * from test_utility_est.mv_dx';
|
|
output_sql_text clob;
|
|
begin
|
|
gms_utility.expand_sql_text(input_sql_text, output_sql_text);
|
|
raise info 'output_sql_text: %', output_sql_text;
|
|
end;
|
|
/
|
|
INFO: output_sql_text: SELECT id FROM test_utility_est.mv_dx
|
|
-- test multi sql, error
|
|
declare
|
|
input_sql_text clob := 'select * from test_utility_est.view1;select * from test_utility_est.view1';
|
|
output_sql_text clob;
|
|
begin
|
|
gms_utility.expand_sql_text(input_sql_text, output_sql_text);
|
|
raise info 'output_sql_text: %', output_sql_text;
|
|
end;
|
|
/
|
|
ERROR: Expand_sql_text only support one query
|
|
CONTEXT: PL/pgSQL function gms_utility.expand_sql_text(clob) line 2 at assignment
|
|
SQL statement "CALL gms_utility.expand_sql_text(input_sql_text,output_sql_text)"
|
|
PL/pgSQL function inline_code_block line 4 at SQL statement
|
|
-- test select into
|
|
insert into test_dx values (3);
|
|
declare
|
|
num int := 0;
|
|
input_sql_text clob := 'select * into t_into from test_utility_est.view1;';
|
|
output_sql_text clob;
|
|
begin
|
|
gms_utility.expand_sql_text(input_sql_text, output_sql_text);
|
|
raise info 'output_sql_text: %', output_sql_text;
|
|
end;
|
|
/
|
|
INFO: output_sql_text: INSERT INTO gms_utility.t_into (id) SELECT id FROM (SELECT test_dx.id FROM test_utility_est.test_dx) view1
|
|
declare
|
|
num int := 0;
|
|
output_sql_text clob;
|
|
begin
|
|
gms_utility.expand_sql_text('select id into num from test_utility_est.view1', output_sql_text);
|
|
raise info 'output_sql_text: %', output_sql_text;
|
|
end;
|
|
/
|
|
INFO: output_sql_text: INSERT INTO gms_utility.num (id) SELECT id FROM (SELECT test_dx.id FROM test_utility_est.test_dx) view1
|
|
--test with select
|
|
declare
|
|
num int := 0;
|
|
input_sql_text clob := 'with ws (id) AS (select id from test_utility_est.view1) select * from ws';
|
|
output_sql_text clob;
|
|
begin
|
|
gms_utility.expand_sql_text(input_sql_text, output_sql_text);
|
|
raise info 'output_sql_text: %', output_sql_text;
|
|
end;
|
|
/
|
|
INFO: output_sql_text: WITH ws(id) AS (SELECT view1.id FROM (SELECT test_dx.id FROM test_utility_est.test_dx) view1) SELECT id FROM ws
|
|
-- test select for update
|
|
declare
|
|
input_sql_text clob := 'select * from test_utility_est.test_dx for update';
|
|
output_sql_text clob;
|
|
begin
|
|
gms_utility.expand_sql_text(input_sql_text, output_sql_text);
|
|
raise info 'output_sql_text: %', output_sql_text;
|
|
end;
|
|
/
|
|
INFO: output_sql_text: SELECT id FROM test_utility_est.test_dx FOR UPDATE OF test_dx
|
|
declare
|
|
input_sql_text clob := 'select * from test_utility_est.view1 for update';
|
|
output_sql_text clob;
|
|
begin
|
|
gms_utility.expand_sql_text(input_sql_text, output_sql_text);
|
|
raise info 'output_sql_text: %', output_sql_text;
|
|
end;
|
|
/
|
|
INFO: output_sql_text: SELECT id FROM (SELECT test_dx.id FROM test_utility_est.test_dx) view1 FOR UPDATE OF view1
|
|
create table test_1(id int, name varchar(20));
|
|
create table test_2(id int, name varchar(20));
|
|
create view view2 as select * from test_1;
|
|
create view view3 as select * from test_1 union select * from test_2;
|
|
create view view4 as select * from test_1 union select * from view3;
|
|
declare
|
|
input_sql_text1 clob := 'select * from test_utility_est.view3';
|
|
output_sql_text1 clob;
|
|
input_sql_text2 clob := 'select * from test_utility_est.view4';
|
|
output_sql_text2 clob;
|
|
begin
|
|
gms_utility.expand_sql_text(input_sql_text1, output_sql_text1);
|
|
raise info 'output_sql_text1: %', output_sql_text1;
|
|
gms_utility.expand_sql_text(input_sql_text2, output_sql_text2);
|
|
raise info 'output_sql_text2: %', output_sql_text2;
|
|
END;
|
|
/
|
|
INFO: output_sql_text1: SELECT id, name FROM (SELECT test_1.id, test_1.name FROM test_utility_est.test_1 UNION SELECT test_2.id, test_2.name FROM test_utility_est.test_2) view3
|
|
INFO: output_sql_text2: SELECT id, name FROM (SELECT test_1.id, test_1.name FROM test_utility_est.test_1 UNION SELECT view3.id, view3.name FROM (SELECT test_1.id, test_1.name FROM test_utility_est.test_1 UNION SELECT test_2.id, test_2.name FROM test_utility_est.test_2) view3) view4
|
|
declare
|
|
input_sql_text1 clob := 'select * from test_utility_est.view3';
|
|
output_sql_text1 clob;
|
|
input_sql_text2 clob := 'select * from test_utility_est.view4';
|
|
output_sql_text2 clob;
|
|
begin
|
|
gms_utility.expand_sql_text(input_sql_text1, output_sql_text1);
|
|
raise info 'output_sql_text1: %', output_sql_text1;
|
|
gms_utility.expand_sql_text(input_sql_text2, output_sql_text2);
|
|
raise info 'output_sql_text2: %', output_sql_text2;
|
|
END;
|
|
/
|
|
INFO: output_sql_text1: SELECT id, name FROM (SELECT test_1.id, test_1.name FROM test_utility_est.test_1 UNION SELECT test_2.id, test_2.name FROM test_utility_est.test_2) view3
|
|
INFO: output_sql_text2: SELECT id, name FROM (SELECT test_1.id, test_1.name FROM test_utility_est.test_1 UNION SELECT view3.id, view3.name FROM (SELECT test_1.id, test_1.name FROM test_utility_est.test_1 UNION SELECT test_2.id, test_2.name FROM test_utility_est.test_2) view3) view4
|
|
-- test not select query, error
|
|
declare
|
|
sqlTxt CLOB;
|
|
begin
|
|
gms_utility.expand_sql_text('create table test(id int)', sqlTxt);
|
|
raise info 'output_sql_text: %', sqlTxt;
|
|
end;
|
|
/
|
|
ERROR: Unsupported query type, only support SELECT query
|
|
CONTEXT: PL/pgSQL function gms_utility.expand_sql_text(clob) line 2 at assignment
|
|
SQL statement "CALL gms_utility.expand_sql_text('create table test(id int)',sqlTxt)"
|
|
PL/pgSQL function inline_code_block line 3 at SQL statement
|
|
declare
|
|
sqlTxt CLOB;
|
|
begin
|
|
gms_utility.expand_sql_text('begin', sqlTxt);
|
|
raise info 'output_sql_text: %', sqlTxt;
|
|
end;
|
|
/
|
|
ERROR: Unsupported query type, only support SELECT query
|
|
CONTEXT: PL/pgSQL function gms_utility.expand_sql_text(clob) line 2 at assignment
|
|
SQL statement "CALL gms_utility.expand_sql_text('begin',sqlTxt)"
|
|
PL/pgSQL function inline_code_block line 3 at SQL statement
|
|
declare
|
|
sqlTxt CLOB;
|
|
begin
|
|
gms_utility.expand_sql_text('update test_1 set name = ''ggboom'' where id = 1', sqlTxt);
|
|
raise info 'output_sql_text: %', sqlTxt;
|
|
end;
|
|
/
|
|
ERROR: Unsupported query type, only support SELECT query
|
|
CONTEXT: PL/pgSQL function gms_utility.expand_sql_text(clob) line 2 at assignment
|
|
SQL statement "CALL gms_utility.expand_sql_text('update test_1 set name = ''ggboom'' where id = 1',sqlTxt)"
|
|
PL/pgSQL function inline_code_block line 3 at SQL statement
|
|
declare
|
|
sqlTxt CLOB;
|
|
begin
|
|
gms_utility.expand_sql_text('delete from test_1', sqlTxt);
|
|
raise info 'output_sql_text: %', sqlTxt;
|
|
end;
|
|
/
|
|
ERROR: Unsupported query type, only support SELECT query
|
|
CONTEXT: PL/pgSQL function gms_utility.expand_sql_text(clob) line 2 at assignment
|
|
SQL statement "CALL gms_utility.expand_sql_text('delete from test_1',sqlTxt)"
|
|
PL/pgSQL function inline_code_block line 3 at SQL statement
|
|
-- test not valid query, error
|
|
declare
|
|
sqlTxt CLOB;
|
|
begin
|
|
gms_utility.expand_sql_text('today is a good day', sqlTxt);
|
|
raise info 'output_sql_text: %', sqlTxt;
|
|
end;
|
|
/
|
|
ERROR: syntax error at or near "today"
|
|
LINE 1: CALL gms_utility.expand_sql_text('today is a good day',sqlTx...
|
|
^
|
|
QUERY: CALL gms_utility.expand_sql_text('today is a good day',sqlTxt)
|
|
CONTEXT: PL/pgSQL function gms_utility.expand_sql_text(clob) line 2 at assignment
|
|
PL/pgSQL function inline_code_block line 3 at SQL statement
|
|
create table t1(srvr_id int);
|
|
create view expandv as select * from t1;
|
|
declare
|
|
vclobin clob :=
|
|
'select distinct srvr_id
|
|
from public.expandv
|
|
where srvr_id not in
|
|
(select srvr_id
|
|
from public.expandv
|
|
minus
|
|
select srvr_id
|
|
from public.t1)';
|
|
vclobout clob;
|
|
begin
|
|
gms_utility.expand_sql_text(vclobin, vclobout);
|
|
raise info 'output_sql_text: %', vclobout;
|
|
end;
|
|
/
|
|
ERROR: relation "public.expandv" does not exist on datanode1
|
|
LINE 1: CALL gms_utility.expand_sql_text(vclobin,vclobout)
|
|
^
|
|
QUERY: CALL gms_utility.expand_sql_text(vclobin,vclobout)
|
|
CONTEXT: PL/pgSQL function gms_utility.expand_sql_text(clob) line 2 at assignment
|
|
PL/pgSQL function inline_code_block line 12 at SQL statement
|
|
-- test privileges
|
|
create user user01 password 'utility@123';
|
|
set session AUTHORIZATION user01 password 'utility@123';
|
|
set search_path to test_utility_est;
|
|
declare
|
|
input_sql_text1 clob := 'select * from test_utility_est.view3';
|
|
output_sql_text1 clob;
|
|
begin
|
|
gms_utility.expand_sql_text(input_sql_text1, output_sql_text1);
|
|
raise info 'output_sql_text1: %', output_sql_text1;
|
|
END;
|
|
/
|
|
ERROR: permission denied for schema test_utility_est
|
|
LINE 1: CALL gms_utility.expand_sql_text(input_sql_text1,output_sql_...
|
|
^
|
|
DETAIL: N/A
|
|
QUERY: CALL gms_utility.expand_sql_text(input_sql_text1,output_sql_text1)
|
|
CONTEXT: PL/pgSQL function gms_utility.expand_sql_text(clob) line 2 at assignment
|
|
PL/pgSQL function inline_code_block line 4 at SQL statement
|
|
RESET SESSION AUTHORIZATION;
|
|
drop view view4;
|
|
drop view view3;
|
|
drop view view2;
|
|
drop view view1;
|
|
drop view expandv;
|
|
drop materialized view mv_dx;
|
|
drop table t1;
|
|
drop table if exists t_into;
|
|
NOTICE: table "t_into" does not exist, skipping
|
|
drop talbe if exists test_2;
|
|
ERROR: syntax error at or near "talbe"
|
|
LINE 1: drop talbe if exists test_2;
|
|
^
|
|
drop talbe if exists test_1;
|
|
ERROR: syntax error at or near "talbe"
|
|
LINE 1: drop talbe if exists test_1;
|
|
^
|
|
drop talbe if exists test_dx;
|
|
ERROR: syntax error at or near "talbe"
|
|
LINE 1: drop talbe if exists test_dx;
|
|
^
|
|
drop schema test_utility_est;
|
|
ERROR: cannot drop schema test_utility_est because other objects depend on it
|
|
DETAIL: table test_dx depends on schema test_utility_est
|
|
table test_1 depends on schema test_utility_est
|
|
table test_2 depends on schema test_utility_est
|
|
HINT: Use DROP ... CASCADE to drop the dependent objects too.
|
|
---------------------------
|
|
-- analyze_schema|database
|
|
---------------------------
|
|
create user test_utility_analyze password "test_utility_analyze@123";
|
|
set search_path to test_utility_analyze;
|
|
create table t1 (id int, c2 text);
|
|
create unique index t1_id_uidx on t1 using btree(id);
|
|
insert into t1 values (generate_series(1, 100), 'aabbcc');
|
|
insert into t1 values (generate_series(101, 200), '123dfg');
|
|
insert into t1 values (generate_series(201, 300), '人面桃花相映红');
|
|
insert into t1 values (generate_series(301, 400), 'fortunate');
|
|
insert into t1 values (generate_series(401, 500), 'open@gauss');
|
|
insert into t1 values (generate_series(501, 600), '127.0.0.1');
|
|
insert into t1 values (generate_series(601, 700), '!@#$!%#!');
|
|
insert into t1 values (generate_series(701, 800), '[1,2,3,4]');
|
|
insert into t1 values (generate_series(801, 900), '{"name":"张三","age":18}');
|
|
insert into t1 values (generate_series(901, 1000), '');
|
|
create or replace function batch_insert(count INT)
|
|
returns int as $$
|
|
declare
|
|
i INT;
|
|
start INT;
|
|
begin
|
|
select count(*) into start from t1;
|
|
for i in select generate_series(1, count) loop
|
|
insert into t1 values (start + i, left((pg_catalog.random() * i)::text, 6));
|
|
end loop;
|
|
|
|
return count;
|
|
end;
|
|
$$ language plpgsql;
|
|
select batch_insert(50000);
|
|
batch_insert
|
|
--------------
|
|
50000
|
|
(1 row)
|
|
|
|
-- analyze_schema
|
|
call gms_utility.analyze_schema('test_utility_analyze', 'ESTIMATE', estimate_rows:=10000);
|
|
analyze_schema
|
|
----------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_schema('test_utility_analyze', 'ESTIMATE', estimate_rows:=-100); -- error
|
|
ERROR: invalid value "-100" for "estimate_rows"
|
|
CONTEXT: SQL statement "CALL gms_utility.analyze_schema_c_fun(schema,method,estimate_rows,estimate_percent,method_opt)"
|
|
PL/pgSQL function gms_utility.analyze_schema(character varying,character varying,numeric,numeric,character varying) line 2 at PERFORM
|
|
call gms_utility.analyze_schema('test_utility_analyze', 'ESTIMATE', estimate_percent:=20);
|
|
analyze_schema
|
|
----------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_schema('test_utility_analyze', 'ESTIMATE', estimate_percent:=0);
|
|
analyze_schema
|
|
----------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_schema('test_utility_analyze', 'ESTIMATE', estimate_percent:=101); -- error
|
|
ERROR: invalid value "101" for "estimate_percent"
|
|
CONTEXT: SQL statement "CALL gms_utility.analyze_schema_c_fun(schema,method,estimate_rows,estimate_percent,method_opt)"
|
|
PL/pgSQL function gms_utility.analyze_schema(character varying,character varying,numeric,numeric,character varying) line 2 at PERFORM
|
|
call gms_utility.analyze_schema('test_utility_analyze', 'ESTIMATE', estimate_rows:=10000, estimate_percent:=101);
|
|
analyze_schema
|
|
----------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_schema('test_utility_analyze', 'ESTIMATE', estimate_rows:=10000, estimate_percent:=101, method_opt:='FOR TABLE');
|
|
analyze_schema
|
|
----------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_schema('test_utility_analyze', 'ESTIMATE', estimate_rows:=10000, estimate_percent:=101, method_opt:='FOR ALL INDEXES');
|
|
analyze_schema
|
|
----------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_schema('test_utility_analyze', 'ESTIMATE', estimate_rows:=10000, estimate_percent:=101, method_opt:='FOR ALL COLUMNS');
|
|
analyze_schema
|
|
----------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_schema('test_utility_analyze', 'ESTIMATE', estimate_rows:=10000, estimate_percent:=101, method_opt:='FOR ALL COLUMNS SIZE 100');
|
|
analyze_schema
|
|
----------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_schema('test_utility_analyze', 'ESTIMATE', estimate_rows:=10000, estimate_percent:=101, method_opt:='FOR ALL INDEXED COLUMNS');
|
|
analyze_schema
|
|
----------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_schema('test_utility_analyze', 'ESTIMATE', estimate_rows:=10000, estimate_percent:=101, method_opt:='FOR ALL INDEXED COLUMNS SIZE 100');
|
|
analyze_schema
|
|
----------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_schema('test_utility_analyze', 'COMPUTE');
|
|
analyze_schema
|
|
----------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_schema('test_utility_analyze', 'COMPUTE', estimate_rows:=10000);
|
|
analyze_schema
|
|
----------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_schema('test_utility_analyze', 'COMPUTE', estimate_percent:=-100);
|
|
analyze_schema
|
|
----------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_schema('test_utility_analyze', 'COMPUTE', estimate_rows:=10000, estimate_percent:=-100);
|
|
analyze_schema
|
|
----------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_schema('test_utility_analyze', 'COMPUTE', method_opt:='FOR TABLE');
|
|
analyze_schema
|
|
----------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_schema('test_utility_analyze', 'COMPUTE', method_opt:='FOR ALL INDEXES');
|
|
analyze_schema
|
|
----------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_schema('test_utility_analyze', 'COMPUTE', method_opt:='FOR ALL INDEXED COLUMNS');
|
|
analyze_schema
|
|
----------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_schema('test_utility_analyze', 'COMPUTE', method_opt:='FOR ALL INDEXED COLUMNS SIZE 100');
|
|
analyze_schema
|
|
----------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_schema('test_utility_analyze', 'COMPUTE', method_opt:='FOR ALL COLUMNS');
|
|
analyze_schema
|
|
----------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_schema('test_utility_analyze', 'COMPUTE', method_opt:='FOR ALL COLUMNS SIZE 100');
|
|
analyze_schema
|
|
----------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_schema('test_utility_analyze', 'COMPUTE', method_opt:='FOR TABLE FOR ALL INDEXES');
|
|
analyze_schema
|
|
----------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_schema('test_utility_analyze', 'COMPUTE', method_opt:='FOR TABLE FOR ALL INDEXED COLUMNS');
|
|
analyze_schema
|
|
----------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_schema('test_utility_analyze', 'COMPUTE', method_opt:='FOR TABLE123'); -- error
|
|
ERROR: invalid input value for method_opt
|
|
CONTEXT: SQL statement "CALL gms_utility.analyze_schema_c_fun(schema,method,estimate_rows,estimate_percent,method_opt)"
|
|
PL/pgSQL function gms_utility.analyze_schema(character varying,character varying,numeric,numeric,character varying) line 2 at PERFORM
|
|
call gms_utility.analyze_schema('test_utility_analyze', 'COMPUTE', method_opt:='111FOR TABLE'); -- error
|
|
ERROR: unrecognized param value: "111FOR TABLE"
|
|
CONTEXT: SQL statement "CALL gms_utility.analyze_schema_c_fun(schema,method,estimate_rows,estimate_percent,method_opt)"
|
|
PL/pgSQL function gms_utility.analyze_schema(character varying,character varying,numeric,numeric,character varying) line 2 at PERFORM
|
|
call gms_utility.analyze_schema('test_utility_analyze', 'DELETE');
|
|
analyze_schema
|
|
----------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_schema('test_utility_analyze', 'DELETE', method_opt:='FOR TABLE'); -- error
|
|
ERROR: The Command did not end correctly
|
|
CONTEXT: SQL statement "CALL gms_utility.analyze_schema_c_fun(schema,method,estimate_rows,estimate_percent,method_opt)"
|
|
PL/pgSQL function gms_utility.analyze_schema(character varying,character varying,numeric,numeric,character varying) line 2 at PERFORM
|
|
DECLARE
|
|
res boolean;
|
|
BEGIN
|
|
gms_utility.analyze_schema('SYSTEM','ESTIMATE', NULL, 10);
|
|
EXCEPTION
|
|
WHEN OTHERS THEN
|
|
NULL;
|
|
END;
|
|
/
|
|
-- test analyze database
|
|
call gms_utility.analyze_database('ESTIMATE', estimate_rows:=10000);
|
|
analyze_database
|
|
------------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_database('ESTIMATE', estimate_rows:=-100); -- error
|
|
ERROR: invalid value "-100" for "estimate_rows"
|
|
CONTEXT: SQL statement "CALL gms_utility.analyze_schema_c_fun(schema_name,method,estimate_rows,estimate_percent,method_opt)"
|
|
PL/pgSQL function gms_utility.analyze_database(character varying,numeric,numeric,character varying) line 5 at PERFORM
|
|
call gms_utility.analyze_database('ESTIMATE', estimate_percent:=20);
|
|
analyze_database
|
|
------------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_database('ESTIMATE', estimate_percent:=0);
|
|
analyze_database
|
|
------------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_database('ESTIMATE', estimate_percent:=101); -- error
|
|
ERROR: invalid value "101" for "estimate_percent"
|
|
CONTEXT: SQL statement "CALL gms_utility.analyze_schema_c_fun(schema_name,method,estimate_rows,estimate_percent,method_opt)"
|
|
PL/pgSQL function gms_utility.analyze_database(character varying,numeric,numeric,character varying) line 5 at PERFORM
|
|
call gms_utility.analyze_database('ESTIMATE', estimate_rows:=10000, estimate_percent:=101);
|
|
analyze_database
|
|
------------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_database('ESTIMATE', estimate_rows:=10000, estimate_percent:=101, method_opt:='FOR TABLE');
|
|
analyze_database
|
|
------------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_database('ESTIMATE', estimate_rows:=10000, estimate_percent:=101, method_opt:='FOR ALL INDEXES');
|
|
analyze_database
|
|
------------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_database('ESTIMATE', estimate_rows:=10000, estimate_percent:=101, method_opt:='FOR ALL COLUMNS');
|
|
analyze_database
|
|
------------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_database('ESTIMATE', estimate_rows:=10000, estimate_percent:=101, method_opt:='FOR ALL COLUMNS SIZE 100');
|
|
analyze_database
|
|
------------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_database('ESTIMATE', estimate_rows:=10000, estimate_percent:=101, method_opt:='FOR ALL INDEXED COLUMNS');
|
|
analyze_database
|
|
------------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_database('ESTIMATE', estimate_rows:=10000, estimate_percent:=101, method_opt:='FOR ALL INDEXED COLUMNS SIZE 100');
|
|
analyze_database
|
|
------------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_database('COMPUTE');
|
|
analyze_database
|
|
------------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_database('COMPUTE', estimate_rows:=10000);
|
|
analyze_database
|
|
------------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_database('COMPUTE', estimate_percent:=-100);
|
|
analyze_database
|
|
------------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_database('COMPUTE', estimate_rows:=10000, estimate_percent:=-100);
|
|
analyze_database
|
|
------------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_database('COMPUTE', method_opt:='FOR TABLE');
|
|
analyze_database
|
|
------------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_database('COMPUTE', method_opt:='FOR ALL INDEXES');
|
|
analyze_database
|
|
------------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_database('COMPUTE', method_opt:='FOR ALL INDEXED COLUMNS');
|
|
analyze_database
|
|
------------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_database('COMPUTE', method_opt:='FOR ALL INDEXED COLUMNS SIZE 100');
|
|
analyze_database
|
|
------------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_database('COMPUTE', method_opt:='FOR ALL COLUMNS');
|
|
analyze_database
|
|
------------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_database('COMPUTE', method_opt:='FOR ALL COLUMNS SIZE 100');
|
|
analyze_database
|
|
------------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_database('COMPUTE', method_opt:='FOR TABLE FOR ALL INDEXES');
|
|
analyze_database
|
|
------------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_database('COMPUTE', method_opt:='FOR TABLE FOR ALL INDEXED COLUMNS');
|
|
analyze_database
|
|
------------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_database('COMPUTE', method_opt:='FOR TABLE123'); -- error
|
|
ERROR: invalid input value for method_opt
|
|
CONTEXT: SQL statement "CALL gms_utility.analyze_schema_c_fun(schema_name,method,estimate_rows,estimate_percent,method_opt)"
|
|
PL/pgSQL function gms_utility.analyze_database(character varying,numeric,numeric,character varying) line 5 at PERFORM
|
|
call gms_utility.analyze_database('COMPUTE', method_opt:='111FOR TABLE'); -- error
|
|
ERROR: unrecognized param value: "111FOR TABLE"
|
|
CONTEXT: SQL statement "CALL gms_utility.analyze_schema_c_fun(schema_name,method,estimate_rows,estimate_percent,method_opt)"
|
|
PL/pgSQL function gms_utility.analyze_database(character varying,numeric,numeric,character varying) line 5 at PERFORM
|
|
call gms_utility.analyze_database('DELETE');
|
|
analyze_database
|
|
------------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_database('DELETE', method_opt:='FOR TABLE'); -- error
|
|
ERROR: The Command did not end correctly
|
|
CONTEXT: SQL statement "CALL gms_utility.analyze_schema_c_fun(schema_name,method,estimate_rows,estimate_percent,method_opt)"
|
|
PL/pgSQL function gms_utility.analyze_database(character varying,numeric,numeric,character varying) line 5 at PERFORM
|
|
ALTER INDEX t1_id_uidx UNUSABLE;
|
|
call gms_utility.analyze_schema('test_utility_analyze', 'COMPUTE', method_opt:='FOR ALL INDEXES');
|
|
ERROR: index "t1_id_uidx" is not available
|
|
CONTEXT: SQL statement "CALL gms_utility.analyze_schema_c_fun(schema,method,estimate_rows,estimate_percent,method_opt)"
|
|
PL/pgSQL function gms_utility.analyze_schema(character varying,character varying,numeric,numeric,character varying) line 2 at PERFORM
|
|
call gms_utility.analyze_database('COMPUTE', method_opt:='FOR ALL INDEXES');
|
|
ERROR: index "t1_id_uidx" is not available
|
|
CONTEXT: SQL statement "CALL gms_utility.analyze_schema_c_fun(schema_name,method,estimate_rows,estimate_percent,method_opt)"
|
|
PL/pgSQL function gms_utility.analyze_database(character varying,numeric,numeric,character varying) line 5 at PERFORM
|
|
ALTER INDEX t1_id_uidx REBUILD;
|
|
call gms_utility.analyze_schema('test_utility_analyze', 'COMPUTE', method_opt:='FOR ALL INDEXES');
|
|
analyze_schema
|
|
----------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.analyze_database('COMPUTE', method_opt:='FOR ALL INDEXES');
|
|
analyze_database
|
|
------------------
|
|
|
|
(1 row)
|
|
|
|
drop table t1 cascade;
|
|
drop function batch_insert;
|
|
reset search_path;
|
|
drop user test_utility_analyze cascade;
|
|
---------------------------
|
|
-- canonicalize
|
|
---------------------------
|
|
create schema test_utility_canonicalize;
|
|
set search_path to test_utility_canonicalize;
|
|
-- canonicalize
|
|
declare
|
|
canon_name varchar2(100);
|
|
begin
|
|
gms_utility.canonicalize('uwclass.test', canon_name, 16);
|
|
raise info 'canon_name: %', canon_name;
|
|
end;
|
|
/
|
|
INFO: canon_name: "UWCLASS"."TEST"
|
|
declare
|
|
canon_name varchar2(100);
|
|
begin
|
|
gms_utility.canonicalize('koll.rooy.nuuop.a', canon_name, 100);
|
|
raise info 'canon_name: %', canon_name;
|
|
end;
|
|
/
|
|
INFO: canon_name: "KOLL"."ROOY"."NUUOP"."A"
|
|
declare
|
|
canon_name varchar2(100);
|
|
begin
|
|
gms_utility.canonicalize('koll.rooy.nuuop.a', canon_name);
|
|
raise info 'canon_name: %', canon_name;
|
|
end; -- error
|
|
/
|
|
ERROR: function "canonicalize" with 2 parameters doesn't exist
|
|
CONTEXT: SQL statement "CALL gms_utility.canonicalize('koll.rooy.nuuop.a',canon_name)"
|
|
PL/pgSQL function inline_code_block line 3 at SQL statement
|
|
-- empty、space
|
|
declare
|
|
canon_name varchar2(100);
|
|
begin
|
|
gms_utility.canonicalize(NULL, canon_name, 100);
|
|
raise info 'canon_name: %', canon_name;
|
|
end; -- NULL
|
|
/
|
|
INFO: canon_name: <NULL>
|
|
declare
|
|
canon_name varchar2(100);
|
|
begin
|
|
gms_utility.canonicalize('', canon_name, 100);
|
|
raise info 'canon_name: %', canon_name;
|
|
end;
|
|
/
|
|
INFO: canon_name: <NULL>
|
|
declare
|
|
canon_name varchar2(100);
|
|
begin
|
|
gms_utility.canonicalize('""', canon_name, 100);
|
|
raise info 'canon_name: %', canon_name;
|
|
end; -- error
|
|
/
|
|
ERROR: Invalid paramter value """" with zero length
|
|
CONTEXT: PL/pgSQL function gms_utility.canonicalize(character varying,integer) line 5 at assignment
|
|
SQL statement "CALL gms_utility.canonicalize('""',canon_name,100)"
|
|
PL/pgSQL function inline_code_block line 3 at SQL statement
|
|
declare
|
|
canon_name varchar2(100);
|
|
begin
|
|
gms_utility.canonicalize(' ', canon_name, 100);
|
|
raise info 'canon_name: %', canon_name;
|
|
end;
|
|
/
|
|
INFO: canon_name:
|
|
declare
|
|
canon_name varchar2(100);
|
|
begin
|
|
gms_utility.canonicalize(' .abcd', canon_name, 100);
|
|
raise info 'canon_name: %', canon_name;
|
|
end; -- error
|
|
/
|
|
ERROR: Invalid paramter value " .abcd" with special words
|
|
CONTEXT: PL/pgSQL function gms_utility.canonicalize(character varying,integer) line 5 at assignment
|
|
SQL statement "CALL gms_utility.canonicalize(' .abcd',canon_name,100)"
|
|
PL/pgSQL function inline_code_block line 3 at SQL statement
|
|
declare
|
|
canon_name varchar2(100);
|
|
begin
|
|
gms_utility.canonicalize('koll..nuuop.a', canon_name, 100);
|
|
raise info 'canon_name: %', canon_name;
|
|
end; -- error
|
|
/
|
|
ERROR: Invalid paramter value "koll..nuuop.a" with special words
|
|
CONTEXT: PL/pgSQL function gms_utility.canonicalize(character varying,integer) line 5 at assignment
|
|
SQL statement "CALL gms_utility.canonicalize('koll..nuuop.a',canon_name,100)"
|
|
PL/pgSQL function inline_code_block line 3 at SQL statement
|
|
declare
|
|
canon_name varchar2(100);
|
|
begin
|
|
gms_utility.canonicalize('koll."".nuuop.a', canon_name, 100);
|
|
raise info 'canon_name: %', canon_name;
|
|
end; -- error
|
|
/
|
|
ERROR: Invalid paramter value "koll."".nuuop.a" with zero length
|
|
CONTEXT: PL/pgSQL function gms_utility.canonicalize(character varying,integer) line 5 at assignment
|
|
SQL statement "CALL gms_utility.canonicalize('koll."".nuuop.a',canon_name,100)"
|
|
PL/pgSQL function inline_code_block line 3 at SQL statement
|
|
declare
|
|
canon_name varchar2(100);
|
|
begin
|
|
gms_utility.canonicalize('koll', canon_name, 100);
|
|
raise info 'canon_name: %', canon_name;
|
|
end;
|
|
/
|
|
INFO: canon_name: KOLL
|
|
declare
|
|
canon_name varchar2(100);
|
|
begin
|
|
gms_utility.canonicalize('"koll"', canon_name, 100);
|
|
raise info 'canon_name: %', canon_name;
|
|
end;
|
|
/
|
|
INFO: canon_name: koll
|
|
declare
|
|
canon_name varchar2(100);
|
|
begin
|
|
gms_utility.canonicalize(' " koll.rooy" .nuuop.a', canon_name, 100);
|
|
raise info 'canon_name: %', canon_name;
|
|
end;
|
|
/
|
|
INFO: canon_name: " koll.rooy"."NUUOP"."A"
|
|
declare
|
|
canon_name varchar2(100);
|
|
begin
|
|
gms_utility.canonicalize('koll.rooy.nuuop.a', canon_name, 100);
|
|
raise info 'canon_name: %', canon_name;
|
|
end;
|
|
/
|
|
INFO: canon_name: "KOLL"."ROOY"."NUUOP"."A"
|
|
declare
|
|
canon_name varchar2(100);
|
|
begin
|
|
gms_utility.canonicalize('"~!#@@#$%)(#@(!@))<>?/*-+".nuuop.a', canon_name, 100);
|
|
raise info 'canon_name: %', canon_name;
|
|
end;
|
|
/
|
|
INFO: canon_name: "~!#@@#$%)(#@(!@))<>?/*-+"."NUUOP"."A"
|
|
declare
|
|
canon_name varchar2(100);
|
|
begin
|
|
gms_utility.canonicalize('koll.rooy#_$nuuop.a', canon_name, 100);
|
|
raise info 'canon_name: %', canon_name;
|
|
end;
|
|
/
|
|
INFO: canon_name: "KOLL"."ROOY#_$NUUOP"."A"
|
|
declare
|
|
canon_name varchar2(100);
|
|
begin
|
|
gms_utility.canonicalize('koll."rooy" abc.nuuop.a', canon_name, 100);
|
|
raise info 'canon_name: %', canon_name;
|
|
end; -- error
|
|
/
|
|
ERROR: Invalid parameter value "koll."rooy" abc.nuuop.a" after quotation
|
|
CONTEXT: PL/pgSQL function gms_utility.canonicalize(character varying,integer) line 5 at assignment
|
|
SQL statement "CALL gms_utility.canonicalize('koll."rooy" abc.nuuop.a',canon_name,100)"
|
|
PL/pgSQL function inline_code_block line 3 at SQL statement
|
|
declare
|
|
canon_name varchar2(100);
|
|
begin
|
|
gms_utility.canonicalize('koll.rooy.nuuop.', canon_name, 100);
|
|
raise info 'canon_name: %', canon_name;
|
|
end; -- error
|
|
/
|
|
ERROR: Invalid paramter value "koll.rooy.nuuop."
|
|
CONTEXT: PL/pgSQL function gms_utility.canonicalize(character varying,integer) line 5 at assignment
|
|
SQL statement "CALL gms_utility.canonicalize('koll.rooy.nuuop.',canon_name,100)"
|
|
PL/pgSQL function inline_code_block line 3 at SQL statement
|
|
declare
|
|
canon_name varchar2(100);
|
|
begin
|
|
gms_utility.canonicalize('koll.rooy.nu"uo"p', canon_name, 100);
|
|
raise info 'canon_name: %', canon_name;
|
|
end; -- error
|
|
/
|
|
ERROR: Invalid parameter value "koll.rooy.nu"uo"p" before quotation
|
|
CONTEXT: PL/pgSQL function gms_utility.canonicalize(character varying,integer) line 5 at assignment
|
|
SQL statement "CALL gms_utility.canonicalize('koll.rooy.nu"uo"p',canon_name,100)"
|
|
PL/pgSQL function inline_code_block line 3 at SQL statement
|
|
declare
|
|
canon_name varchar2(100);
|
|
begin
|
|
gms_utility.canonicalize('koll.rooy."nu"u"o"p', canon_name, 100);
|
|
raise info 'canon_name: %', canon_name;
|
|
end; -- error
|
|
/
|
|
ERROR: Invalid parameter value "koll.rooy."nu"u"o"p" after quotation
|
|
CONTEXT: PL/pgSQL function gms_utility.canonicalize(character varying,integer) line 5 at assignment
|
|
SQL statement "CALL gms_utility.canonicalize('koll.rooy."nu"u"o"p',canon_name,100)"
|
|
PL/pgSQL function inline_code_block line 3 at SQL statement
|
|
declare
|
|
canon_name varchar2(100);
|
|
begin
|
|
gms_utility.canonicalize('koll."rooy"."nuuop.', canon_name, 100);
|
|
raise info 'canon_name: %', canon_name;
|
|
end; -- error
|
|
/
|
|
ERROR: Invalid paramter value "koll."rooy"."nuuop." with quotation not closed
|
|
CONTEXT: PL/pgSQL function gms_utility.canonicalize(character varying,integer) line 5 at assignment
|
|
SQL statement "CALL gms_utility.canonicalize('koll."rooy"."nuuop.',canon_name,100)"
|
|
PL/pgSQL function inline_code_block line 3 at SQL statement
|
|
-- identy
|
|
declare
|
|
canon_name varchar2(100);
|
|
begin
|
|
gms_utility.canonicalize('koll.123rooy.nuuop.a', canon_name, 100);
|
|
raise info 'canon_name: %', canon_name;
|
|
end; -- error
|
|
/
|
|
ERROR: Invalid paramter value "koll.123rooy.nuuop.a" with special words
|
|
CONTEXT: PL/pgSQL function gms_utility.canonicalize(character varying,integer) line 5 at assignment
|
|
SQL statement "CALL gms_utility.canonicalize('koll.123rooy.nuuop.a',canon_name,100)"
|
|
PL/pgSQL function inline_code_block line 3 at SQL statement
|
|
declare
|
|
canon_name varchar2(100);
|
|
begin
|
|
gms_utility.canonicalize('koll._rooy.nuuop.a', canon_name, 100);
|
|
raise info 'canon_name: %', canon_name;
|
|
end; -- ok for og, error for A
|
|
/
|
|
INFO: canon_name: "KOLL"."_ROOY"."NUUOP"."A"
|
|
declare
|
|
canon_name varchar2(100);
|
|
begin
|
|
gms_utility.canonicalize('koll."_rooy".nuuop.a', canon_name, 100);
|
|
raise info 'canon_name: %', canon_name;
|
|
end;
|
|
/
|
|
INFO: canon_name: "KOLL"."_rooy"."NUUOP"."A"
|
|
declare
|
|
canon_name varchar2(100);
|
|
begin
|
|
gms_utility.canonicalize('table', canon_name, 100);
|
|
raise info 'canon_name: %', canon_name;
|
|
end;
|
|
/
|
|
INFO: canon_name: TABLE
|
|
declare
|
|
canon_name varchar2(100);
|
|
begin
|
|
gms_utility.canonicalize('koll.rooy.table.a', canon_name, 100);
|
|
raise info 'canon_name: %', canon_name;
|
|
end; -- error
|
|
/
|
|
ERROR: Invalid paramter value "koll.rooy.table.a" with special words
|
|
CONTEXT: PL/pgSQL function gms_utility.canonicalize(character varying,integer) line 5 at assignment
|
|
SQL statement "CALL gms_utility.canonicalize('koll.rooy.table.a',canon_name,100)"
|
|
PL/pgSQL function inline_code_block line 3 at SQL statement
|
|
declare
|
|
canon_name varchar2(100);
|
|
begin
|
|
gms_utility.canonicalize('column', canon_name, 100);
|
|
raise info 'canon_name: %', canon_name;
|
|
end;
|
|
/
|
|
INFO: canon_name: COLUMN
|
|
declare
|
|
canon_name varchar2(100);
|
|
begin
|
|
gms_utility.canonicalize('koll.column.nuuop.a', canon_name, 100);
|
|
raise info 'canon_name: %', canon_name;
|
|
end; -- error
|
|
/
|
|
ERROR: Invalid paramter value "koll.column.nuuop.a" with special words
|
|
CONTEXT: PL/pgSQL function gms_utility.canonicalize(character varying,integer) line 5 at assignment
|
|
SQL statement "CALL gms_utility.canonicalize('koll.column.nuuop.a',canon_name,100)"
|
|
PL/pgSQL function inline_code_block line 3 at SQL statement
|
|
-- test indentifier overlength; og >= 64, A >= 128
|
|
declare
|
|
canon_name varchar2(100);
|
|
begin
|
|
gms_utility.canonicalize('koll.DoYouThinkCausalUnderstandingIsADefiningCharacteristicOfHumanCognition.nuuop.a', canon_name, 100);
|
|
raise info 'canon_name: %', canon_name;
|
|
end;
|
|
/
|
|
ERROR: identifier too long, max length is 64
|
|
CONTEXT: PL/pgSQL function gms_utility.canonicalize(character varying,integer) line 5 at assignment
|
|
SQL statement "CALL gms_utility.canonicalize('koll.DoYouThinkCausalUnderstandingIsADefiningCharacteristicOfHumanCognition.nuuop.a',canon_name,100)"
|
|
PL/pgSQL function inline_code_block line 3 at SQL statement
|
|
-- param canon_len
|
|
declare
|
|
canon_name varchar2(100);
|
|
begin
|
|
gms_utility.canonicalize('koll.rooy.nuuop.a', canon_name, -10);
|
|
raise info 'canon_name: %', canon_name;
|
|
end; -- error
|
|
/
|
|
ERROR: Input parameter "canon_len" value "-10" is less than zero
|
|
CONTEXT: PL/pgSQL function gms_utility.canonicalize(character varying,integer) line 5 at assignment
|
|
SQL statement "CALL gms_utility.canonicalize('koll.rooy.nuuop.a',canon_name,-10)"
|
|
PL/pgSQL function inline_code_block line 3 at SQL statement
|
|
declare
|
|
canon_name varchar2(100);
|
|
begin
|
|
gms_utility.canonicalize('koll.rooy.nuuop.a', canon_name, 0);
|
|
raise info 'canon_name: %', canon_name;
|
|
end;
|
|
/
|
|
INFO: canon_name:
|
|
declare
|
|
canon_name varchar2(100);
|
|
begin
|
|
gms_utility.canonicalize('koll.rooy.nuuop.a', canon_name, 10);
|
|
raise info 'canon_name: %', canon_name;
|
|
end;
|
|
/
|
|
INFO: canon_name: "KOLL"."RO
|
|
declare
|
|
canon_name varchar2(100);
|
|
begin
|
|
gms_utility.canonicalize('开车.rooy.nuuop.a', canon_name, 10);
|
|
raise info 'canon_name: %', canon_name;
|
|
end;
|
|
/
|
|
INFO: canon_name: "开车"."
|
|
declare
|
|
canon_name varchar2(10);
|
|
begin
|
|
gms_utility.canonicalize('koll.rooy.nuuop.a', canon_name, 20);
|
|
raise info 'canon_name: %', canon_name;
|
|
end; -- error
|
|
/
|
|
ERROR: value too long for type character varying(10)
|
|
CONTEXT: PL/pgSQL function inline_code_block line 3 at SQL statement
|
|
drop schema test_utility_canonicalize;
|
|
---------------------------
|
|
-- compile schema
|
|
---------------------------
|
|
create schema test_utility_compile;
|
|
set search_path to test_utility_compile;
|
|
create table classes (
|
|
class_id int primary key,
|
|
class_name varchar(50) not null
|
|
)
|
|
with (orientation=row, compression=no);
|
|
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "classes_pkey" for table "classes"
|
|
|
|
create table students (
|
|
student_id int primary key,
|
|
class_id int,
|
|
student_name varchar(50) not null
|
|
)
|
|
with (orientation=row, compression=no);
|
|
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "students_pkey" for table "students"
|
|
|
|
create table studentscores (
|
|
score_id int primary key,
|
|
student_id int,
|
|
subject varchar(50),
|
|
score int
|
|
)
|
|
with (orientation=row, compression=no);
|
|
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "studentscores_pkey" for table "studentscores"
|
|
create or replace procedure insertclass(in classname varchar(50))
|
|
as
|
|
ct int;
|
|
begin
|
|
select count(class_id) into ct from classes;
|
|
insert into classes values (ct + 1, classname);
|
|
end;
|
|
/
|
|
|
|
create or replace procedure insertstudent(in classname varchar(50), in studentname varchar(50))
|
|
as
|
|
classid int;
|
|
ct int;
|
|
begin
|
|
select class_id into classid from classes where class_name = classname;
|
|
select count(student_id) into ct from students;
|
|
insert into students values (ct + 1, classid, studentname);
|
|
end;
|
|
/
|
|
create or replace function insertscore(studentid int, subject varchar(50), score int)
|
|
returns int
|
|
as $$
|
|
declare
|
|
ct int;
|
|
begin
|
|
select count(score_id) into ct from studentscores;
|
|
insert into studentscores values (ct + 1, studentid, subject, score);
|
|
return ct + 1;
|
|
end;
|
|
$$ language plpgsql;
|
|
create or replace function getrank(score int)
|
|
returns text
|
|
as $$
|
|
declare
|
|
rk text;
|
|
begin
|
|
if score = 100 then
|
|
rk := 'perfect';
|
|
elsif score >= 80 then
|
|
rk := 'nice';
|
|
elsif score >= 60 then
|
|
rk := 'ordinary';
|
|
elsif score >= 0 then
|
|
rk := 'poor';
|
|
else
|
|
raise notice 'error input';
|
|
end if;
|
|
|
|
return rk;
|
|
end;
|
|
$$ language plpgsql;
|
|
call insertclass('class a');
|
|
insertclass
|
|
-------------
|
|
|
|
(1 row)
|
|
|
|
call insertclass('class b');
|
|
insertclass
|
|
-------------
|
|
|
|
(1 row)
|
|
|
|
|
|
call insertstudent('class a', 'alice');
|
|
insertstudent
|
|
---------------
|
|
|
|
(1 row)
|
|
|
|
call insertstudent('class a', 'bob');
|
|
insertstudent
|
|
---------------
|
|
|
|
(1 row)
|
|
|
|
call insertstudent('class b', 'charlie');
|
|
insertstudent
|
|
---------------
|
|
|
|
(1 row)
|
|
|
|
call insertstudent('class b', 'rose');
|
|
insertstudent
|
|
---------------
|
|
|
|
(1 row)
|
|
|
|
call insertstudent('class b', 'jack');
|
|
insertstudent
|
|
---------------
|
|
|
|
(1 row)
|
|
|
|
|
|
select insertscore(1, 'math', 90);
|
|
insertscore
|
|
-------------
|
|
1
|
|
(1 row)
|
|
|
|
select insertscore(1, 'science', 85);
|
|
insertscore
|
|
-------------
|
|
2
|
|
(1 row)
|
|
|
|
select insertscore(2, 'math', 95);
|
|
insertscore
|
|
-------------
|
|
3
|
|
(1 row)
|
|
|
|
select insertscore(2, 'science', 88);
|
|
insertscore
|
|
-------------
|
|
4
|
|
(1 row)
|
|
|
|
select insertscore(3, 'math', 78);
|
|
insertscore
|
|
-------------
|
|
5
|
|
(1 row)
|
|
|
|
select insertscore(3, 'science', 82);
|
|
insertscore
|
|
-------------
|
|
6
|
|
(1 row)
|
|
|
|
select insertscore(4, 'math', 100);
|
|
insertscore
|
|
-------------
|
|
7
|
|
(1 row)
|
|
|
|
select insertscore(4, 'science', 66);
|
|
insertscore
|
|
-------------
|
|
8
|
|
(1 row)
|
|
|
|
select insertscore(5, 'math', 57);
|
|
insertscore
|
|
-------------
|
|
9
|
|
(1 row)
|
|
|
|
select insertscore(5, 'science', 68);
|
|
insertscore
|
|
-------------
|
|
10
|
|
(1 row)
|
|
|
|
create view stud_class as select s.student_name, c.class_name from students s join classes c on s.class_id = c.class_id;
|
|
create view stud_score as select b.student_name, a.subject, a.score, getrank(a.score) from studentscores a join students b on a.student_id = b.student_id;
|
|
create table t_pkg (c1 text);
|
|
create package p_batch as
|
|
procedure insertpkg(txt text);
|
|
function batchinsert(num int) return int;
|
|
end p_batch;
|
|
/
|
|
create package body p_batch as
|
|
procedure insertpkg(txt text)
|
|
as
|
|
begin
|
|
insert into t_pkg values (txt);
|
|
end;
|
|
|
|
function batchinsert(num int)
|
|
return int
|
|
is
|
|
start int := 0;
|
|
i int := 0;
|
|
begin
|
|
select count(*) into start from t_pkg;
|
|
for i in select generate_series(1, num) loop
|
|
insert into t1 values (start + i, left((pg_catalog.random() * i)::text, 6));
|
|
end loop;
|
|
|
|
return i;
|
|
end;
|
|
end p_batch;
|
|
/
|
|
call gms_utility.compile_schema('test_utility_compile');
|
|
compile_schema
|
|
----------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.compile_schema('test_utility_compile', false);
|
|
compile_schema
|
|
----------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.compile_schema('test_utility_compile', true, false);
|
|
compile_schema
|
|
----------------
|
|
|
|
(1 row)
|
|
|
|
call gms_utility.compile_schema(); -- error
|
|
ERROR: function "compile_schema" with 0 parameters doesn't exist
|
|
call gms_utility.compile_schema(1); -- error
|
|
ERROR: schema "1" does not exists
|
|
CONTEXT: SQL statement "CALL gms_utility.compile_schema_c_func(schema,compile_all,reuse_settings)"
|
|
PL/pgSQL function gms_utility.compile_schema(character varying,boolean,boolean) line 2 at PERFORM
|
|
call gms_utility.compile_schema('no_schema'); -- error
|
|
ERROR: schema "no_schema" does not exists
|
|
CONTEXT: SQL statement "CALL gms_utility.compile_schema_c_func(schema,compile_all,reuse_settings)"
|
|
PL/pgSQL function gms_utility.compile_schema(character varying,boolean,boolean) line 2 at PERFORM
|
|
create or replace function getrank(score int)
|
|
returns text
|
|
as $$
|
|
declare
|
|
rk text;
|
|
begin
|
|
if score = 100 then
|
|
rk := 'perfect';
|
|
elsif score >= 80 then
|
|
rk := 'nice';
|
|
elsif score >= 60 then
|
|
rk := 'ordinary';
|
|
elsif score >= 0 then
|
|
rk := 'poor';
|
|
else
|
|
rk := 'error';
|
|
end if;
|
|
|
|
return rk;
|
|
end;
|
|
$$ language plpgsql;
|
|
call gms_utility.compile_schema('test_utility_compile');
|
|
compile_schema
|
|
----------------
|
|
|
|
(1 row)
|
|
|
|
drop package body p_batch;
|
|
drop package p_batch;
|
|
NOTICE: drop cascades to 2 other objects
|
|
DETAIL: drop cascades to function test_utility_compile.insertpkg(text)
|
|
drop cascades to function test_utility_compile.batchinsert(integer)
|
|
drop table t_pkg;
|
|
drop view stud_score;
|
|
drop view stud_class;
|
|
drop function getrank;
|
|
drop function insertscore;
|
|
drop procedure insertstudent;
|
|
drop procedure insertclass;
|
|
drop table studentscores;
|
|
drop table students;
|
|
drop table classes;
|
|
-- test invalid object compile
|
|
set behavior_compat_options = 'plpgsql_dependency';
|
|
create type s_type as (
|
|
id integer,
|
|
name varchar,
|
|
addr text
|
|
);
|
|
create or replace procedure type_alter(a s_type)
|
|
is
|
|
begin
|
|
raise info 'call a: %', a;
|
|
end;
|
|
/
|
|
select valid from pg_object where object_type='p' and object_oid in (select oid from pg_proc where propackageid = 0 and proname='type_alter' and pronamespace = (select oid from pg_namespace where nspname = 'test_utility_compile'));
|
|
valid
|
|
-------
|
|
(0 rows)
|
|
|
|
alter type s_type add attribute a int;
|
|
select valid from pg_object where object_type='p' and object_oid in (select oid from pg_proc where propackageid = 0 and proname='type_alter' and pronamespace = (select oid from pg_namespace where nspname = 'test_utility_compile'));
|
|
valid
|
|
-------
|
|
(0 rows)
|
|
|
|
call gms_utility.compile_schema('test_utility_compile', false);
|
|
compile_schema
|
|
----------------
|
|
|
|
(1 row)
|
|
|
|
select valid from pg_object where object_type='p' and object_oid in (select oid from pg_proc where propackageid = 0 and proname='type_alter' and pronamespace = (select oid from pg_namespace where nspname = 'test_utility_compile'));
|
|
valid
|
|
-------
|
|
(0 rows)
|
|
|
|
create table stu(sno int, name varchar, sex varchar, cno int);
|
|
create type r1 as (a int, c stu%rowtype);
|
|
create or replace package pkg
|
|
is
|
|
procedure proc1(p_in r1);
|
|
end pkg;
|
|
/
|
|
create or replace package body pkg
|
|
is
|
|
declare
|
|
v1 r1;
|
|
v2 stu%rowtype;
|
|
procedure proc1(p_in r1) as
|
|
begin
|
|
raise info 'call p_in: %', p_in;
|
|
end;
|
|
end pkg;
|
|
/
|
|
call pkg.proc1((1,(1,'zhang','m',1)));
|
|
INFO: call p_in: (1,"(1,zhang,m,1)")
|
|
proc1
|
|
-------
|
|
|
|
(1 row)
|
|
|
|
select valid from pg_object where object_type='b' and object_oid in (select oid from gs_package where pkgname='pkg' and pkgnamespace = (select oid from pg_namespace where nspname = 'test_utility_compile'));
|
|
valid
|
|
-------
|
|
(0 rows)
|
|
|
|
alter table stu add column b int;
|
|
select valid from pg_object where object_type='b' and object_oid in (select oid from gs_package where pkgname='pkg' and pkgnamespace = (select oid from pg_namespace where nspname = 'test_utility_compile'));
|
|
valid
|
|
-------
|
|
(0 rows)
|
|
|
|
call gms_utility.compile_schema('test_utility_compile', false);
|
|
compile_schema
|
|
----------------
|
|
|
|
(1 row)
|
|
|
|
select valid from pg_object where object_type='b' and object_oid in (select oid from gs_package where pkgname='pkg' and pkgnamespace = (select oid from pg_namespace where nspname = 'test_utility_compile'));
|
|
valid
|
|
-------
|
|
(0 rows)
|
|
|
|
create view v_stu as select * from stu;
|
|
select * from v_stu;
|
|
sno | name | sex | cno | b
|
|
-----+------+-----+-----+---
|
|
(0 rows)
|
|
|
|
select valid from pg_object where object_type='v' and object_oid in (select oid from pg_class where relname='v_stu' and relkind='v' and relnamespace = (select oid from pg_namespace where nspname = 'test_utility_compile'));
|
|
valid
|
|
-------
|
|
t
|
|
(1 row)
|
|
|
|
alter table stu drop column b;
|
|
select valid from pg_object where object_type='v' and object_oid in (select oid from pg_class where relname='v_stu' and relkind='v' and relnamespace = (select oid from pg_namespace where nspname = 'test_utility_compile'));
|
|
valid
|
|
-------
|
|
f
|
|
(1 row)
|
|
|
|
call gms_utility.compile_schema('test_utility_compile', false);
|
|
WARNING: Compile view "v_stu" failed
|
|
CONTEXT: SQL statement "CALL gms_utility.compile_schema_c_func(schema,compile_all,reuse_settings)"
|
|
PL/pgSQL function gms_utility.compile_schema(character varying,boolean,boolean) line 2 at PERFORM
|
|
compile_schema
|
|
----------------
|
|
|
|
(1 row)
|
|
|
|
select valid from pg_object where object_type='v' and object_oid in (select oid from pg_class where relname='v_stu' and relkind='v' and relnamespace = (select oid from pg_namespace where nspname = 'test_utility_compile'));
|
|
valid
|
|
-------
|
|
f
|
|
(1 row)
|
|
|
|
select * from v_stu;
|
|
ERROR: The view v_stu is invalid, please make it valid before operation.
|
|
HINT: Please re-add missing table fields.
|
|
alter table stu add column b int;
|
|
select valid from pg_object where object_type='v' and object_oid in (select oid from pg_class where relname='v_stu' and relkind='v' and relnamespace = (select oid from pg_namespace where nspname = 'test_utility_compile'));
|
|
valid
|
|
-------
|
|
f
|
|
(1 row)
|
|
|
|
call gms_utility.compile_schema('test_utility_compile', false);
|
|
compile_schema
|
|
----------------
|
|
|
|
(1 row)
|
|
|
|
select valid from pg_object where object_type='v' and object_oid in (select oid from pg_class where relname='v_stu' and relkind='v' and relnamespace = (select oid from pg_namespace where nspname = 'test_utility_compile'));
|
|
valid
|
|
-------
|
|
t
|
|
(1 row)
|
|
|
|
select * from v_stu;
|
|
sno | name | sex | cno | b
|
|
-----+------+-----+-----+---
|
|
(0 rows)
|
|
|
|
drop view v_stu;
|
|
drop package body pkg;
|
|
drop package pkg cascade;
|
|
ERROR: syntax error at or near "cascade"
|
|
LINE 1: drop package pkg cascade;
|
|
^
|
|
drop type r1;
|
|
drop table stu;
|
|
drop procedure type_alter;
|
|
-- test procedure compile error
|
|
set behavior_compat_options = 'plpgsql_dependency';
|
|
create or replace procedure proc_err(p_in s_type) as
|
|
begin
|
|
raise info 'call p_in: %', p_in;
|
|
end;
|
|
/
|
|
drop type s_type;
|
|
select valid from pg_object where object_type='p' and object_oid in (select oid from pg_proc where propackageid = 0 and proname='proc_err' and pronamespace = (select oid from pg_namespace where nspname = 'test_utility_compile'));
|
|
valid
|
|
-------
|
|
(0 rows)
|
|
|
|
call gms_utility.compile_schema('test_utility_compile', false);
|
|
WARNING: "proc_err" header is undefined, you can try to recreate.
|
|
CONTEXT: SQL statement "CALL gms_utility.compile_schema_c_func(schema,compile_all,reuse_settings)"
|
|
PL/pgSQL function gms_utility.compile_schema(character varying,boolean,boolean) line 2 at PERFORM
|
|
compile_schema
|
|
----------------
|
|
|
|
(1 row)
|
|
|
|
select valid from pg_object where object_type='p' and object_oid in (select oid from pg_proc where propackageid = 0 and proname='proc_err' and pronamespace = (select oid from pg_namespace where nspname = 'test_utility_compile'));
|
|
valid
|
|
-------
|
|
(0 rows)
|
|
|
|
drop procedure proc_err;
|
|
drop schema test_utility_compile cascade;
|
|
NOTICE: drop cascades to 2 other objects
|
|
--?DETAIL: drop cascades to package.*
|
|
drop cascades to function test_utility_compile.proc1(undefined)
|
|
---------------------------
|
|
-- name tokenize
|
|
---------------------------
|
|
create schema test_utility_tokenize;
|
|
set search_path to test_utility_tokenize;
|
|
declare
|
|
name varchar2(50) := 'peer.lokppe.vuumee@ookeyy';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end;
|
|
/
|
|
INFO: a = PEER, b = LOKPPE, c = VUUMEE, dblink = OOKEYY, nextpos = 25
|
|
-- test empty character
|
|
declare
|
|
name varchar2(50) := NULL;
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end; -- error
|
|
/
|
|
ERROR: Invalid input value
|
|
CONTEXT: PL/pgSQL function gms_utility.name_tokenize(character varying) line 4 at assignment
|
|
SQL statement "CALL gms_utility.name_tokenize(name,a,b,c,dblink,nextpos)"
|
|
PL/pgSQL function inline_code_block line 8 at SQL statement
|
|
declare
|
|
name varchar2(50) := '';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end; -- error
|
|
/
|
|
ERROR: Invalid input value
|
|
CONTEXT: PL/pgSQL function gms_utility.name_tokenize(character varying) line 4 at assignment
|
|
SQL statement "CALL gms_utility.name_tokenize(name,a,b,c,dblink,nextpos)"
|
|
PL/pgSQL function inline_code_block line 8 at SQL statement
|
|
declare
|
|
name varchar2(50) := '""';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end; -- error
|
|
/
|
|
ERROR: Invalid input value """" with zero length
|
|
CONTEXT: PL/pgSQL function gms_utility.name_tokenize(character varying) line 4 at assignment
|
|
SQL statement "CALL gms_utility.name_tokenize(name,a,b,c,dblink,nextpos)"
|
|
PL/pgSQL function inline_code_block line 8 at SQL statement
|
|
declare
|
|
name varchar2(50) := ' . ';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end; -- error
|
|
/
|
|
ERROR: Invalid input value " . " with special words
|
|
CONTEXT: PL/pgSQL function gms_utility.name_tokenize(character varying) line 4 at assignment
|
|
SQL statement "CALL gms_utility.name_tokenize(name,a,b,c,dblink,nextpos)"
|
|
PL/pgSQL function inline_code_block line 8 at SQL statement
|
|
declare
|
|
name varchar2(50) := '" . "';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end;
|
|
/
|
|
INFO: a = . , b = <NULL>, c = <NULL>, dblink = <NULL>, nextpos = 7
|
|
declare
|
|
name varchar2(50) := 'peer. lokppe.vuumee@ookeyy';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end;
|
|
/
|
|
INFO: a = PEER, b = LOKPPE, c = VUUMEE, dblink = OOKEYY, nextpos = 28
|
|
declare
|
|
name varchar2(50) := 'peer." lokppe.vuumee"@ookeyy';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end;
|
|
/
|
|
INFO: a = PEER, b = lokppe.vuumee, c = <NULL>, dblink = OOKEYY, nextpos = 30
|
|
-- test support special char
|
|
declare
|
|
name varchar2(50) := 'peer._lokppe.vuumee@ookeyy';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end; -- ok for og, error for A
|
|
/
|
|
INFO: a = PEER, b = _LOKPPE, c = VUUMEE, dblink = OOKEYY, nextpos = 26
|
|
declare
|
|
name varchar2(50) := 'peer.lokp_pe.vuumee@ookeyy';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end;
|
|
/
|
|
INFO: a = PEER, b = LOKP_PE, c = VUUMEE, dblink = OOKEYY, nextpos = 26
|
|
declare
|
|
name varchar2(50) := 'peer.$lokppe.vuumee@ookeyy';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end; -- ok for og, error for A
|
|
/
|
|
INFO: a = PEER, b = $LOKPPE, c = VUUMEE, dblink = OOKEYY, nextpos = 26
|
|
declare
|
|
name varchar2(50) := 'peer.lokp$pe.vuumee@ookeyy';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end;
|
|
/
|
|
INFO: a = PEER, b = LOKP$PE, c = VUUMEE, dblink = OOKEYY, nextpos = 26
|
|
declare
|
|
name varchar2(50) := 'peer.lokp233pe.vuumee@ookeyy';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end;
|
|
/
|
|
INFO: a = PEER, b = LOKP233PE, c = VUUMEE, dblink = OOKEYY, nextpos = 28
|
|
declare
|
|
name varchar2(50) := 'peer.233lokppe.vuumee@ookeyy';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end; -- a = PEER, b = , c = , dblink = , nextpos = 4
|
|
/
|
|
INFO: a = PEER, b = <NULL>, c = <NULL>, dblink = <NULL>, nextpos = 4
|
|
declare
|
|
name varchar2(50) := 'peer.-lokppe.vuumee@ookeyy';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end; -- error
|
|
/
|
|
ERROR: Invalid input value "eer.-lokppe.vuumee@ookeyy"
|
|
CONTEXT: PL/pgSQL function gms_utility.name_tokenize(character varying) line 4 at assignment
|
|
SQL statement "CALL gms_utility.name_tokenize(name,a,b,c,dblink,nextpos)"
|
|
PL/pgSQL function inline_code_block line 8 at SQL statement
|
|
declare
|
|
name varchar2(50) := 'peer."-lokppe".vuumee@ookeyy';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end;
|
|
/
|
|
INFO: a = PEER, b = -lokppe, c = VUUMEE, dblink = OOKEYY, nextpos = 28
|
|
declare
|
|
name varchar2(50) := 'peer.lokp-pe.vuumee@ookeyy';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end; -- a = PEER, b = LOKP, c = , dblink = , nextpos = 9
|
|
/
|
|
INFO: a = PEER, b = LOKP, c = <NULL>, dblink = <NULL>, nextpos = 9
|
|
declare
|
|
name varchar2(50) := 'peer.lokp=pe.vuumee@ookeyy';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end; -- a = PEER, b = LOKP, c = , dblink = , nextpos = 9
|
|
/
|
|
INFO: a = PEER, b = LOKP, c = <NULL>, dblink = <NULL>, nextpos = 9
|
|
declare
|
|
name varchar2(50) := 'peer.=lokppe.vuumee@ookeyy';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end; -- error
|
|
/
|
|
ERROR: Invalid input value "eer.=lokppe.vuumee@ookeyy"
|
|
CONTEXT: PL/pgSQL function gms_utility.name_tokenize(character varying) line 4 at assignment
|
|
SQL statement "CALL gms_utility.name_tokenize(name,a,b,c,dblink,nextpos)"
|
|
PL/pgSQL function inline_code_block line 8 at SQL statement
|
|
declare
|
|
name varchar2(50) := 'peer.lokp`pe.vuumee@ookeyy';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end; -- error
|
|
/
|
|
ERROR: Invalid input value "peer.lokp`pe.vuumee@ookeyy" with special character
|
|
CONTEXT: PL/pgSQL function gms_utility.name_tokenize(character varying) line 4 at assignment
|
|
SQL statement "CALL gms_utility.name_tokenize(name,a,b,c,dblink,nextpos)"
|
|
PL/pgSQL function inline_code_block line 8 at SQL statement
|
|
declare
|
|
name varchar2(50) := 'peer.lokp~pe.vuumee@ookeyy';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end; -- error
|
|
/
|
|
ERROR: Invalid input value "peer.lokp~pe.vuumee@ookeyy" with special character
|
|
CONTEXT: PL/pgSQL function gms_utility.name_tokenize(character varying) line 4 at assignment
|
|
SQL statement "CALL gms_utility.name_tokenize(name,a,b,c,dblink,nextpos)"
|
|
PL/pgSQL function inline_code_block line 8 at SQL statement
|
|
declare
|
|
name varchar2(50) := 'peer.lokp%pe.vuumee@ookeyy';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end; -- error
|
|
/
|
|
ERROR: Invalid input value "peer.lokp%pe.vuumee@ookeyy" with special character
|
|
CONTEXT: PL/pgSQL function gms_utility.name_tokenize(character varying) line 4 at assignment
|
|
SQL statement "CALL gms_utility.name_tokenize(name,a,b,c,dblink,nextpos)"
|
|
PL/pgSQL function inline_code_block line 8 at SQL statement
|
|
-- test keyword
|
|
declare
|
|
name varchar2(50) := 'peer.table.vuumee@ookeyy';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end; -- error
|
|
/
|
|
ERROR: Invalid input value "peer.table.vuumee@ookeyy" with special words
|
|
CONTEXT: PL/pgSQL function gms_utility.name_tokenize(character varying) line 4 at assignment
|
|
SQL statement "CALL gms_utility.name_tokenize(name,a,b,c,dblink,nextpos)"
|
|
PL/pgSQL function inline_code_block line 8 at SQL statement
|
|
-- test @
|
|
declare
|
|
name varchar2(50) := 'peer.lokppe.vuumee@';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end; -- error
|
|
/
|
|
ERROR: Invalid input value "peer.lokppe.vuumee@" with special words
|
|
CONTEXT: PL/pgSQL function gms_utility.name_tokenize(character varying) line 4 at assignment
|
|
SQL statement "CALL gms_utility.name_tokenize(name,a,b,c,dblink,nextpos)"
|
|
PL/pgSQL function inline_code_block line 8 at SQL statement
|
|
declare
|
|
name varchar2(50) := 'peer.lokppe.vuumee@_ookeyy';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end; -- ok for og, error for A
|
|
/
|
|
INFO: a = PEER, b = LOKPPE, c = VUUMEE, dblink = _OOKEYY, nextpos = 26
|
|
declare
|
|
name varchar2(50) := 'peer.lokppe.vuumee@"_ookeyy"';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end;
|
|
/
|
|
INFO: a = PEER, b = LOKPPE, c = VUUMEE, dblink = _ookeyy, nextpos = 28
|
|
declare
|
|
name varchar2(50) := 'peer.lokppe.vuumee@$ookeyy';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end; -- ok for og, error for A
|
|
/
|
|
INFO: a = PEER, b = LOKPPE, c = VUUMEE, dblink = $OOKEYY, nextpos = 26
|
|
declare
|
|
name varchar2(50) := 'peer.lokppe.vuumee@"$ookeyy"';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end;
|
|
/
|
|
INFO: a = PEER, b = LOKPPE, c = VUUMEE, dblink = $ookeyy, nextpos = 28
|
|
declare
|
|
name varchar2(50) := 'peer.lokppe.vuumee@123ookeyy';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end; -- error
|
|
/
|
|
ERROR: Invalid dblink value "peer.lokppe.vuumee@123ookeyy"
|
|
CONTEXT: PL/pgSQL function gms_utility.name_tokenize(character varying) line 4 at assignment
|
|
SQL statement "CALL gms_utility.name_tokenize(name,a,b,c,dblink,nextpos)"
|
|
PL/pgSQL function inline_code_block line 8 at SQL statement
|
|
declare
|
|
name varchar2(50) := 'peer.lokppe.vuumee@ookeyy.zk';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end;
|
|
/
|
|
INFO: a = PEER, b = LOKPPE, c = VUUMEE, dblink = OOKEYY.ZK, nextpos = 28
|
|
declare
|
|
name varchar2(50) := 'peer.lokppe.vuumee@';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end; -- error
|
|
/
|
|
ERROR: Invalid input value "peer.lokppe.vuumee@" with special words
|
|
CONTEXT: PL/pgSQL function gms_utility.name_tokenize(character varying) line 4 at assignment
|
|
SQL statement "CALL gms_utility.name_tokenize(name,a,b,c,dblink,nextpos)"
|
|
PL/pgSQL function inline_code_block line 8 at SQL statement
|
|
declare
|
|
name varchar2(50) := '@vuumee';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end; -- error
|
|
/
|
|
ERROR: Invalid input value "@vuumee" with special words
|
|
CONTEXT: PL/pgSQL function gms_utility.name_tokenize(character varying) line 4 at assignment
|
|
SQL statement "CALL gms_utility.name_tokenize(name,a,b,c,dblink,nextpos)"
|
|
PL/pgSQL function inline_code_block line 8 at SQL statement
|
|
declare
|
|
name varchar2(50) := 'peer.lokppe.vuumee@ook=eyy';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end; -- a = PEER, b = LOKPPE, c = VUUMEE, dblink = OOK, nextpos = 22
|
|
/
|
|
INFO: a = PEER, b = LOKPPE, c = VUUMEE, dblink = OOK, nextpos = 22
|
|
declare
|
|
name varchar2(50) := 'peer.lokppe.vuumee@=ookeyy';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end; -- error
|
|
/
|
|
ERROR: Invalid input value "eer.lokppe.vuumee@=ookeyy" with special words
|
|
CONTEXT: PL/pgSQL function gms_utility.name_tokenize(character varying) line 4 at assignment
|
|
SQL statement "CALL gms_utility.name_tokenize(name,a,b,c,dblink,nextpos)"
|
|
PL/pgSQL function inline_code_block line 8 at SQL statement
|
|
declare
|
|
name varchar2(50) := 'peer.lokppe.vuumee@ook%eyy';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end; -- error
|
|
/
|
|
ERROR: Invalid input value "peer.lokppe.vuumee@ook%eyy" with special character
|
|
CONTEXT: PL/pgSQL function gms_utility.name_tokenize(character varying) line 4 at assignment
|
|
SQL statement "CALL gms_utility.name_tokenize(name,a,b,c,dblink,nextpos)"
|
|
PL/pgSQL function inline_code_block line 8 at SQL statement
|
|
-- test double quote
|
|
declare
|
|
name varchar2(50) := 'peer.lokppe.vuumee"@ookeyy"';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end; -- a = PEER, b = LOKPPE, c = VUUMEE, dblink = , nextpos = 18
|
|
/
|
|
INFO: a = PEER, b = LOKPPE, c = VUUMEE, dblink = <NULL>, nextpos = 18
|
|
declare
|
|
name varchar2(50) := 'peer.lokppe.vu"ume"e@ookeyy';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end; -- a = PEER, b = LOKPPE, c = VU, dblink = , nextpos = 14
|
|
/
|
|
INFO: a = PEER, b = LOKPPE, c = VU, dblink = <NULL>, nextpos = 14
|
|
declare
|
|
name varchar2(50) := 'peer.lokppe."vu"ume"e@ookeyy';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end; -- a = PEER, b = LOKPPE, c = vu, dblink = , nextpos = 16
|
|
/
|
|
INFO: a = PEER, b = LOKPPE, c = vu, dblink = <NULL>, nextpos = 16
|
|
declare
|
|
name varchar2(50) := 'peer.lokppe.vuumee.aking@ookeyy';
|
|
a varchar2(50);
|
|
b varchar2(50);
|
|
c varchar2(50);
|
|
dblink varchar2(50);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end; -- error
|
|
/
|
|
ERROR: Invalid input value "peer.lokppe.vuumee.aking@ookeyy"
|
|
CONTEXT: PL/pgSQL function gms_utility.name_tokenize(character varying) line 4 at assignment
|
|
SQL statement "CALL gms_utility.name_tokenize(name,a,b,c,dblink,nextpos)"
|
|
PL/pgSQL function inline_code_block line 8 at SQL statement
|
|
-- test idnetifier overlength. og >= 64, A >= 128
|
|
declare
|
|
name varchar2(100) := 'peer.DoYouThinkCausalUnderstandingIsADefiningCharacteristicOfHumanCognition.vuumee.aking@ookeyy';
|
|
a varchar2(100);
|
|
b varchar2(100);
|
|
c varchar2(100);
|
|
dblink varchar2(100);
|
|
nextpos integer;
|
|
begin
|
|
gms_utility.name_tokenize(name, a, b, c, dblink, nextpos);
|
|
raise info 'a = %, b = %, c = %, dblink = %, nextpos = %', a, b, c, dblink, nextpos;
|
|
end; -- error
|
|
/
|
|
ERROR: identifier too long, max length is 64
|
|
CONTEXT: PL/pgSQL function gms_utility.name_tokenize(character varying) line 4 at assignment
|
|
SQL statement "CALL gms_utility.name_tokenize(name,a,b,c,dblink,nextpos)"
|
|
PL/pgSQL function inline_code_block line 8 at SQL statement
|
|
drop schema test_utility_tokenize;
|
|
---------------------------
|
|
-- name resolve
|
|
---------------------------
|
|
reset search_path;
|
|
-- prepare data
|
|
CREATE TABLE public.t_resolve (c1 NUMBER, c2 VARCHAR2(100));
|
|
CREATE UNIQUE INDEX IF NOT EXISTS public.t_resolve_c1_udx ON public.t_resolve(c1);
|
|
CREATE TABLE public.t_log (c1 NUMBER, c2 TIMESTAMP);
|
|
CREATE SEQUENCE IF NOT EXISTS public.t_seq
|
|
INCREMENT BY 1
|
|
NOMINVALUE
|
|
NOMAXVALUE
|
|
START WITH 1
|
|
NOCYCLE;
|
|
SELECT public.t_seq.NEXTVAL;
|
|
nextval
|
|
---------
|
|
1
|
|
(1 row)
|
|
|
|
CREATE OR REPLACE FUNCTION public.add_numbers (
|
|
p_num1 IN INT,
|
|
p_num2 IN INT
|
|
) RETURN NUMBER IS
|
|
v_result INT;
|
|
BEGIN
|
|
v_result := p_num1 + p_num2;
|
|
RETURN v_result;
|
|
END;
|
|
/
|
|
CREATE OR REPLACE PROCEDURE public.insert_val(name VARCHAR2(100)) IS
|
|
id INT := 0;
|
|
BEGIN
|
|
SELECT public.t_seq.NEXTVAL INTO id;
|
|
INSERT INTO public.t_resolve VALUES (id, name);
|
|
END;
|
|
/
|
|
CREATE OR REPLACE FUNCTION public.tg_log() RETURNS TRIGGER AS
|
|
$$
|
|
BEGIN
|
|
INSERT INTO public.t_log VALUES (NEW.c1, SYSDATE);
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE PLPGSQL;
|
|
CREATE TRIGGER log_resolve_after_insert
|
|
AFTER INSERT ON public.t_resolve
|
|
FOR EACH ROW
|
|
EXECUTE PROCEDURE public.tg_log();
|
|
CREATE OR REPLACE PACKAGE public.t_pkg IS
|
|
FUNCTION multi_number(num1 IN INT, num2 IN INT) RETURN INT;
|
|
PROCEDURE delete_val(id IN INT);
|
|
END t_pkg;
|
|
/
|
|
CREATE OR REPLACE PACKAGE BODY public.t_pkg IS
|
|
FUNCTION multi_number(num1 IN INT, num2 IN INT) RETURN INT IS
|
|
v_res INT;
|
|
BEGIN
|
|
v_res = num1 * num2;
|
|
return v_res;
|
|
END multi_number;
|
|
|
|
PROCEDURE delete_val(id IN INT) AS
|
|
BEGIN
|
|
DELETE FROM t_resolve WHERE c1 = id;
|
|
END delete_val;
|
|
END t_pkg;
|
|
/
|
|
CREATE TYPE public.t_typ AS (t1 INT, t2 TEXT);
|
|
CREATE OR REPLACE SYNONYM public.syn_tbl FOR t_resolve;
|
|
CREATE OR REPLACE SYNONYM public.syn_idx FOR t_resolve_c1_udx;
|
|
CREATE OR REPLACE SYNONYM public.syn_seq FOR t_seq;
|
|
CREATE OR REPLACE SYNONYM public.syn_fun FOR add_numbers;
|
|
CREATE OR REPLACE SYNONYM public.syn_pro FOR insert_val;
|
|
CREATE OR REPLACE SYNONYM public.syn_tg FOR log_resolve_after_insert;
|
|
CREATE OR REPLACE SYNONYM public.syn_pkg FOR t_pkg;
|
|
CREATE OR REPLACE SYNONYM public.syn_typ FOR t_typ;
|
|
-- test table
|
|
declare
|
|
name varchar2 := 'public.t_resolve';
|
|
context number := 0;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = T_RESOLVE, part2 = <NULL>, dblink = <NULL>, part1_type = 2, object_number =.*
|
|
declare
|
|
name varchar2 := 't_resolve';
|
|
context number := 0;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = T_RESOLVE, part2 = <NULL>, dblink = <NULL>, part1_type = 2, object_number =.*
|
|
declare
|
|
name varchar2 := 'public.syn_tbl';
|
|
context number := 0;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = T_RESOLVE, part2 = <NULL>, dblink = <NULL>, part1_type = 2, object_number =.*
|
|
declare
|
|
name varchar2 := 'syn_tbl';
|
|
context number := 0;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = T_RESOLVE, part2 = <NULL>, dblink = <NULL>, part1_type = 2, object_number =.*
|
|
-- test PL/SQL
|
|
declare
|
|
name varchar2 := 'public.add_numbers';
|
|
context number := 1;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = <NULL>, part2 = ADD_NUMBERS, dblink = <NULL>, part1_type = 8, object_number =.*
|
|
declare
|
|
name varchar2 := 'add_numbers';
|
|
context number := 1;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = <NULL>, part2 = ADD_NUMBERS, dblink = <NULL>, part1_type = 8, object_number =.*
|
|
declare
|
|
name varchar2 := 'public.syn_fun';
|
|
context number := 1;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = <NULL>, part2 = ADD_NUMBERS, dblink = <NULL>, part1_type = 8, object_number =.*
|
|
declare
|
|
name varchar2 := 'syn_fun';
|
|
context number := 1;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = <NULL>, part2 = ADD_NUMBERS, dblink = <NULL>, part1_type = 8, object_number =.*
|
|
declare
|
|
name varchar2 := 'public.insert_val';
|
|
context number := 1;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = <NULL>, part2 = INSERT_VAL, dblink = <NULL>, part1_type = 7, object_number =.*
|
|
declare
|
|
name varchar2 := 'insert_val';
|
|
context number := 1;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = <NULL>, part2 = INSERT_VAL, dblink = <NULL>, part1_type = 7, object_number =.*
|
|
declare
|
|
name varchar2 := 'public.syn_pro';
|
|
context number := 1;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = <NULL>, part2 = INSERT_VAL, dblink = <NULL>, part1_type = 7, object_number =.*
|
|
declare
|
|
name varchar2 := 'syn_pro';
|
|
context number := 1;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = <NULL>, part2 = INSERT_VAL, dblink = <NULL>, part1_type = 7, object_number =.*
|
|
-- test package
|
|
declare
|
|
name varchar2 := 'public.t_pkg.multi_number';
|
|
context number := 1;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = T_PKG, part2 = MULTI_NUMBER, dblink = <NULL>, part1_type = 9, object_number =.*
|
|
declare
|
|
name varchar2 := 'public.t_pkg.delete_val';
|
|
context number := 1;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = T_PKG, part2 = DELETE_VAL, dblink = <NULL>, part1_type = 9, object_number =.*
|
|
declare
|
|
name varchar2 := 'public.t_pkg.qwer';
|
|
context number := 1;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = T_PKG, part2 = QWER, dblink = <NULL>, part1_type = 9, object_number =.*
|
|
declare
|
|
name varchar2 := 't_pkg.multi_number';
|
|
context number := 1;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = T_PKG, part2 = MULTI_NUMBER, dblink = <NULL>, part1_type = 9, object_number =.*
|
|
declare
|
|
name varchar2 := 't_pkg.delete_val';
|
|
context number := 1;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = T_PKG, part2 = DELETE_VAL, dblink = <NULL>, part1_type = 9, object_number =.*
|
|
declare
|
|
name varchar2 := 't_pkg.qwer';
|
|
context number := 1;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = T_PKG, part2 = QWER, dblink = <NULL>, part1_type = 9, object_number =.*
|
|
declare
|
|
name varchar2 := 'public.syn_pkg.multi_number';
|
|
context number := 1;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = T_PKG, part2 = MULTI_NUMBER, dblink = <NULL>, part1_type = 9, object_number =.*
|
|
declare
|
|
name varchar2 := 'public.syn_pkg.delete_var';
|
|
context number := 1;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = T_PKG, part2 = DELETE_VAR, dblink = <NULL>, part1_type = 9, object_number =.*
|
|
declare
|
|
name varchar2 := 'public.syn_pkg.qwer';
|
|
context number := 1;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = T_PKG, part2 = QWER, dblink = <NULL>, part1_type = 9, object_number =.*
|
|
declare
|
|
name varchar2 := 'syn_pkg.multi_number';
|
|
context number := 1;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = T_PKG, part2 = MULTI_NUMBER, dblink = <NULL>, part1_type = 9, object_number =.*
|
|
declare
|
|
name varchar2 := 'syn_pkg.delete_var';
|
|
context number := 1;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = T_PKG, part2 = DELETE_VAR, dblink = <NULL>, part1_type = 9, object_number =.*
|
|
declare
|
|
name varchar2 := 'syn_pkg.qwer';
|
|
context number := 1;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = T_PKG, part2 = QWER, dblink = <NULL>, part1_type = 9, object_number =.*
|
|
-- test trigger
|
|
declare
|
|
name varchar2 := 'public.log_resolve_after_insert';
|
|
context number := 3;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = LOG_RESOLVE_AFTER_INSERT, part2 = <NULL>, dblink = <NULL>, part1_type = 12, object_number =.*
|
|
declare
|
|
name varchar2 := 'public.syn_tg';
|
|
context number := 3;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end; -- error
|
|
/
|
|
ERROR: The Object is not exists
|
|
CONTEXT: PL/pgSQL function gms_utility.name_resolve(character varying,numeric) line 4 at assignment
|
|
SQL statement "CALL gms_utility.name_resolve(name,context,schema,part1,part2,dblink,part1_type,object_number)"
|
|
PL/pgSQL function inline_code_block line 10 at SQL statement
|
|
declare
|
|
name varchar2 := 'log_resolve_after_insert';
|
|
context number := 3;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = LOG_RESOLVE_AFTER_INSERT, part2 = <NULL>, dblink = <NULL>, part1_type = 12, object_number =.*
|
|
declare
|
|
name varchar2 := 'syn_tg';
|
|
context number := 3;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end; -- error
|
|
/
|
|
ERROR: The Object is not exists
|
|
CONTEXT: PL/pgSQL function gms_utility.name_resolve(character varying,numeric) line 4 at assignment
|
|
SQL statement "CALL gms_utility.name_resolve(name,context,schema,part1,part2,dblink,part1_type,object_number)"
|
|
PL/pgSQL function inline_code_block line 10 at SQL statement
|
|
-- test sequence
|
|
declare
|
|
name varchar2 := 'public.t_seq';
|
|
context number := 2;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = T_SEQ, part2 = <NULL>, dblink = <NULL>, part1_type = 6, object_number =.*
|
|
declare
|
|
name varchar2 := 'public.syn_seq';
|
|
context number := 2;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = T_SEQ, part2 = <NULL>, dblink = <NULL>, part1_type = 6, object_number =.*
|
|
declare
|
|
name varchar2 := 't_seq';
|
|
context number := 2;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = T_SEQ, part2 = <NULL>, dblink = <NULL>, part1_type = 6, object_number =.*
|
|
declare
|
|
name varchar2 := 'syn_seq';
|
|
context number := 2;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = T_SEQ, part2 = <NULL>, dblink = <NULL>, part1_type = 6, object_number =.*
|
|
-- test type
|
|
declare
|
|
name varchar2 := 'public.t_typ';
|
|
context number := 7;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = T_TYP, part2 = <NULL>, dblink = <NULL>, part1_type = 13, object_number =.*
|
|
declare
|
|
name varchar2 := 'public.syn_typ';
|
|
context number := 7;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = T_TYP, part2 = <NULL>, dblink = <NULL>, part1_type = 13, object_number =.*
|
|
declare
|
|
name varchar2 := 't_typ';
|
|
context number := 7;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = T_TYP, part2 = <NULL>, dblink = <NULL>, part1_type = 13, object_number =.*
|
|
declare
|
|
name varchar2 := 'syn_typ';
|
|
context number := 7;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = T_TYP, part2 = <NULL>, dblink = <NULL>, part1_type = 13, object_number =.*
|
|
-- test index
|
|
declare
|
|
name varchar2 := 'public.t_resolve_c1_udx';
|
|
context number := 9;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = T_RESOLVE_C1_UDX, part2 = <NULL>, dblink = <NULL>, part1_type = 1, object_number =.*
|
|
declare
|
|
name varchar2 := 'public.syn_idx';
|
|
context number := 9;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end; -- error
|
|
/
|
|
ERROR: The Object is not exists
|
|
CONTEXT: PL/pgSQL function gms_utility.name_resolve(character varying,numeric) line 4 at assignment
|
|
SQL statement "CALL gms_utility.name_resolve(name,context,schema,part1,part2,dblink,part1_type,object_number)"
|
|
PL/pgSQL function inline_code_block line 10 at SQL statement
|
|
declare
|
|
name varchar2 := 't_resolve_c1_udx';
|
|
context number := 9;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
--?INFO: schema = PUBLIC, part1 = T_RESOLVE_C1_UDX, part2 = <NULL>, dblink = <NULL>, part1_type = 1, object_number =.*
|
|
declare
|
|
name varchar2 := 'syn_idx';
|
|
context number := 9;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end; -- error
|
|
/
|
|
ERROR: The Object is not exists
|
|
CONTEXT: PL/pgSQL function gms_utility.name_resolve(character varying,numeric) line 4 at assignment
|
|
SQL statement "CALL gms_utility.name_resolve(name,context,schema,part1,part2,dblink,part1_type,object_number)"
|
|
PL/pgSQL function inline_code_block line 10 at SQL statement
|
|
-- test java
|
|
declare
|
|
name varchar2 := 'public.java.class';
|
|
context number := 4;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
ERROR: Unsupported context type: 4
|
|
CONTEXT: PL/pgSQL function gms_utility.name_resolve(character varying,numeric) line 4 at assignment
|
|
SQL statement "CALL gms_utility.name_resolve(name,context,schema,part1,part2,dblink,part1_type,object_number)"
|
|
PL/pgSQL function inline_code_block line 10 at SQL statement
|
|
declare
|
|
name varchar2 := 'public.java.class';
|
|
context number := 5;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
ERROR: Unsupported context type: 5
|
|
CONTEXT: PL/pgSQL function gms_utility.name_resolve(character varying,numeric) line 4 at assignment
|
|
SQL statement "CALL gms_utility.name_resolve(name,context,schema,part1,part2,dblink,part1_type,object_number)"
|
|
PL/pgSQL function inline_code_block line 10 at SQL statement
|
|
declare
|
|
name varchar2 := 'public.java.class';
|
|
context number := 6;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
ERROR: Unsupported context type: 6
|
|
CONTEXT: PL/pgSQL function gms_utility.name_resolve(character varying,numeric) line 4 at assignment
|
|
SQL statement "CALL gms_utility.name_resolve(name,context,schema,part1,part2,dblink,part1_type,object_number)"
|
|
PL/pgSQL function inline_code_block line 10 at SQL statement
|
|
declare
|
|
name varchar2 := 'public.java.class';
|
|
context number := 8;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
ERROR: Unsupported context type: 8
|
|
CONTEXT: PL/pgSQL function gms_utility.name_resolve(character varying,numeric) line 4 at assignment
|
|
SQL statement "CALL gms_utility.name_resolve(name,context,schema,part1,part2,dblink,part1_type,object_number)"
|
|
PL/pgSQL function inline_code_block line 10 at SQL statement
|
|
-- test with dblink
|
|
declare
|
|
name varchar2 := 'peer.lokppe.vuumee@ookeyy';
|
|
context number := 0;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
INFO: schema = PEER, part1 = LOKPPE, part2 = VUUMEE, dblink = OOKEYY, part1_type = 0, object_number = 0
|
|
declare
|
|
name varchar2 := 'peer@ookeyy';
|
|
context number := 1;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
INFO: schema = PEER, part1 = <NULL>, part2 = <NULL>, dblink = OOKEYY, part1_type = 0, object_number = 0
|
|
declare
|
|
name varchar2 := 'peer@ookeyy';
|
|
context number := 2;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
INFO: schema = PEER, part1 = <NULL>, part2 = <NULL>, dblink = OOKEYY, part1_type = 0, object_number = 0
|
|
declare
|
|
name varchar2 := 'peer.@ookeyy';
|
|
context number := 3;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end; -- error
|
|
/
|
|
ERROR: Invalid input value "peer.@ookeyy" with special words
|
|
CONTEXT: PL/pgSQL function gms_utility.name_resolve(character varying,numeric) line 4 at assignment
|
|
SQL statement "CALL gms_utility.name_resolve(name,context,schema,part1,part2,dblink,part1_type,object_number)"
|
|
PL/pgSQL function inline_code_block line 10 at SQL statement
|
|
declare
|
|
name varchar2 := 'peer.lokppe@ookeyy';
|
|
context number := 7;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
INFO: schema = PEER, part1 = LOKPPE, part2 = <NULL>, dblink = OOKEYY, part1_type = 0, object_number = 0
|
|
declare
|
|
name varchar2 := 'peer.lokppe@ookeyy';
|
|
context number := 9;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
INFO: schema = PEER, part1 = LOKPPE, part2 = <NULL>, dblink = OOKEYY, part1_type = 0, object_number = 0
|
|
declare
|
|
name varchar2 := 'peer.lokppe@ookeyy';
|
|
context number := 10;
|
|
schema varchar2;
|
|
part1 varchar2;
|
|
part2 varchar2;
|
|
dblink varchar2;
|
|
part1_type number;
|
|
object_number number;
|
|
begin
|
|
gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
|
|
raise info 'schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
|
|
end;
|
|
/
|
|
INFO: schema = PEER, part1 = LOKPPE, part2 = <NULL>, dblink = OOKEYY, part1_type = 0, object_number = 0
|
|
DROP SYNONYM syn_tbl;
|
|
DROP SYNONYM syn_idx;
|
|
DROP SYNONYM syn_seq;
|
|
DROP SYNONYM syn_fun;
|
|
DROP SYNONYM syn_pro;
|
|
DROP SYNONYM syn_tg;
|
|
DROP SYNONYM syn_pkg;
|
|
DROP SYNONYM syn_typ;
|
|
DROP TYPE public.t_typ;
|
|
DROP PACKAGE BODY public.t_pkg;
|
|
DROP PACKAGE public.t_pkg;
|
|
NOTICE: drop cascades to 2 other objects
|
|
DETAIL: drop cascades to function public.multi_number(integer,integer)
|
|
drop cascades to function public.delete_val(integer)
|
|
DROP TRIGGER log_resolve_after_insert ON t_resolve;
|
|
DROP FUNCTION public.tg_log;
|
|
DROP FUNCTION public.add_numbers;
|
|
DROP PROCEDURE public.insert_val;
|
|
DROP INDEX public.t_resolve_c1_udx;
|
|
DROP TABLE public.t_log;
|
|
DROP TABLE public.t_resolve;
|
|
DROP SEQUENCE public.t_seq;
|
|
drop extension gms_utility cascade;
|
|
NOTICE: drop cascades to 2 other objects
|
|
DETAIL: drop cascades to table gms_utility.t_into
|
|
drop cascades to table gms_utility.num
|
|
reset search_path;
|