OraFAQ Forum: Training & Certification

Home » Other » Training & Certification » update plsql table (Oracle 9i)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

update plsql table [message #349902]

Tue, 23 September 2008 07:46

pointers
Messages: 324
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.

Report message to a moderator

Re: update plsql table [message #349913 is a reply to message #349902]

Tue, 23 September 2008 08:21

Michel Cadot
Messages: 54167
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

Report message to a moderator

Re: update plsql table [message #349921 is a reply to message #349902]

Tue, 23 September 2008 08:42

pointers
Messages: 324
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.

Report message to a moderator

Re: update plsql table [message #349924 is a reply to message #349921]

Tue, 23 September 2008 08:50

Michel Cadot
Messages: 54167
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

Report message to a moderator

Re: update plsql table [message #349928 is a reply to message #349902]

Tue, 23 September 2008 09:06

pointers
Messages: 324
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;

Report message to a moderator

Re: update plsql table [message #349954 is a reply to message #349928]

Tue, 23 September 2008 10:28

Michel Cadot
Messages: 54167
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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue May 21 19:15:16 CDT 2013

Total time taken to generate the page: 0.16465 seconds