Comando FOR

The syntax for the FOR Loop is:


FOR loop_counter IN [REVERSE] lowest_number..highest_number
LOOP
   {.statements.}
END LOOP;

You would use a FOR Loop when you want to execute the loop body a fixed number of times.


Let's take a look at an example.
FOR Lcntr IN 1..20LOOP
   LCalc := Lcntr * 31;

END LOOP;

This example will loop 20 times. The counter will start at 1 and end at 20.



The FOR Loop can also loop in reverse. For example:

FOR Lcntr IN REVERSE 1..15
LOOP
   LCalc := Lcntr * 31;
END LOOP;

This example will loop 15 times. The counter will start at 15 and end at 1. (loops backwards)

Roteiro para geração de arquivo texto:

DECLARE

    arquivo_saida                    UTL_File.File_Type;

    Cursor Cur_Linhais select to_char(sysdate,'dd/mm/yyyy hh24:mi:ss "século" CC') DATA from dual;

BEGIN

     /*

       A    Append Text

       AB   Append Byte Mode

       R      Read Text

       RB   Read Byte Mode

       W      Write Text

       WB   Write Byte Mode

     */

   arquivo_saida := UTL_File.Fopen('/home/oracle/','alfa.txt', 'W');

   For Reg_Linha in Cur_linha Loop

       UTL_File.Put_Line(arquivo_saida, Reg_linha.Data);

    End Loop;

   UTL_File.Fclose(arquivo_saida);

    Dbms_Output.Put_Line('Arquivo gerado com sucesso.');

EXCEPTION

      WHEN UTL_FILE.INVALID_OPERATION THEN

               Dbms_Output.Put_Line('Operação
inválida no arquivo.');

              UTL_File.Fclose(arquivo_saida);

      WHEN UTL_FILE.WRITE_ERROR THEN

               Dbms_Output.Put_Line('Erro de
gravação no arquivo.');

              UTL_File.Fclose(arquivo_saida);

      WHEN UTL_FILE.INVALID_PATH THEN

               Dbms_Output.Put_Line('Diretório
inválido.');

              UTL_File.Fclose(arquivo_saida);

      WHEN UTL_FILE.INVALID_MODE THEN

               Dbms_Output.Put_Line('Modo de acesso
inválido.');

              UTL_File.Fclose(arquivo_saida);

      WHEN Others THEN

              Dbms_Output.Put_Line('Problemas na
geração do arquivo.');

              UTL_File.Fclose(arquivo_saida);

END;

Roteiro para leitura de arquivo texto:

DECLARE

    arquivo_ler                    UTL_File.File_Type;

    Linha                               Varchar2(100);

BEGIN

    arquivo_ler := UTL_File.Fopen('caminho completo’,’nm arquivo', 'r');

    Loop

       UTL_File.Get_Line(arquivo_ler, Linha);

    End Loop;

    UTL_File.Fclose(arquivo_ler);

    Dbms_Output.Put_Line('Arquivo processado com sucesso.');

EXCEPTION

   WHEN No_data_found THEN

               UTL_File.Fclose(arquivo_ler);

               Commit;

   WHEN
UTL_FILE.INVALID_PATH THEN

               Dbms_Output.Put_Line('Diretório
inválido.');

              UTL_File.Fclose(arquivo_ler);

    WHEN Others THEN

               Dbms_Output.Put_Line
('Problemas na leitura
do arquivo.');

               UTL_File.Fclose(arquivo_ler);

END;

Erro ORA-12638

Esse erro ocorreu quando tentei fazer uma conexão remota

Conectar apenas via protocolo Oracle, isso se faz alterando-se o arquivo SQLNET.ORA do cliente, na

linha que tem :

      sqlnet.authenticaton_services=(.....)
mude-a para :
      sqlnet.authentication_services=(NONE)

Script Relacionamentos

O script abaixo mostra os relacionamentos e as colunas de uma determinada tabela:

-- -------------------------------------------------
-- cons.sql
-- -------------------------------------------------
-- Mostra quais são as constraints PAIS e FILHOS de
-- uma determinada tabela. Também mostra os campos
-- dela lado a lado para consulta.
-- -------------------------------------------------
-- Shows what constraints has relationship with the
-- informed table. Also shows the columns of these
-- relation constraints. (Pai-> father, Filho-> Son)
-- -------------------------------------------------
-- Created by Thomas F. Glufke
--
-- * vers 1.0 - (12/04/2006) Initial
-- * vers 1.1 - (27/11/2006) Changed from "USER_"
--              tables to ALL_.
--            - Added new column "OWNER"
--            - Upper in table_name parameter.
-- * vers 1.2 - (09/05/2008) Not showing table_name
--              when OwnerConstraint<>OwnerTable.
-- Find last version in:
-- http://www.glufke.net/oracle/viewtopic.php?p=2419
-- -------------------------------------------------
SET show off feedback off ver off echo off
SET PAGESIZE 80
COL COLUMNS FORMAT A60
accept TABELA prompt "TABLE NAME : "
break on tipo
SELECT
 'PAI' TIPO
, A.OWNER
, ( SELECT TABLE_NAME FROM ALL_CONSTRAINTS WHERE CONSTRAINT_NAME = A.R_CONSTRAINT_NAME AND OWNER=A.R_OWNER) TABELA
, B.COLUMNS
, A.R_CONSTRAINT_NAME
FROM ALL_CONSTRAINTS A
, ( SELECT
      A.OWNER
    , A.TABLE_NAME
    , A.CONSTRAINT_NAME
    , MAX(DECODE(POSITION, 1,      CNAME,NULL)) ||
      MAX(DECODE(POSITION, 2,', '||CNAME,NULL)) ||
      MAX(DECODE(POSITION, 3,', '||CNAME,NULL)) ||
      MAX(DECODE(POSITION, 4,', '||CNAME,NULL)) ||
      MAX(DECODE(POSITION, 5,', '||CNAME,NULL)) ||
      MAX(DECODE(POSITION, 6,', '||CNAME,NULL)) ||
      MAX(DECODE(POSITION, 7,', '||CNAME,NULL)) ||
      MAX(DECODE(POSITION, 8,', '||CNAME,NULL)) ||
      MAX(DECODE(POSITION, 9,', '||CNAME,NULL)) ||
      MAX(DECODE(POSITION,10,', '||CNAME,NULL)) ||
      MAX(DECODE(POSITION,11,', '||CNAME,NULL)) ||
      MAX(DECODE(POSITION,12,', '||CNAME,NULL)) ||
      MAX(DECODE(POSITION,13,', '||CNAME,NULL)) ||
      MAX(DECODE(POSITION,14,', '||CNAME,NULL)) ||
      MAX(DECODE(POSITION,15,', '||CNAME,NULL)) ||
      MAX(DECODE(POSITION,16,', '||CNAME,NULL)) COLUMNS
    FROM (SELECT OWNER,
                 SUBSTR(COLUMN_NAME,1,30) CNAME,
                 SUBSTR(TABLE_NAME,1,30) TABLE_NAME,
                 SUBSTR(CONSTRAINT_NAME,1,30) CONSTRAINT_NAME,
                 POSITION
            FROM ALL_CONS_COLUMNS) A,
         ALL_CONSTRAINTS B
    WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
      AND A.OWNER           = B.OWNER
      AND B.CONSTRAINT_TYPE = 'R'
    GROUP BY A.OWNER, A.TABLE_NAME, A.CONSTRAINT_NAME ) B
WHERE A.TABLE_NAME = upper('&TABELA')
  AND A.OWNER           = B.OWNER
  AND A.CONSTRAINT_TYPE ='R'
  AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
UNION
SELECT
  'FILHO' TIPO
, A.OWNER
, A.TABLE_NAME  TABELA
, B.COLUMNS
, A.CONSTRAINT_NAME
FROM ALL_CONSTRAINTS A
, ( SELECT
      A.OWNER
    , A.TABLE_NAME
    , A.CONSTRAINT_NAME,
      MAX(DECODE(POSITION, 1,      CNAME,NULL)) ||
      MAX(DECODE(POSITION, 2,', '||CNAME,NULL)) ||
      MAX(DECODE(POSITION, 3,', '||CNAME,NULL)) ||
      MAX(DECODE(POSITION, 4,', '||CNAME,NULL)) ||
      MAX(DECODE(POSITION, 5,', '||CNAME,NULL)) ||
      MAX(DECODE(POSITION, 6,', '||CNAME,NULL)) ||
      MAX(DECODE(POSITION, 7,', '||CNAME,NULL)) ||
      MAX(DECODE(POSITION, 8,', '||CNAME,NULL)) ||
      MAX(DECODE(POSITION, 9,', '||CNAME,NULL)) ||
      MAX(DECODE(POSITION,10,', '||CNAME,NULL)) ||
      MAX(DECODE(POSITION,11,', '||CNAME,NULL)) ||
      MAX(DECODE(POSITION,12,', '||CNAME,NULL)) ||
      MAX(DECODE(POSITION,13,', '||CNAME,NULL)) ||
      MAX(DECODE(POSITION,14,', '||CNAME,NULL)) ||
      MAX(DECODE(POSITION,15,', '||CNAME,NULL)) ||
      MAX(DECODE(POSITION,16,', '||CNAME,NULL)) COLUMNS
    FROM (SELECT OWNER,
                 SUBSTR(COLUMN_NAME,1,30) CNAME,
                 SUBSTR(TABLE_NAME,1,30) TABLE_NAME,
                  SUBSTR(CONSTRAINT_NAME,1,30) CONSTRAINT_NAME,
                 POSITION
            FROM ALL_CONS_COLUMNS) A,
         ALL_CONSTRAINTS B
    WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
      AND A.OWNER           = B.OWNER
      AND B.CONSTRAINT_TYPE = 'R'
    GROUP BY A.OWNER, A.TABLE_NAME, A.CONSTRAINT_NAME ) B
WHERE A.R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINTS WHERE TABLE_NAME = upper('&&TABELA')
AND CONSTRAINT_TYPE ='P')
  AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
  AND A.OWNER           = B.OWNER
ORDER BY TIPO DESC, TABELA, COLUMNS
/
clear breaks
set feedback on
set ver on
SET PAGESIZE 20