Error Reading File Pl Sql Developer
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us how to import csv file in pl sql developer Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community pl sql developer initialization error Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up PL SQL pl sql developer download 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 am doing? pl sql developer tutorial 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
Pl Sql Developer Jobs
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 159k14203250 1 I have given the path for a the local client machine. I have no idea what an Oracle directory object is. now since you mentioned it I saw in google that I need to create a directory object and map it to my local client machine. Is this correct? –Vabs Apr 1 '11 at 5:38 3 @Vabs - PL/SQL runs on the database server. That means that it can only access files that are stored on the database server. Code running in the database cannot access files stored on your local client machine. –Justin Cave Apr 1 '11 at 6:03 thanks so how can I acess files on my local client machine? –Vabs Apr 1 '11 at 6:12 @Vabs - See APC's answer. Generally, as he indicates, you want to move the filr to the server first. –Justin Cave Apr 1 '11 at 7:18 add a comment| up vote 5 down vote "I have given the path for a the local client machine" PL/SQL runs on the database serv
here for a quick overview of the site Help Center Detailed answers to any questions
Pl Sql Developer Interview Questions
you might have Meta Discuss the workings and policies of this pl sql developer download 64 bit site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers pl sql developer for mac or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a http://stackoverflow.com/questions/5509444/pl-sql-not-able-to-read-from-file community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Error reading a file using UTL_FILE up vote 0 down vote favorite 1 I am facing some issue in reading a file using utl_file. The same code is working fine in some of http://stackoverflow.com/questions/28475760/error-reading-a-file-using-utl-file the instances and failing in one instance. Below is the issue: Code: procedure del_mul_tags(p_request_id in number) is fileID UTL_FILE.FILE_TYPE; fileID1 UTL_FILE.FILE_TYPE; line varchar2(32000); l_dir varchar2(1000); l_file varchar2(20); l_file1 varchar2(20); l_request_id number; l_count number := 0; l_count_t number :=0; l_write boolean := TRUE; cursor c_dir is select substr(OUTFILE_NAME,1,instr(OUTFILE_NAME,'/',-1,1)-1),substr(OUTFILE_NAME,instr(OUTFILE_NAME,'/',-1,1)+1) from fnd_concurrent_requests where request_id = p_request_id; begin open c_dir; fetch c_dir into l_dir,l_file; close c_dir; execute immediate 'create or replace directory W2_OUT_DIR as ''' || l_dir || ''''; begin fileID1 := UTL_FILE.FOPEN ('W2_OUT_DIR', l_file, 'R'); -----Getting the error right here!!!----------- loop begin UTL_FILE.GET_LINE (fileID1, line); --Some Logic EXCEPTION WHEN NO_DATA_FOUND THEN UTL_FILE.FCLOSE(fileID1); EXIT; end; end loop; fileID := UTL_FILE.FOPEN ('W2_OUT_DIR', l_file, 'R'); loop begin UTL_FILE.GET_LINE (fileID, line); --Some Logic EXCEPTION WHEN NO_DATA_FOUND THEN UTL_FILE.FCLOSE(fileID); EXIT; end; end loop; end; EXCEPTION WHEN OTHERS THEN fnd_file.put_line(fnd_file.log,'Error while deleting the xml tags: '||SQLERRM); end; ................ In this instance file, l_file 'ACHAKR01.23067873' was created through another concurrent process. Its OS user is 'appsofde'. And th
things to note there, 1, I didn't have to put the extension on the file and b, I assumed SQL*Plus http://www.oraclenerd.com/2013/02/run-scripts-in-sql-developer.html was running from the directory where my file was located. If I was running the script from a different directory, I would have to use either a relative path...or something, but I digress. I wanted to be able to run my script in a SQL Developer worksheet. How? @clean_tables Error starting at line 38 in command: @clean_tables Error report: Unable to open file: "clean_tables.sql" Twitter. Jeff pl sql Smith hangs out there, a lot. He supposedly has a real job as the Senior Assistant Principal Skinner Product Dude for SQL Developer at Oracle. Crazy title, I know. Back to Twitter. @oraclenerd working directory would be directory of parent file— Jeff Smith (@thatjeffsmith) February 22, 2013 Since he lives there (Twitter) (and I'm glad he does), I got an immediate response. Yay for Jeff. Wait, pl sql developer what? Parent file? WTF are you talking about? (I then remove the snark and try to put more details) (oh, and I don't like that I can't just embed a single tweet...sorry, their fault, not mine) @thatjeffsmith "@ test(.sql)" I just want to know where to put test.sql.— oraclenerd (@oraclenerd) February 22, 2013 Two seconds later: @oraclenerd i think this, Tools > Preferences > Database > Worksheet > Select default path to look for scripts. Please test :)— Jeff Smith (@thatjeffsmith) February 22, 2013 Tested, and it works. Yay for me. Yay for Jeff. In case it isn't obvious, I'm being sarcastic. Jeff is a fantastic advocate for SQL Developer. Yes, he gets paid to do it, but he goes above and beyond on a daily basis. Oracle is lucky to have him. Posted by chet justice at 2/21/2013 08:53:00 PM Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest Labels: funny, howto, sql developer 2 comments: thatjeffsmith said... So if you had a file open, we'd look in that same directory first...again, I think. That's what my cryptic tweet was referencing.Also, thanks for the promotion. February 21, 2013 at 8:59 PM chet justice said... I was h