Solved

run pl/sql statement

Posted on 2002-03-18
1
171 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 50 total points
ID: 6879170
sqlplus -s user/secret @$test.sql
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
AIX Server 10 96
reinstall 1 98
What are recommended OS for exim mail server? 10 132
SSH to Solaris server (which uses NIS) 1 52
My previous tech tip, Installing the Solaris OS From the Flash Archive On a Tape (http://www.experts-exchange.com/articles/OS/Unix/Solaris/Installing-the-Solaris-OS-From-the-Flash-Archive-on-a-Tape.html), discussed installing the Solaris Operating S…
FreeBSD on EC2 FreeBSD (https://www.freebsd.org) is a robust Unix-like operating system that has been around for many years. FreeBSD is available on Amazon EC2 through Amazon Machine Images (AMIs) provided by FreeBSD developer and security office…
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.
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.

752 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