Reading a text file in oracle sql

FOPEN is a handle to a directory object not the a string containing the directory path. Improve this answer. MT0 MT0 k 9 9 gold badges 46 46 silver badges bronze badges. Thanks for the explanation. While your code is compiled without error when i tried executing it i am getting runt time error invalid file operation and for the previous code had error: numeric or value error character to number conversion error. What is the full error message incluing ORA- number?

It a local directory, dont think there is any permissions issue. This is not an issue with the SQL but an issue of permissions. You need to check that a the directory exists b you have permissions to read from the directory c the file exists d that you have permissions to read the file try opening it through the OS and e that the oracle user has permissions to open the file run the GRANT command.

You have a pretty strange concept. What you are talking about 20 years old approach then first PC was used mostly as typewriters. Insure that the value is reasonable and changed by someone authorized to do so by controlling the entry and storage. That way the FY change would happen on schedule without intervention.

Some way of letting the user know when the FY change would be included at least from just before the earliest valid change date until the change took place.

The app would only be used to advance or delay the change date or revert if intended delay was not updated in time. If the automatic part of that method worked well enough or you thought it would , you might skip the user update feature for now. In those cases, DBAs made the changes as soon as authoriztion was received from the appropriate user. What I suggested — using the name of a file to determine what you want to retrieve — takes a simple piece of SSIS.

However, if you want to provide a readable report that can also be provided in Excel or PDF format, break out SSRS, do the report off the query you already have in a stored procedure, with parameters , put a parameter or two onto the report to control the sp, and let the user decide what he or she wants to see, see it, decide that it is what they want, and then export it out to Excel, PDF, etc.

I am sorry if I misinterpreted myself by saying reporting, there is no involvement of pdf or excel or access or webpage or anything else. I do not want to add any more complexities. Thanks Brett That is exactly what I am trying to do but since this is my first SSIS package that I am creating, it would be helpful if you have some syntax.

Add a variable call it strFileName. From the toolbox add a ForEach task. Right-click the task and select edit.

Fill in the fields so that you tell it the path, the extension. Go to the Parameters tab and set the zeroth parameter to be mapped to your variable. This should get you the file name of any txt files in your directory. Tell the users to put a file called Next, add a DataFlow Task from the toolbox and connect the green output from the for each task to it. Please advise me. You would need to resort to a Java stored procedure, i. Hi Justin, I executed an action plan from the site as your mentioned.

Nick Galemmo Posted October 12, 0 Comments. Anonymous Posted October 12, 0 Comments. Or better, from an auditing POV, pass the actual filename in to the procedure. Defry Afrian Posted October 13, 0 Comments. Hi Justin, This is solved since I explored and executed what you mentioned. Register or Login. Welcome back! Sign in with Email. Reset Your Password We'll send an email with a link to reset your password. Stay ahead! Get the latest news, expert insights and market research, tailored to your interests.

Sign up with Email. Sign in with email Enter the email address associated with your account. You auth link is expired or incorrect, please try again. Have you ever had the need to read a text file into Oracle? And parse out pieces of information when you do it? The example below shows you how to read an Oracle text alert log into an Oracle table, but you can use this method to read any text file.

Note that we set the length of the line to Just set it to the maximum length of the line.