CODEWITHSUNDEEP
oracle-databaseplsql
Mitali
Mitali

Reputation: 121

Execute select/insert statement within an IF clause Oracle

I need to execute some statements within the IF clause only if a table exists. But the issue I am facing is, even when the condition is false, the statements are getting executed.

DECLARE
  count_matching_row NUMBER := 0;
  count_matching_tbl NUMBER := 0;
BEGIN
  SELECT COUNT(*)
  INTO count_matching_tbl
  FROM user_tables 
  WHERE LOWER(table_name) = 'tab1';
  IF(count_matching_tbl = 1)
    THEN

      SELECT COUNT (*)
      INTO   count_matching_row
      FROM   test1
      WHERE  ID IN (SELECT ID FROM tab1);

      IF(count_matching_row = 0)
        THEN

          INSERT INTO review_case
            SELECT 
              DISTINCT ID, d,e
            FROM tab1
            WHERE ID IS NOT NULL;

          INSERT INTO review_case_payer
            SELECT 
              a,b,c
            FROM tab1
            WHERE a IS NOT NULL;
         COMMIT;
      END IF;
  END IF;
END;
/

Whenever I execute these statements, if the table 'tab1' exists it works fine. If the table tab1 does not exist I get the error

"ORA-06550: line 13, column 14: PL/SQL: ORA-00942: table or view does not exist" I get similar errors for each line where I try to access table "tab1"

I tried with ref cursor but still the same, I cannot use it for insert statements.

Upvotes: 0

Views: 3714

Answers (1)

Aleksej
Aleksej

Reputation: 22969

Your error is due to the fact that you're using a table that may not exist; this error is thrown because the script has compile problems, not data problems, so the way you try to use the IF is not enough to handle your situation.

You need to use some dynamic SQL to handle an object that could not exist; for example, see the following.

If the table does not exist, nothing will be done:

SQL> select * from tab1;
select * from tab1
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> declare
  2      vCountTab number;
  3  begin
  4      select count(1)
  5      into vCountTab
  6      from user_tables
  7      where table_name = 'TAB1';
  8
  9      if vCountTab = 1 then
 10          execute immediate 'insert into TAB1 values (1, 2)';
 11      end if;
 12  end;
 13  /

PL/SQL procedure successfully completed.

If the table exists, the insert will be done:

SQL> create table tab1(a number, b number);

Table created.

SQL> declare
  2      vCountTab number;
  3  begin
  4      select count(1)
  5      into vCountTab
  6      from user_tables
  7      where table_name = 'TAB1';
  8
  9      if vCountTab = 1 then
 10          execute immediate 'insert into TAB1 values (1, 2)';
 11      end if;
 12  end;
 13  /

PL/SQL procedure successfully completed.

SQL> select * from tab1;

         A          B
---------- ----------
         1          2

SQL>

Upvotes: 1

Related Questions