Data Definition Automation

Problem Statement:

When a new requirement of any BI Publisher report is given, the developer creates the SQL Query for Data Definition, creates XML elements/tags for each column present in the query, gets the datatype of each column, gets the alias of each column, and adds them in the element tag. If the developer has around 40 columns in one SQL query in most of the BI Publisher Reports. Creating 40 XML elements requires a lot of manual copy-paste effort.

Solution:

1.      Insert the value of ORG_ID in the below package. Compile the below package in your database

create or replace package xml_dd_create is

PROCEDURE create_dd(query IN VARCHAR2, data_definition OUT VARCHAR2);

end xml_dd_create;

create or replace package body xml_dd_create is

PROCEDURE create_dd(query IN VARCHAR2, data_definition OUT VARCHAR2) IS

l_cursor  NUMBER := dbms_sql.open_cursor;
l_desc    dbms_sql.desc_tab2;
l_cnt     NUMBER;
v_datatype VARCHAR2(200);
v_output VARCHAR2(32767);
stmt_name VARCHAR2(100) := ‘COMPONENT’;

BEGIN
mo_global.set_policy_context(‘S’,ORG_ID);
dbms_sql.parse(l_cursor, query, dbms_sql.native);
dbms_sql.describe_columns2(l_cursor, l_cnt, l_desc);
data_definition := ‘<dataQuery>’ || CHR(10) ||
‘<sqlStatement name=”Q_’ || stmt_name || ‘”>’|| CHR(10);
data_definition := data_definition || ‘ <![CDATA[‘ || query || ‘]]>’|| CHR(10);
data_definition := data_definition || ‘</sqlStatement>’|| CHR(10)||
‘</dataQuery>’|| CHR(10);
data_definition := data_definition || ‘<dataStructure>’|| CHR(10);
data_definition := data_definition || ‘ <group name=”G_’ || stmt_name ||
‘” dataType=”VARCHAR2″ source=”Q_’ || stmt_name || ‘”>’|| CHR(10);
FOR i IN 1 .. l_cnt LOOP
v_datatype := l_desc(i).col_type;
IF v_datatype = ‘1’ THEN
v_datatype := ‘VARCHAR2’;
ELSIF v_datatype = ‘2’ THEN
v_datatype := ‘NUMBER’;
ELSIF v_datatype = ’12’ THEN
v_datatype := ‘DATE’;
ELSE
v_datatype := ‘VARCHAR2’;
END IF;
data_definition := data_definition || ‘   <element name=”‘ || l_desc(i).col_name ||
‘” dataType=”‘ || v_datatype || ‘” value=”‘ || l_desc(i)
.col_name || ‘” />’|| CHR(10);
END LOOP;
data_definition := data_definition || ‘ </group>’|| CHR(10);
data_definition := data_definition || ‘</dataStructure>’|| CHR(10);
dbms_sql.close_cursor(l_cursor);

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘Error: ‘ || SQLERRM);
dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
IF DBMS_SQL.IS_OPEN(l_cursor) THEN
DBMS_SQL.CLOSE_CURSOR(l_cursor);
END IF;
END create_dd;
end xml_dd_create;



2.      Execute the below Anonymous block:

DECLARE
dd_elements VARCHAR2(32767);
v_query VARCHAR2(32767);
BEGIN
v_query := ‘SELECT 1 id FROM DUAL’;
xml_dd_create. create_dd(v_query,dd_elements);
dbms_output.put_line(dd_elements);
END;

OUTPUT:

<dataQuery>
<sqlStatement name=”Q_COMPONENT”>
<![CDATA[SELECT 1 id FROM DUAL]]>
</sqlStatement>
</dataQuery>
<dataStructure>
<group name=”G_COMPONENT” dataType=”VARCHAR2″ source=”Q_COMPONENT”>
<element name=”id” dataType=”NUMBER” value=”id” />
</group>
</dataStructure>

Leave a reply:

Your email address will not be published.

Sliding Sidebar