Home » SQL & PL/SQL » SQL & PL/SQL » datafile for External table (Oracle 10g and winddows 2003)
datafile for External table [message #446663] Tue, 09 March 2010 21:47 Go to next message
aijaz786
Messages: 91
Registered: February 2010
Member
Hi,

Every day we have use different data file for an external table to read from.

filename like this "filenameddmmyyyy"

Every day, I have to change name of datafile in external table script which is not a good way.

I want the automated way for external table to read the latest datafile from specified directory.

Further, there is anyway that we could know that the latest data file is available for external table.

An early reply will highly be appreciated.

Thanks

Re: datafile for External table [message #446666 is a reply to message #446663] Tue, 09 March 2010 22:17 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>filename like this "filenameddmmyyyy"

col now new_value now
select to_char(sysdate,'YYYYMMDD') now from dual;
set termout on
spool filename_&now
Re: datafile for External table [message #446670 is a reply to message #446663] Tue, 09 March 2010 22:38 Go to previous messageGo to next message
aijaz786
Messages: 91
Registered: February 2010
Member
Hi,

My question is how can an extertable read the most latest data file?

How can be chceked which file is the lastest one and how external table will use the same automatically.

Thanks.
Re: datafile for External table [message #446673 is a reply to message #446670] Tue, 09 March 2010 22:44 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#sthref2448

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Show us that you can access an External Table with fixed name.
Re: datafile for External table [message #446679 is a reply to message #446670] Tue, 09 March 2010 23:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Following BlackSwan example:
alter table ext_table location ('filename_&now');

Regards
Michel
Re: datafile for External table [message #446885 is a reply to message #446663] Wed, 10 March 2010 22:10 Go to previous messageGo to next message
aijaz786
Messages: 91
Registered: February 2010
Member
Hi,

This is my script for external table. Now every day, filename in location will change i.e. latest datafile. How can I automate this process that Location parameter always read latest available data file.

CREATE TABLE Techcalls_EXT
( call_no VARCHAR2(25 BYTE),
call_date DATE,
call_closed DATE

)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY DATA_FILES_DIR
ACCESS PARAMETERS
( records delimited by newline
badfile bad_files_dir:'closed_calls_ext.bad'
logfile log_files_dir:'closed_calls_ext.log'
fields terminated by ',' optionally enclosed by '"'
missing field values are null
( call_no,
call_date char date_format date mask "dd/mm/yyyy",
call_closed char date_format date mask "dd/mm/yyyy")
)
LOCATION
( 'calls22112009.dat'
)
)
REJECT LIMIT UNLIMITED;

Thnaks.
Re: datafile for External table [message #446887 is a reply to message #446885] Wed, 10 March 2010 22:22 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
when all else fails, Read The Fine Manual

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_3001.htm#i2104234

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: datafile for External table [message #446888 is a reply to message #446663] Wed, 10 March 2010 22:42 Go to previous messageGo to next message
aijaz786
Messages: 91
Registered: February 2010
Member
Hi Guys,

My question is very simple and unfortunately I am unable to find the proper advice or help or method to achieve the same.

Please.

Thanks.
Re: datafile for External table [message #446890 is a reply to message #446888] Wed, 10 March 2010 22:53 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
> I am unable to find the proper advice or help or method to achieve the same.

The solution has been laid at your feet.
If you refuse to grasp it & deploy it, you must deal with the consequences.
We are not here to provide you a turnkey solution while you contribute nothing.

Re: datafile for External table [message #446927 is a reply to message #446663] Thu, 11 March 2010 00:29 Go to previous messageGo to next message
aijaz786
Messages: 91
Registered: February 2010
Member
Hi Blackswan,

I used the following code to alter file location but it is prompting me to enter filename. Please advise if anything with this.

declare

vFileName varchar2(30);

begin

select 'filename'||to_char(sysdate-1,'DDMMYYYY') into vFileName from dual;
dbms_output.put_line(vFileName);
execute immediate 'alter table xyz location ('&vFileName')';

end;

Thanks.
Re: datafile for External table [message #446933 is a reply to message #446927] Thu, 11 March 2010 00:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to read about the different variables in SQL*Plus:
SQL*PlusĀ® User's Guide and Reference
Chapter 5 Using Scripts in SQL*Plus
Section Defining Substitution Variables and following ones
Section Using Bind Variables

And about variables in PL/SQL:
PL/SQL User's Guide and Reference
Chapter 1 Overview of PL/SQL
Section Understanding the Main Features of PL/SQL
Paragraph Understanding PL/SQL Variables and Constants

Regards
Michel

[Updated on: Thu, 11 March 2010 00:42]

Report message to a moderator

Re: datafile for External table [message #447374 is a reply to message #446663] Sun, 14 March 2010 05:55 Go to previous messageGo to next message
aijaz786
Messages: 91
Registered: February 2010
Member
Please help identify issue. Still not working. variable vFileName is not being replace by value.

declare

vFileName varchar2(30);

begin

select 'filename'||to_char(sysdate-1,'DDMMYYYY') into vFileName from dual;
dbms_output.put_line(vFileName);
execute immediate 'alter table xyz location (':vFileName')';

end;

Thanks.
Re: datafile for External table [message #447375 is a reply to message #447374] Sun, 14 March 2010 07:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read the links I provided.
Do not try to guess what should be the syntax, try to understand the documentation (which is by the full of examples) and what you write.

Use SQL*Plus and copy and paste your session.
Post it FORMATTED.

Regards
Michel

[Updated on: Sun, 14 March 2010 07:03]

Report message to a moderator

Re: datafile for External table [message #447379 is a reply to message #447374] Sun, 14 March 2010 09:48 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Please help identify issue. Still not working
Still not following Posting Guidelines.
Sad

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Build complete & valid SQL statement in a single string variable before using it in EXECUTE IMMEDIATE statement.

[Updated on: Sun, 14 March 2010 11:06]

Report message to a moderator

Re: datafile for External table [message #447384 is a reply to message #446663] Sun, 14 March 2010 23:29 Go to previous messageGo to next message
aijaz786
Messages: 91
Registered: February 2010
Member
Hi Blackswan,

I made the following change

declare

vFileName varchar2(30);
sql_stmt VARCHAR2(1000);

begin

select 'filename'||to_char(sysdate-1,'DDMMYYYY') into vFileName from dual;
sql_stmt :='alter table xyz location ('':vFileName'')';
dbms_output.put_line(sql_stmt);
dbms_output.put_line(vFileName);
execute immediate sql_stmt;

end;

Code is executed successfully but filename stored into variable vFileName is not subsituted for location and its takes vFileName as vFileName.

Please help me If I am doing any mistake.

Thanks

Re: datafile for External table [message #447386 is a reply to message #447384] Sun, 14 March 2010 23:37 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Please help me If I am doing any mistake.

I can't see the content of "sql_stmt"

I am sorry to see that CUT & PASTE are broken for you.

[Updated on: Sun, 14 March 2010 23:38]

Report message to a moderator

Re: datafile for External table [message #447389 is a reply to message #447384] Mon, 15 March 2010 00:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sun, 14 March 2010 13:02
Please read the links I provided.
Do not try to guess what should be the syntax, try to understand the documentation (which is by the full of examples) and what you write.

Use SQL*Plus and copy and paste your session.
Post it FORMATTED.

Regards
Michel

Re: datafile for External table [message #447405 is a reply to message #446663] Mon, 15 March 2010 01:28 Go to previous messageGo to next message
aijaz786
Messages: 91
Registered: February 2010
Member
It was done.

Thank you for all your help.

Re: datafile for External table [message #447410 is a reply to message #447405] Mon, 15 March 2010 01:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How? Post it, you may not be the only one insterested in the solution.

Regards
Michel
Re: datafile for External table [message #447538 is a reply to message #446663] Mon, 15 March 2010 22:50 Go to previous messageGo to next message
aijaz786
Messages: 91
Registered: February 2010
Member

declare

vFileName varchar2(30);

begin

select 'filename'||to_char(sysdate-1,'DDMMYYYY')||'.csv' into vFileName from dual;
execute immediate 'alter table abc location(''' || trim(vFileName) || ''')';

end;

Thanks
Re: datafile for External table [message #447541 is a reply to message #447538] Mon, 15 March 2010 23:05 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
I showed how it could be done without PL/SQL which would be slightly more efficient.
Previous Topic: sql to alter
Next Topic: Adding Partition fails
Goto Forum:
  


Current Time: Fri Dec 09 13:49:07 CST 2016

Total time taken to generate the page: 0.11676 seconds