How 2 pass Host Variable, Command line args 2 PL/SQL block ???

From: Victor Rajendra Kumar Addepalli <victor_at_hermes.cs.uh.edu>
Date: 1995/06/28
Message-ID: <3sso4c$rhj_at_masala.cc.uh.edu>#1/1


I would like to know how to pass the command line arguments from a sql script to the PL/SQL procedure/block. I have tried the following but it did not work. If anyone out there has some suggestions or any direction towards the solution, I did appreciate that. Thanks in advance.

-------------------------CODE FOLLOWS-------------------------------
 

SQL>_at_my_command.sql 'hello'  

Contents of 'my_command.sql' looks like this:  

rem begin of SQL script
define input_data='&1'  

variable host_data varchar2(100);  

host_data := input_data;  

rem PL/SQL block begins here  

declare
  local_data varchar2(100) := 'bye bye'; begin
  ...  

  :host_data := local_data;  

  ...
end;
/
rem end of PL/SQL.  

print host_data;
undefine input_data
undefine 1
exit
rem end of SQL script.


 

The only way I can pass data into PL/SQL block from outside is through the host variables (besides passsing parameters ofcourse). But in the above code I am trying to pass the string 'hello' as the command line argument into the sql script 'my_command.sql` and I can take that data into the define variable as " input_data='&1' ".  

In order to pass this data into my PL/SQL block I tried this:  

host_data := input_data;
or
host_data = input_data;  

and I got the following error message on this statement. unknown command beginning "host_data..." - rest of line ignored.  

Is there a better way of doing this or am I missing the boat !    

thanks,
victor addepalli
email: victor_at_cs.uh.edu

       vaddepa_at_heron.mnet.uswest.com

-- 
=========================================================================

| Victor Addepalli : Dept. of Comp. Sc., Univ. of Houston |
| e-mail : victor_at_cs.uh.edu : University Park, Houston, TX - 77204 |
| US-mail: 4371 Faculty Lane, Houston, TX - 77004 |
Received on Wed Jun 28 1995 - 00:00:00 CEST

Original text of this message