Solved

run pl/sql statement

Posted on 2002-03-18
1
163 Views
Last Modified: 2010-04-21
I have a file test.sql  listed below.. All I want to do is execute this from a UNIX environment.. How do I do this?


Thanks!



--      

spool xyz;
set serveroutput on size 1000000;
set echo on


DECLARE
v_lawson_id     number(09);
v_new_fica     number(09);

cursor new_999_optni
is
select fica_nbr, last_name, first_name, opt_memb_id_nu
from lawson.xoptwebrec
where company = 3
--and fica_nbr like '999-99%'
-- for Lawson Upgrade
and rtrim(ltrim(fica_nbr)) like '999-99%'
and rcom_opt_qt > 0
order by fica_nbr;

new_999_optni_rec     new_999_optni%ROWTYPE;

BEGIN

     v_lawson_id := 10142;
     v_new_fica := 883811303;

     OPEN new_999_optni;  
     LOOP
        FETCH new_999_optni INTO new_999_optni_rec;
          EXIT WHEN new_999_optni%NOTFOUND;

               
        dbms_output.put_line(new_999_optni_rec.last_name||' '||
                             new_999_optni_rec.first_name||' '||
                             new_999_optni_rec.opt_memb_id_nu||' '||
                             new_999_optni_rec.fica_nbr||' '||
                             v_lawson_id||' '||v_new_fica);

               UPDATE lawson.xoptwebrec
               SET fica_nbr = substr(v_new_fica,1,3)||'-'||
                         substr(v_new_fica, 4,2)||'-'||
                         substr(v_new_fica, 6,4),
                   opt_memb_id_nu = v_lawson_id
               WHERE fica_nbr = new_999_optni_rec.fica_nbr;
               
               v_lawson_id := v_lawson_id + 1;
               v_new_fica := v_new_fica + 1;

     END LOOP;
     CLOSE new_999_optni;        
ROLLBACK;
END;
/
spool off
0
Comment
Question by:TEALTEAL
1 Comment
 
LVL 51

Accepted Solution

by:
ahoffmann earned 50 total points
ID: 6879170
sqlplus -s user/secret @$test.sql
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Attention: This article will no longer be maintained. If you have any questions, please feel free to mail me. jgh@FreeBSD.org Please see http://www.freebsd.org/doc/en_US.ISO8859-1/articles/freebsd-update-server/ for the updated article. It is avail…
Java performance on Solaris - Managing CPUs There are various resource controls in operating system which directly/indirectly influence the performance of application. one of the most important resource controls is "CPU".   In a multithreaded…
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now