A customer ‘ambushed me’ – how dare they!?! – at the conference today, needing help getting their scripts to work.
So something like this:
And the way the customer wants to run scripta.sql (which calls scriptb.sql)
So, where is SQL Developer going to look for scriptA? And when it gets to the @child\scriptb.sql?
We can call scripts.sql using
@c:\users\jdsmith\desktop\parent\scriptA.sql
But, we’re going to have a problem.
So, you have 2 options.
First, you COULD tell SQL Developer where to look explicitly for your scripts in the preferences.
So what’s your second option?
So, What Do We Do?
Use the CD command 🙂
Yes, CD!
It’s a command we added to SQLcl. It’s pretty simple. It changes the directory we read from, and write to.
I am HR ON orcl > help cd CD --- Changes path TO look FOR script at after startup. (SHOW SQLPATH shows the FULL SEARCH path currently: - CD CURRENT directory setting SET BY LAST cd command - baseURL (url FOR subscripts) - topURL (top most url WHEN starting script) - LAST Node opened (i.e. file IN worksheet) - WHERE LAST script started - LAST opened ON sqlcl path related file chooser - SQLPATH setting - "." IF IN SQLDeveloper UI (included IN SQLPATH IN command line (sdsql)) ). I am HR ON orcl>
So, knowing this, let’s see JUST where SQLDev is looking for files.
Since I had used the CD, it’s looking in THAT directory first. Then it’s looking here:
SQLPATH : C:\Users\jdsmith\Desktop\Parent;C:\Users\jdsmith\AppData\Local\Temp\;C:\Oracle\product\12.1.0\client_1\dbs;c:\Users\jdsmith\Desktop;.
And By The Way…
If you’re ever at a conference, ALWAYS take the opportunity to approach Oracle Database product managers. It’s what we’re here for. Direct feedback is one of the most valuable things we get from leaving our little caves 🙂
4 Comments
Hello Jeff,
I am writing a big SQL script for others to use. I do not know which client type and version users will use to run that script. I want it to work in both SQLcl and SQL*Plus. The script is using the CD command – which will fail in SQL*Plus (and that is OK) but I need to be sure the command is available in their SQLcl. I need to know in which version the CD command was introduced in SQLcl, but I could not find that information anywhere. Downloading and checking chained previous versions from SQLcl website would not help as they do not go far enough (based on the date of this article, CD was introduced in or before year 2018). Can you please help me with this?
Boris
Since at least 2014
https://www.thatjeffsmith.com/archive/2014/12/sdsql-installing-and-connecting-with-our-new-command-line-interface-to-oracle-database/
With traditional SQL-PLUS you can set your sqlpath in the registry of windows to ‘dir1;dir2;dir3’ (a list of directories). With SQLcl you just set the working directory with CD and that is where your script file must reside if you want to execute it with @myfilename. With traditional sqlplus your script can reside in wathever folder, as long as it is mentionned in your sqlpath (wich cant be set wit SQLcl to multiple directories)
Right?
Incorrect.
Using CD just updates SQLPATH basically
I am HR on orcl > show sqlpath
SQLPATH : c:\Program Files\Oracle\sqlcl\18.1\bin\;.;C:\Oracle\product\12.1.0\client_1\dbs;c:\Users\jdsmith\Desktop