Oracle q: Passing data from anonymous block to Unix environment

Factual Questions
1

You may know how you can embed a SQL script in a Kornshell script, do a single row select, and pass the result back to the shell script. For instance, you can have:

BATCH=sqlplus -s user/pass <<END set serveroutput off echo off feedback off header off pagesize 0 select a_field from a_table; END

echo the value of Batch is $BATCH

and the environment variable BATCH will be populated with whatever came back from your select statement.

But what if the select statement invokes a function, that updates the database? Then you get the dreaded Oracle error “Function not guaranteed not to update the database”. What if that’s exactly what you want to happen, for example when you want to get the next available instance id in a process registry, and update the registry table at the same time?

Evidently the code will run if you put it inside an anonymous block. But then how do you get the value back to the shell script?

Has anyone ever managed to do this?

2

Right off the bat, something like



#!/bin/bash
BATCH=`sqlplus -s user/pass@db &lt;&lt;EOF
set serveroutput on
set echo off
set feeback off
declare
  i_batch_id integer;
begin
  i_batch_id:=Get_Batch_Id();
  dbms_output.put_line(i_batch_id);
end;
/
exit
EOF
`

The idea is that use dbms_output.put_line to write your value out to STDOUT where the `` will pick it up. It might be better to turn the tables around and have sql*plus execute a shell script via the host command. Another option is to use something like Perl with DBI

3

Thanks, that seems like a really cool idea. But unfortunately it didn’t work, though it’s possible I have some other bug I need to find. I’ll keep working on it and reply to the thread if I get it to work.

4

The problem with NotMrKnowItAll’s suggestion was that serveroutput has to be off at the beginning, and set in-flight before the DBMS_OUTPUT.PUT_LINE call. That might be possible but I don’t know how to do it.

But I did find I was able to do it by having one embedded SQL script to run the function in a block, and having a second one to retrieve the process id, which is based on a randomized number that is used in the first script.

5

dbms_output.enable() and dbms_output.disable()

Syntax can be found in a place like $ORACLE_HOME/rdbms/admin/dbmsotpt.sql

If you curious. That directory contains lots of documentation.