0

I suppose this is somewhat of an extension of the question asked here.

However, I believe the linked OP and my own reason for reading a file with SQL Developer are different. I am learning SQL and databases and am attempting to create a model database (as in, I won't be editing the data after insertion, just set up search queries and what not). I want it to be large (over 100,000 entries), so I've created a C++ program that wrote randomly generate entries for the database on a .txt file (one entry per line in the .txt file) instead of hard coding the insertion of each entry. Now what I want to do is read the .txt file in SQL Developer and inserts it into a table.

My problem lies in the fact that I am not able to create directories. I am using a university oracle connection and I do not have the privileges to actually make a directory so that I can use UTL_FILE on my .txt file as was answered in the linked question. Assuming there is no way for me to gain this permission, is there an alternate way I can accomplish reading a .txt file for data for my table? Is there a better way to go about creating "dummy data" for my database?

Community
  • 1
  • 1
The Alex
  • 211
  • 1
  • 5
  • 11
  • 2
    you can use `sqlldr` to import the txt files into the Oracle database. `sqlldr` is a client side tool and you don't need any special privileges to use it (except of course the insert privilege for the tables in question). Ther eare many, many other tools that can import txt files into a database. A quick internet search should turn up several –  Oct 19 '14 at 20:30
  • If you have a DML file(.txt) and connection ready in SQL developer, why not simply execute the contents of that file in SQL developer after connecting to the database? Why do you need write access to the remote machine's filesystem where your database is hosted? – toddlermenot Oct 19 '14 at 20:35
  • +1 for sqlldr. Another option - you may create an SQL script (which performs all the required DML) in C++ and run it in SQL*Plus (@filename.sql). If there are a lot of DML operations in the script, make intermediate commits (maybe after each 100 inserts). – Multisync Oct 19 '14 at 21:34
  • If your aim is to insert dummy data, you can do that by writing queries. There is no need to generate it outside Oracle and then load it. Have a look at http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_random.htm#ARPLS040 – Noel Oct 20 '14 at 05:29

1 Answers1

0

What I ended up doing to insert my mock data was change the way the .txt file was formatted. Instead of having my C++ code write the data one entry per row, I actually made the code so that it wrote SQL code to the .txt file as I think @toddlermenot was suggesting, more or less. After I had the C++ code write as many inserts-with-mock-entries as I needed to the text file, I just copy/pasted it to SQL developer and achieved the desired results.

My problem is a classic case of making the process more complicated than it needed to be.

Also, even though I did not use the method,@Multisync provided an interesting way to go about achieving my goal. I had no idea SQL had the tools for me to generate mock data. Thanks for introducing me to that.

The Alex
  • 211
  • 1
  • 5
  • 11