Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Script question: SQL & PL/SQL variables

Re: Script question: SQL & PL/SQL variables

From: Peter Amundson <pjamundson_at_cotlnospam.com>
Date: Wed, 4 Oct 2000 13:40:07 -0700
Message-ID: <8rg4nb$qcp$1@brokaw.wa.com>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US