How to create a query with a nonexistent table and no error

2

I have a query that runs on several different schemas in the database, and there is a table that specifies that some schemas have, and if the value exists, it 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: table or view does not exist

Comments:

  • Can not use DBA or SYS access.
  • I can not create this table.
  • I can not create a function in the schema.
asked by anonymous 09.11.2017 / 20:31

1 answer

0

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;    
/

Source: link

    
10.11.2017 / 11:45