Oracle and table variables, how well are they handled?

[Maim]

I usually write my procedures in SQL Server 2000 but I need to start writing them for Oracle and DB2. One issue that concerns me is how table variables are handled by Oracle.

For example;

DECLARE @myTable(
   f1 integer,
   f2 varchar(256),
   f3 smallint)
INSERT INTO @myTable
SELECT ... a whole bunch of records ...

SELECT * FROM @myTable

Would the above be possible in Oracle 8+?
I haven't installed Oracle on my machine yet, so I don't have any documentation to verify this.

Thanks in advance
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning." - Rich Cook

 

[wwwian]

Hi D,

Welcome to the world of ANSI standards (not)

As no-one has answered yet, the way it is done in Oracle is:

CREATE TABLE @myTable
(
f1 NUMBER, --NUMBER covers a multitude of types
f2 VARCHAR2(256),
f3 NUMBER
)
/ --execute statement!!

INSERT INTO @myTable
VALUES
(
..
);
/

/*Unfortunately you can only insert one line at a time using this method*/

SELECT * FROM @myTable
/

Good luck with Oracle

 

[Maim]

http://wwwian

thanks for your answer. Only one record at a time hunh?

I'd still need to use a cursor to traverse all the records and insert them into the table variable... or am I still thinking like a newbie? "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning." - Rich Cook

 

[Bimalaggarwal]

YOu can insert multiple recored at one tme as you had mentioned before.

INSERT INTO @myTable
SELECT ... a whole bunch of records ...