Check If Column Exists Before Executing Oracle

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

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

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.

Function to see if column exists

There's no need of dynamic SQL

CREATE OR REPLACE FUNCTION exist (
v_col IN VARCHAR2
)
RETURN integer is
res integer;
BEGIN

select 1 INTO res from all_tab_cols where
column_name = v_col and rownum = 1; --can have >1 columns with same name
return res;
EXCEPTION when no_data_found THEN
res := 0;
RETURN res;
END;
/

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).

Check if both column and table exist and run queries based on the result

You can use:

DECLARE
nCount NUMBER;
BEGIN
SELECT COUNT(*)
INTO nCount
FROM USER_TAB_COLS
WHERE TABLE_NAME = 'TESTTABLE' AND
COLUMN_NAME = 'TESTCOLUMN';

IF nCount > 0 THEN
-- Run a SQL which returns the result
ELSE
SELECT COUNT(*)
FROM USER_TABLES
WHERE TABLE_NAME = 'TESTTABLE';

IF nCount > 0 THEN
Run a different sql which also returns the result
ELSE
print some defined string
END;

You'll have to add code to run whatever SQL you're trying to run, and to print whatever message you need.

Best of luck.

Fast way to determine if an field exist in a ORACLE table

the primary key of all_tab_columns is owner, table_name, column_name so looking for a particular owner will be faster (or use user_tab_columns).

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.



Related Topics



Leave a reply



Submit