Create a gist now

Instantly share code, notes, and snippets.

@dermoton /pos.xml
Last active Oct 12, 2016

SQLcl Alias POS: Moves a table column to a particular position shifting the other columns accordingly : http://dermotoneill.blogspot.co.uk/2015/11/sqlcl-aliases-invisible-column-trick.html
<?xml version="1.0" encoding="UTF-8"?>
<aliases xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="aliases.xsd">
<alias name="pos">
<description><![CDATA[moves a table column to a particular position shifting the other columns accordingly]]></description>
<queries>
<query>
<sql><![CDATA[
SET feedback off
SET sqlformat ansiconsole
SET serveroutput ON
DECLARE
v_stmt VARCHAR2(4000);
v_current_pos NUMBER;
v_cursor SYS_REFCURSOR;
v_table_name VARCHAR2(4000);
v_column_name VARCHAR2(4000);
v_position NUMBER;
BEGIN
v_table_name := :table_name;
v_column_name := :column_name;
v_position := :position;
IF :column_name IS NULL THEN
DBMS_OUTPUT.PUT_LINE('EX SQL>pos tablename columnname 3');
OPEN v_cursor FOR SELECT column_name,column_id FROM user_tab_columns WHERE lower(table_name) = lower(:table_name) ORDER BY column_id;
DBMS_SQL.RETURN_RESULT(v_cursor);
ELSE
BEGIN
SELECT column_id
INTO v_current_pos
FROM user_tab_columns
WHERE lower(table_name) = lower(:table_name)
AND lower(column_name) = lower(:column_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('TABLE OR COLUMN NOT FOUND');
GOTO end_block;
END;
DBMS_OUTPUT.PUT_LINE(:column_name ||' current position='||v_current_pos || ' new position=' || :position);
IF v_current_pos = :position THEN
DBMS_OUTPUT.PUT_LINE(:table_name||'.'||:column_name ||' IS ALREADY AT POSITION '||:position);
ELSE
IF v_current_pos < :position THEN
v_stmt :='ALTER TABLE '||:table_name|| ' MODIFY ('||:column_name ||' INVISIBLE)'; --move this column all the way to back first
DBMS_OUTPUT.PUT_LINE(v_stmt);
EXECUTE IMMEDIATE v_stmt;
v_stmt:='ALTER TABLE '||:table_name|| ' MODIFY ('||:column_name ||' VISIBLE)'; --rest the column
DBMS_OUTPUT.PUT_LINE(v_stmt);
EXECUTE IMMEDIATE v_stmt;
END IF;
BEGIN
SELECT 'ALTER TABLE '||:table_name|| ' MODIFY ('||LISTAGG(column_name||' INVISIBLE', ',') WITHIN GROUP (ORDER BY column_id)||')' AS stmt
INTO v_stmt
FROM user_tab_columns
WHERE lower(table_name) = lower(:table_name)
AND column_id >= :position
AND lower(column_name) <> lower(:column_name)
ORDER BY column_id;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('TABLE OR COLUMN NOT FOUND OR POSITION NOT VALID');
GOTO end_block;
END;
IF( INSTR(v_stmt,'INVISIBLE') > 0 )THEN
DBMS_OUTPUT.PUT_LINE(v_stmt);
EXECUTE IMMEDIATE v_stmt;
v_stmt := REPLACE(v_stmt,'INVISIBLE','VISIBLE');
DBMS_OUTPUT.PUT_LINE(v_stmt);
EXECUTE IMMEDIATE v_stmt;
END IF;
END IF;
END IF;
<<end_block>>
NULL;
END;
/
SELECT column_name, column_id
FROM user_tab_columns
WHERE lower(table_name) = lower(:table_name)
ORDER BY column_id;
]]>
</sql>
</query>
</queries>
</alias>
</aliases>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment