Hi ,
It's working fine if excel file is in local system
using this command :
( SELECT NUMBER_VAL from table( as_read_xlsx.read( as_read_xlsx.file2blob( 'DATA_EXCEL', 'SCALING.xlsx' ), '1:1', 'G2' ) );)
but when I shared my excel on the network
and have created directory afterwards I am trying to get result from excel file ,it's showing some error.
ORA-22288: file or LOB operation FILEOPEN failed
Access is denied.
ORA-06512: at "PRODUCTION.AS_READ_XLSX", line 319
22288. 00000 - "file or LOB operation %s failed\n%s"
*Cause: The operation attempted on the file or LOB failed.
*Action: See the next error message in the error stack for more detailed
information. Also, verify that the file or LOB exists and that
the necessary privileges are set for the specified operation. If
the error still persists, report the error to the DBA.Actually,i want to get result from client machine so that's why i have shared excel file on client machine.
Please Provide me better solutions for this.
Best Regards,
Avinash Kumar
The files must be accessible to the database server itself. So you need to mount a folder from your machine on the database server.
As you're getting "Access is denied.", there's a problem with the configuration of this. You'll need to work with your sysadmins to figure this out.
But...
DO NOT DO THIS. IT IS A TERRIBLE IDEA!
First up, it creates a security loophole. It makes it easier for anyone else with access to your computer to transfer malicious code to the database. This is bad.
It also adds a dependency between your machine and the database. What happens to the process if your machine is switched off? Or you have a laptop, and take with you when you're out of the office?
What should you do instead?
There are a couple of options:
Have third (secure) location you can transfer files to. And the database can read/transfer them from. So if you need to load a new file, you copy it over. Then run the upload process.
OR
Create an application to load the data. You upload the file to the application and it takes care of the load.