Home » SQL & PL/SQL » SQL & PL/SQL » condition (oracle 10g)
condition [message #420655] Tue, 01 September 2009 05:14 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
ALTER TABLE EmrLicenseInfo ADD LICENSE_GROUP VARCHAR2(100) default null
/

Note: Add a condition that if column is not there then add column

hi how to do this
Re: condition [message #420656 is a reply to message #420655] Tue, 01 September 2009 05:21 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In the right way for your environment.

Regards
Michel
Re: condition [message #420658 is a reply to message #420656] Tue, 01 September 2009 05:55 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
if not exists(select * from user_tab_columns where table_name = 'EmrLicenseInfo' and column_name = 'LICENSE_GROUP' )
ALTER TABLE EmrLicenseInfo ADD LICENSE_GROUP VARCHAR(100) default null
/

throwing exception

unknown command
Re: condition [message #420660 is a reply to message #420658] Tue, 01 September 2009 05:59 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: condition [message #420661 is a reply to message #420660] Tue, 01 September 2009 06:10 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
if not exists(select * from user_tab_columns where table_name = 'EmrLicenseInfo' and column_name = 'LICENSE_GROUP' ) then
ALTER TABLE EmrLicenseInfo ADD LICENSE_GROUP VARCHAR(100) default null
/




Error starting at line 1 in command:
if not exists(select * from user_tab_columns where table_name = 'EmrLicenseInfo' and column_name = 'LICENSE_GROUP' ) then
Error report:
Unknown Command
ALTER TABLE EmrLicenseInfo succeeded.
Re: condition [message #420662 is a reply to message #420661] Tue, 01 September 2009 06:16 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 01 September 2009 12:59
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Use SQL*Plus and copy and paste your session.

Regards
Michel

You failed on ALL points but one.

[Updated on: Tue, 01 September 2009 06:16]

Report message to a moderator

Re: condition [message #420663 is a reply to message #420655] Tue, 01 September 2009 06:23 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
I think, You have to use dynamic SQL to ALTER TABLE.
Moreover, SQL statement in PL/SQL block ends with ; (as per your environment settings).
Next, IF construct ends with END IF.
Next, IF construct goes in BEGIN-END section.
Next, it does not require PL/SQL to ALTER TABLE.

regards,
Delna
Re: condition [message #420664 is a reply to message #420663] Tue, 01 September 2009 06:27 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
please help me in giving a query tried like

BEGIN 
IF (0=('Select Count(TName) From tab Where TName =' ||CHR(39) || 'EmrLicenseInfo' || CHR(39))) THEN 
EXECUTE IMMEDIATE('ALTER TABLE EmrLicenseInfo ADD LICENSE_GROUP VARCHAR(100) default null')
END IF; 
END;



but not helpful

[Updated on: Tue, 01 September 2009 06:29]

Report message to a moderator

Re: condition [message #420665 is a reply to message #420664] Tue, 01 September 2009 06:36 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
BEGIN
  IF <COND> THEN
    EXECUTE IMMEDIATE '<ALTER TABLE...>';
  END IF;
END;
/


And you didn't show outcome of your last effort.

regards,
Delna
Re: condition [message #420667 is a reply to message #420655] Tue, 01 September 2009 06:40 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
See syntax of ALTER TABLE here

regards,
Delna
Re: condition [message #420668 is a reply to message #420664] Tue, 01 September 2009 06:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why check manually what the db can (and will) check for you? Simply execute the alter table and catch the exceptions that may occur.
Re: condition [message #420669 is a reply to message #420665] Tue, 01 September 2009 06:41 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
please mention that condition too
Re: condition [message #420672 is a reply to message #420669] Tue, 01 September 2009 07:07 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Forum members are here to help you in solving your question.
Not to solve your problems.
Which condition you are talking about?
And as suggested by Frank sir, you are not required to check any condition to alter table.

regards,
Delna
Re: condition [message #420746 is a reply to message #420655] Wed, 02 September 2009 00:29 Go to previous messageGo to next message
bishtoo
Messages: 20
Registered: August 2009
Junior Member
Using a PLSQL sub program we can do this.
We can pass table name and column to be added to that procedure.

We can check column_name in user_tab_cols whether this new column already exists or not.
then, by using NDS we can alter and add new column to that table.

sqlstmt:= 'ALTER TABLE' || ' ' || tab1 || ' ' || 'ADD' || ' '|| col1 || ' ' || 'VARCHAR2(100) default null';

execute immediate sqlstmt;
Re: condition [message #420752 is a reply to message #420746] Wed, 02 September 2009 01:06 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
finally i got it


DECLARE
  v_count NUMBER(1) := 0;
BEGIN
  SELECT 1
  INTO v_count
  FROM USER_TAB_COLUMNS
  WHERE table_name = 'EmrLicenseInfo'
  AND column_name  = 'LICENSE_GROUP';
EXCEPTION
WHEN NO_DATA_FOUND THEN
  EXECUTE IMMEDIATE 'ALTER TABLE EmrLicenseInfo ADD LICENSE_GROUP VARCHAR(100) default null';
END;
/


thank you
Re: condition [message #420755 is a reply to message #420752] Wed, 02 September 2009 01:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Sorry, but this is not going to work.
You query all_tab_columns for a mixed case tablename, but you add a column to a table whose name is in all caps (as it should be)
Re: condition [message #420757 is a reply to message #420755] Wed, 02 September 2009 01:33 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition between SELECT and ALTER TABLE someone could have added the column yet.
As Frank said just ALTER and trap the exception.

Regards
Michel
Re: condition [message #420758 is a reply to message #420752] Wed, 02 September 2009 01:33 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
May be this code can help you to achieve what you are trying. But I have a doubt about the table name given.

That is given in

WHERE table_name = 'EmrLicenseInfo'


Which I see it will always send to NO_DATA_FOUND exception and
raise another exception of columns already exists if it is executed second time or first time when the column is already there.

This is due to the table name providing. The main query will never return any count because table name in dictionary views are stored in upper.
Re: condition [message #420760 is a reply to message #420758] Wed, 02 September 2009 01:39 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Ops .... I missed both.
Re: condition [message #420767 is a reply to message #420757] Wed, 02 September 2009 02:13 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel Cadot wrote on Wed, 02 September 2009 08:33
In addition between SELECT and ALTER TABLE someone could have added the column yet.

LOL
Having DDL in code generally means problems (except if this is part of installation software), but you really describe a horror-situation here...
Gives me the creeps!
Previous Topic: bulk update is not working..
Next Topic: How to populate the dynamic recordset while passing the tablename dynamically
Goto Forum:
  


Current Time: Mon Sep 26 00:54:09 CDT 2016

Total time taken to generate the page: 0.13909 seconds