Solved

run pl/sql statement

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

I promised to write further about my project, and here I am.  First, I needed to setup the Primary Server.  You can read how in this article: Setup FreeBSD Server with full HDD encryption (http://www.experts-exchange.com/OS/Unix/BSD/FreeBSD/A_3660-S…
Every server (virtual or physical) needs a console: and the console can be provided through hardware directly connected, software for remote connections, local connections, through a KVM, etc. This document explains the different types of consol…
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
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

635 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