How to create a query with a non-existent and error-free table

Question

I have a query that runs in several different schemas in the database, and there is a specific table that only some schemas have, and if there is the value returns.

SELECT t.coluna1
      ,t.coluna2
      ,(SELECT coluna3
         FROM tabelaquepodenaoexistir
        WHERE coluna4 = 'S'
          AND coluna5 = 'N') as coluna 3
  FROM tabelaqueexisteemtodosschemas t

In some schemas this query returns what it should return, but in some it returns the error:

ORA-00942: the table or view does not exist

Remarks:

• You cannot use DBA or SYS access.
• I can’t create this table.
• I cannot create a Function in schema.

Answer 1

Try something like :

CREATE OR REPLACE PROCEDURE get_record
  (user_name    IN  VARCHAR2,
   service_type IN  VARCHAR2,
   record       OUT VARCHAR2)
IS
  query VARCHAR2(4000);
BEGIN  
    BEGIN
      -- Following SELECT statement is vulnerable to modification
      -- because it uses concatenation to build WHERE clause.
      query := 'SELECT value FROM secret_records WHERE user_name='''
               || user_name 
               || ''' AND service_type=''' 
               || service_type 
               || '''';
      DBMS_OUTPUT.PUT_LINE('Query: ' || query);
      EXECUTE IMMEDIATE query INTO record;
      DBMS_OUTPUT.PUT_LINE('Record: ' || record);
    EXCEPTION
      WHEN OTHERS THEN
        NULL;--IGNORA O ERRO 
    END;
END;    
/

Treating the error, I think there is even an "Handle" for the table query error does not exist.

Source : https://docs.oracle.com/database/121/LNPLS/dynamic.htm#LNPLS01101