Re: Script question: SQL & PL/SQL variables

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 - 13:32:33 CDT