Skip to Main Content
  • Questions

  • How to store procedure out variables in shell variable ?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Akash.

Asked: May 21, 2019 - 8:20 am UTC

Last updated: May 28, 2019 - 5:44 am UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hi Ask TOM team,

I have created a PLSQL package , which has a procedure in it. The procedure has 3 out parameters.
I am calling this procedure from a shell script using sqlplus in an anonymous block.
I need to store the procedure output variables in corresponding shell variables. What is the way to achieve this as assigning to shell variable is not working within the anonymous block?

As a workaround, I have added dbms_output.put_line statements in the package procedure to print the output variables and re-directing the output of sqlplus in shell script to a file. Then I read this file to populate the variables.

Is there an easier way to achieve this ?

Thanks

and Connor said...

Not really. There are variations on a theme here:

- output to a file, then a grep etc
- output to standard output, then something SHELL_VAR=`my script`

but ultimately they are all pretty much the same because there is no native integration between a shell and a database call.

You can go to extremes and right a C or Java program because it can "see" both the database and the OS layer, but my personal opinion is that is often overkill. I quite *like* spooling output to a file and then grep-ing etc for values, because in the event of an error, I've got the entire spool file to look it for ORA- errors etc.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Use a function

Gh, May 28, 2019 - 7:29 am UTC

Cal yourbprocedure from a function which returns concatenated outparameters as string say seperated by a pipe.
Then get it since your script and defeat back parameters from pipe concatanated string.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library