PLSQL displays error: PLS-00487: Invalid reference to variabel

Question

PLSQL displays error: PLS-00487: Invalid reference to variabel

Navigation

#1 by (2 votes)

0

I have a PLSQL code: in which I get two values one numeric and another varchar.

Code:

create or replace FUNCTION hospitalTeste123(MatriculaMedico in number,  nomeSchema in varchar2 ) return varchar is

  type Tmedicos is record( 
    matMedico     nomeSchema.Tmedico.matricula%type, 
    especialidade nomeSchema.Tmedico.especialidade%type,
    cd_setor      nomeSchema.Tmedico.cd_setor%type
  );  

medico Tmedicos;

begin

  medico.matMedico       := 123;
  medico.especialidade   := 'geral';
  medico.cd_setor        := 'urgencia';

 RETURN medico.MatMedico;
end;

Tables Creation

 create table TMedico(
  matMedico     number(10) NOT NULL,
  especialidade varchar2(50) NOT NULL,
  cd_setor      varchar2(50) NOT NULL
 );

insert into TMedico(matMedico,especialidade,cd_setor) Values (120,'clinico-geral','UTI');
insert into TMedico(matMedico,especialidade,cd_setor) Values (150,'cardiologista','UTI');
insert into TMedico(matMedico,especialidade,cd_setor) Values (180,'pediatria','UTI');

Error message:

PLS-00487: Invalid reference to variabel 'nomeSchema'

Error (3.3): PL / SQL: Item ignored Error (4.19): PLS-00487: Invalid   reference to variable 'NOMESCHEMA' Error (13,3): PL / SQL: Statement   ignored Error (13,10): PLS-00320: the declaration of the type of this   expression is incomplete or malformed Error (14,3): PL / SQL: Statement   ignored Error (14,10): PLS-00320: the declaration of the type of this   expression is incomplete or malformed Error (15,3): PL / SQL: Statement   ignored Error (15,10): PLS-00320: the declaration of the type of this   expression is incomplete or malformed Error (17,2): PL / SQL: Statement   ignored Error (17,16): PLS-00320: the declaration of the type of this   expression is incomplete or malformed

The error is when I pass the name of the schema on which the table is stored. As each hospital has a different Schema.

Picture of the table with Schema

Callingthefunction

declarebeginDBMS_OUTPUT.PUT_LINE(hospitalTeste123(123,'dbamv'));end;

Errormessage:

ErrorReporting-ORA-06550:Line4,Column24:PLS-00905:Object DBAMV.HOSPITALTESTE123isinvalidORA-06550:line4,column3: PL/SQL:Statementignored 06550.00000-"line% s, column% s: \ n% s" * Cause: Usually a PL / SQL compilation error. * Action:

sql oracle pl-sql

asked by anonymous 14.05.2018 / 21:28

1 answer

How to create an Operation Confirmation alert? Save data in an Activity when changing Activity

score 2 · Accepted Answer

As I said in the comments, since a restructuring of the way of work is not possible, I will propose two solutions, one simpler and less dynamic and another dynamic, but that will be very annoying to maintain.

1) Create a Type with the same definitions as the tables and use this in their function.

Ex:

create or replace type Tmedicos as Object(
    matMedico     varchar(10),
    especialidade varchar(50),
    cd_setor      varchar(50)
  );

create or replace FUNCTION hospitalTeste123(MatriculaMedico in number,  nomeSchema in varchar2 ) return varchar is

   medico Tmedicos;

begin
  medico := TMedicos('123', 'geral', 'urgencia');

  :Retorno := medico.MatMedico;
end;

Not the most dynamic solution in the world, but since PL / SQL is a compiled language, you can not have this dynamism by passing the owner by parameter. This option will be very performative, but any changes to your table definitions need to be replicated in the type.

2) Treat everything with immediate execute, passing an output parameter to return the desired result.

Ex:

CREATE OR REPLACE FUNCTION hospitalTeste123(MatriculaMedico in number,  nomeSchema in varchar2 ) return varchar is

  retorno varchar2(10);
  Script CLOB;
begin
  Script := 'Declare' || chr(10) ||
            '  type Tmedicos is record(' || chr(10) ||
            '    matMedico     :USER.Tmedico.MatMedico%type,' || chr(10) ||
            '    especialidade :USER.Tmedico.especialidade%type,' || chr(10) ||
            '    cd_setor      :USER.Tmedico.cd_setor%type' || chr(10) ||
            '  );' || chr(10) ||
            '' || chr(10) ||
            '  medico Tmedicos;' || chr(10) ||
            '' || chr(10) ||
            'begin' || chr(10) ||
            '' || chr(10) ||
            '  medico.matMedico       := 123;' || chr(10) ||
            '  medico.especialidade   := ''geral'';' || chr(10) ||
            '  medico.cd_setor        := ''urgencia'';' || chr(10) ||
            '' || chr(10) ||
            ' :Retorno := medico.MatMedico;' || chr(10) ||
            'end;';
  Script := Replace(Script, ':USER', nomeSchema);

  execute immediate Script
    using out retorno; 

  RETURN retorno;
end;

This option is as dynamic as possible, but it is very laborious to maintain and will be slower, but it would have the dynamism to work with several owners. One option to improve maintenance would be to create a table that stores the script.