How to pass schema by paramentro in PLSQL?

Asked

Viewed 61 times

2

I have a function that receives a varchar2 value with the Schema name to save the data in the database but in the 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 it is possible to pass the schema in the function ?

java oracle pl-sql

  • There’s no way to use the name of schema directly into the configuration of variables in this way. To use the variable in the body of the function, you will need to mount the queries/commands in strings and run them dynamically with DBMS_SQL.EXECUTE or EXECUTE IMMEDIATE

  • My claim is to make this function generic for all schemas in the bank

  • To make it generic, all queries need to be dynamic, mounted at runtime from strings

  • Could you share an example ?

  • I don’t have Oracle installed here to test, but it’s something like: a := 'SELECT x INTO y FROM ' || nomeSchema || '.Tmedico.' || nomeMedico; and then: EXECUTE IMMEDIATE a; or crd = dbms_sql.parse(a); cre = dbms_sql.execute(crd);. This is just an example, see the documentation for details of these commands

1 answer

0


I managed to pass the schema using an artificial inside the 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 reply was drawn up from this post:

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

Browser other questions tagged java oracle pl-sql

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