Home » SQL & PL/SQL » SQL & PL/SQL » add column dynamically (oracle11g, 11.1.0.7.0, aix61)
add column dynamically [message #430286] Mon, 09 November 2009 23:16 Go to next message
chandu_imax
Messages: 46
Registered: May 2008
Location: Hyderabad
Member
Hi,

We require PL/SQL block for adding a column into some
dynamic tables. I will give you detail information.
Table names are like 'CALL_DETAIL_MMYY', here MMYY varies.
MM represents months and YY represents years.
For known single table i have the code as below.
DECLARE
 x	varchar2(100); 
BEGIN
  x := 'ALTER  TABLE CALL_DETAIL_0109 add col10 varchar2(15)';
  EXECUTE IMMEDIATE x;
END;
/

Now i want code for every dynamic CALL_DETAIL_% tables.
Please help me. Thanks..!
Re: add column dynamically [message #430289 is a reply to message #430286] Mon, 09 November 2009 23:27 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
>Now i want code for every dynamic CALL_DETAIL_% tables.
How does code know where to start, where to end, & every step in between?

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: add column dynamically [message #430291 is a reply to message #430286] Mon, 09 November 2009 23:32 Go to previous messageGo to next message
baekyasi
Messages: 4
Registered: July 2009
Location: Republic of Korea
Junior Member
Hi.

An example is

x := 'ALTER TABLE CALL_DETAIL_' || to_char(SYSDATE, 'YYMM') || ' add col10 varchar2(15)';


Result X is
==> ALTER TABLE CALL_DETAIL_0911 add col10 varchar2(15)
Re: add column dynamically [message #430295 is a reply to message #430291] Mon, 09 November 2009 23:39 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
baekyasi wrote on Mon, 09 November 2009 21:32
Hi.

An example is

x := 'ALTER TABLE CALL_DETAIL_' || to_char(SYSDATE, 'YYMM') || ' add col10 varchar2(15)';


Result X is
==> ALTER TABLE CALL_DETAIL_0911 add col10 varchar2(15)


iterate through all combinations of YY & MM
Re: add column dynamically [message #430296 is a reply to message #430286] Mon, 09 November 2009 23:42 Go to previous messageGo to next message
chandu_imax
Messages: 46
Registered: May 2008
Location: Hyderabad
Member
Thank you.. But these dynamic tables are generated
long back only in one database user(when application has run).
I was tried with below, but stuck up with some problems.
Am unable to pass cursor value v_tab to the variable x.

DECLARE
 v_tab		  varchar2(100);
 x		  varchar2(100); 
 CURSOR call_details_rows IS 
 SELECT TNAME from tab 
 where tname like 'CALL_DETAIL_____';
BEGIN
  OPEN call_details_rows;
    LOOP
  	  FETCH call_details_rows 
  	  INTO v_tab;
  	  EXIT WHEN call_details_rows%NOTFOUND;
  	  x := 'ALTER  TABLE '||v_tab||' add col6 varchar2(15)';
  	  EXECUTE IMMEDIATE x;
    END LOOP;
   CLOSE call_details_rows;
END;
/

Now it was clear i think. Please suggest me.
Re: add column dynamically [message #430297 is a reply to message #430296] Mon, 09 November 2009 23:50 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
>Am unable to pass cursor value v_tab to the variable x.
How can anyone reproduce problem you claim exists?

>Now it was clear i think.
It may be clear to you but I see no error to fix/correct.
icon14.gif  Re: add column dynamically [message #430302 is a reply to message #430286] Tue, 10 November 2009 00:11 Go to previous messageGo to next message
chandu_imax
Messages: 46
Registered: May 2008
Location: Hyderabad
Member
Yes.. I got it. Thanks you all for you support.
Above Code is fine only...
Re: add column dynamically [message #430309 is a reply to message #430286] Tue, 10 November 2009 00:23 Go to previous messageGo to next message
chandu_imax
Messages: 46
Registered: May 2008
Location: Hyderabad
Member
Hi,
Now a challenging task for me is updating a value 'test' for an
existing rows of this new column col6(for all tables).
Please help me how to proceed. Razz
Re: add column dynamically [message #430312 is a reply to message #430302] Tue, 10 November 2009 00:28 Go to previous message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
chandu_imax wrote on Mon, 09 November 2009 22:11
Yes.. I got it. Thanks you all for you support.
Above Code is fine only...


proceed to where?
Previous Topic: number format issue
Next Topic: create custom function in oracle (merged)
Goto Forum:
  


Current Time: Mon Sep 26 06:00:59 CDT 2016

Total time taken to generate the page: 0.05153 seconds