Home » SQL & PL/SQL » SQL & PL/SQL » pl/sql table (Oracle 12c)
pl/sql table [message #656607] Wed, 12 October 2016 10:55 Go to next message
SamMen
Messages: 4
Registered: October 2016
Junior Member
Hi,
Is there any way I can add data into pl/sql table manually?

TYPE dept_table_tab_RECORD IS RECORD (
                   DEPTNO DEPT1.deptno%TYPE,
                   DNAME  DEPT1.DNAME%TYPE,
                   LOC DEPT1.LOC%TYPE

                   );

TYPE dept_table_tab2 IS TABLE OF dept_table_tab_RECORD; -- NESTED TABLES CREATED OF A RECORD TYPE CREATED

ldata2 dept_table_tab2;
begin

SELECT   deptno,dname,loc
     BULK COLLECT INTO ldata1
     FROM   dept1;  -- ASSIGN VALUES DIRECTLY NESTED TABLES CREATED DIRECTLY AS AN EXISTING OBJECT


--I want to do something like:
insert into ldata1
select '10','Dept no 10','TX' from dual;
end;
Re: pl/sql table [message #656608 is a reply to message #656607] Wed, 12 October 2016 11:02 Go to previous messageGo to next message
John Watson
Messages: 7212
Registered: January 2010
Location: Global Village
Senior Member
There is the syntax
INSERT INTO TABLE ( collection_expression )
where the collection can be a varray. Is that any good to you?
Re: pl/sql table [message #656611 is a reply to message #656608] Wed, 12 October 2016 12:19 Go to previous messageGo to next message
SamMen
Messages: 4
Registered: October 2016
Junior Member
Thanks...let me check on that..Thank you very much..I will get back in case I run into more issues..
Do you have any ready example?

I have just shifted from the sql server world to Oracle..
Back there, they had #temp tables, which can be used as permanent tables in session (inserts, updates, deletes, selects etc)
I was searching for an equivalent of that in Oracle.

thanks
Sam
Re: pl/sql table [message #656613 is a reply to message #656611] Wed, 12 October 2016 12:49 Go to previous messageGo to next message
BlackSwan
Messages: 25793
Registered: January 2009
Location: SoCal
Senior Member
More often than not, "temporary" table is not required in Oracle

but if you do consider then below

http://docs.oracle.com/database/121/CNCPT/tablecls.htm#CNCPT1138
Re: pl/sql table [message #656614 is a reply to message #656611] Wed, 12 October 2016 13:01 Go to previous message
John Watson
Messages: 7212
Registered: January 2010
Location: Global Village
Senior Member
Quote:
I have just shifted from the sql server world to Oracle..
Back there, they had #temp tables, which can be used as permanent tables in session (inserts, updates, deletes, selects etc)
I was searching for an equivalent of that in Oracle.
Don't do it, man! Just use subqueries. In the Oracle environment, using any sort of temporary storage is almost always a program design error with bad implications for performance and and use of resources. Trust Uncle Oracle to take of it for you.
Previous Topic: Anonymous block in sql plus
Next Topic: How to generate Query aggregation in oracle
Goto Forum:
  


Current Time: Tue Jan 16 16:19:37 CST 2018

Total time taken to generate the page: 0.01787 seconds