Home » SQL & PL/SQL » SQL & PL/SQL » insert in plsql table
insert in plsql table [message #227961] Fri, 30 March 2007 08:14 Go to next message
jaydeep.desai
Messages: 4
Registered: January 2007
Location: Mumbai
Junior Member

i m creating dynamically a plsql table.
PL/Sql table created successfully with more than one column but now i want to insert data in that
how can i

CREATE table V_DQUAL_MASTER
(DQ_ENTITY_NAME, DQ_DQUAL_NAME, DQ_SRNO, DQ_DQUAL_NO)
AS
SELECT dq_entity_name,DQ_DQUAL_name,DQ_SRNO,DQ_DQUAL_NO FROM dqual_master
WHERE dq_entity_name='COMPANY';

declare
type best_fit_rec IS record (COMPANY varchar2(50),ENV_COUNTRY varchar2(50),ENV_LINE varchar2(50),ENV_STATE varchar2(50),ENV_SUBLINE varchar2(50));
best_fit_rec_obj best_fit_rec;
type best_fit IS table of best_fit_rec;
lt best_fit := best_fit();
BEGIN
/* for x in ( SELECT dq_entity_name,DQ_SRNO ,1 FROM dqual_master
WHERE dq_entity_name='COMPANY'
-- AND DQ_SRNO=4
union
SELECT DQ_DQUAL_name, DQ_SRNO ,DQ_DQUAL_NO FROM dqual_master
WHERE dq_entity_name='COMPANY'
--AND DQ_SRNO=4
ORDER BY 2 desc ,3) loop
lt(lt.count+1) := */
here i want to write insert statement as selected rows goes in to respected columns.
New rows started FROM change of DQ_SRNO
NULL;
end;

please do need ful n revert me on
magicjayu@gmail.com
Re: insert in plsql table [message #227966 is a reply to message #227961] Fri, 30 March 2007 08:24 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Try to structure your posting http://www.orafaq.com/forum/t/42428/91729/

Quote:
please do need ful n revert me on

What does that mean ?
Re: insert in plsql table [message #228665 is a reply to message #227961] Tue, 03 April 2007 10:24 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Thats the problem with building your tables dynamically - no stored procedure or function can really reference them.

If I were you, I would forget all about creating tables in procedures and either use Global Temporary Tables instead, or just create the table once outside of a procedure and use it.

In my many years of oracle development, I might have come across one case where creating tables dynamically was justified, and that was before GTTs came along.

It always complicates things, leads to errors, and generally indicates that you've not spet enough time thinking about the problem.
Previous Topic: hoststring in sql*plus
Next Topic: SQL Division
Goto Forum:
  


Current Time: Sun Dec 04 02:50:39 CST 2016

Total time taken to generate the page: 0.25461 seconds