Re: PL/SQL - passing variables to SQL*PLUS.....

From: L120bj <l120bj_at_aol.com>
Date: 1997/03/17
Message-ID: <19970317230001.SAA10471_at_ladder01.news.aol.com>#1/1

You could try:-

create procedure last_c_lname (p_source_file in out varchar2,

                                              p_target_file in out

varchar2) as

begin

   declare

      save_last_c varchar2(1);
      len NUMBER;
      position_of_ NUMBER;

begin

   select fname into p_source_file from employee

          where salary = 38000;

   DBMS_output.enable(200);
   len := LENGTH(p_source_file);
   position_of_ := len-1;
   save_last_c := SUBSTR(p_source_file,-1);    if SUBSTR(p_source_file,position_of_,1) = '-'

     THEN
       p_target_file:=SUBSTR(p_source_file,1,position_of_);
       p_target_file:=p_target_file||'0'||save_last_c;

   end if;

   p_target_file := p_target_file||'.jpeg';    DBMS_output.put_line('file_name is: '||p_target_file); exception

   WHEN OTHERS THEN DBMS_OUTPUT.put_line('blop'); end;
end;

variable p_source_file varchar2(20)
variable p_target_file varchar2(20)

column o_source_file new_value o_source_file noprint column o_target_file new_value o_target_file noprint

get last_c_lname.sql;
/
execute last_c_lname(:p_source_file, :p_target_file);

select :p_source_file o_source_file, :p_target_file o_target_file from dual;
.
start slave.sql;
.

!clear

host cp &o_source_file &o_target_file
!wlctl reload 8887

Hope this helps

Rob

---

---

Subject: PL/SQL - passing variables to SQL*PLUS..... From: syam_at_rs3.tcs.tulane.edu (Syamasundaran T Unnithan) Date: 17 Mar 1997 21:49:32 GMT
Message-ID: <5gke9c$ams$1_at_rs10.tcs.tulane.edu>

Hello newsgroup subscriber,

I am using oracle V7.3.2 on IBM RS6000 in AIX 4.1.5.  

What I want to do:
In PL/SQL block ,I want to generate a 'file_name'. I want to pass 'file_name' to SQL*PLUS, then to UNIX level. At UNIX level I want to issue 'wlctl .. .. ' command also. I want to do all these dynamically, or runtime.

What I am able to do:
In PL/SQL I can generate a 'file_name'.
From SQL*PLUS level, I can go to UNIX and issue the commands.

Where do I need help?
How can I pass 'file_name' to SQL*PLUS and UNIX dynamically.

Hints...
On p.507 of ORACLE PL/SQL programming,by Steven Feuerstein ,author suggests it is possible.
Author suggests the use of DBMS_SQL, ie.Dynamic SQL. Author mentions ideas of using OPEN_CURSOR , BIND_VARIABLE, EXECUTE, CLOSE_CURSOR.... Please help me in this matter...
An example will be great help.
I am including the PL/SQL block and SQL*PLUS command files.

Thanking you in advance, this is, Syam Unnithan.

create procedure last_c_lname as

begin

   declare

      file_nam varchar2(20);
      file_name varchar2(20);
      save_last_c varchar2(1);
      len NUMBER;
      position_of_ NUMBER;

begin

   select fname into file_nam from employee

          where salary = 38000;

   DBMS_output.enable(200);
   len := LENGTH(file_nam);
   position_of_ := len-1;
   save_last_c := SUBSTR(file_nam,-1);
   if SUBSTR(file_nam,position_of_,1) = '-'

     THEN
       file_name:=SUBSTR(file_nam,1,position_of_);
       file_name:=file_name||'0'||save_last_c;

   end if;

   file_name := file_name||'.jpeg';
   DBMS_output.put_line('file_name is: '||file_name); exception

   WHEN OTHERS THEN DBMS_OUTPUT.put_line('blop'); end;
end;

get last_c_lname.sql;
/
execute last_c_lname;
.
start slave.sql;
.

!clear
!cp file_nam file_name
!wlctl reload 8887
Received on Mon Mar 17 1997 - 00:00:00 CET