Migrando BD 11.2.0.4.0 para Forms 19.0.0.0.0

[tiago_pimenta]

Pessoal, boa tarde, tudo bem ???

Na empresa que trabalho, estamos com esse projeto de migrar o database da versão 11.2.0.4.0 para 19.0.0.0.0, porém, estamos com alguns objetos inválidos, por causa do type JSON, que no 11 não era nativo e se não me engano, a partir da versão 12, já é nativo.

Alguém passou por isso ? Estou tentando procurar nos documentos da Oracle algo para me ajudar nessa migração, mas não encontrei até o momento.

Obrigado.

[dr_gori]

Qual erro que está dando ?

[tiago_pimenta]

dr_gori, boa tarde, tudo bem ?

Os erros que estão dão são em vários objetos que utilizam de alguma forma, o type JSON. Abrindo o type JSON, os erros são esses:

Selecionar tudo

Compilation errors for TYPE BODY UMBRELLA.JSON

Error: PLS-00103: Encountered the symbol "." when expecting one of the following:
       
          (
Line: 80
Text: insert_value json_value := nvl(pair_value, json_value.makenull);

Error: PLS-00103: Encountered the symbol ")" when expecting one of the following:
       
          . ( , * @ % & = - + < / > at in is mod remainder not rem
          <an exponent (**)> <> or != or ~= >= <= <> and or like like2
          like4 likec between || multiset member submultiset format
       The symbol "(" was substituted for ")" to continue.
Line: 155
Text: put(pair_name, json_value(pair_value), position);

Error: PLS-00103: Encountered the symbol ";" when expecting one of the following:
       
          . ( ) , * % & = - + < / > at in is mod remainder not rem =>
          <an exponent (**)> <> or != or ~= >= <= <> and or like like2
          like4 likec between || multiset member submultiset
       The symbol ")" was substituted for ";" to continue.
Line: 155
Text: put(pair_name, json_value(pair_value), position);

Error: PLS-00103: Encountered the symbol ")" when expecting one of the following:
       
          ( - + case mod new not null <an identifier>
          <a double-quoted delimited-identifier> <a bind variable>
          continue avg count current exists max min prior sql stddev
          sum variance execute forall merge time timestamp interval
          date <a string literal with character set specification>
          <a number> <a single-quoted SQL string> pipe
          <an alternatively-quoted string literal with character set specification>
          <an alternatively
Line: 161
Text: put(pair_name, json_value(), position);

Error: PLS-00103: Encountered the symbol "ELSE"
Line: 162
Text: else

Error: PLS-00103: Encountered the symbol ")" when expecting one of the following:
       
          . ( , * @ % & = - + < / > at in is mod remainder not rem
          <an exponent (**)> <> or != or ~= >= <= <> and or like like2
          like4 likec between || multiset member submultiset format
       The symbol "(" was substituted for ")" to continue.
Line: 163
Text: put(pair_name, json_value(pair_value), position);

Error: PLS-00103: Encountered the symbol ";" when expecting one of the following:
       
          . ( ) , * % & = - + < / > at in is mod remainder not rem =>
          <an exponent (**)> <> or != or ~= >= <= <> and or like like2
          like4 likec between || multiset member submultiset
       The symbol ")" was substituted for ";" to continue.
Line: 163
Text: put(pair_name, json_value(pair_value), position);

Error: PLS-00103: Encountered the symbol "END"
Line: 165
Text: end;

Error: PLS-00103: Encountered the symbol ")" when expecting one of the following:
       
          ( - + case mod new not null <an identifier>
          <a double-quoted delimited-identifier> <a bind variable>
          continue avg count current exists max min prior sql stddev
          sum variance execute forall merge time timestamp interval
          date <a string literal with character set specification>
          <a number> <a single-quoted SQL string> pipe
          <an alternatively-quoted string literal with character set specification>
          <an alternatively
Line: 170
Text: put(pair_name, json_value(), position);

Error: PLS-00103: Encountered the symbol "ELSE"
Line: 171
Text: else

Error: PLS-00103: Encountered the symbol ")" when expecting one of the following:
       
          . ( , * @ % & = - + < / > at in is mod remainder not rem
          <an exponent (**)> <> or != or ~= >= <= <> and or like like2
          like4 likec between || multiset member submultiset format
       The symbol "(" was substituted for ")" to continue.
Line: 172
Text: put(pair_name, json_value(pair_value), position);

Error: PLS-00103: Encountered the symbol ";" when expecting one of the following:
       
          . ( ) , * % & = - + < / > at in is mod remainder not rem =>
          <an exponent (**)> <> or != or ~= >= <= <> and or like like2
          like4 likec between || multiset member submultiset
       The symbol ")" was substituted for ";" to continue.
Line: 172
Text: put(pair_name, json_value(pair_value), position);

Error: PLS-00103: Encountered the symbol "END"
Line: 174
Text: end;

Error: PLS-00103: Encountered the symbol ")" when expecting one of the following:
       
          ( - + case mod new not null <an identifier>
          <a double-quoted delimited-identifier> <a bind variable>
          continue avg count current exists max min prior sql stddev
          sum variance execute forall merge time timestamp interval
          date <a string literal with character set specification>
          <a number> <a single-quoted SQL string> pipe
          <an alternatively-quoted string literal with character set specification>
          <an alternatively
Line: 190
Text: put(pair_name, json_value(), position);

Error: PLS-00103: Encountered the symbol "ELSE"
Line: 191
Text: else

Error: PLS-00103: Encountered the symbol "END"
Line: 194
Text: end;

Error: PLS-00103: Encountered the symbol ")" when expecting one of the following:
       
          ( - + case mod new not null <an identifier>
          <a double-quoted delimited-identifier> <a bind variable>
          continue avg count current exists max min prior sql stddev
          sum variance execute forall merge time timestamp interval
          date <a string literal with character set specification>
          <a number> <a single-quoted SQL string> pipe
          <an alternatively-quoted string literal with character set specification>
          <an alternatively
Line: 199
Text: put(pair_name, json_value(), position);

Error: PLS-00103: Encountered the symbol "ELSE"
Line: 200
Text: else

Error: PLS-00103: Encountered the symbol "END"
Line: 203
Text: end;

[dr_gori]

Tenta criar um SYNONYM chamado JSON_VALUE que aponta para PLJSON_VALUE.
Talvez tenha que criar mais alguns:

Selecionar tudo

create synonym json_parser for pljson_parser;
create synonym json_printer for pljson_printer;
create synonym json_ext for pljson_ext;
create synonym json_dyn for pljson_dyn;
create synonym json_ml for pljson_ml;
create synonym json_xml for pljson_xml;
create synonym json_util_pkg for pljson_util_pkg;
create synonym json_helper for pljson_helper;
create synonym json_ac for pljson_ac;
create synonym json for pljson;
create synonym json_list for pljson_list;
create synonym json_value_array for pljson_value_array;
create synonym json_value for pljson_value;
create synonym json_table for pljson_table;

[tiago_pimenta]

dr_gori,

Ele já tinha a maioria dos SYNONYM criados, se não me engano criou só o último da lista.

Selecionar tudo

create synonym json_table for pljson_table;

Recompilei os objetos inválidos e continua com os mesmos erros, mas dessa vez, antes de aparecer os mesmos erros que colei acima, apareceu o seguinte erro por uns 2 segundos:

Selecionar tudo

PLS-00905 object BASE.JSON_VALUE_ARRAY is invalid
PL/SQL: Compilation unit analysis terminated

Ai depois apareceu os mesmos erros.

[tiago_pimenta]

dr_gori,

O erro "PLS-00905 object BASE.JSON_VALUE_ARRAY is invalid" que comentei acima, está dando na seguinte linha, no body do type do JSON.

Selecionar tudo

json_data json_value_array,

E editando o type 'JSON_VALUE_ARRAY", está dando o seguinte erro

Selecionar tudo

CREATE OR REPLACE TYPE "JSON_VALUE_ARRAY"                                          as table of json_value;

Erro

Selecionar tudo

Compilation errors for TYPE UMBRELLA.JSON_VALUE_ARRAY

Error: PLS-00488: 'JSON_VALUE' must be a type
Line: 1
Text: CREATE OR REPLACE TYPE "JSON_VALUE_ARRAY"                                          as table of json_value;

Error: PL/SQL: Compilation unit analysis terminated
Line: 1
Text: CREATE OR REPLACE TYPE "JSON_VALUE_ARRAY"                                          as table of json_value;

[dr_gori]

Parece que ele não está achando o JSON_VALUE.

Selecionar tudo

Error: PLS-00488: 'JSON_VALUE' must be a type

Que foi exatamente o sinônimo que a gente criou apontando pra pljson_value.

Roda ai essa query:

Selecionar tudo

SELECT * FROM DBA_OBJECTS where object_name like 'json$_%' escape '$'; 

E essa também:

Selecionar tudo

SELECT * FROM DBA_OBJECTS where object_name like 'pljson$_%' escape '$';