Home » Other » Training & Certification » update plsql table (Oracle 9i)
update plsql table [message #349902] Tue, 23 September 2008 07:46 Go to next message
pointers
Messages: 336
Registered: May 2008
Senior Member
I have a doubt..
how to load records into plsql table.
i have created the code. now i need to load the records into a plsql table.
DECLARE 
CURSOR rf_cr IS SELECT countryid FROM mine_country;
TYPE rf IS REF CURSOR; 
rc rf;
id NUMBER;
BEGIN
FOR a IN rf_cr LOOP
EXECUTE IMMEDIATE 'select count(cityid) from mine_city where countryid=:a' INTO id USING a.countryid; 
END LOOP;
END;


i want to load or update the id in to a plsql table.

Regards,
Pointers.
Re: update plsql table [message #349913 is a reply to message #349902] Tue, 23 September 2008 08:21 Go to previous messageGo to next message
Michel Cadot
Messages: 57616
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you post the actual need, your code is so oversimplified that it is meaningless.
For instance, you are talking about PL/SQL table but there is none in your code.

Regards
Michel
Re: update plsql table [message #349921 is a reply to message #349902] Tue, 23 September 2008 08:42 Go to previous messageGo to next message
pointers
Messages: 336
Registered: May 2008
Senior Member
Hi Michel,
I have to create an anonymous block. which reads a row of a column
and check the count for that row in another table. That count needs to be copied into a plsql table and then i want to show the output(the records in the plsql table) on screen (i should not use dbms_output as the number of records may be large) so i need to create a refcursor on the final plsql table. Now how to print that refcursor in an anonymous block.
some how i have done upto this. But how to print refcursor useed in an anonymous block.
DECLARE 
CURSOR rf_cr IS SELECT countryid FROM mine_country;
TYPE rf IS REF CURSOR; 
rc rf;
id NUMBER;
TYPE tt IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
t tt;
i NUMBER:=0;
l NUMBER:=0;
BEGIN
FOR a IN rf_cr LOOP
EXECUTE IMMEDIATE 'select count(cityid) from mine_city where countryid=:a' INTO id USING a.countryid; 
t(i):=id;
i:=i+1;
END LOOP;
l:=t.COUNT;
OPEN rc FOR SELECT * FROM t;
END;





Regards,
Pointers.
Re: update plsql table [message #349924 is a reply to message #349921] Tue, 23 September 2008 08:50 Go to previous messageGo to next message
Michel Cadot
Messages: 57616
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The only way to output from PL/SQL is dbms_output.
If you don't want all rows stop when a limit is reached.
If you want all rows then you have to output all rows.

Why not use a single SQL statement?
select countryid, coungt(cityid) nb from mine_city
where countryid in (select countryid from mine_country)
group by countryid
/


Regards
Michel
Re: update plsql table [message #349928 is a reply to message #349902] Tue, 23 September 2008 09:06 Go to previous messageGo to next message
pointers
Messages: 336
Registered: May 2008
Senior Member
Yes micheal...
I got the same answer (sql). But i was asked to do using anonymous block that to using dynamic sql. The problem has all those boundaries.


One more doubt.

Is the last statement right..? Can one open a refcursor for a plsql table like below.
open rc for select * from t;

here t is an plsql table.
I have tried by including all in a package and calling using sql * plus and i got the folowing error.

SQL> variable aa refcursor;
SQL> begin
  2  Mine_Ref.mine_proc(:aa);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> print aa;
ERROR:
ORA-24338: statement handle not executed


SP2-0625: Error printing variable "aa"
SQL> print :aa;
SP2-0625: Error printing variable "aa"


the package is as follos
CREATE OR REPLACE PACKAGE Mine_Ref AS
TYPE rc IS REF CURSOR;
PROCEDURE mine_proc(rr IN OUT rc);
END;



CREATE OR REPLACE PACKAGE BODY Mine_Ref AS
PROCEDURE mine_proc(rr IN OUT rc) IS
CURSOR rf_cr IS SELECT countryid FROM mine_country;
TYPE rf IS REF CURSOR; 
rc rf;
id NUMBER;
TYPE tt IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
t tt;
i NUMBER:=0;
l NUMBER:=0;
BEGIN
FOR a IN rf_cr LOOP
EXECUTE IMMEDIATE 'select count(cityid) from mine_city where countryid=:a' INTO id USING a.countryid; 
t(i):=id;
i:=i+1;
END LOOP;
l:=t.COUNT;
OPEN rc FOR SELECT * FROM t;
END;
END;



Re: update plsql table [message #349954 is a reply to message #349928] Tue, 23 September 2008 10:28 Go to previous message
Michel Cadot
Messages: 57616
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove all the useless stuff for what you want to show and repost.
Do you think we are garbage collector? Show some respect for those who try to help you.

Regards
Michel
Previous Topic: Need help in Oracle application Framework
Next Topic: Difference between Oracle 9i and Oracle 10g OCA developer's exam
Goto Forum:
  


Current Time: Mon Apr 21 08:28:27 CDT 2014

Total time taken to generate the page: 0.16016 seconds