Oracle dynamic query in columns

Asked

Viewed 342 times

2

I have the following situation, I need to create a dynamic query between the columns to mount a Script. Assuming there is a change in the versions where an extra column is added in the table, the Script should recognize these changes without having maintenance in it.

Select should return column1, column2, column3, column 4

I tried to use Listagg but does not answer, because the values become String.

CREATE TABLE TESTE_STACK( COLUNA1 VARCHAR2(4000), COLUNA2 VARCHAR2(4000), COLUNA3 VARCHAR2(4000) );

ALTER TABLE TESTE_STACK add COLUNA4 VARCHAR2(4000);

SELECT Listagg(A.COLUMN_NAME, ',' ) Within GROUP(ORDER BY A.COLUMN_ID) Colunas
FROM ALL_TAB_COLUMNS A
WHERE A.OWNER = ''
AND   A.TABLE_NAME = 'TESTE_STACK';

SELECT (SELECT Listagg(A.COLUMN_NAME, ',' ) Within GROUP(ORDER BY A.COLUMN_ID) Colunas
FROM ALL_TAB_COLUMNS A
WHERE A.OWNER = ''
AND   A.TABLE_NAME = 'TESTE_STACK') 
FROM TESTE_STACK A
;
</i></code>
sql oracle pl-sql oracle11g

  • "... The query should return 'column1, column2, column3, column 4' ..." this is a string, which you need to return?

  • I need to return as if it were columns, the return with Listagg already comes in String Listagg = 'coluna1, column2, column3, column 4' as if it were columns, or some way to concatenate with the alias TESTE_STEACK.column1, TESTE_STACK.column2 ....

  • Listagg(table_name||'.'||column_name,',')

  • This concatenates with alias, but is still in String format.

  • Why not do a JOIN ?

  • Describe your problem in more detail

  • But what would it be like. In the scenario I need to assemble a Script to make updates in the columns of dynamic form, where you do not need to give maintenance when performing an add colunm in the table

  • And if you do the relationship, and the table where you’ll have new columns put something like ALIAS.* That way if you have a new spine you’ll bring

  • Good somehow I managed to solve the situation for Script ;UPDATE TESTE_STACK SET A.COLUNA1 = A.COLUNA1;&#xA; DECLARE&#xA; Valor VARCHAR2(4000);&#xA;BEGIN&#xA; FOR i IN (SELECT a.Column_Name FROM All_Tab_Columns a WHERE a.Owner = 'tst' AND a.Table_Name = 'TESTE_STACK')LOOP&#xA; FOR x IN (SELECT 'A.' || i.Column_Name AS Coluna FROM Teste_Stack a)LOOP&#xA; Valor := x.Coluna;&#xA; Dbms_Output.Put_Line('UPDATE TESTE_STACK '||' SET '||x.Coluna||' = '||Valor||';'||Chr(10));&#xA; END LOOP;&#xA; END LOOP;&#xA;END;

  • Put as answer, and delete this comment

Show 5 more comments

1 answer

0


I solved the problem using Run Immediet, in this way it is possible to save the value of the columns in a variable to perform the update. This script is using when there is creation of columns in the table, dynamically it will recognize the new column and its value

DECLARE
        VALOR VARCHAR2(4000);
        VSQL VARCHAR2(4000);
        VTABLE VARCHAR2(4000);
        BEGIN 
            VTABLE := UPPER('&TABLE_NAME');
            FOR i IN (
                        SELECT TO_CHAR(a.Column_Name) COL 
                        FROM All_Tab_Columns a 
                        WHERE a.Owner = 'tst' 
                        AND a.Table_Name = VTABLE
            )LOOP 
                    VSSQL := 'SELECT '||I.COL||' FROM '||VTABLE||' ';
                    EXECUTE IMMEDIATE VSSQL INTO VALOR;
                    Dbms_Output.Put_Line('UPDATE '||VTABLE|| '
                                        ||' SET '||I.Col||' = '||Valor||';'
                                        ||Chr(10)); 

            END LOOP; 
        END;

Browser other questions tagged sql oracle pl-sql oracle11g

You are not signed in. Login or sign up in order to post.