Home » SQL & PL/SQL » SQL & PL/SQL » Bulk Collect (Oracle 9i)
Bulk Collect [message #334112] Tue, 15 July 2008 08:13 Go to next message
subbu_tce
Messages: 98
Registered: July 2007
Location: pune
Member
Dear All,
CREATE OR REPLACE
TYPE FDL_REC_STRINGS5 AS OBJECT
(
STRINGVAL1 VARCHAR2(2000),
STRINGVAL2 VARCHAR2(2000),
STRINGVAL3 VARCHAR2(2000),
STRINGVAL4 VARCHAR2(2000),
STRINGVA51 VARCHAR2(2000)
) ;
/
CREATE OR REPLACE
TYPE FDL_REC_STRINGS5_LST AS TABLE OF FDL_REC_STRINGS5 ;
/
CREATE TABLE fdl_cntry(id_val NUMBER , country VARCHAR2(100));

DECLARE
p_list FDL_REC_STRINGS5_LST;
BEGIN
p_list :=FDL_REC_STRINGS5_LST();
p_list.EXTEND();
p_list(p_list.COUNT):=FDL_REC_STRINGS5('Russia',NULL,NULL,NULL,NULL);
SELECT FDL_REC_STRINGS5(country,NULL,NULL,NULL,NULL) BULK COLLECT
INTO p_list FROM fdl_cntry;
DBMS_OUTPUT.PUT_LINE(p_list.COUNT);
END;

I want the first record as 'Russia' in the p_list and then i
want all data from fdl_cntry,but am getting only fdl_cntry data in the p_list.why ? What needs to be done.Kindly suggests.
Re: Bulk Collect [message #334120 is a reply to message #334112] Tue, 15 July 2008 08:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What needs to be done.Kindly suggests.

You should first 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 the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Quote:
I want the first record as 'Russia' in the p_list and then i
want all data from fdl_cntry,but am getting only fdl_cntry data in the p_list.why ?

Your code has no relation with what you want.
With your code, whatever there is the table, you have NULL in all the fields but the first one and it may or may not be "Russia" depending on what is in the table.

Regards
Michel
Re: Bulk Collect [message #334142 is a reply to message #334112] Tue, 15 July 2008 09:22 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> What needs to be done.Kindly suggests.
Get all desired rows in one query; if FDL_CNTRY table does not contain 'Russia', use UNION ALL; then appropriately sort it using ORDER BY clause.
Previous Topic: upgradation document
Next Topic: ORA-01410: invalid ROWID
Goto Forum:
  


Current Time: Mon Dec 05 08:28:42 CST 2016

Total time taken to generate the page: 0.04813 seconds