Convert a variable of type string to an object

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Poston Thu, 30 Dec 2010 11:27 am

Good afternoon, guys!


I'm with a doubt, can you help me?

There is a possibility, or another way, I get the name of a table in a variable and within a pl/sql block perform a select, for example? Here is a simple example: declare tab1 varchar2 (30): = ' tab_cliente '; --VNome TABLE NAME varchar2 (30);
begin select nome_cli into vNome from tab1--Here the var with the name of the tab;
where cod_cli = 1;

end;

Any other query, exist in oracle a command that performs a string as the EXEC statement of sql server?

from already thank you
Eder
Location: MG

Poston Thu, 30 Dec 2010 12:21 pm

I don't know of SQL Server, the more I believe that the \"execute immediate\" Oracle should be equivalent.

Has threads here on the forum mentioning this functionality.
rafaelfrocha
Location: Uberlândia

Rafael Rocha

Poston Thu, 30 Dec 2010 12:29 pm

that's right viewtopic.php?t=333
victorhugomuniz
Location: Rio de Janeiro - RJ

Jesus está voltando, volte antes para Ele.

Image JavaBlackBelt

Poston Thu, 30 Dec 2010 1:09 pm

understand..people will take a look at the issue, but a matter of immediate pass the table name in a variable and do a select for example in this variable? It is possible to
Eder
Location: MG

Poston Thu, 30 Dec 2010 1:53 pm

Code: Select all
select table_name into vNome from all_tables where? = ?;
victorhugomuniz
Location: Rio de Janeiro - RJ

Jesus está voltando, volte antes para Ele.

Image JavaBlackBelt

Poston Thu, 30 Dec 2010 2:00 pm

Hi Eder, you can run dynamic SQL in this form, by concatenating the name of the table in SQL, but it is strongly recommended to be of last resort solution. Dynamic SQL is generally useful in cases where without it you would need to write a lot more code and would be a much more complex solution with static SQL.

Here are some relevant points in my opinion:-dynamic SQL and concatenating strings in SQL make code vulnerable to SQL injection, which is a serious flaw in database applications. Be aware of that and validate always the incoming strings to concatenate. Read about the DBMS_ASSERT package.
http://download.oracle.com/docs/cd/E118 ... htm-static SQL has the syntax validated at compile time. Dynamic SQL does not.
-Static SQL is validated at compile time to ensure that the user has the grants needed to perform the operation. Not dynamic.
-As a consequence your code much more complicated to maintain and trimming to make debug; and more error-prone.
-Static SQL in PL/SQL uses binding of variables (parameters in where) automatically. In dynamic SQL you need to do it manually or is subject to parse SQL repeatedly, ruining the performance of BD.

I'm sure there are more points which could be strengthened but this is what I managed to remember now.

Has more information about dynamic SQL on the link below: http://download.oracle.com/docs/cd/E118 ... ynamic.htm
fsitja
Location: Gaúcho no Rio de Janeiro - RJ

"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

Poston Thu, 30 Dec 2010 2:46 pm

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

Thank you all for your help
Eder
Location: MG

Poston Thu, 30 Dec 2010 5:02 pm

Good afternoon.

I'm with the same impasse.
I just need the name of the field in the table.
This would save me many selects, because the tables are large.

This is the code: FUNCTION SP_INT_POPULAR_LINHA (PI_TABELA VARCHAR2, VARCHAR, PI_CHAVE PI_ID NUMBER, PI_DADOS GSH_INTEGRA_SAIDA_PESSOA% ROWTYPE) RETURN VARCHAR2 IS LINE VARCHAR2 (10000);
W_SQL VARCHAR2 (255);
W_VALOR VARCHAR2 (3000);
W_CAMPO VARCHAR2 (255);
BEGIN ROW: = NULL;
FOR R IN (SELECT c. COLUMN_NAME, DATA_TYPE, c. 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. > 0 THEN W_CAMPO DATA_LENGTH: = NVL (LPAD (' ' || R. COLUMN_NAME | | ', 0), ' || R. DATA_LENGTH || ', 0)';
ELSE W_CAMPO: = NVL (LPAD (' ' || 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;

-replace the 2 lines above by this W_VALOR: = varcha2tocolumn (r. column_name);

LINE: = LINE || W_VALOR;
END LOOP;

RETURN LINE;
END;
ivairb
Location: Fortlaeza - CE


  • See also
    Replies
    Views
    Last Post


      Return to PL/SQL

      Who is online

      Users browsing this forum: Google [Bot] and 11 guests