How to vary a table in the select

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 Fri, 31 Oct 2008 9:03 am

Personally, I have a doubt, I have q do the same query on several tables and I keep their names in a varchar2.

I'm using a cursor in which I pass the name of this table as parameter and do the seal only the oracle shows the msg: table or view doesnt exist.

Obs1: consider that there would be a loop by modifying the value of the variable base Note2: I tried to make the statement in the cursor parameter as varchar2 (50) also didn't work.

example:
Code: Select all
DECLARE

   base VARCHAR2(50);


   CURSOR C_PEDIDOS (base VARCHAR2)IS
    select *  FROM base;

 
  BEGIN
       base := 'minha_tabela';
       open C_PEDIDOS(base);
       close C_PEDIDOS;
  END;
mcello
Location: são paulo - SP

Poston Fri, 31 Oct 2008 11:14 am

Take a look at this topic. I think it may help: viewtopic.php?t=333 T +
joaogarcia
Location: Campinas - SP

Cordialmente,
João C. Garcia

Poston Fri, 31 Oct 2008 1:36 pm

as is explained in the link suggested, not good for me because I just take the name of the tables already inside the procedure and therefore could not pass the names as parameter.

What I need is that within a particular script inside my procedure in this script, I can vary the table that I'm using in the select.
mcello
Location: são paulo - SP

Poston Tue, 04 Nov 2008 8:53 pm

try it like this:
Code: Select all
DECLARE
  v_sql varchar2(2000);
  base VARCHAR2(50);
  c_type is ref_ursor;
  v_cursor c_type;
  v_result minha_tabela%rowtype;
BEGIN
  base := 'minha_tabela';
  v_sql := 'Select * from '|| base;
  open v_cursor for v_sql;
  loop
    fetch v_cursor into v_result;
    ...
  end loop;
  close v_cursor;
END;
Or maybe like this also work, I needed to test!

Code: Select all
DECLARE
  v_sql varchar2(2000);
  base VARCHAR2(50);
BEGIN
  base := 'minha_tabela';
  v_sql := 'Select * from '|| base;
  for c into v_sql loop
    ...
  end loop;
END;
rcoelho_6
Location: SP - Sao Paulo

Poston Wed, 05 Nov 2008 10:28 am

Then, with the execute immediate you would have to set up something like this:
Code: Select all
DECLARE
  --
  /* Para testar, mude o nome da tabela abaixo: */
  V_TABELA  VARCHAR2(30) := 'USER_PROCEDURES';
  V_SCRIPT  VARCHAR2(3000);
  --
  CURSOR C_COLUNAS IS SELECT T.COLUMN_NAME
                        FROM ALL_TAB_COLS T
                       WHERE T.TABLE_NAME = V_TABELA;
  --
BEGIN
  --
  V_SCRIPT := 'DECLARE'                                                              ||CHR(10)||
              '  CURSOR C_GERAL IS SELECT * FROM '||V_TABELA||' WHERE ROWNUM <= 1;' ||CHR(10)||
              'BEGIN'                                                                ||CHR(10)||
              '  FOR CUR IN C_GERAL LOOP'                                            ||CHR(10);
  --
  FOR CUR IN C_COLUNAS LOOP 
    --
    V_SCRIPT := V_SCRIPT || '  DBMS_OUTPUT.PUT_LINE(''' ||CUR.COLUMN_NAME|| ': ''||'  ||'CUR.'|| CUR.COLUMN_NAME ||');'||CHR(10);
    --
  END LOOP;
  --
  V_SCRIPT := V_SCRIPT ||'  END LOOP;' ||CHR(10);
  V_SCRIPT := V_SCRIPT ||'  END;';
  --
  EXECUTE IMMEDIATE V_SCRIPT;
  --
END;
T + there
joaogarcia
Location: Campinas - SP

Cordialmente,
João C. Garcia

Poston Fri, 07 Nov 2008 7:15 am

The last example (posted by abdussamad) worked for the variation of the table, which I haven't been able to, but I need to ask is whether the cursor query (SELECT * FROM ' ||V_TABELA | | ' WHERE ROWNUM < = 1) has return null or not so I can use in my script, i.e. need the return of the query as a whole and not a particular column, just need to know if returns null or not.
mcello
Location: são paulo - SP

Poston Fri, 07 Nov 2008 8:24 am

I don't know if I understand you, but come on.
If the query does not return anything, simply won't enter the for loop ...

This query that I sent, I put WHERE ROWNUM < = 1 to restrict the data (is an example). In your case if you want to give a full in the table, delete the query.

T +
joaogarcia
Location: Campinas - SP

Cordialmente,
João C. Garcia

Poston Fri, 07 Nov 2008 10:44 am

Unlike the John said msmo when the return is null runs part of the code inside the for loop, I need to figure out a way to work the return null, so that it enters the loop.
mcello
Location: são paulo - SP

Poston Fri, 07 Nov 2008 11:01 am

Put your code there and explains what you need to do better.
Is easier than analyzing the code of the example.
joaogarcia
Location: Campinas - SP

Cordialmente,
João C. Garcia

Poston Fri, 07 Nov 2008 12:11 pm

I do a query dynamically but I need to know if given null to do some things.

DECLARE-/* to test, change the name of the table below: */V_TABELA VARCHAR2 (30);
V_SCRIPT VARCHAR2 (3000);
esse_tem VARCHAR2 (30);
VARCHAR2 value (30);
INTEGER test;
integer counter;

--CURSOR C_COLUNAS IS SELECT T. COLUMN_NAME FROM ALL_TAB_COLS T WHERE T. TABLE_NAME = V_TABELA;
--BEGIN--test: = 0;

counter: = 0;

esse_tem: = ' 62015468 ';

V_TABELA: = ' my_table ';

V_SCRIPT := 'DECLARE' ||CHR (10) ||
C_GERAL CURSOR IS ' SELECT * FROM ' ||V_TABELA | | ' WHERE attribute = ' || esse_tem | | 'and ROWNUM = 0; ' ||CHR (10) ||
'BEGIN' ||CHR (10) ||
' FOR IN ' LOOP C_GERAL CUR ||CHR (10);
--IS IN LOOP-C_COLUNAS-CUR V_SCRIPT: = V_SCRIPT || DBMS_OUTPUT.PUT_LINE '.PUT_LINE (' ' ' ||CUR.COLUMN_NAME || ': ''||' ||'CUR. ' || CUR.COLUMN_NAME | | '); ' ||CHR (10);


dbms_output.put_line (' from here I do things ');


--END LOOP;
--V_SCRIPT: = V_SCRIPT | | ' END LOOP; ' ||CHR (10);
V_SCRIPT: = V_SCRIPT | | ' END; ';
--EXECUTE IMMEDIATE V_SCRIPT;


-END;
mcello
Location: são paulo - SP

Poston Fri, 07 Nov 2008 12:12 pm

the counter was toast I am not using now
mcello
Location: são paulo - SP

Poston Mon, 10 Nov 2008 11:05 am

I used the execute immediate with into then managed to verify the return of select.

code:
Code: Select all
declare
vRows number;
vTab varchar2(50);
valor VARCHAR2(30);


begin
iccid := '11001100';

vTab :='minha tabela';

execute immediate 'select count(*) from ' || vTab ||' where atributo= '|| valor into vRows;


dbms_output.put_line(vRows);

if(vRows>0)then
dbms_output.put_line('esse não serve!!!!!!!!!!!');
else
dbms_output.put_line('esse serve!!!!!!!!!!!');
end if;

EXCEPTION
        WHEN OTHERS THEN
        dbms_output.put_line('q q houve!!!!!!!!!!!');

end;
mcello
Location: são paulo - SP


  • See also
    Replies
    Views
    Last Post


    Return to PL/SQL

    Who is online

    Users browsing this forum: No registered users and 6 guests