How to move a excel file into oracle

[Kalyan Ganesan]

I am familiar with the DBA_DIRECTORIES and ALL_DIRECTORIES

But i want to know how to move a excel file into Oracle Directory

Like when i run this query


select * from dba_directories

SYS ORACLE_OCM_CONFIG_DIR2 /oracle/app/oracle/product/11.2.0.4/db_risk/ccr/state
SYS DV_ADMIN_DIR /oracle/app/oracle/product/11.2.0.4/db_risk/dv/admin/
SYS INFO1CTR_TRACE_LOG /oracle/app/oracle/diag/rdbms/risk/risk/trace/
SYS LQDTN_DPUMP_DIR /landing_pad/dev
SYS ORAN /download/info1ctr/amit
SYS INFO1CTR_DIR /download/info1ctr
SYS TEST /backup/exports/ryan
SYS EXPORT /backup/exports/san
SYS DUMP /download/infamgr/dump
SYS EXPDP_TEMP /backup/exports/ITSM_C02177586
SYS ITSMC02160689 /download/ITSM/ITSMC02160689

it pulls up 100s of rows like this

i under the owner directory name and directory path are the above,but what is their system path like which drive etc

and how can i move a excel file in a directory that i can create CREATE DIRECTORY test_dir AS 'i:\project';

 

[Beilstwh]

A directory object can ONLY point to a location on the database server.

Bill
Lead Application Developer
New York State, USA

 

[Kalyan Ganesan]

So the file can be moved to the database server?

How can that be done?

For example
i See a directory name as RCO and the directory path points to /download/rco

So where is this directory and how can i move the excel file there so that i can read it thru PL SQl Code

 

[Beilstwh]

the path is /download/rco from the root of the server. All paths specified are absolute. Also the folder rco must be owned by the oracle user and must have read/write privileges. Also once the directory object is setup you will need to grant a user the rights to use it. For example

grant read,write on directory RCO to your_oracle_user;

your_oracle_user can be the oracle user, an oracle role, or the word PUBLIC which means anyone can access it.

As for transferring the excel spreadsheet to the folder there are a number of transfer options that you have available. Without knowing your available tools or operating systems I have no idea what to suggest.

I hope you are aware that oracle can only access the file as a whole and has no way to be able to actually read the rows and columns in the excel file.

Bill
Lead Application Developer
New York State, USA

 

[Kalyan Ganesan]

actually i moved the excel file into the folder TNS_NAMES_DIR thats the folder where the tnsnames files are stored, couldnt find the path for RCO but i just guessed that TNS path would be where the TNS Files are , but i get the permissions error when i try to access the file

i created a stored proc

CREATE OR REPLACE PROCEDURE print_file( p_file IN VARCHAR2 ) IS
v_file UTL_FILE.FILE_TYPE;
s VARCHAR2(200);
BEGIN
v_file := UTL_FILE.FOPEN('RCO ', p_file, 'r');
UTL_FILE.GET_LINE(v_file,s);
dbms_output.put_line(s);
UTL_FILE.FCLOSE(v_file);
END print_file;

execute print_file( 'test.xls' );

ORA-29289: directory access denied
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at "LSDMGR.PRINT_FILE", line 5
ORA-06512: at line 1


so tried giving the read only grant on that folder to myself,it wont let me to

 

[Kalyan Ganesan]

Because there is no read only access,even this query wouldnt work right?

SELECT t.* FROM TABLE(
ExcelTable.getRows(
ExcelTable.getFile('TNS_NAMES_DIR','Test.xlsx')
, 'Sheet_name_goes_here'
, ' "Account_NBR" for ordinality
, "Account Name" Number
, "INVESTOR_DESC" varchar2(255) '
, 'A2'
)
) t ;

 

[Beilstwh]

as what user did you issue the grant?

As the user that is running the code type
select * from ALL_DIRECTORIES;

If the TNS_NAMES_DIR doesn't show then you have no rights to the directory.

Bill
Lead Application Developer
New York State, USA

 

[Kalyan Ganesan]

select * from ALL_DIRECTORIES;

shows only three directories and TNS_NAMES_DIR is NOT among them

so that means i cant have access correct

also what is the code to see all the files in a directory in oracle

is UTL_FILE used for that too?

 

[Beilstwh]

there is not really a way using utl_file to get a list of file. That has always annoyed me. it would be so easy thing for oracle to do. There are a number of inconvenient hacks to get a list but each one depend on your database version.

Also the tool ExcelTable suggested by a previous response is not an oracle tool. It they would like to provide the source for they application it would probably be appreciated.

too grant a user the rights to that directory (TNS_NAMES_DIR) you need to get in as a priviledged user (SYS, SYSTEM for sure) and issue the following command

grant read,write on directory TNS_NAMES_DIR to xxx;

(this is assuming that the oracle schema owner is xxx. If it is not use the correct name.



Bill
Lead Application Developer
New York State, USA

 

[Kalyan Ganesan]

I too just now noticed that ExcelTable is some package we need to download to use it,i am surprised they dont have a way to see the files in a oracle directory in a straight forward way,like a function that would return all the file details you know..well the version of oracle i am using is

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production


I just expected a query like this to see file details of a folder

SELECT NAME,
FILE#,
STATUS,
CHECKPOINT_CHANGE# "CHECKPOINT"
FROM V$DATAFILE;

Thanks a lot Bill for all your replies appreciate that,i have been a crystal reports,BO,VB.net,SQL,oracle reports,pl sqlprogrammer all along but never got too much into dba stuff,i am finding it hard to understand why there is sooo much complexity,i understand part of it is security but still it seems a little too complex

 

[Beilstwh]

Administrating oracle is like writing code in assembler. If you get down in the weeds you can do just about anything. Unfortunately that makes it complex to setup. Fortunately if you just run a plain vanilla database it is simple to install and use it and let the database take care of it's self.

Bill
Lead Application Developer
New York State, USA

 

[Kalyan Ganesan]

Hi Bill(Beilstwh)

So lets say i get the grants done to access the excel file from the directory in Oracle

What would be the code to insert the data from two columns in the excel file into the oracle table..I need the exact code without any external packages like ExcelTable and all that

Thanks

Kalyan

 

[Beilstwh]

Oracle is NOT capable of reading the data from an excel spreadsheet. However their is a free package at

https://technology.amis.nl/wp-content/uploads/2011/02/as_xlsx11.txt

It can write excel spreadsheets.

Otherwise you can dump into a csv file to user sql loader to import or excel itself can attach to an oracle database to push/pull data

Bill
Lead Application Developer
New York State, USA

 

[Kalyan Ganesan]

Hi Bill,

So now that you said Oracle is NOT capable of reading the data from an excel spreadsheet,can oracle read CSV file thru pl sql and insert two columns into a oracle table?i can do this manually(thru the import tool) i know that but all this needs to be run in a script in production that is why..

 

[Beilstwh]

It sure can, Thats normally how an excel file is moved into oracle. save your excel file as a csv file (I would use a pipe "|" as a delimiter) and then use sql loader to insert it into the database or setup an external table. If you need help setting up either put an example of a row from your excel spreadsheet and the layout of the table that it is being inserted into. I assume your loading into oracle 11 sincle thats the forum.

Bill
Lead Application Developer
New York State, USA