vendredi 15 juillet 2016

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.

Aucun commentaire:

Enregistrer un commentaire