[delphi-en] WTA - Multiple Resultset In Oracle or PostgreSQL (Stored Procedure)

[Niksen Harjanto]

Dear all,
Does any one know how to make Oracle or PostgreSQL give multiple
resultset from their SP like MySQL or MsSQL Server does? And how to do
that?

In simple question... How to translate this SP (I wrote in MySQL) to
Oracle SP or PostgreSQL SP.

(Just a sample)

DELIMITER ~

DROP PROCEDURE IF EXISTS testing~

CREATE PROCEDURE testing (
 pintPoId INTEGER UNSIGNED)
BEGIN
 CREATE TEMPORARY TABLE TempMaster (
   po_id INTEGER UNSIGNED NOT NULL PRIMARY KEY,
   po_code VARCHAR(10) NOT NULL,
   customer_id INTEGER NOT NULL  );

 CREATE TEMPORARY TABLE TempDetail (
   po_id INTEGER UNSIGNED NOT NULL,
   sequence INTEGER UNSIGNED NOT NULL,
   material_id INTEGER UNSIGNED NOT NULL,
   price DECIMAL(19,4) UNSIGNED NOT NULL,
   PRIMARY KEY (po_id, sequence)  );

 INSERT TempMaster
 SELECT 1, 'Test 1', 10 UNION
 SELECT 2, 'Test 2', 20 UNION
 SELECT 3, 'Test 3', 30 UNION
 SELECT 4, 'Test 4', 40;

 INSERT TempDetail
 SELECT 1, 1, 100, 1000 UNION
 SELECT 1, 2, 200, 2000 UNION
 SELECT 1, 3, 300, 3000 UNION
 SELECT 2, 1, 100, 1000 UNION
 SELECT 3, 1, 100, 1000 UNION
 SELECT 3, 2, 200, 2000 UNION
 SELECT 4, 1, 100, 1000 UNION
 SELECT 4, 2, 200, 2000 UNION
 SELECT 4, 3, 300, 3000;


 SELECT *
 FROM TempMaster
 WHERE po_id = pintPoId;

 SELECT *
 FROM TempDetail
 WHERE po_id = pintPoId
 ORDER BY sequence;
END~

DELIMITER ;


CALL testing(1);

Thank you very much....

-- 
"Sabbe Satta Bhavantu Sukhitatta"

Niksen - 782 (nomor buntut 702)
D 5470 WQ