0

Can anyone let me know how to upload a flat-file without using SQL*loader or external table in oracle database? Like is there any function available in oracle to complete this task?

Please let me know the different ways to upload flat files apart from SQL*loader and External table.

Mogit
  • 7
  • 1

2 Answers2

1

Oracle SQL has no other built-ins apart from external tables to load CSVs.

The new(-ish) sqlcl utility (the replacement for SQL*Plus) has a load command for CSV files. Find out more. This is good enough for ad hoc loading of reasonably sized flat files. For performative loading of large amounts of data from the client-side - or having more control - SQL*Loader remains the tool of choice. External tables are the best option for automated loads.

APC
  • 144,005
  • 19
  • 170
  • 281
0

You could write a PL/SQL program which will use UTL_FILE, read contents of that file and insert rows into some table. You'll have to talk to DBA to create a directory, grant read/write privileges on it to you (i.e. user which will load data), possibly grant execute on UTL_FILE (again to you).

Another option - if there's Apex (Oracle Application Express) installed on that database - is to create a load data set of pages (don't worry, you don't have to do anything, the Wizard will create everything for you). I don't know what's in the background - maybe it is SQL*Loader, can't tell, but - you wouldn't be "explicitly" using it, but do everything in GUI.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • Well, sqlldr or external tables are the very tools designed and intended for the purpose of bringing external data into the database. Why do you want some other method? It would be pointless to try to suggest something else without knowing your objections to using the very tools that are specifically designed for the job. – EdStevens Jun 16 '20 at 12:02
  • @EdStevens, I guess you meant to post that comment under the question. – Littlefoot Jun 16 '20 at 12:09
  • yes, you are correct. It should have been under the opening question. – EdStevens Jun 16 '20 at 12:11
  • @EdStevens,In my last PL/SQL interview ,interviewer asked me that question. So I just want the solution now. – Mogit Jun 17 '20 at 05:19
  • Why didn't you say it was a job interview question? Perhaps the "solution" is to tell the interviewer that there are no other good solutions. Perhaps the question was as much or more about _how_ you would answer instead of any specific technical "solution". When I've been conducting interviews, I always include such a question. – EdStevens Jun 17 '20 at 11:56