run pl/sql statement

Posted on 2002-03-18
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?



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

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

cursor new_999_optni
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;


     v_lawson_id := 10142;
     v_new_fica := 883811303;

     OPEN new_999_optni;  
        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;        
spool off
Question by:TEALTEAL
1 Comment
LVL 51

Accepted Solution

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

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. Please see 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