Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

run pl/sql statement

Posted on 2002-03-18
1
Medium Priority
?
173 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 51

Accepted Solution

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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tech tip describes how to install the Solaris Operating System from a tape backup that was created using the Solaris flash archive utility. I have used this procedure on the Solaris 8 and 9 OS, and it shoudl also work well on the Solaris 10 rel…
Using libpcap/Jpcap to capture and send packets on Solaris version (10/11) Library used: 1.      Libpcap (http://www.tcpdump.org) Version 1.2 2.      Jpcap(http://netresearch.ics.uci.edu/kfujii/Jpcap/doc/index.html) Version 0.6 Prerequisite: 1.      GCC …
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
In a previous video, we went over how to export a DynamoDB table into Amazon S3.  In this video, we show how to load the export from S3 into a DynamoDB table.
Suggested Courses

704 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