On Running Oracle Scripts with Relative Paths

A customer ‘ambushed me’ – how dare they!?! – at the conference today, needing help getting their scripts to work.

So something like this:

A script that calls another script.

And the way the customer wants to run scripta.sql (which calls scriptb.sql)

Note that the path to scriptb.sql is relative.

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.

Well, fudge.

So, you have 2 options.

First, you COULD tell SQL Developer where to look explicitly for your scripts in the preferences.

I don’t like this way, as it will always look for scripts here, might not be what you always want.

So what’s your second option?

So, What Do We Do?

Use the CD command 🙂

Wait…what, CD?

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.

Ahhhh.

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 🙂

关注dbDao.com的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569