Home » Other » Test » test
test [message #570455] Fri, 09 November 2012 16:16 Go to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member

create or replace procedure myproc
is
v_sql varchar2(4000);
TYPE t_Ref_Cur IS REF CURSOR;
type t_p_rec is record(x number, y number);

 TYPE t_F_Rec IS TABLE OF t_p_rec INDEX BY BINARY_INTEGER;
 
 tv t_F_Rec;


Post_Rec t_p_rec;

i number := 1;

pcur t_Ref_Cur;
begin
  
 tv.delete;
  dbms_output.put_line('1');

dbms_output.put_line('2');
v_sql := 'select x, y  from cc';
  
 dbms_output.put_line(v_sql);
    dbms_output.put_line('3');                
   OPEN pcur FOR v_Sql;
     
               LOOP
                 dbms_output.put_line('4');  
                  -- dbms_output.put_line('i: '||i);
                    FETCH pcur INTO Post_Rec;
                   dbms_output.put_line('5');  
               EXIT WHEN pcur%NOTFOUND;
             
               tv(i).x := Post_Rec.x;
               tv(i).y := post_rec.y;
            
           
                        
             
              
             --  dbms_output.put_line('6');  
               i := i +1;
               end loop;
               close pcur;
               
               dbms_output.put_line('tc.count: '||tv.count);
               
 for i in 1..tv.count loop
  insert into cc_res values(tv(i).x, tv(i).y); 
 
 end loop;               
commit;          
          

end;

Re: test [message #570466 is a reply to message #570455] Sat, 10 November 2012 01:05 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I know, you didn't ask for anyone's opinion. Anyway: you properly used [code] tags which is excellent! However, indentation and overall formatting is rather poor.

If you could spare 20 seconds of your time and have a look at How to use [code] tags and make your code easier to read?, you'd see a link which leads to Instant SQL Formatter. Basically, you should copy that code, paste it to SQL Formatter, choose Oracle/PLSQL and push the Format button. You'd then copy/paste the result (now nicely formatted) back here.

/forum/fa/10502/0/

The result:
CREATE OR replace PROCEDURE Myproc 
IS 
  v_sql    VARCHAR2(4000); 
  TYPE t_ref_cur IS ref CURSOR; 
  TYPE t_p_rec IS RECORD( 
    x NUMBER, 
    y NUMBER); 
  TYPE t_f_rec 
    IS TABLE OF T_P_REC INDEX BY BINARY_INTEGER; 
  tv       T_F_REC; 
  post_rec T_P_REC; 
  i        NUMBER := 1; 
  pcur     T_REF_CUR; 
BEGIN 
    tv.DELETE; 

    dbms_output.Put_line('1'); 

    dbms_output.Put_line('2'); 

    v_sql := 'select x, y  from cc'; 

    dbms_output.Put_line(v_sql); 

    dbms_output.Put_line('3'); 

    OPEN pcur FOR v_sql; 

    LOOP 
        dbms_output.Put_line('4'); 

        -- dbms_output.put_line('i: '||i); 
        FETCH pcur INTO post_rec; 

        dbms_output.Put_line('5'); 

        EXIT WHEN pcur%NOTFOUND; 

        Tv(i).x := post_rec.x; 

        Tv(i).y := post_rec.y; 

        --  dbms_output.put_line('6');   
        i := i + 1; 
    END LOOP; 

    CLOSE pcur; 

    dbms_output.Put_line('tc.count: ' 
                         ||tv.count); 

    FOR i IN 1..tv.count LOOP 
        INSERT INTO cc_res 
        VALUES     (Tv(i).x, 
                    Tv(i).y); 
    END LOOP; 

    COMMIT; 
END; 
Previous Topic: TEST TEST
Next Topic: test code
Goto Forum:
  


Current Time: Thu Mar 28 21:16:44 CDT 2024