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

Question

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

Navigation

#1 by (0 votes)

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.

sql oracle oracle11g

asked by anonymous 09.11.2017 / 20:31

1 answer

Problem in using malloc and realloc Transfer information from one View to Another

score 0 · 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;    
/

Source: link