CODEWITHSUNDEEP
oracle-databaseplsql
Kirill A.
Kirill A.

Reputation: 1815

Oracle function trouble

This is fu function:

create or replace
FUNCTION pk_max_value(t_name VARCHAR) RETURN NUMBER
is
  rws number;
  pk_column_name varchar(300);
  sql_text VARCHAR(2048);
BEGIN

  sql_text := 'SELECT cols.column_name ' || 
          'FROM all_constraints cons, all_cons_columns cols ' ||
          'WHERE cols.table_name = ' || t_name ||
          ' AND cons.constraint_type = ' || 'chr(39) P chr(39) ' ||
          'AND cons.constraint_name = cols.constraint_name ' ||
          'AND cons.owner = cols.owner ' || 
          'ORDER BY cols.table_name, cols.position;';
  execute immediate sql_text into pk_column_name;

  sql_text := 'SELECT MAX(' || pk_column_name || ')  FROM ' || t_name;
  EXECUTE IMMEDIATE sql_text INTO rws;
  RETURN rws;
END;

when I execute this, Oracle gives me an answer:

SQL command not properly ended.

Can somebody tell me, where is my fall?

Upvotes: 0

Views: 85

Answers (1)

Florin Ghita
Florin Ghita

Reputation: 17643

First sql_text should not end with ;

And should be:

sql_text := 'SELECT cols.column_name  
      FROM all_constraints cons, all_cons_columns cols 
      WHERE cols.table_name = '''||t_name||''' 
       AND cons.constraint_type = ''P''
       AND cons.constraint_name = cols.constraint_name 
      AND cons.owner = cols.owner  
      ORDER BY cols.table_name, cols.position';

Obs: this function will fail when PK type is not NUMBER.

Obs2: the t_name should come uppercase...

Upvotes: 3

Related Questions