Calling a Procedure with name of Table as argument and assigning same datatype to variables as in Table in PL / SQL

following is the code. What I am doing here is passing the table_name as argument (I don’t know how to pass it, I just searched it on stackoverflow and other websites, and came up with this solution. Apparently it is a way to pass the table dynamically however, I don’t know the logic behind) to a procedure and then using that table to get the maximum and minimum salary of employees from employee table. Following is the Procedure code (it works well)

CREATE or REPLACE PROCEDURE MINMAX (Employee in varchar2)
IS
LOWEST emp.SAL%TYPE;
HIGHEST emp.SAL%TYPE;
BEGIN
execute immediate 'SELECT MIN(SAL) from ' || Employee into LOWEST;
execute immediate 'SELECT MAX(SAL) from ' || Employee into HIGHEST;
DBMS_OUTPUT.PUT_LINE('MINIMUM SALARY is : ' || Lowest  );
DBMS_OUTPUT.PUT_LINE('MAXIMUM SALARY is : ' || Highest  );
END;
/

And this is how I tried to execute it, but it gave me error.

Begin 
MINMAX(emp); 
END; 
/

This is the error I got

ORA-06550: line 2, column 9:
PLS-00357: Table,View Or Sequence reference 'EMP' not allowed in this context
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

Table with name of “emp” does exist, but I don’t know how to pass that as an argument to the procedure..

Secondly, if you look at the procedure code, I had to declare the variables like this,

LOWEST emp.SAL%TYPE;
HIGHEST emp.SAL%TYPE;

Can I change these to the datatype in accordance to the passed table (as argument)? Like this,

LOWEST Employee.SAL%TYPE;
HIGHEST Employee.SAL%TYPE;

I tried doing this, and it also gave me an error on this.

Answer

You can use the below procedure to achieve the declaration of variables based on table input

CREATE OR replace PROCEDURE Minmax (employee IN VARCHAR2)
    IS
    BEGIN
        EXECUTE IMMEDIATE 'DECLARE 
    HIGHEST '||employee||'.SAL%TYPE;
    LOWEST  '||employee||
        '.SAL%TYPE;
    BEGIN
    SELECT MIN(SAL) INTO LOWEST from '|| employee||
        ';
    SELECT MAX(SAL) into HIGHEST from '|| employee||' ;
    DBMS_OUTPUT.PUT_LINE(''MINIMUM SALARY is : '' || Lowest  );
    DBMS_OUTPUT.PUT_LINE(''MAXIMUM SALARY is : ''|| Highest );
    END;';
    END; 

Execute it using

      Begin 
      MINMAX('emp');  
      END;

Output

   MINIMUM SALARY is : 800
   MAXIMUM SALARY is : 5000

You can also pass the column_name as a parameter like the below

        CREATE OR replace PROCEDURE Minmax (table_name IN VARCHAR2,column_name in varchar2)
        IS
        BEGIN
            EXECUTE IMMEDIATE 'DECLARE 
        HIGHEST '||table_name||'.'||column_name||'%TYPE;
        LOWEST  '||table_name||'.'||column_name||'%TYPE;
        BEGIN
        SELECT MIN('||column_name||') INTO LOWEST from '|| table_name||
            ';
        SELECT MAX('||column_name||') into HIGHEST from '|| table_name||' ;
        DBMS_OUTPUT.PUT_LINE(''MINIMUM '||column_name||' is : '' || Lowest  );
        DBMS_OUTPUT.PUT_LINE(''MAXIMUM '||column_name||' is : ''|| Highest );
        END;';
        END;

Execute it using

  BEGIN
  Minmax('emp','sal');
  end;  

Output is:-

MINIMUM sal is : 800
MAXIMUM sal is : 5000