Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic Fetching records when Table is created dynamically
Dynamic Fetching records when Table is created dynamically [message #300375] Fri, 15 February 2008 04:18 Go to next message
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 #300379 is a reply to message #300375] Fri, 15 February 2008 04:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Do you really write your code like this without any indentation?
Or did you pick up a code somewhere (anywhere) and post it?
What is the relation between your question and your code?

Quote:
Please suggest me a way to dynamically create table as well as fetch the records going through some LOOP.

Nothing dynamic in your code
Nothing relative to create table in your code
No loop in your code.

Make a little effort to post something consistent.

By the way, you don't dynamically create tables in Oracle.

Regards
Michel

Re: Dynamic Fetching records when Table is created dynamically [message #300382 is a reply to message #300375] Fri, 15 February 2008 04:42 Go to previous messageGo to next message
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 #300408 is a reply to message #300382] Fri, 15 February 2008 05:46 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

I think I had not phrased my problem clearly.
My approach may be worng but here is what I need precisely.
I have to do some manipulations of data present in some tables which is already present at runtime.
I will know the name of the table in runtime only.
Then I will need to loop through the records and do some process.
Please suggest me a way to do this.
Re: Dynamic Fetching records when Table is created dynamically [message #300410 is a reply to message #300408] Fri, 15 February 2008 05:50 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Can you explain why you only know the tablename at runtime?
Re: Dynamic Fetching records when Table is created dynamically [message #300419 is a reply to message #300408] Fri, 15 February 2008 06:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Keywords: CURSOR (OPEN/FETCH/CLOSE), EXECUTE IMMEDIATE
But first answer Frank question.

Regards
Michel
Re: Dynamic Fetching records when Table is created dynamically [message #300420 is a reply to message #300408] Fri, 15 February 2008 06:20 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #300484 is a reply to message #300419] Fri, 15 February 2008 11:40 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi Frank,
As Raj said I too have a similar situation where system will select the table name and based on table name selection I have to do specfic manipulation with corresponding table's record.
But theprolem I am facing is how to dynamically use the table name ...may be in a variable to use in Cursor to again loop through the records to do the required manipulation.

I tried using
Execute Immediate 'Cursor C_rec is select vcol_1 from vtab1'


But this is not working.
The Execute Immediate works for a select statement but not working for a Cursor.

Please advice as I have not used Execute Immediate before but only to create table i pl/sql block.

Please help me on this. If possible please give me an example to fetch records from a cursor which is created from a table which is given in a varible at runtime.

Thanks,
Mona
Re: Dynamic Fetching records when Table is created dynamically [message #300487 is a reply to message #300484] Fri, 15 February 2008 11:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
c cursor;
open c for <your statement>

Regards
Michel
Re: Dynamic Fetching records when Table is created dynamically [message #300501 is a reply to message #300375] Fri, 15 February 2008 12:36 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: Dynamic Fetching records when Table is created dynamically [message #300702 is a reply to message #300501] Sun, 17 February 2008 22:07 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi coleing,
I will know the name of the tablename in runtime only. So just to see how your script runs I am passing value of table name in the variable 'v_table_name', as :-
create table t1(
name varchar2(100),
salary number);

insert into t1 values('goldey','50000');
insert into t1 values('soumyaa','3000000');
insert into t1 values('sonali','200000');


   -- Build PLSQL Block
   declare
   v_table_name varchar2(100) := 't1';
   v_sql := '
   begin
     for cur in (select * from #tab1#) loop
      dbms_output.put_line(cur.name);
     end loop;
   end;
  ';
  -- Substitute variables into PLSQL
  v_sql := replace(v_sql,#tab1#,v_table_name);
 execute immediate(v_sql);
 


I will be passing the name of the tablename through a procedure of function. But now just wanted to see how to work with your script if the table name is given in a variable.

This is giving some errors.
Please look into this ....I think I can get a way if is scripted correctly and I am doing something wrong here.

Thanks for your help
Mona
Re: Dynamic Fetching records when Table is created dynamically [message #300754 is a reply to message #300702] Mon, 18 February 2008 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
It is not the one that gives the longest answer that gives the best one.

Regards
Michel
Re: Dynamic Fetching records when Table is created dynamically [message #300835 is a reply to message #300375] Mon, 18 February 2008 04:06 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi Michel,

I looked into your answer
c cursor;
open c for <your statement>

But my problem is that I do not know the name of the table but only on runtime.
So <your statement> will need the name of the table.....
Please advice.


Thanks,
Mona
Re: Dynamic Fetching records when Table is created dynamically [message #300837 is a reply to message #300835] Mon, 18 February 2008 04:11 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
<your statement> can be a dynamic string, a string variable.

C must then be of "ref cursor" type (for instance, sys_refcursor).

Regards
Michel

[Updated on: Mon, 18 February 2008 04:13]

Report message to a moderator

Previous Topic: Comparsion between column names of 2 tables
Next Topic: Problem
Goto Forum:
  


Current Time: Tue Feb 11 13:42:32 CST 2025