SQL Select statement - default field value when field doesn't exist

Question

I'm wondering whether there is a way to display some default value for select statement where queries field doesn't exist.

For instance,

SELECT t.name, t.type, t.price, t.brand FROM some_table t;

If the 'brand' field doesn't exist in the some_table I would like this statement to display 'brand' as 'not available'.

Eventually I want to create a view from that select statement. I'm just curious whether there is a way to do that in PL/SQL.

EDIT: To avoid confusion, I want the statement to compile and work when the 'brand' column doesn't exist in the table.

Answer 1

I have just seen the question above. It seems very weird design or requirement. I am posting a code snippet which may suffice your problem but ideally this should not be like this.

--So i get chance to look into the question asked simple way to get a workaround for your problem is to fetch out the columns list from table
var p_lst refcursor;
SET serveroutput ON;
DECLARE
  lv_sql LONG;
  lv_tab_name VARCHAR2(100);
  lv_col_chk  VARCHAR2(1000 CHAR);
BEGIN
  FOR I IN
  (SELECT * FROM ALL_TAB_COLUMNS WHERE OWNER = 'AVROY' AND TABLE_NAME = 'EMP'
  )
  LOOP
    lv_tab_name:=I.TABLE_NAME;
    lv_sql     :=lv_sql||','||i.column_name;
  END LOOP;
  lv_sql:='SELECT '||SUBSTR(lv_sql,2,LENGTH(lv_sql));
  dbms_output.put_line(lv_sql);
  lv_col_chk:=INSTR(UPPER(lv_sql),'BRAND',1);
  dbms_output.put_line(lv_col_chk);
  IF lv_col_chk = 0 THEN
    lv_sql     :=SUBSTR(lv_sql,1,LENGTH(lv_sql))||', ''Not_available'' as Brand_col  FROM '||lv_tab_name;
    dbms_output.put_line(LV_SQL);
  ELSE
    lv_sql:=SUBSTR(lv_sql,1,LENGTH(lv_sql))||' FROM '||lv_tab_name;
    dbms_output.put_line(LV_SQL);
  END IF;
  OPEN :p_lst FOR lv_sql;
END;
PRINT p_lst;