Convert a string type variable to an object

[Eder]

Good afternoon people!


I have a question, can you help me?

Is there the possibility, or another way, from receiving the name of a table in a variable and within a PL / SQL block perform a SELECT, for example? Follow a simple example:

Select all

declare 
 
tab1          varchar2(30) := 'tab_cliente'; --NOME DA TABELA 
vNome      varchar2(30); 
begin 
 
select nome_cli into vNome from tab1 -- Aqui a var com o nome da tab; 
where cod_cli = 1; 
 
end;

Another question, exists in Oracle a command that performs a statement string as the SQL Server exec?

Thanks in advance

[rafaelfrocha]

I do not know from SQL Server, but I believe Oracle's "Immediate" execute should be equivalent.

has topics here in the forum that cite this functionality.

[victorhugomuniz]

This is it http://en.glufke.net/oracle/viewtopic.php?t=333

[Eder]

I understood ... I'll take a look at the execute immediate, but in the question of passing the table name in a variable and do a select for example in this variable? It's possible

[victorhugomuniz]

Select all

 
select table_name into vNome from all_tables where ? = ?; 

[fsitja]

Hello Eder,

You can run Dynamic SQL in this form, concatenating the name of the table in SQL, but it is strongly recommended that it is the last alternative [/b] solution. Dynamic SQL is usually useful in cases where without it you would need to write much more code and would be a much more complex solution with static SQL.

follow some relevant points in my opinion:

- Dynamic SQL and SQL strings concatenation make the code vulnerable to SQL Injection, which is a serious fault Security in database applications. Be aware of this and validate always the strings received to concatenar. Read about the dbms_assert package. http://download.oracle.com/docs/cd/E118 ... assert.htm - Static SQL has the syntax validated at compile time. Dynamic SQL No.
- Static SQL is validated at compile time to ensure that the user has the necessary grants to perform the operation. Dynamic No.
- As a consequence its much more complicated code to maintain and appeal to make debug; And much more subject to mistakes.
- Static SQL in PL / SQL uses variables binding (parameters in the WHERE) automatically. In the dynamic SQL you need to do it manually or are subject to parse from SQL repeatedly, ending the performance of the BD.

I'm sure there are more points that could be reinforced but that's what I got to remember now.

has more information about dynamic SQL in the link below: http://download.oracle.com/docs/cd/E118 ... ynamic.htm

[Eder]

I understood ..
I will study more about and see if I find another solution to the problem.

Thanks to all for aid

[ivairb]

Good afternoon.

I have the same impasse.
Only need the field name in the table.
This would save me many selects, as the tables are large.

This is the code:

Select all

   FUNCTION SP_INT_POPULAR_LINHA(PI_TABELA VARCHAR2, PI_CHAVE VARCHAR, PI_ID NUMBER, PI_DADOS GSH_INTEGRA_SAIDA_PESSOA%ROWTYPE) RETURN VARCHAR2 
   IS 
     LINHA VARCHAR2(10000); 
     W_SQL VARCHAR2(255); 
     W_VALOR VARCHAR2(3000); 
     W_CAMPO VARCHAR2(255);  
   BEGIN 
      LINHA := NULL; 
      FOR R IN (SELECT C.COLUMN_NAME, C.DATA_TYPE, C.DATA_LENGTH  
                     FROM USER_TAB_COLUMNS C  
                     WHERE C.TABLE_NAME = PI_TABELA 
                     ORDER BY c.COLUMN_ID)  
      LOOP 
         IF R.DATA_TYPE IN ('VARCHAR', 'VARCHAR2', 'CHAR') THEN 
            W_CAMPO := 'RPAD(NVL('|| R.COLUMN_NAME ||', '' ''), ' || R.DATA_LENGTH || ', '' '')'; 
         ELSIF R.DATA_TYPE = 'DATE' THEN 
            IF R.DATA_LENGTH <= 10 THEN 
               W_CAMPO := 'NVL(TO_CHAR(' || R.COLUMN_NAME ||', ''DD/MM/YYYY''), ''          '')';  
            ELSE 
               W_CAMPO := 'NVL(TO_CHAR(' || R.COLUMN_NAME ||', ''DD/MM/YYYY HH24:MI:SS''), ''                     '')';  
            END IF; 
         ELSIF R.DATA_TYPE = 'NUMBER' THEN 
            IF R.DATA_LENGTH > 0 THEN 
               W_CAMPO := 'LPAD(NVL(' || R.COLUMN_NAME ||', 0), ' || R.DATA_LENGTH || ', 0)'; 
            ELSE 
               W_CAMPO := 'LPAD(NVL(' || R.COLUMN_NAME ||', 0), 32 , 0)'; 
            END IF; 
         END IF; 
 
          
         W_SQL := 'SELECT ' || W_CAMPO || ' FROM ' || PI_TABELA || ' WHERE ' || PI_CHAVE || ' = ' || PI_ID; 
         EXECUTE IMMEDIATE w_sql INTO W_VALOR; 
 
         --substituiria as 2 linhas acima por esta 
         W_VALOR := varcha2tocolumn(r.column_name); 
 
         LINHA := LINHA || W_VALOR; 
      END LOOP; 
 
      RETURN LINHA; 
   END;