I am trying to write an sql script in toad for oracle version 9.5 that when run will allow the user to select data from a txt file from the server where the parameters / variables are entered.
The version of oracle is 10g
e.g
select employee name from employees where employee-name in "C:\names.txt"
Evening Ora,
On 21/03/15 13:20, ora wrote:
I am trying to write an sql script in toad for oracle version 9.5 that
when run will allow the user to select data from a txt file from the
server where the parameters / variables are entered.The version of oracle is 10g
e.g
|select employee name from employees where employee-name in "C:\names.txt"|
What you need to do is create an EXTERNAL table for your C:\names.txt file - look up that chapter in the Oracle Utilities manual for full details.
Then it is as simple as your statement above, although it would look more like this:
select employee_name
from employees
where employee_name in (select employee_name from names_external);
Assuming of course, you created the table as names_external.
HTH
-- Cheers,
Norm. [TeamT]
Something like the following which assumes that your names are a maximum of 50 characters in the text file, one per line.
create table names_external (
employee_name varchar2(50)
)
organization external
( type oracle_loader
default directory c_drive
access parameters
( records delimited by newline )
location (‘names.txt’)
);
You will also need an Oracle Directory that maps onto your C: drive, in the above, I’ve called it “c_drive”, so, as the SYS user unless your user has CREATE DIRECTORY privilege:
create directory c_drive as ‘c:’;
grant read on c_drive to ;
If you need to write as well to that location, then:
grant write on c_drive to ;
Now, all yo have to do is put a names.txt file on the C drive, and SELECT away!
HTH
– Cheers,
Norm. [TeamT]
great!
On Sat, Mar 21, 2015 at 2:47 PM, Norm [TeamT] bounce-NormTeamT@toadworld.com wrote:
Re: script that pulls data from a txt file in the where clause
Reply by Norm [TeamT]
Something like the following which assumes that your names are a maximumof 50 characters in the text file, one per line.
create table names_external (
employee_name varchar2(50)
)
organization external
( type oracle_loader
default directory c_drive
access parameters
( records delimited by newline )
location ('names.txt')
);
You will also need an Oracle Directory that maps onto your C: drive, in
the above, I've called it "c_drive", so, as the SYS user unless your
user has CREATE DIRECTORY privilege:
create directory c_drive as 'c:';
grant read on c_drive to ;
If you need to write as well to that location, then:
grant write on c_drive to ;
Now, all yo have to do is put a names.txt file on the C drive, and
SELECT away!
HTH
--
Cheers,
Norm. [TeamT]
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Oracle notifications altogether.
Toad World - Oracle Discussion Forum
Flag this post as spam/abuse.
--
Roxanne Reshma Singh
(1-868)729-3201
Um, for some reason, the grant statements in my post appear to have lost the username. Obviously, you would grant read on c_drive to your_user;
Cheers,
Norm [ TeamT ]
–
Sent from my Android device with K-9 Mail. Please excuse my brevity.
Yes :). I tested this yesterday with the first tip you gave with the usage of external tables. That feature is incredible.
On Sun, Mar 22, 2015 at 3:56 PM, Norm [TeamT] bounce-NormTeamT@toadworld.com wrote:
Re: script that pulls data from a txt file in the where clause
Reply by Norm [TeamT]
Um, for some reason, the grant statements in my post appear to have lost the username. Obviously, you would grant read on c_drive to your_user;
Cheers,
Norm [ TeamT ]
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Oracle notifications altogether.
Toad World - Oracle Discussion Forum
Flag this post as spam/abuse.
--
Roxanne Reshma Singh
(1-868)729-3201
Hello,
I tried this on the c drive of the same machine where the database was installed on the c drive and it worked fine.
However, when the directory was created on the server, the table was created but i am getting an error when i try to see the data in the external table.
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04080: directory object DHHSMAPSIS not found
ORA-06512: at "SYS.ORACLE_LOADER", line 19
Please assist as soon as possible.
On Sun, Mar 22, 2015 at 4:07 PM, ora bounce-rrs@toadworld.com wrote:
Re: script that pulls data from a txt file in the where clause
Reply by ora
Yes :). I tested this yesterday with the first tip you gave with the usage of external tables. That feature is incredible.
On Sun, Mar 22, 2015 at 3:56 PM, Norm [TeamT] bounce-NormTeamT@toadworld.com wrote:
Re: script that pulls data from a txt file in the where clause
Reply by Norm [TeamT]
Um, for some reason, the grant statements in my post appear to have lost the username. Obviously, you would grant read on c_drive to your_user;
Cheers,
Norm [ TeamT ]
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Oracle notifications altogether.
Toad World - Oracle Discussion Forum
Flag this post as spam/abuse.
--
Roxanne Reshma Singh
(1-868)729-3201
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Oracle notifications altogether.
Toad World - Oracle Discussion Forum
Flag this post as spam/abuse.
--
Roxanne Reshma Singh
(1-868)729-3201
Furthermore the oracle 10g runs off a Linux 5.5 machine. The scripts are ran on xp machine with Toad 9.5
On Mon, Mar 23, 2015 at 11:22 AM, ora bounce-rrs@toadworld.com wrote:
Re: script that pulls data from a txt file in the where clause
Reply by ora
Hello,
I tried this on the c drive of the same machine where the database was installed on the c drive and it worked fine.
However, when the directory was created on the server, the table was created but i am getting an error when i try to see the data in the external table.
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04080: directory object DHHSMAPSIS not found
ORA-06512: at "SYS.ORACLE_LOADER", line 19
Please assist as soon as possible.
On Sun, Mar 22, 2015 at 4:07 PM, ora bounce-rrs@toadworld.com wrote:
Re: script that pulls data from a txt file in the where clause
Reply by ora
Yes :). I tested this yesterday with the first tip you gave with the usage of external tables. That feature is incredible.
On Sun, Mar 22, 2015 at 3:56 PM, Norm [TeamT] bounce-NormTeamT@toadworld.com wrote:
Re: script that pulls data from a txt file in the where clause
Reply by Norm [TeamT]
Um, for some reason, the grant statements in my post appear to have lost the username. Obviously, you would grant read on c_drive to your_user;
Cheers,
Norm [ TeamT ]
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Oracle notifications altogether.
Toad World - Oracle Discussion Forum
Flag this post as spam/abuse.
--
Roxanne Reshma Singh
(1-868)729-3201
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Oracle notifications altogether.
Toad World - Oracle Discussion Forum
Flag this post as spam/abuse.
--
Roxanne Reshma Singh
(1-868)729-3201
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Oracle notifications altogether.
Toad World - Oracle Discussion Forum
Flag this post as spam/abuse.
--
Roxanne Reshma Singh
(1-868)729-3201
Evening Ora,
On 23/03/15 15:22, ora wrote:
I tried this on the c drive of the same machine where the database was
installed on the c drive and it worked fine.
Good!
However, when the directory was created on the server, the table was
created but i am getting an error when i try to see the data in the
external table.
Have you made sure that the oracle account on the server has read/write access to the directory you are using to store the files in?
Have you created a new Oracle Database Directory object, that points at the new path on the server?
Does the user reading the names.txt from this new directory object have the READ permissions granted to the Oracle Directory Object?
KUP-04080: directory object DHHSMAPSIS not found
ORA-06512: at "SYS.ORACLE_LOADER", line 19
The above sounds like you have changed the Oracle Directory Object name to "DHHSMAPSIS" - you should now do a select * from all_directories where directory_name = 'DHHSMAPSIS' and see if you get a resulting row. If you do, it's most likely permissions - did yo grant read on DHHSMAPSIS to your_username?
Please assist as soon as possible.
My invoice will be in the post tomorrow!
HTH
-- Cheers,
Norm. [TeamT]
