External table

[terearaujo]

Hello everyone, good afternoon!

I created an external table to be populated with fields of an Excel file, but I need this procedure to be done daily. The fields will always be the same, only the data will change.
I would like to know if you have how I can use dbms_job for an external table, because I tried to create it inside a stored procedure and was not allowed because of SQL Loader.
Can anyone help me? !!!

Grata!

Terezinha

[diegolenhardt]

Would not it be the case to use CronTab?

[terearaujo]

Hi, I looked on the Convrab and I did not quite understand, sorry, I'm a beginner.
I would only like to know if it is possible to use DMBS_JOB or a Scheduler to run the script with an external table daily.

Grata !!

Terezinha Lima

[diegolenhardt]

What are the commands of your script?

CronTab is a Linux Scheduler
in Windows has the scheduled tasks

in Oracle has dbms_job and an evolution that is dbms_scheduler

[terearaujo]

Hi!

In fact I have not yet set up my script.
I created a procedure with the external table.
follows the code below:

Select all

 
create Or Replace Package Body PKG_EXTERNAL AS  
Procedure PRC_TAB_EXT_TESTE  
    ( P_Nome_Local       In  Varchar2  
    , P_Nome_Arq         In  Varchar2  
    )  
Is  
  ---------------  
  -- Variaveis --  
  ---------------  
  sqlString  VARCHAR2(4000); 
  sqlCreate  varchar2(4000):= 'CREATE TABLE TABELA1A (COD_PROCESSO1 VARCHAR2(400 BYTE), 
 NOM_PROCESSO1 VARCHAR2(400 BYTE), 
 DAT_CADASTRO1 VARCHAR2(400 BYTE) );'; 
  v_filename varchar2(100) := 'teste.csv'; 
   
Begin  
  --------------------------  
  -- Define no do Arquivo --  
  --------------------------  
   
    execute immediate 
 'create table TEST_TABELA1A '              || 
      '(cod_processo1     VARCHAR2(4000),'     || 
        'nom_processo1     VARCHAR2(4000),'    || 
        'dat_cadastro1     VARCHAR2(4000))'    || 
      '  organization external '                  ||   
      ' (type oracle_loader '                     ||  
      '  default directory  JCNDIR '               || 
      '  access parameters '                      ||   
      '    ( records delimited by newline '       ||    
      '    nobadfile '                            || 
      '    nologfile '                            || 
      '    nodiscardfile '                        ||   
      '    fields terminated by '','''           || 
      '    missing field values are null '       ||    
      '    (cod_processo1, nom_processo1, dat_cadastro1)  '  || 
      '  ) '                                          ||  
      '  location ('''||v_filename||''') )'       ||    
      '  reject limit unlimited ' 
      ;  
       
      execute immediate sqlCreate; 
    
      sqlString := 
      'INSERT INTO TABELA1A (cod_processo1, 
                            NOM_PROCESSO1, 
                            DAT_CADASTRO1) 
                    (SELECT cod_processo1, 
                            nom_processo1, 
                            dat_cadastro1 
                     FROM TEST_TABELA1A)'; 
      EXECUTE IMMEDIATE sqlString; 
       
      execute immediate 'drop tabela1a'; 
      execute immediate 'drop test_tabela1'; 
       
  Commit;  
End PRC_TAB_EXT_TESTE;  
End PKG_EXTERNAL;  
/  
 

and wanted to know if it is possible through a job or schedule to rotate her daily.

It is not working 100% yet, as I am without enough privileges in the bank. But acho that is not in error of syntax, because compiled ...

Grata for the attention !!!

Terezinha Lima

[diegolenhardt]

The simplest way for you to do this is to access Enterprise Manager, enter the Scheduler and schedule this task, there will ask if it is PL / SQL or procedure block,

[marcus.kobel]

Terezinha
One thing is an external table and another is the Scheduler of Oracle (dbms_job).

If you want to load data from a file daily, you have two options: Use the Linux Cron tab or the Oracle Scheduler. This would solve the problem of wheels your code every day. You will probably have to write a prodecure in the bank of sending it to be round.

The second, it's how you will load the data. External Table is a viable solution. Another would use SQL Loader, which is a program the Oracle part that only makes data load from external files. Yet there is the possibility to do all this by PL / SQL code by reading the line text file per line and inserting into a table (other than external) inside the bank.

I hope I have helped!

[lmendes.cps]

You can create a job that runs your procedure ... More information about Jobs: http://www.dba-oracle.com/tips_oracle_dbms_job.htm
Good job!