CODEWITHSUNDEEP
oracle-databaseplsql
user1742716
user1742716

Reputation: 29

Function to see if column exists

i am having issue with function to check if column exist in table

Trying to see if v_col is valid column in database table all_tab_cols

create or replace function exist(v_col in varchar2) 
 Return integer is
 Res integer:= 0;
 Begin
 v_sql := 'SELECT ' ||
  'COLUMN_NAME ' ||
  'FROM ' ||
  ' all_tab_cols ' ||
  'WHERE ' ||
  'COLUMN_NAME = ''|| v_col||''';
Begin
Execute immediate v_sql;   
Res:=1;
Exception when other then null;
End;
Return (Res);
End;
BEGIN
DBMS_OUTPUT.PUT_LINE(exist('ORDER_NUMBER' )); 
END;
/

Upvotes: 0

Views: 425

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

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;
/

Upvotes: 2

Related Questions