Home » SQL & PL/SQL » SQL & PL/SQL » Code Review (oracle 10g)
Code Review [message #427492] Thu, 22 October 2009 23:02 Go to next message
pkumar_2008
Messages: 19
Registered: November 2008
Location: usa
Junior Member

Hi All,

This is the code to archive the data from production to a staging environment..

This is working code and I guess, there is some redundancy in the code....Can anyone please review and recommend the changes.

CREATE OR REPLACE
PROCEDURE WF_ARCHIVE
IS
TYPE T_INSTRUCTION IS  TABLE OF INSTRUCTION%ROWTYPE;
  L_INSTRUCTION T_INSTRUCTION;
TYPE INSTRKEYSLIST IS  TABLE OF INSTRUCTION.INSTRUCTIONKEY%TYPE;
  INSTRKEYS INSTRKEYSLIST;
  CURSOR C1
  IS
     SELECT INSTRUCTIONKEY
       FROM BPHADMIN.INSTRUCTION
      WHERE EXECUTIONDATEIDX <= TRUNC(SYSDATE-FLOOR(45));
  
  ERROR_CODE NUMBER:=0;
  ERROR_MSG  VARCHAR2(200);
BEGIN
  OPEN C1;
  FETCH C1 BULK COLLECT INTO INSTRKEYS;
  
  FOR I IN INSTRKEYS.FIRST .. INSTRKEYS.LAST
  LOOP
     SELECT * BULK COLLECT
       INTO L_INSTRUCTION
       FROM BPHADMIN.INSTRUCTION
      WHERE TRUNC(EXECUTIONDATEIDX) < = (SYSDATE - 45)
    AND INSTRUCTIONKEY              = INSTRKEYS(I) ;
    
    FORALL I IN L_INSTRUCTION.FIRST .. L_INSTRUCTION.LAST
     INSERT INTO INSTRUCTION VALUES L_INSTRUCTION
      (I
      );
  END LOOP;
  COMMIT;
  CLOSE C1;
END ;


Thanks


[Mod-edit: Frank added [code]-tags to improve readability]

[Updated on: Fri, 23 October 2009 00:05] by Moderator

Report message to a moderator

Re: Code Review [message #427498 is a reply to message #427492] Thu, 22 October 2009 23:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: Code Review [message #427508 is a reply to message #427492] Fri, 23 October 2009 00:14 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What is the result of FLOOR(45) ?
You don't use the your declared ERROR* variables

You can replace the whole beast with a single insert.

Also, it seems you did not actually test the results of your code. Do you know what the contents of I are?
Check this:
  1  declare
  2    cursor c_emp is select * from emp;
  3    type t_emp is table of c_emp%rowtype;
  4    r_emp t_emp;
  5  begin
  6    open c_emp;
  7    fetch c_emp bulk collect into r_emp;
  8    for i in r_emp.first..r_emp.last
  9    loop
 10      dbms_output.put_line('i is '||i);
 11    end loop;
 12* end;
SQL> /
i is 1
i is 2
i is 3
i is 4
i is 5
i is 6
i is 7
i is 8
i is 9
i is 10
i is 11
i is 12
i is 13
i is 14

PL/SQL procedure successfully completed.


So, basically you are inserting a number of numbers, not data from bphadmin.instruction!
If the INSTRUCTION table you are inserting into has the same structure as the BPHADMIN.INSTRUCTION table you are selecting from, this will even error out (insufficient values)
Previous Topic: access previous records
Next Topic: Problem with a select distinct
Goto Forum:
  


Current Time: Wed Dec 07 23:58:14 CST 2016

Total time taken to generate the page: 0.17267 seconds