How to Check If a Column Exists Before Adding It to an Existing Table in Pl/Sql

How to check if a column exists before adding it to an existing table in PL/SQL?

All the metadata about the columns in Oracle Database is accessible using one of the following views.

user_tab_cols; -- For all tables owned by the user

all_tab_cols ; -- For all tables accessible to the user

dba_tab_cols; -- For all tables in the Database.

So, if you are looking for a column like ADD_TMS in SCOTT.EMP Table and add the column only if it does not exist, the PL/SQL Code would be along these lines..

DECLARE
  v_column_exists number := 0;  
BEGIN
  Select count(*) into v_column_exists
    from user_tab_cols
    where upper(column_name) = 'ADD_TMS'
      and upper(table_name) = 'EMP';
      --and owner = 'SCOTT --*might be required if you are using all/dba views

  if (v_column_exists = 0) then
      execute immediate 'alter table emp add (ADD_TMS date)';
  end if;
end;
/

If you are planning to run this as a script (not part of a procedure), the easiest way would be to include the alter command in the script and see the errors at the end of the script, assuming you have no Begin-End for the script..

If you have file1.sql

alter table t1 add col1 date;
alter table t1 add col2 date;
alter table t1 add col3 date;

And col2 is present,when the script is run, the other two columns would be added to the table and the log would show the error saying "col2" already exists, so you should be ok.

Can I check if table or column exists before altering it?

You have to use ALL_TAB_COLUMNS:

declare 
    t NUMBER;
    w NUMBER;
begin
    -- checking if table exists
    select count(*) into t from ALL_TABLES where TABLE_NAME='TAB';

    -- checking if column does not exist
    select count(*) into w 
    from ALL_TAB_COLUMNS 
    where TABLE_NAME='TAB' AND COLUMN_NAME = 'COLUMN_1';

     if (t>0) AND (w=0) then
        EXECUTE IMMEDIATE 'alter table TAB add COLUMN_1 varchar(20)';
     end if;
end;
/

db<>fiddle demo

Check if column exists before executing Oracle

You can modify your existing query like this:

SELECT table_name AS Table_name,
       'select  count(*) from ' || table_name || ' where language!=0' as query
  FROM all_tables t
 WHERE t.owner = 'databaseName' 
   AND (t.table_name LIKE 'HH%' 
        OR t.table_name LIKE 'TT%')
   AND EXISTS (SELECT 1 
                 FROM all_tab_columns c 
                WHERE c.table_name = t.table_name
                  AND c.owner = t.owner
                  AND UPPER(c.column_name) = 'LANGUAGE')

Let me know if it helps.

Thanks
Idrees

Alter Table if column doesn't exist

Try this:

declare p_count NUMBER;

select count(1) int p_count
from ALL_TAB_COLUMNS 
where OWNER = '<SCHEMA_NAME>' 
and TABLE_NAME = '<TABLE_NAME>' 
and COLUMN_NAME = '<COLUMN_NAME>';

IF p_count = 0 THEN
    --add your column
END IF;

Eventually (depending on the rights) You can use user_tab_columns.

Can you check if a column exists and perform different actions with oracle?

"I load them into RAM from the database"

You already have the source data in the database so you should do the processing in the database. Instantiating a list of 5 million strings in local memory is not a cheap operation, especially when it's unnecessary.

Oracle supports a MERGE capability which we can use to test whether a record exists in the target table and populate a new row conditionally. Being a set operation MERGE is way more performative than single row inserts in a Java loop.

The tricky bit is uniqueness. You need to have a driving query from the source table which contains unique values (otherwise MERGE will hurl). In this example I aggregate a count of each occurrence of value1 in the source table. This gives us a set of value1 plus a figure we can use to maintain the count column on the target table.

merge into you_target_table tt
using ( select value1
               , count(*) as dup_cnt
        from your_source_table
        group by value1
      ) st
on ( st.value1 = tt.value1 )
when not matched then
   insert (id, value1, cnt) 
   values (someseq.nextval, st.value1, st.dup_cnt)
when matched then
   update
   set tt.cnt = tt.cnt + st.dup_cnt;

(I'm assuming the ID column of the target table is populated by a sequence; amend that as you require).

PL/SQL check to see if an inputted value exists in a different table

INSERT ... VALUES ... cannot have a WHERE clause. But INSERT ... SELECT ... can.

INSERT INTO order
            SELECT '&storename ',
                   '&price ',
                   '&tax',
                   '&total ',
                   '&invoicenumber '
                   FROM dual
                   WHERE EXISTS (SELECT *
                                        FROM storelocation
                                        WHERE upper(storelocation.storename) = upper('&storename '))
            
                         AND NOT EXISTS (SELECT *
                                                FROM invoicehistory 
                                                WHERE invoicehistory.invoicenumber = '&invoicenumber ');

But you should make a habit of explicitly listing the targeted columns in an INSERT. That makes sure everything goes where it's supposed to go.

(You may also check if you really want/need that trailing space in the string literals.)

Proper way of checking if row exists in table in PL/SQL block

I wouldn't push regular code into an exception block. Just check whether any rows exist that meet your condition, and proceed from there:

declare
  any_rows_found number;
begin
  select count(*)
  into   any_rows_found
  from   my_table
  where  rownum = 1 and
         ... other conditions ...

  if any_rows_found = 1 then
    ...
  else
    ...
  end if;

Oracle with Python - How to check if column already exists?

I'd suggest simply building up the create table statement instead of building the table and then altering it to add columns to it!

You can get rid of duplicates in a list by using the following code:

listWithoutDups = list(dict.fromkeys(listWithDups))

Then, you can build your statement as follows:

columns = ['"%s" varchar2(255)' % n for n in listWithoutDups]
sql = "create table SomeTableName (%s)" % ",".join(columns)
cursor.execute(sql)

You'll note I included double quotes around the column names -- that's necessary if you want to create columns that don't follow Oracle standards (include special characters, spaces, etc.) but be aware that also makes the names case sensitive and you will need to specify quotes as well when you perform any operation on the table.

Check table exist or not before create it in Oracle

As Rene also commented, it's quite uncommon to check first and then create the table.
If you want to have a running code according to your method, this will be:

declare
nCount NUMBER;
v_sql LONG;

begin
SELECT count(*) into nCount FROM dba_tables where table_name = 'EMPLOYEE';
IF(nCount <= 0)
THEN
v_sql:='
create table EMPLOYEE
(
ID NUMBER(3),
NAME VARCHAR2(30) NOT NULL
)';
execute immediate v_sql;

END IF;
end;

But I'd rather go catch on the Exception, saves you some unnecessary lines of code:

declare
v_sql LONG;
begin

v_sql:='create table EMPLOYEE
  (
  ID NUMBER(3),
  NAME VARCHAR2(30) NOT NULL
  )';
execute immediate v_sql;

EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE = -955 THEN
        NULL; -- suppresses ORA-00955 exception
      ELSE
         RAISE;
      END IF;
END; 
/

oracle string replace odd number of slash at the end of string

You can use REGEXP_SUBSTR, LENGTH and MOD functions as follows:

SELECT YOUR_COLUMN, 
       CASE WHEN MOD(LENGTH(REGEXP_SUBSTR(YOUR_COLUMN,'[\]+$')),2) = 1 
            THEN YOUR_COLUMN || '\' 
       ELSE YOUR_COLUMN 
       END AS RESULT
FROM YOUR_TABLE;

---

Related Topics