DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Need help in reading a _fmb.XML and writing the item properties to a table

user49914949919675123Sep 30 2011 — edited Oct 6 2011

We want to get all the Forms item properties to a table.
Table has this format:

Describing oracle_forms_item_list....
NAME                            Null?     Type
------------------------------- --------- -----
FORM_NAME                       NOT NULL  VARCHAR2(100)
ITEM_NAME                       NOT NULL  VARCHAR2(50)
ITEM_TYPE                       NOT NULL  VARCHAR2(50)
PROPERTY                        NOT NULL  VARCHAR2(50)
PROPERTY_VALUE                            VARCHAR2(500)

We want to get all the items (blocks, canvases, text items etc.) of D_e_p_a_r_t_m_e_n_t_s.fmb. So First we convert it to XML and we get the file D_e_p_a_r_t_m_e_n_t_s_fmb.xml which is listed below.

<?xml version = '1.0' encoding = 'UTF-8'?>
<Module version="101020002" xmlns="http://xmlns.oracle.com/Forms">
   <FormModule Name="D_E_P_A_R_T_M_E_N_T_S" ConsoleWindow="WINDOW1" DirtyInfo="true" MenuModule="DEFAULT&amp;SMARTBAR" Title="MODULE5">
      <Coordinate CharacterCellWidth="7" CoordinateSystem="Real" CharacterCellHeight="14" RealUnit="Point" DefaultFontScaling="true"/>
      <Alert Name="ALERT6" DirtyInfo="true" DefaultAlertButton="Button 2" AlertMessage="Do you want to save ???" Button2Label="No" AlertStyle="Caution" Title="Saving........................... &lt;>" Button1Label="Yes"/>
      <Block Name="DEPT" ScrollbarTabPageName="" DirtyInfo="true" QueryDataSourceName="dept" ScrollbarWidth="14" ScrollbarYPosition="39" ShowScrollbar="true" ScrollbarCanvasName="CANVAS4" ScrollbarLength="70" RecordsDisplayCount="5" ScrollbarXPosition="237">
         <Item Name="DEPTNO" DirtyInfo="true" Height="14" PromptAlign="Center" XPosition="14" Width="27" ColumnName="DEPTNO" DataType="Number" YPosition="39" PromptDisplayStyle="First Record" ItemsDisplay="0" MaximumLength="3" PromptAttachmentEdge="Top" ItemType="Text Item" TabPageName="" CanvasName="CANVAS4" Prompt="Deptno"/>
         <Item Name="DNAME" DirtyInfo="true" Height="14" PromptAlign="Center" XPosition="41" Width="101" ColumnName="DNAME" YPosition="39" Tooltip="Dep name goooes here." DataLengthSemantics="BYTE" Hint="Entter the department name" PromptDisplayStyle="First Record" ItemsDisplay="0" MaximumLength="14" PromptAttachmentEdge="Top" ItemType="Text Item" TabPageName="" CanvasName="CANVAS4" Prompt="Dname"/>
         <Item Name="LOC" DirtyInfo="true" Height="14" PromptAlign="Center" XPosition="142" Width="95" ColumnName="LOC" YPosition="39" DataLengthSemantics="BYTE" PromptDisplayStyle="First Record" ItemsDisplay="0" MaximumLength="13" PromptAttachmentEdge="Top" ItemType="Text Item" TabPageName="" CanvasName="CANVAS4" Prompt="Loc"/>
         <DataSourceColumn Type="Query" DSCType="NUMBER" DSCNochildren="false" DSCLength="0" DSCPrecision="2" DSCName="DEPTNO" DSCScale="0" DSCMandatory="false"/>
         <DataSourceColumn Type="Query" DSCType="VARCHAR2" DSCNochildren="false" DSCLength="14" DSCPrecision="0" DSCName="DNAME" DSCScale="0" DSCMandatory="false"/>
         <DataSourceColumn Type="Query" DSCType="VARCHAR2" DSCNochildren="false" DSCLength="13" DSCPrecision="0" DSCName="LOC" DSCScale="0" DSCMandatory="false"/>
      </Block>
      <Canvas Name="CANVAS4" ViewportHeight="324" DirtyInfo="true" Height="324" WindowName="WINDOW1" Width="540" ViewportWidth="540" CanvasType="Content">
         <Graphics Name="FRAME5" GraphicsText="" FrameTitleOffset="14" Height="108" VerticalMargin="14" GraphicsFontColor="" GraphicsFontSpacing="Ultradense" Width="251" GraphicsFontSize="0" GraphicsFontWeight="Ultralight" StartPromptOffset="7" FillPattern="none" GraphicsFontColorCode="0" HorizontalObjectOffset="0" EdgeBackColor="white" FrameTitle="Departments" ShowScrollbar="true" RecordsDisplayCount="5" LayoutStyle="Tabular" DirtyInfo="true" XPosition="7" Bevel="Inset" GraphicsFontStyle="0" ScrollbarWidth="14" HorizontalMargin="7" FrameTitleSpacing="7" EdgePattern="solid" YPosition="15" GraphicsType="Frame" GraphicsFontName="" LayoutDataBlockName="DEPT"/>
      </Canvas>
      <ProgramUnit Name="ASK_FROM_USER" ProgramUnitType="Function" ProgramUnitText="FUNCTION ask_from_user RETURN BOOLEAN IS&amp;#10; v_button number;&amp;#10;BEGIN&amp;#10;  v_button := SHOW_ALERT('ALERT6');&amp;#10;  &amp;#10;  if v_button = ALERT_BUTTON2 THEN&amp;#10;  	RETURN false;&amp;#10;  ELSE&amp;#10;  	RETURN TRUE;&amp;#10;  END IF;&amp;#10;END;"/>
      <Trigger Name="POST-DATABASE-COMMIT" TriggerText="/*&amp;#10;   Created by ABC de Silva&amp;#10;   &lt;&lt;&lt;&lt;&lt;..>> &amp;#10;   testing for special characters &lt; rock &amp; roll &amp;#10;*/&amp;#10;BEGIN&amp;#10;	MESSAGE('*** Records successfully &lt;&lt;&lt;> commmited to the DB. ***');&amp;#10;	PAUSE;&amp;#10;END;" DirtyInfo="true"/>
      <Window Name="WINDOW1" Height="324" Width="540"/>
   </FormModule>
</Module>

Now, we want to read this file using UTL_FILE (in a PL/SQL stored procedure) and fill the above table like this:

FORM_NAME                  ITEM_NAME   ITEM_TYPE  PROPERTY         PROPERTY_VALUE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
----------------------------------------------------------------------------------------------------------
D_e_p_a_r_t_m_e_n_t_s.fmb  ALERT6      Alert      Title            Saving........................... <>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
D_e_p_a_r_t_m_e_n_t_s.fmb  DEPTNO      Text Item  Prompt           Dname                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
D_e_p_a_r_t_m_e_n_t_s.fmb  DEPTNO      Text Item  MaximumLength    3

Thing is, I went through Google, nobody has give a complete solution. All are partial solutions.

Any help will be greatly apprectiated.

Edited by: Channa on Sep 30, 2011 6:31 AM

This post has been answered by odie_63 on Oct 6 2011

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Nov 3 2011

Added on Sep 30 2011

#pl-sql-xml-programming, #xml

11 comments

1,213 views