Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: Script question: SQL & PL/SQL variables
Thanks, Shana! That's the solution that I had ended up with as well, but it seems pretty hacked. Well, if it works, I won't mess with it. Thanks again,
-Peter
"Shana Chen" <schen_at_cnri.reston.va.us> wrote in message
news:39DB77C1.97014360_at_cnri.reston.va.us...
> Peter Amundson wrote:
>
> > Hey all:
> >
> > I'm trying to write a simple SQL script (to be run from SQL*Plus) which
will
> > accept input from the user, truncate the input to 10 characters, then
pass
> > this result as a parameter to another script. This seems simple enough,
but
> > I'm stuck!
> >
> > I believe that I must use PL/SQL to truncate the user's input (actually
> > using substr), but I can't figure out how to pass this value outside of
the
> > PL/SQL block so that I can call another script. What I want is
something
> > like:
> >
> > ----------------------
> > accept longinput prompt 'Enter long string: '
> > variable g_short varchar2(10)
> > begin
> > :g_short=substr('&longinput',1,10);
> > end;
> > /
> > @nextscript :g_short
> > -----------------------
> >
> > The syntax of this last line doesn't work. I can use "print g_short" to
see
> > the value of that variable outside of the PL/SQL block, but can I do
> > anything else with the value other than print it? If there's a way to
> > manipulate the substitution variable without using PL/SQL, that would be
> > even better (but I don't think this is possible).
> >
> > Any help would be appreciated!
> >
> > -Peter
>
> You may consider create an intermidate sql script to perform your task,
> since sqlplus is not a programing language. Here is my suggestion:
>
> Create and run thefirstscript.sql as:
>
> accept longinput prompt 'Enter long string: ';
>
> set head off
> set verify off
>
> spool call_nextscript.sql
> select '@nextscript '||substr('&longinput',1,10) from dual;
> spool off
>
> @call_nextscript
> exit;
>
> Assume that nextscript exists already at the current directory and
> Note that substr is used to truncate the user input longinput.
>
> Shana Chen
>
>
>
Received on Wed Oct 04 2000 - 15:40:07 CDT