If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > SQLPlus Return Value Help!

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Oct 2004
Posts: 12
SQLPlus Return Value Help!

Hi there.

I am writing a DOS batch script that calls SQLPlus and I want to be able to have SQLPlus return a value back to the DOS batch script.

My sql "code" calls a User-defined function that returns different values based on various inputs.

I have seen examples of how to return error codes (not values) in Unix, but NOT values and NOT in DOS. Any help would be appreciated.

Mike
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Apr 2004
Posts: 246
Have your script output the result you want to capture as the last thing it does (select it, use dbms_output, whatever). The FOR command is then used to capture that last line of output to variable x. x can then be passed to everything else. Set "delims" to something that won't be in your output - the default is whitespace, so the output "10/10/2004 11:23" would be parsed as two fields, and you'd only get the first value. The "-s" option prevents sqlplus disconnect messages from being retrieved.

@echo off
FOR /F "usebackq delims=!" %%i IN (`sqlplus -s u/p @t`) DO set x=%%i
echo %x%
__________________
Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Oct 2004
Posts: 12
Thanks for your help!

So, the real key is the %%i. Can I set it another way? Do I have to do it in a FOR loop as I am only going through the loop once? I pass in the current community and get either a '1' or '2' back as the partition.

Here is my UDF:

FUNCTION GETPARTITION (
l_Comm IN VARCHAR2
)
RETURN VARCHAR2 IS
l_partition VARCHAR2(1) default null;
/************************************************** ****************************
NAME: GETPARTITION
PURPOSE: To return the Current Partition for a given community.
%USERNAME% (set in TOAD Options, Procedure Editor)
************************************************** ****************************/
BEGIN

SELECT CURRENT_PARTITION_NUMBER INTO l_partition FROM CURRENT_PARTITION WHERE Community = l_Comm;
EXCEPTION
WHEN NO_DATA_FOUND THEN
Null;
WHEN OTHERS THEN
Null;

RETURN l_partition;
END GETPARTITION;
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Jun 2003
Location: West Palm Beach, FL
Posts: 2,657
Cool

Another way:

In sql code use/do something like this example:
Code:
var rc number;
whenever sqlerror exit :rc
exec :rc:=1;
select count(*) from emp
/
exec :rc:=2;
create table emp2 as
  select * from emp
   where deptno='10'
/   
exec :rc:=0;
exit :rc
In your DOS cmd file do:
Code:
rem test.cmd
sqlplus scott/tiger@mydb @test.sql
IF ERRORLEVEL==1 GOTO ONE
IF ERRORLEVEL==2 GOTO TWO
  echo 'No errors.'
GOTO END
:ONE
  echo 'Error Level 1'
GOTO END
:TWO
  echo 'Error Level 2'
:END
__________________
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Oct 2004
Posts: 12
thanks for your help LK. I certainly could use error codes as I only have a couple of potential values, but it seems a little kludgy. But, I may have to get off of my high horse! Thanks again.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Oct 2004
Posts: 12
I may end up just writing a little 'C' program to overcome other issues (Like enhanced error handling). Can I get the return value into another program in a similar way?
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Apr 2004
Posts: 246
Don't use the exit code - it makes it impossible to check for actual errors.

I've done the pro*c method, but then it becomes very static - one program for each request.

The FOR method loop over all the output from the entire sqlplus session (hence the -s option to prevent disconnected messages), but will only store the final value. It actually sets the variable on every line, overwriting whatever it had before, so only the last one is actually captured. If you need multiple rows, you could play around with the FOR command and try to set the variables as i_#, where # increments. If you do it, please send it back for me. The FOR command can also be used to break the output line into multiple vars: output="123/abc/9-sep-04", you could set the delim to "/", and then DO (set x=%%i ) ; (set y=%%j) ; (set z=%%k)
(not sure about the parens and semicolons, didn't have time to fully test)


If you need multiple lines of output captured, you could spool it to a file, and then use that file to set the variables. Spool a file that actually has the SET commands in it, then just execute the file after you exit sqlplus.
__________________
Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On