running an sql script from sqlplus command line

Dev Shed Forums Sponsor:

SlickEdit: Code in over 40 languages across 7 platforms. SlickEdit’s unmatched power, speed, and flexibility allows even the most accomplished developers to write better code faster. Download a free trial today!

January 27th, 2005, 08:29 AM

fkoskotas

Contributing User

Join Date: Oct 2004

Posts: 56

Time spent in forums: 4 h 26 m 16 sec
Reputation Power: 4

running an sql script from sqlplus command line

I have a question, I'm trying to run this script from sqlplus command line and it's not running. What am I doing wrong?

l06db > run charge_back_loop2.sql
1 DECLARE
2 v_Counter BINARY_INTEGER :=1;
3 v_Begin NUMBER :=1;
4 v_End NUMBER :=0;
5 BEGIN
6 WHILE v_Counter<=160000 LOOP
7 INSERT into CHARGE_BACK_ARCH
8 (select * from charge_back where CHRGBK_ID in
9 (select CHRGBK_ID from chrgbk_index where ASSY_PART_NUM in
10 (select distinct prod_id from product where PRIMARY_FAMILY in
11 (select family_id from PRODUCT_ARCHIVE_TMP where TIME<sysdate-180))
12 and CHRGBK_ID!='F00001')
13 and ROWNUM <= v_End);
14 delete from charge_back where CHRGBK_ID in
15 (select CHRGBK_ID from chrgbk_index where ASSY_PART_NUM in
16 (select distinct prod_id from product where PRIMARY_FAMILY in
17 (select family_id from PRODUCT_ARCHIVE_TMP where TIME<sysdate-180))
18 and CHRGBK_ID!='F00001'
19 and ROWNUM <= v_END);
20 commit;
21 dbms_output.put_line('v_begin initiallly is ' || v_Begin);
22 dbms_output.put_line('v_end is initially ' || v_End);
23 v_Counter :=v_Counter+1;
24 dbms_output.put_line('Counter is ' || v_Counter);
25 v_Begin :=v_Begin+50;
26 dbms_output.put_line('v_begins is ' || v_Begin);
27 v_End :=v_End+50;
28 dbms_output.put_line('v_end is ' || v_End);
29 END LOOP;
30* END;
DECLARE
*
ERROR at line 1:
ORA-06550: line 7, column 14:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 7, column 2:
PL/SQL: SQL Statement ignored

January 27th, 2005, 04:24 PM

jim mcnamara

......@.........

Join Date: Jun 2004

Posts: 1,307

Time spent in forums: 1 Week 3 Days 4 h 28 m 57 sec
Reputation Power: 48

Please use code tags - this is almost unreadable. If you code really looks like this, you will never be able to maintain it.

You should use joins, not select where in (select....). It will execute a lot faster
V_end is zero so you will not get any rows inserted or deleted.

Assuming you want to move data from one table to an archive table then delete the original try something like this:

Code:

DECLARE
   v_Counter BINARY_INTEGER :=1;
   v_Begin NUMBER :=1;
   v_End NUMBER :=0;    
   cback charge_back.chrgbk_id%TYPE;
   CURSOR main IS
   SELECT chrgbk_id 
   FROM charge_back, chrgbck_index, product, product_archive_tmp
   WHERE 
        charge_back.chrgbk_id!='F00001' AND         
        charge_back.chrgbk_id = chrgbck_index.chrgbck_id AND
        assy_part_num = prod_id AND
        primary_family=family_id AND
        TIME < SYSDATE -180;        
   
BEGIN
   FOR x IN main 
   LOOP
         cback:=x.chrgbk_id;
         INSERT INTO charg_back_arch
         (SELECT * FROM charge_back WHERE chrgbk_id= cback);
         DELETE FROM charge_back WHERE chrgbk_id= cback); 
         /* remove this junk
         dbms_output.put_line('v_begin initiallly is ' || v_Begin);
         dbms_output.put_line('v_end is initially ' || v_End);
         v_Counter :=v_Counter+1;
         dbms_output.put_line('Counter is ' || v_Counter);
         v_Begin :=v_Begin+50;
         dbms_output.put_line('v_begins is ' || v_Begin);
         v_End :=v_End+50;
         dbms_output.put_line('v_end is ' || v_End); */
    END LOOP;
    /* uncomment this commit; when you think this script runs correctly*/
    ROLLBACK;  /* comment this ROLLBACK and use the commit when this works */
 END;
 /

January 28th, 2005, 06:56 AM

fkoskotas

Contributing User

Join Date: Oct 2004

Posts: 56

Time spent in forums: 4 h 26 m 16 sec
Reputation Power: 4

Quote:

Originally Posted by jim mcnamara

Code:

DECLARE
   v_Counter BINARY_INTEGER :=1;
   v_Begin NUMBER :=1;
   v_End NUMBER :=0;    
   cback charge_back.chrgbk_id%TYPE;
   CURSOR main IS
   SELECT chrgbk_id 
   FROM charge_back, chrgbck_index, product, product_archive_tmp
   WHERE 
        charge_back.chrgbk_id!='F00001' AND         
        charge_back.chrgbk_id = chrgbck_index.chrgbck_id AND
        assy_part_num = prod_id AND
        primary_family=family_id AND
        TIME < SYSDATE -180;        
   
BEGIN
   FOR x IN main 
   LOOP
         cback:=x.chrgbk_id;
         INSERT INTO charg_back_arch
         (SELECT * FROM charge_back WHERE chrgbk_id= cback);
         DELETE FROM charge_back WHERE chrgbk_id= cback); 
         /* remove this junk
         dbms_output.put_line('v_begin initiallly is ' || v_Begin);
         dbms_output.put_line('v_end is initially ' || v_End);
         v_Counter :=v_Counter+1;
         dbms_output.put_line('Counter is ' || v_Counter);
         v_Begin :=v_Begin+50;
         dbms_output.put_line('v_begins is ' || v_Begin);
         v_End :=v_End+50;
         dbms_output.put_line('v_end is ' || v_End); */
    END LOOP;
    /* uncomment this commit; when you think this script runs correctly*/
    ROLLBACK;  /* comment this ROLLBACK and use the commit when this works */
 END;
 /

Thanks! But how do i run this script from the sqlplus command line??

January 28th, 2005, 11:35 AM

jim mcnamara

......@.........

Join Date: Jun 2004

Posts: 1,307

Time spent in forums: 1 Week 3 Days 4 h 28 m 57 sec
Reputation Power: 48

SQLPLUS > @scriptfilename

Use the @ character

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: