Oracle SQL Loader – Running Oracle’s SQLLDR on VirtualBox VM from MacBook

🙂 I'm not even CLOSE to a DBA, just an engineer that finds myself learning Oracle. Any help is much appreciated!

Here's my question (which is a lot simpler than the sea of words which follow would seem to indicate):

How do I use sqlldr to load data files into my VM database from my MacBook when the following conditions are true:

1. My database is in a Linux VM installed using VirtualBox (https://www.virtualbox.org/wiki/Downloads);

2. The database itself was downloaded from the Oracle DB Developer VM (a.k.a. "appliance"), located here: https://www.oracle.com/database/technologies/databaseappdev-vm.html)

3. I am running SQL Developer from my host desktop (my MacBook) and connected to the database inside the VM by 'talking' to port 1521 on my machine, which is setup to send traffic to port 1521 on the Linux image;

   

4. I created 6 tables from my host desktop version of SQL Developer, called D, M, P, R, S, and T;

   

5. All the data I want to load into these tables and the files I need to do the load (e.g., the control file, etc.) are located on my MacBook here: …/Desktop/Work/Database/DATA_TABLE/

6. sqlldr exists inside my VM image (which I confirmed by getting back the expected output from a no-argument run of sqlldr inside the VM);

7. I do not have sqlldr on my MacBook.

I'd like to be able to run the sqlldr command from my MacBook and have it do the load on the VM side. Here's what I imagine as possible scenarios:

(a) Issue the sqlldr command to run on the VM through an SSH connection to it…

ssh -p 2222 oracle@localhost

…with the VM accessing files through a mounted connection:

https://techsupport.screenplay.com/hc/en-us/articles/360035782711-Creating-a-Shared-Folder-between-your-Mac-running-Catalina-and-a-Virtual-Machine

One problem with this, though — I ran the above ssh command, and the connection worked, but sqlldr could not be found:

(b) Forget ssh-ing, instead installing sqlldr on my MacBook, as shown in the link below…

https://medium.com/@niharikabitra/how-to-install-sqlldr-4541e91e67a

…then run my sqlldr commands from my MacBook.

Of course, this leaves me wondering — how does the data actually make it to the VM database? For that matter, how did the tables I created above get into the database? Are they even there? Is all this happening through port 1521?

Putting the "solutions" I've imagined aside, what is the usual way folks do this? Say, an Oracle instructor using a VM in a class he or she is teaching?

For instance, this link seems pretty relevant…

https://www.thatjeffsmith.com/archive/2018/10/yes-sqlloader-is-faster-at-loading-records/

…because it runs the following type of sqlldr command:

sqlldr hr/oracle@localhost:1521/orcl CONTROL=TABLE_EXPORT_DATA.ctl LOG=run1.log BAD=records.bad

BUT, IT'S NOT CLEAR TO ME HOW HIS HOST MACHINE (IN HIS CASE, WINDOWS) EVEN KNOWS WHAT THE SQLLDR COMMAND EVEN IS. Did he install it on his Windows machine, or is the port forwarding somehow asking the VM version of sqlldr to run?

Thanks for ANY help here,

Sincerely, a confused dude,

Justin

EDIT TO INCLUDE THIS IMAGE FOR ED STEVENS:

EDIT TO GIVE MY FINAL RESOLUTION:

@EdStevens, @Suresh, and @Albert Godfrind, thank you for all of your input. Combining this input, and relying on Albert's installation instructions for Oracle Instant Client, I was able to finally load my data. Points of note are as follows:

1. Instead of downloading the .zip files, I downloaded the .dmg files, which worked just great.

2. I then combined the files from the different .dmg files into one directory per the instructions given at the download link, then I moved the combined directory (instantclient_19_8) from my Downloads folder into the /usr/local/oracle/ directory, as Albert suggested via his recommended ORACLE_HOME environment variable.

3. Because I am on Big Sur, where zsh seems to be the default, I elected to modify my ~/.zshrc file instead of my ~/.bash_profile file. The added lines are similar to those from Albert, with one exception — the TNS_ADMIN environment variable:

# Setup for Oracle / SQLPLUS
export ORACLE_HOME=/usr/local/oracle/instantclient_19_8
export PATH=$ORACLE_HOME:$PATH
export DYLD_LIBRARY_PATH="$ORACLE_HOME"
export SQLPATH="$ORACLE_HOME"
# export TNS_ADMIN="/usr/local/oracle/network/admin"
export TNS_ADMIN="$ORACLE_HOME/network/admin"
export NLS_LANG="AMERICAN_AMERICA.UTF8"

I do not have a tnsnames.ora file because, "[b]y default, when you install Instant Client, Oracle Universal Installer does not include a sample tnsnames.ora file nor the Oracle Net Configuration Assistant utility normally used to create it," as noted in Section 4.2.3.2 here:

https://docs.oracle.com/cd/B19306_01/install.102/b14312/post_install.htm

Therefore, I simply specified the TNS_ADMIN directory already created in the downloaded Oracle Instant Client directory structure.

4. I created 6 separate parfiles, one for each table, so that I could simply have a single parameter in the sqlldr command. For example, for table 'D', the parfile looks like this:

userid=JUSTIN/oracle@localhost:1521/orcl
control=/Users/ricej/Desktop/Work/Database/DATA_TABLE/input_files/D/D.ctl
data=/Users/ricej/Desktop/Work/Database/DATA_TABLE/input_files/D/D.ldr
log=/Users/ricej/Desktop/Work/Database/DATA_TABLE/output_files/D.log
bad=/Users/ricej/Desktop/Work/Database/DATA_TABLE/output_files/D.bad
discard=/Users/ricej/Desktop/Work/Database/DATA_TABLE/output_files/D.dsc
direct=true

(P.S., @EdStevens, I certainly need more descriptive table names — Your comment is noted. :))

All said and done, I am going to select the resolution from @Albert Godfrind as the winner, but I want to thank @EdStevens for his prompt reply and back-and-forth exchange, which was very helpful for a new guy like me trying to learn the basics. Thank you all!