Re: Stored Proc Question

From: Nandakumar <nanban_at_my-deja.com>
Date: Fri, 30 Jul 1999 22:29:09 GMT
Message-ID: <7nt8vb$5dp$1@nnrp1.deja.com>

Hope you could find this code useful.

#!/bin/ksh

# Nandakumar
# Usage : get_date_of_next_day date day
# : get_date_of_next_day yyyy-mm-dd Monday

get_date_of_next_day()
{
 Date=${1}
 Day=${2}

 export ORACLE_HOME=                      **
 export ORACLE_SID=                       **
 export DISPLAY=                          **

 export PATH=.:..:$PATH:$ORACLE_HOME/bin  ${ORACLE_HOME}/bin/sqlplus -s user/password_at_SID.world <<END  set head off
 select
 to_char(next_day(to_date('$Date','YYYY-MM-DD'),'$Day'),'YYYY-MM-DD')  from dual;
END
}

# Invoke the function

next_date=`get_date_of_next_day 1999-01-01 Sunday`

Now the 'next_date' will be having the mesge printed within the function ie) the output shown by sql server.

--

In article <933353024.1717.0.pluto.d4ee154e_at_news.demon.nl>,   "Sybrand Bakker" <postmaster_at_sybrandb.nospam.demon.nl> wrote:

> You can only return variables with a function.
> As you need to have it in korn shell, why don't you just use
> dbms_output.put_line. Redirection works with sqlplus!
> call it as follows
> sqlplus -s <uname>/<pw>@<database> @<yourscript>
> and the normal banner/signon message is suppressed and you will have

the

> variable only.
>
> Hth,
>
> Sybrand Bakker, Oracle DBA
>
> <jane604_at_my-deja.com> wrote in message

news:7nsc3v$fmc$1_at_nnrp1.deja.com...

> > I'm new at using oracle and I have a question
> > about using stored procedures.
> > I've written a very simple stored proc that
> > selects a field into a variable.  I want to
> > return that variable to a korn shell.  When I try
> > using RETURN <var_name> I get the message
> > PLS-00372: In a procedure, RETURN statement
> > cannot contain an expression.
> >
> > My question is can you only use a RETURN
> > statement with functions?  I tried using OUT, but
> > since I need the variable for a script, I can't
> > get a handle on the variable that way.
> >
> > Any suggestions?
> >
> >
> > Sent via Deja.com http://www.deja.com/

> > Share what you know. Learn what you don't.
>
>

--
Nandakumar
Systems Analyst
New York
(N.Kumar_at_rocketmail.com)

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Jul 30 1999 - 17:29:09 CDT