Dynamic Fetching records when Table is created dynamically [message #300375] |
Fri, 15 February 2008 04:18  |
monasingh
Messages: 229 Registered: May 2006 Location: Mumbai
|
Senior Member |

|
|
Hi,
I have to dynamically create Table and columns.
Please suggest me a way to dynamically create table as well as fetch the records going through some LOOP.
I am trying to do this using TYPE.
But I am facing problem in accessing the records in LOOP using rec_no.FIRST and rec_no.LAST.
Please suggest me something as I have not much idea on this.
Declare
TYPE rec IS RECORD(first_name employee.first_name%TYPE,
last_name employee.last_name%TYPE,
salary employee.salary%TYPE);
rec_no rec;
begin
select first_name,last_name,salary into rec_no from employee;
DBMS_OUTPUT.PUT_LINE('first subscript is ' || rec_no.first);
--DBMS_OUTPUT.PUT_LINE('last subscript is ' || rec_no.LAST);
end;
Thanks,
Mona
|
|
|
|
Re: Dynamic Fetching records when Table is created dynamically [message #300382 is a reply to message #300375] |
Fri, 15 February 2008 04:42   |
dhananjay
Messages: 635 Registered: March 2002 Location: Mumbai
|
Senior Member |
|
|
Quote: | But I am facing problem in accessing the records in LOOP using rec_no.FIRST and rec_no.LAST
|
you have declare rec_no as RECORD.and you cannot use FIRST and LAST with a RECORD.
you could do something like :
Declare
TYPE rec IS RECORD(first_name employee.first_name%TYPE,
last_name employee.last_name%TYPE,
salary employee.salary%TYPE);
Type rec_ty is table of rec index by binary_integer;
rec_tab rec_ty;
begin
select first_name,last_name,salary bulk collect into rec_tab....
it's not a good practice of creating tables on a fly.
regards
|
|
|
|
|
|
Re: Dynamic Fetching records when Table is created dynamically [message #300420 is a reply to message #300408] |
Fri, 15 February 2008 06:20   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
Search for dbms_sql package in oracle reference manual. Hopefully that should give you a clue how to do it.
My own personal experience where I have been forced to use dynamic sql. But the only good point is we know the portion of the table_name but we don't know what it will be called until runtime.
I have had the same issue (System will not know the name of the table which I am going to query) in the current system I am working. I completely agree with everybody it is a BAD practice but sometimes certain systems they have some weird conventions by which they create table on the fly every month and they tend to add the month and year along with the table_name. So I have been left with only one option dynamic sql. I would like to know if there is any other alternative to avoid it and I will be more than happy to change my processing because I personally against the process of creating and dropping tables on the fly. So in the end we tried to do avoid sql injection to make the system scalable. Since these are existing systems running for years and years without any issues, nobody is willing to or wanting to change it because ... you know the reason.
Regards
Raj
P.S : @Michael, I didn't see you reply as I was busy in drafting this relatively long piece of message.
[Updated on: Fri, 15 February 2008 06:21] Report message to a moderator
|
|
|
Re: Dynamic Fetching records when Table is created dynamically [message #300432 is a reply to message #300420] |
Fri, 15 February 2008 06:52   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
S.Rajaram wrote on Fri, 15 February 2008 13:20 | ... by which they create table on the fly every month and they tend to add the month and year along with the table_name. So I have been left with only one option dynamic sql.
|
This is why I asked. This might be solved by creating a view over all the <table_YYYYMM> tables. This view could have an extra column containing the YYYYMM of the source table.
Since the new YYYYMM-table has to be created at some point, you can at the same time recreate the view.
|
|
|
|
|
Re: Dynamic Fetching records when Table is created dynamically [message #300501 is a reply to message #300375] |
Fri, 15 February 2008 12:36   |
coleing
Messages: 213 Registered: February 2008
|
Senior Member |
|
|
Also,
Execute immediate can also do dynamic PLSQL as well as SQL.
example:-
-- Build PLSQL Block
v_sql := '
begin
for cur in (select * from #tab1#) loop
do stuff...
end loop;
exception
stuff;
end;
';
-- Substitute variables into PLSQL
v_sql := replace(v_sql,'#tab1#,v_table_name);
execute immediate(v_sql);
|
|
|
Re: Dynamic Fetching records when Table is created dynamically [message #300502 is a reply to message #300484] |
Fri, 15 February 2008 12:37   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
The "system" doesn't select a table.
Try to find the underlying reason why the table you have to select from is not known.
Do you at least know the column-names and their datatypes?
For a quick resolution of the problem, use Michels advise. For a solution in the long run, try to find the actual problem.
There may be one, or it may be that there is a funded reason why the table is dynamic. At least you made some effort to make sure then.
|
|
|
|
|
|
|