I have the following procedure (insert_mapping):

create or replace procedure p_insert_mapping
  (header_id in number,
   position in number,
   xml_mapping in varchar2,
   id out number,
   result_code out number
  )
is
  l_id number;
begin
  -- check for errors
  if header_id not in (select log_push_readouts_headers.id from log_push_readouts_headers) then
     result_code := 9302;
     raise_application_error(-RESULT_CODE, 'Foreign key constraint violated for headers');


  end if;

  -- if there are no errors, do insert
  if result_code is null then
     -- fetch sequence number
     id := mapping_seq.nextval;

     insert into log_push_readouts_mappings
       (id, position, xml_mapping)
       values
       (id, position, xml_mapping);
  end if;

  commit;

end;

In the following line:

if header_id not in (select log_push_readouts_headers.id from log_push_readouts_headers) then

I need to check if foreign key exists in main table.

How to do that?

Could someone give me an example on how to check if foreign key is in a table with primary key?

I am getting the following error: Compilation errors for PROCEDURE

AMM_MDM.P_INSERT_MAPPING

Error: PLS-00405: subquery not allowed in this context
Line: 12
Text: if header_id not in (select log_push_readouts_headers.id from log_push_readouts_headers) then

Error: PL/SQL: Statement ignored
Line: 12
Text: if header_id not in (select log_push_readouts_headers.id from log_push_readouts_headers) then

2 Answers

1
Community On

regarding the compilation error, please refer Using a subquery within PLSQL conditional logic; error PLS-00405

and for checking foreign key constraint on a column of a table, we can query from all_constrainsts, where CONSTRAINT_TYPE can be checked

doc ref: https://docs.oracle.com/cd/B14117_01/server.101/b10755/statviews_1037.htm#i1576022

0
Tejaswa On

What you can do is fetch the matching count of sub-query into a number type variable and then use that in if-else statement to compute the flow of your code

select count(1) into <some variable> from log_push_readouts_headers a where log_push_readouts_headers.id = header_id

Now check this count against your desired value in if-else statement.