| insert in plsql table [message #227961] |
Fri, 30 March 2007 08:14  |
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 #228665 is a reply to message #227961] |
Tue, 03 April 2007 10:24  |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
