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
External table
-
- Moderador
- Posts: 1177
- Joined: Thu, 15 Oct 2009 10:28 am
- Location: Recife
Would not it be the case to use CronTab?
-
- Rank: Estagiário Pleno
- Posts: 3
- Joined: Thu, 30 Jun 2011 3:38 pm
- Location: SP
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
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
-
- Moderador
- Posts: 1177
- Joined: Thu, 15 Oct 2009 10:28 am
- Location: Recife
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
CronTab is a Linux Scheduler
in Windows has the scheduled tasks
in Oracle has dbms_job and an evolution that is dbms_scheduler
-
- Rank: Estagiário Pleno
- Posts: 3
- Joined: Thu, 30 Jun 2011 3:38 pm
- Location: SP
Hi!
In fact I have not yet set up my script.
I created a procedure with the external table.
follows the code below:
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
In fact I have not yet set up my script.
I created a procedure with the external table.
follows the code below:
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;
/
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
-
- Moderador
- Posts: 1177
- Joined: Thu, 15 Oct 2009 10:28 am
- Location: Recife
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,
-
- Rank: Programador Sênior
- Posts: 45
- Joined: Thu, 12 May 2011 4:54 pm
- Location: Porto Alegre - RS
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!
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!
-
- Rank: Estagiário Sênior
- Posts: 13
- Joined: Mon, 20 Jun 2011 2:14 pm
- Location: Campinas - SP
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!
Good job!
-
- Information
-
Who is online
Users browsing this forum: No registered users and 2 guests