CODEWITHSUNDEEP
oracle-databaseplsql
mystarrocks
mystarrocks

Reputation: 4088

PL/SQL create function with param for a check constraint

Given the syntax:

CREATE [OR REPLACE] FUNCTION [Owner.]FunctionName 
     [(arguments [IN|OUT|IN OUT][NOCOPY] DataType [DEFAULT expr][,...])]
     RETURN DataType [InvokerRightsClause] [DETERMINISTIC]
     {IS|AS}

I think my query is syntactically correct, but for some reason, I get these errors during compilation:

Error(6,5): PL/SQL: SQL Statement ignored

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

CREATE or replace FUNCTION aCombinationMismatches(p_column1 IN VARCHAR2)
  RETURN Number
IS
duplicate_count NUMBER(4,0);
BEGIN
    select count(*) into duplicate_count
    from schema1.tableA a
    inner join schema1.tableB b
    on a.b_id = b.id and a.column1 = p_column1
    group by a.b_id, a.column1, a.column2, b.column1, b.column2, b.column3;
    return duplicate_count;
END;

Anyone see anything wrong with my query above?

Also I'd like to how to set this UDF up to be used to create a CHECK constraint. How exactly do I specify the param: p_param1 to the function assuming this is the value of a field column1 in a row that a user is trying to insert? I just don't want the user to insert a record into tableA that consists of duplicate combinations of fields across tables: tableA and tableB.

Note: The tables tableA and tableB do exist - a select query like below indicates it. So the error above is rather confusing to me, I must add. (All table and column names in the two queries were found/replaced with dummy values.)

select count(*)
from schema1.tableA a
inner join schema1.tableB b
on a.b_id = b.id
group by a.b_id, a.column1, a.column2, b.column1, b.column2, b.column3;

Output:

Count(*)

Upvotes: 2

Views: 1660

Answers (2)

Marcin Wroblewski
Marcin Wroblewski

Reputation: 3571

OK, you already know that you have problem with priviliges. I wanted to add that you won't be able to create CHECK constraint basing on your function. According to documentation:

  • The condition of a check constraint can refer to any column in the table, but it cannot refer to columns of other tables.
  • Conditions of check constraints cannot contain the following constructs:
    • Subqueries and scalar subquery expressions
    • Calls to the functions that are not deterministic (CURRENT_DATE, CURRENT_TIMESTAMP, DBTIMEZONE, LOCALTIMESTAMP, SESSIONTIMEZONE, SYSDATE, SYSTIMESTAMP, UID, USER, and USERENV)
    • Calls to user-defined functions

So to achieve what you want, you would have to define some triggers, or make use of some combination of MATERIALIZED VIEW and CHECK constraint. See for example this discussion on Ask Tom

Upvotes: 2

Ricardo Arnold
Ricardo Arnold

Reputation: 913

You probably have access to TableA and TableB through a Role. This means that you can query the table, but you cannot create a procedure that reads or writes that table. In order to compile your procedure you should at least grant select on the table to your user.

In the link below you'll find more info

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1065832643319

Upvotes: 1

Related Questions