running sqlplus from within Julia

Question

I'm a very beginner Julia user but would like to use it for some of my projects.

Many of my projects require me to make a quick connection to Oracle to get an ID number of some other data. I can do this by running sqlplus from other programs like shell or tcl, but I've tried the syntax in the Julia documentation, but always get one error or another.

In Tcl it looks something like this

exec sqlplus -s user/pass@dbname << "
            SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
            select ID from table1 where name='abc';
            exit;
            "

From julia, I'm trying to use the run command like this

run(`sqlplus -s user/pass@dbname << "
   SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
   select ID from table1 where name='abc';
   exit;
   "
   `)

but I get various errors from Julia like

Stacktrace:
[1] depwarn(::String, ::Symbol) at ./deprecated.jl:70
[2] warn_shell_special(::String) at ./shell.jl:8
[3] #shell_parse#236(::String, ::Function, ::String, ::Bool) at ./shell.jl:103
[4] (::Base.#kw##shell_parse)(::Array{Any,1}, ::Base.#shell_parse, ::String, ::Bool) at ./<missing>:0 (repeats 2 times)
[5] @cmd(::ANY) at ./process.jl:796
[6] eval(::Module, ::Any) at ./boot.jl:235
[7] eval_user_input(::Any, ::Base.REPL.REPLBackend) at ./REPL.jl:66
[8] macro expansion at ./REPL.jl:97 [inlined]
[9] (::Base.REPL.##1#2{Base.REPL.REPLBackend})() at ./event.jl:73

Any help from anyone?

already tried that. I get an error like "could not spawn sqlplus -s user/pass@dbname. no such file or directory" — Jonjilla
Jon: to me this particular error means sqlplus is not in the path / accessible from your julia environment. I.e. it has nothing to do with the particular syntax you used (obviously this doesn't mean that if you put it in the path, it will immediately work, since the syntax may also be slightly wrong). — Tasos Papastylianou
alternatively, you may be passing that string to your pipeline, which julia interprets as an input filename to pipe from, so naturally it complains there's no such file. If this is the case, try echoing the string instead, and using that as the input to your pipeline — Tasos Papastylianou
no, that's not the case. That's the first thing I checked. I've tried the fully qualified path with the same errors. and some variation of the backticks, I do get an sqlplus error, which means that sqlplus is correctly defined in the julia environment — Jonjilla

Dan Getz Dan Getz · Accepted Answer · 2017-07-28T14:29:43

Here is a function which works on my machine, it also returns the output of sqlplus command in a variable (if this is needed). If the output is not needed, a simpler solution might be available.

sqlplus_script = """
   SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
   select ID from table1 where name='abc';
   exit;
"""

sqlplus_cmd = `sqlplus -s user/pass@dbname`
# sqlplus_cmd = `cat`         # used for testing

function stringpipe(cmd,instring)
    inpipe = Pipe()
    outpipe = Pipe()
    p = spawn(pipeline(cmd,stdin=inpipe,stdout=outpipe))
    write(inpipe, instring)
    close(inpipe)
    close(outpipe.in)
    s = read(outpipe,String)
    return s
end

println(stringpipe(sqlplus_cmd, sqlplus_script))

It is mostly self-explanatory (BTW using Julia version 0.6 but should probably work on 0.5).

running sqlplus from within Julia

1 Answers