This discussion is archived
5 Replies Latest reply: Jun 13, 2013 6:38 AM by CraigB RSS

pass detail records of master details relationship to other procedure or package.

sam8682 Newbie
Currently Being Moderated

Hello,

I've master details relationship. At design time it look like this

 

Ex nameTb name
ex_coltb_col
ex_coltb_col
ex_coltb_col
ex_coltb_col

 

when i run the form the result is like this

 

Ex nameTb name
okall right
Ttally
Ffinish
gGo

Now i want to pass these column values (Ex name and Tb name) another procedure or package. Or pass these values to other variables. How can i do this?

Please suggest something.

 

Thank you

Sam

  • 1. Re: pass detail records of master details relationship to other procedure or package.
    CraigB Guru
    Currently Being Moderated

    First, it would be very helpful to know what Forms version you are using.

    sam8682 wrote:

     

    Now i want to pass these column values (Ex name and Tb name) another procedure or package. Or pass these values to other variables. How can i do this?

    It depend on how you need the values passed.  Do you need them passed one row at a time or all of the rows as a group?

     

    To pass them one row at a time, your procedure signature will look something like:

    PROCEDURE <YOUR_PROC_NAME> ( P_EX_NAME VARCHAR2, P_TB_NAME VARCHAR2 )

    Your code will look something like this:

    BEGIN
        GO_BLOCK('YOUR_BLOCK');
        FIRST_RECORD;
        WHILE ( :SYSTEM.LAST_RECORD != 'TRUE' ) LOOP
           /* Call your procedure and pass values. */
           YOUR_PROC_NAME( :YOUR_BLOCK.EX_NAME, :YOUR_BLOCK.TB_NAME);
           NEXT_RECORD;
        END LOOP;
    END;

    To pass all of the rows as a group (collection) then you will need to create an object in your database or package to store the collection of rows.  This method is more complicated than looping through your block and passing the individual values so let us know if this is how you want to pass your block values to your procedure/package and I/we will give you an example of how this method works.  Also, as I mentioned in the beginning, we need to know your Forms version for this option as well because your Forms version may not support all "Collection" methods.

     

    Craig...

  • 2. Re: pass detail records of master details relationship to other procedure or package.
    sam8682 Newbie
    Currently Being Moderated

    Thanks for reply craig,

    I am using forms 6i. Now i will explain what I've and what i want to achieve.

    I've a form for importing excl to oracle with column mapping. i.e I've display excl column heading on the form.

    In front of each excel column heading I've provide a list item which contains table column heading so user can map his excl column with table column

    and according to data gets insert into the database table. (So far excel column mapping and insert these excel record in to oracle table is done/working properly).

    [CODE]

      design time condition is like this

    excel column                               table column

    ---------------------------------------------------------------------------------

    EXCL_COL1                               TAB_COL1       < ---list item

    EXCL_COL2                               TAB_COL2       < ---list item

    EXCL_COL3                               TAB_COL3        < ---list item

      .                                                 .

      .                                                 .

    Run time condition is like this

    excel column                                 table column

    -----------------------------------------------------------------------------------------------

    ROUTE                                            ROUTE

    TRANSACTION_ID                           TID

    TIME                                               TRANSACTION_TIME

      .                                                            .

      .                                                            .

    [/CODE]

    The form works perfectly. Now the problem is,

    There are 28 excel and table column headings on the form every time user will have to map the column. Its very time consuming job.

    That's why I've decide to provide template to user. e.g user first time map the excel columns with table column, save this combination as 'template 1' .

    when user runs the form next time he/she needs to just select template-1 and that template get displayed on the forms field.

    Then user will press the process button and data gets inserted into respective table

      Now I've created two tables. And i am able to save the combination of excel column and table columns in these two tables.

    [code]

    1. mst_template

    -------------------------------------------

    template_name        varchar2(100) ---primary key.

     

    2. template

    --------------------------------------------------

    template_name   varchar2(100) --- foreign key (ref. tempalte_name - mst_template)

    ex_col                   varchar2(100)

    tb_col                    varchar2(100)

     

    [/code]

     

    On existing form I've created two block 'mst_template' (master block) and template(detail block) and display these master details on another canvas.

     

    [code]

    at design time the condition is like this

    excel                                  table

    ----------------------------------------------------------------------

    x_col                                 t_col

    x_col                                 t_col

    x_col                                 t_col

       .                                       .

       .                                       .

       .                                       .

    [/code]

     

    I want to pass this this x_col, t_col values, to main canvas variables i.e EXCL_COL1, TAB_COL1, EXCL_COL2, TAB_COL2..etc.

    This is what i want to achieve. Should i go with procedure, package, collection? Please suggest something.

     

    Thanks again.

    Sam.

  • 3. Re: pass detail records of master details relationship to other procedure or package.
    CraigB Guru
    Currently Being Moderated

    So, if I understand you correctly, you want your form to default the Excel to Oracle table mapping using the template you've stored.  Is there an order in which the columns should be mapped?  My guess is that you are using TEXT_IO to read the Excel file into your form.  That being the case, your Excel file is most likely a comma delimited file (.csv).  Based on this assumption, the column order in Excel is fixed and the Oracle table column is dynamic.  I recommend you add a sequence to your template table to keep your Excel columns in the right/expected order.  Remember, the Oracle database doesn't always return a result set in the order in which the data was written to the table so it is always best to be explicit.

    Also, is Forms mapping the data and writing the data to the table?  Based on your description, my assumption is yes.  That being the case, I think I would use a Forms Program Unit (procedure) to encapsulate the logic.  Depending on how the logic of your process flows, you may even want to use a Forms Program Unit (package) to modularize the logic into logical groupings. 

    Having said that I see the process flow as something like the following:
    1.  User selects template.
    2.  User confirms template layout.
    3.  User clicks button to process data based on the template.
    4.  Forms Program Unit (FPU) reads the template to build the INSERT (column list) part of the DML.
    5.  TEXT_IO reads the Excel file and completes the VALUES part of the insert DML statement.
    6.  FPU executes the completed INSERT DML statment and starts over building the VALUE half of the statement by reading the next line of the Excel file (the INSERT part gets reused).

    Here's the problem with this method - you need to be able to execute a dynamic DML statment through Oracle Forms 6i.  For that, you will need to use the FORMS_DDL package or send the completed SQL statement to the database to executed using Dynamic SQL or Native Dynamic SQL.  Since you are already doing everything else in forms - I would lean more towards using the FORMS_DDL package.  Keep in mind, that the FORMS_DDL package executes an "implicit" COMMIT so if you have other data changes pending in your form, they will automatically be commited when you execute the FORMS_DDL built-in. If you have never used the FORMS_DDL built-in, I recommend you look at the Forms Help on this built-in to familiarize yourself with it and to see some examples of how to use it.

    Craig...

  • 4. Re: pass detail records of master details relationship to other procedure or package.
    sam8682 Newbie
    Currently Being Moderated

    Thank You so much Craig, for your detail explanation.

    Finally I achieved my expected result by following your suggestion.

    Thanks again for perfectly understanding my problem and your explanation really helps me.

     

    Regards

    Sam.

  • 5. Re: pass detail records of master details relationship to other procedure or package.
    CraigB Guru
    Currently Being Moderated

    Glad I could help Sam.

     

    Happy coding...

    Craig...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points