APPS-ORACLE.RU

Скрипт вывода столбцов таблицы в XML шаблон данных( BI Publisher Data Template )

declare
  p_table_owner varchar2(30) := 'APPLSYS';
  p_table_name  varchar2(30) := 'FND_USER';
begin
  for i_cur in (
                  select t.column_name
                        ,t.data_type
                        ,max(length(t.column_name)) over() 
                         - length(t.column_name) as cur_gap_length
                  from all_tab_columns t
                  where 1=1
                        and t.owner = p_table_owner
                        and t.table_name = p_table_name
                  order by t.column_id
                )
   loop
     dbms_output.put_line('<element name="' ||i_cur.column_name
                          ||'"'||rpad(' ',i_cur.cur_gap_length)||' value="'||i_cur.column_name
                          ||'" />');
   end loop;
end;

-- Пример вывода
<element name="USER_ID"                       value="USER_ID" />
<element name="USER_NAME"                     value="USER_NAME" />
<element name="LAST_UPDATE_DATE"              value="LAST_UPDATE_DATE" />
<element name="LAST_UPDATED_BY"               value="LAST_UPDATED_BY" />
<element name="CREATION_DATE"                 value="CREATION_DATE" />
<element name="CREATED_BY"                    value="CREATED_BY" />
<element name="LAST_UPDATE_LOGIN"             value="LAST_UPDATE_LOGIN" />
<element name="ENCRYPTED_FOUNDATION_PASSWORD" value="ENCRYPTED_FOUNDATION_PASSWORD" />
<element name="ENCRYPTED_USER_PASSWORD"       value="ENCRYPTED_USER_PASSWORD" />
<element name="SESSION_NUMBER"                value="SESSION_NUMBER" />
<element name="START_DATE"                    value="START_DATE" />
<element name="END_DATE"                      value="END_DATE" />
<element name="DESCRIPTION"                   value="DESCRIPTION" />
<element name="LAST_LOGON_DATE"               value="LAST_LOGON_DATE" />
<element name="PASSWORD_DATE"                 value="PASSWORD_DATE" />
<element name="PASSWORD_ACCESSES_LEFT"        value="PASSWORD_ACCESSES_LEFT" />
<element name="PASSWORD_LIFESPAN_ACCESSES"    value="PASSWORD_LIFESPAN_ACCESSES" />
<element name="PASSWORD_LIFESPAN_DAYS"        value="PASSWORD_LIFESPAN_DAYS" />
<element name="EMPLOYEE_ID"                   value="EMPLOYEE_ID" />
<element name="EMAIL_ADDRESS"                 value="EMAIL_ADDRESS" />
<element name="FAX"                           value="FAX" />
<element name="CUSTOMER_ID"                   value="CUSTOMER_ID" />
<element name="SUPPLIER_ID"                   value="SUPPLIER_ID" />
<element name="WEB_PASSWORD"                  value="WEB_PASSWORD" />
<element name="USER_GUID"                     value="USER_GUID" />
<element name="GCN_CODE_COMBINATION_ID"       value="GCN_CODE_COMBINATION_ID" />
<element name="PERSON_PARTY_ID"               value="PERSON_PARTY_ID" />