Hi,
Greetings..!!
I am creating a package or Procedure to insert a new column in between using oracle 12c new feature invisible column. As our client does not want to change their application and want to handle it from database side. so I have written a code which will take 4 parameter as input (tab_name IN VARCHAR2, col_name IN VARCHAR2, v_data_type IN VARCHAR2, next_to IN VARCHAR2) respectively.
tab_name = is the name of the table in which you wan to add a new column.
col_name = is the column name that you want to create.
v_data_type = is the data type for that particular column.
next_to = is the column name next which you want to add column.
CREATE OR REPLACE PACKAGE dbms_cols
AS
PROCEDURE re_order_column(tab_name IN VARCHAR2, col_name IN VARCHAR2, v_data_type IN VARCHAR2, next_to IN VARCHAR2 );
PROCEDURE add_col(tab_name IN VARCHAR2, col_name IN VARCHAR2, v_data_type IN VARCHAR2, next_to IN VARCHAR2 );
PROCEDURE reset_column(tab_name IN VARCHAR2, col_name IN VARCHAR2, v_data_type IN VARCHAR2, next_to IN VARCHAR2 );
END dbms_cols;
/
CREATE OR REPLACE PACKAGE body dbms_cols
AS
plsql_stmt VARCHAR(1000);
v_column_name user_tab_cols.column_name%type;
--------------------------------------------------------------------
PROCEDURE re_order_column(tab_name IN VARCHAR2, col_name IN VARCHAR2, v_data_type IN VARCHAR2, next_to IN VARCHAR2 )
IS
-- v_column_id user_tab_cols.column_id%type;
-- v_column_name user_tab_cols.column_name%type;
-- v_hidden_column user_tab_cols.hidden_column%type;
CURSOR c1
IS
SELECT column_name FROM user_tab_cols WHERE table_name = upper(tab_name) AND column_id > (SELECT column_id
FROM user_tab_cols WHERE table_name = upper(tab_name) AND column_name = upper(next_to)) ORDER BY column_id;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_column_name;
EXIT
WHEN c1%notfound;
plsql_stmt := 'ALTER TABLE '||upper(tab_name)||' modify ('||v_column_name||' invisible)';
dbms_output.put_line(plsql_stmt);
--EXECUTE immediate plsql_stmt;
END LOOP;
CLOSE c1;
END;
PROCEDURE add_col(tab_name IN VARCHAR2, col_name IN VARCHAR2, v_data_type IN VARCHAR2, next_to IN VARCHAR2 )
IS
-- plsql_stmt VARCHAR(1000);
BEGIN
plsql_stmt := 'ALTER TABLE '||upper(tab_name)||' ADD ('||upper(col_name)||' '||upper(v_data_type)||')';
dbms_output.put_line(plsql_stmt);
--EXECUTE immediate plsql_stmt;
END;
PROCEDURE reset_column(tab_name IN VARCHAR2, col_name IN VARCHAR2, v_data_type IN VARCHAR2, next_to IN VARCHAR2 )
IS
-- plsql_stmt VARCHAR(1000);
-- v_column_id user_tab_cols.column_id%type;
-- v_column_name user_tab_cols.column_name%type;
-- v_hidden_column user_tab_cols.hidden_column%type;
CURSOR c1
IS
--SELECT column_name FROM user_tab_cols WHERE table_name = upper(tab_name) AND column_id > (SELECT column_id
--FROM user_tab_cols WHERE table_name = upper(tab_name) AND column_name = upper(next_to) ) ORDER BY column_id;
SELECT column_name FROM user_tab_cols WHERE table_name = upper(tab_name) AND column_id > (SELECT column_id+1
FROM user_tab_cols WHERE table_name = upper(tab_name) AND column_name = upper(next_to) ) ORDER BY column_id;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_column_name;
EXIT
WHEN c1%notfound;
plsql_stmt := 'ALTER TABLE '||upper(tab_name)||' modify ('||v_column_name||' visible)';
dbms_output.put_line(plsql_stmt);
--EXECUTE immediate plsql_stmt;
END LOOP;
CLOSE c1;
END;
END dbms_cols;
My package do the first two things that is it make the column invisible and then add the new column but at last it does make invisible column visible using the procedure. It remains hidden.
for e.g. below column D & E; where order also changed. it should be a,b,c,d,e.
COLUMN_ID COLUMN_NAME HIDDEN_COLUMN
1 A NO
2 B NO
3 C NO
- E YES
- D YES
Here is the net outcome expected
SQL> CREATE TABLE t (
a INT,
b INT,
d INT,
e INT );
Table created.
SQL>
SQL> alter table t modify d invisible;
Table altered.
SQL> alter table t modify e invisible;
Table altered.
SQL>
SQL> SELECT column_id,
2 column_name,
3 hidden_column
4 FROM user_tab_cols
5 WHERE table_name = 'T'
6 ORDER BY column_id;
COLUMN_ID COLUMN_NAME HID
---------- ------------------------------ ---
1 A NO
2 B NO
D YES
E YES
4 rows selected.
SQL> alter table t add c int;
Table altered.
SQL> SELECT column_id,
2 column_name,
3 hidden_column
4 FROM user_tab_cols
5 WHERE table_name = 'T'
6 ORDER BY column_id;
COLUMN_ID COLUMN_NAME HID
---------- ------------------------------ ---
1 A NO
2 B NO
3 C NO
E YES
D YES
5 rows selected.
SQL> alter table t modify d visible;
Table altered.
SQL> alter table t modify e visible;
Table altered.
SQL> SQL>
SQL> SELECT column_id,
2 column_name,
3 hidden_column
4 FROM user_tab_cols
5 WHERE table_name = 'T'
6 ORDER BY column_id;
COLUMN_ID COLUMN_NAME HID
---------- ------------------------------ ---
1 A NO
2 B NO
3 C NO
4 D NO
5 E NO
5 rows selected.
-Rajesh G