CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqlplsqldeveloperdynamicquery
bin
bin

Reputation: 131

Dynamic Function- Oracle

I am trying to create a function by giving 2 variables as inputs. These 2 variables are used dynamically in the function. Am using a select statement in the beginning of the loop to find the primary key column in a particular table. This column is assign to a variable value1. Am using this value1 variable as a sequence variable.

create FUNCTION Test(schemaname in varchar2, tablename in varchar2)
return number
IS cnpParmId NUMBER;
good VARCHAR(1) := 'F';
exist VARCHAR(1) := 'F';
value1 varchar2(500);
  
begin
good  := 'F';
exist := 'F';
loop
   SELECT cols.column_name into value1
        FROM all_constraints cons, all_cons_columns cols
        WHERE cols.TABLE_NAME= 'tablename' 
        And cols.OWNER='schemaname'
        And cons.constraint_type = 'P'
        AND cons.constraint_name = cols.constraint_name
        AND cons.owner = cols.owner
        ORDER BY cols.table_name, cols.position; 
  
   select schemaname.value1_seq.nextval into cnpParmId from dual;
   begin
      select 'T' into good from dual 
      where cnpParmId not in 
      (select value1 from schemaname.tablename);
      exception when NO_DATA_FOUND then good := 'F';
   end;
   exit when good = 'T';
end loop;
return cnpParmId;
end;
/

Test(XYZ,ABC);

but am getting the following errors:

Error(21,11): PLS-00487: Invalid reference to variable 'SCHEMANAME'

Error(21,22): PL/SQL: ORA-02289: sequence does not exist

Error(23,7): PL/SQL: SQL Statement ignored

Error(25,38): PL/SQL: ORA-00942: table or view does not exist

Upvotes: 0

Views: 765

Answers (1)

Ed Gibbs
Ed Gibbs

Reputation: 26363

When you introduce variables as table or column names you need to do a dynamic query using EXECUTE IMMEDIATE.

Your sequence query should be:

execute immediate 'select ' || schemaname || '.nextval from dual' into cnpParmId;

And your "get the return value" query should be:

execute immediate
  'select ''T'' from dual where cnpParmId not in ' ||
  '(select value1 from ' || schemaname || '.' || tablename || ')' into good;

Also note that your query to get value1 is looking for the literal values schemaname and tablename:

SELECT cols.column_name into value1
    FROM all_constraints cons, all_cons_columns cols
    WHERE cols.TABLE_NAME= 'tablename'
    And cols.OWNER='schemaname'
    ... and so on

You can use variables to represent values, so just get rid of the single quotes around the variable names:

SELECT cols.column_name into value1
    FROM all_constraints cons, all_cons_columns cols
    WHERE cols.TABLE_NAME= tablename
    And cols.OWNER=schemaname
    ... and so on

Upvotes: 1

Related Questions