| test [message #570455] |
Fri, 09 November 2012 16:16  |
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  |
 |
Littlefoot
Messages: 21823
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.
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; |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
