PLSQL shows error: PLS-00487: Invalid reference to Variabel

Question

PLSQL shows error: PLS-00487: Invalid reference to Variabel

Asked 6 years, 1 month ago

Viewed 436 times

0

I have a PLSQL code: in which I receive two values one numerical 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;

Script for Creating Tables

 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 'chemName'

Error(3,3): PL/SQL: Item Ignored Error(4,19): PLS-00487: Invalid Reference to variable 'SCHEMNAME' 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 in which the table is saved. Like every hospital has a different Schema.

Screenshot of the table with Schema

Calling the function

declare

begin
  DBMS_OUTPUT.PUT_LINE(hospitalTeste123(123,'dbamv')); 
end;

Error message:

Bug report - ORA-06550: row 4, column 24: PLS-00905: Object DBAMV.HOSPITALTESTE123 is invalid ORA-06550: row 4, column 3: PL/SQL: Statement Ignored 06550. 00000 - "line %s, column %s: n%s" *Cause: Usually a PL/SQL Compilation error. *Action:

The reference you are using to make this type, is it a valid table? Try to take the name of the schema and declare Tmedico.matricula%type

– Diego

2018/05/14 at 19:38
Another thing, came to check if you have Grant in these tables?

– Diego

2018/05/14 at 19:38
This valid. The point is that there is a schema for each hospital

– alexjosesilva

2018/05/14 at 19:39
I do have permission to record in the tables

– alexjosesilva

2018/05/14 at 19:39
The big question is to make this Generic process

– alexjosesilva

2018/05/14 at 19:40
As each hospital has a schema and step it by reference, so each doctor can be registered in a different hospital

– alexjosesilva

2018/05/14 at 19:41
I get it. The call would be correct, I think it’s something in releasing the schema access to the table

– Diego

2018/05/14 at 19:45
Let’s go continue this discussion in chat.

– alexjosesilva

2018/05/14 at 19:47
There is no OWNER named Schema on the server. There is?

– Reginaldo Rigo

2018/05/14 at 19:51
The table exists. I posted the photo with her schema

– alexjosesilva

2018/05/14 at 20:12
It is not possible to do this implementation by passing the Owner parameter in a varchar2, as oracle cannot guarantee that there will be these table definitions coming from a string, so it cannot compile. What you can do is create a global type on a User that all other users would have access Grant and use this type for their function, thus ensuring the necessary structure.

– Confundir

2018/05/15 at 12:15
Could I give example of this implementation @Confuse ?

– alexjosesilva

2018/05/16 at 21:04
@alexjosesilva, yes I can, but first get me one more question about the structure you’re working on. I understand you have the same table in each schema, but you want to access this same table in another schema, correct? It would no longer be feasible to have a "Template" schema with the definition of this table (since all need to be equal) and create the function in this same user?

– Confundir

2018/05/17 at 12:45
A table however with different schemas. Your suggestion is welcome but I can not change the system. I found it like this.

– alexjosesilva

2018/05/17 at 17:25
@Confound, I await your example.

– alexjosesilva

2018/05/17 at 23:00

Show 10 more comments

1 answer

Browser other questions tagged sql oracle pl-sql

You are not signed in. Login or sign up in order to post.

by Confundir • **1,144** points · Answer 1 · 2018-05-18T13:40:04+00:00

As mentioned 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 boring to give maintenance.

1) Create a Type with the same table settings and use it in its 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;

It is not the most dynamic solution in the world, but since PL/SQL is a compiled language, you cannot have this dynamism by passing Owner by parameter. This option will be very performative, but any change in the definitions of your table, needed to be replicated in the type.

2) Treat everything with immediate run, 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 the most dynamic possible, but it is very difficult to maintain and will be slower, but it would have the dynamism of working with several Owners. An option to improve maintenance would be to create a table that stores the script.