How to pass the schema param param in PLSQL?

Question

How to pass the schema param param in PLSQL?

Navigation

#1 by (0 votes)

2

I have a function that receives a value in varchar2 with the name of Schema in order to write the data in the database but in the informed schema.

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

    MATMEDICO nomeSchema.Tmedico.MATMEDICO%type;

begin

 /*corpo da função*/
 RETURN MatriculaMedico||''||nomeSchema;

end;

Error message:

Error (4,11): PLS-00487: Invalid reference to variable 'NOMESCHEMA'

How can I pass the schema on the function?

java oracle pl-sql

asked by anonymous 16.05.2018 / 16:07

1 answer

When to carry out e2e tests in your application? C # Windows Service Alternative

score 0 · Accepted Answer

I was able to pass the schema using an artifice inside PLSQL.

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

     retorno varchar2(10);
     Script CLOB;
begin
 Script := 'Declare' || chr(10) ||
           'MATMEDICO '||nomeSchema||'.Tmedico.MATMEDICO%type;' || chr(10) ||
           'begin'|| chr(10) ||
           'matmedico:= '||MatriculaMedico||';'|| chr(10) ||
           ':Retorno:=matmedico;'|| chr(10) ||
           'end;';

 /*corpo da função*/

  execute immediate Script
    using out retorno; 

 RETURN retorno||'-'||nomeSchema;

end;

This response is based on this post:

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