Error Reading File Pl/sql
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have pl sql read csv file Meta Discuss the workings and policies of this site About Us pl sql read text file Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with pl sql read xml file us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just pl sql read excel file like you, helping each other. Join them; it only takes a minute: Sign up PL SQL not able to read from file up vote 1 down vote favorite I am new to PL/SQL and am trying to read from a file through PL/SQL as below. I keep getting "ORA-20000" for this. Is there something wrong I
Pl Sql Error Message
am doing? declare output_file utl_file.file_type; firstline VARCHAR2(1000); secondline VARCHAR2(1000); BEGIN output_file := utl_file.fopen (&1,&2, 'W'); utl_file.fclose(output_file); EXCEPTION WHEN utl_file.invalid_path THEN RAISE_APPLICATION_ERROR(-20000, 'ERROR: INVALID PATH FOR FILE.'); END; / sql oracle plsql share|improve this question edited Apr 1 '11 at 6:20 Sathya 13.2k1667106 asked Apr 1 '11 at 5:20 Vabs 62 my suggestion is to use sqlldr (or another language like C# or Java) to load the file first. Once in an Oracle table (a staging table), manipulate as needed. –tbone Apr 1 '11 at 11:49 add a comment| 2 Answers 2 active oldest votes up vote 5 down vote Presumably, this is a SQL*Plus script, right? What values are you providing for the two substitution variables? Are you trying to read a file on the server? Or on your local client machine? Have you created an Oracle directory object that matches the name of the directory object you are entering at runtime? share|improve this answer answered Apr 1 '11 at 5:29 Justin Cave 159k1420325
back to return to the previous page. Enter your Username and Password http://forums.allroundautomations.com/ubb/ubbthreads.php?ubb=showflat&Number=26606 to log in. If you have not yet registered, you can register here. Username: Password: Have you forgotten your login information? Mark all read Contact Us · · Top Generated in 0.008 seconds in which 0.002 seconds were spent on a total of 3 queries. Zlib compression disabled.Powered by UBB.threads™ PHP Forum Software 7.4.1p2
This Site Careers Other all forums Forum: Oracle/OAS Getting error while reading file by using UTL_FILE srikanth sankurthi Greenhorn Posts: 9 https://coderanch.com/t/604007/Oracle-OAS/error-reading-file-UTL-FILE posted 3 years ago DECLARE fileID UTL_FILE.FILE_TYPE; strbuffer VARCHAR2(100); firstTen VARCHAR2(10); BEGIN fileID := UTL_FILE.FOPEN ('D:\srikanth.txt', 'srikanth.txt', 'R'); UTL_FILE.GET_LINE (fileID, strbuffer); UTL_FILE.FCLOSE(fileID); END; Gives the following error. ================= Error starting at line 25 in command: DECLARE fileID UTL_FILE.FILE_TYPE; strbuffer VARCHAR2(100); firstTen VARCHAR2(10); BEGIN fileID := UTL_FILE.FOPEN ('D:\srikanth.txt', 'srikanth.txt', 'R'); UTL_FILE.GET_LINE (fileID, strbuffer); UTL_FILE.FCLOSE(fileID); END; pl sql Error report: ORA-29280: invalid directory path ORA-06512: at "SYS.UTL_FILE", line 29 ORA-06512: at "SYS.UTL_FILE", line 448 ORA-06512: at line 6 29280. 00000 - "invalid directory path" *Cause: A corresponding directory object does not exist. *Action: Correct the directory object parameter, or create a corresponding directory object with the CREATE DIRECTORY command. Martin pl sql read Vajsar Sheriff Posts: 3752 62 I like... posted 3 years ago To access a directory in PL/SQL, you need to create a named directory object. It is done using CREATE DIRECTORY command, which is also hinted in your error message. Oracle then allows admin to grant permissions on this object, which is how access to directories can be secured in Oracle. The first parameter of the UTL_FILE.FOPEN function must be the name of the directory object. Have a look at the documentation of the CREATE DIRECTORY command and UTL_FILE.FOPEN function. Feel free to ask again if anything is unclear. Post Reply Bookmark Topic Watch Topic New Topic Similar Threads Read File in PL/SQL PL / SQL to create synonyms for all tables Read a text file in Unix Error in Oracle like clause Large data file read All times are in JavaRanch time: GMT-6 in summer, GMT-7 in winter Contact Us | advertise | mobile view | Po