How can i detect the data type of a column?

0

Hi team,

I want to develop a generic duplicate field procedure so users can copy the value from the row above with one keystroke, just like F3 in Oracle Forms. Currently they have to do copy-paste and that's a lot of work.

The api_datasource.getColumnValueDT/TX/CL procedures need to know the datatype of the column in order to function correctly.

How can i detect the datatype?

By the way, api_datasource.setColumnValue does NOT need to know the data type. Probably formspider derives the type from the value-variable being set.

Kind regards, Jan Willem Vermeer

asked 29 Sep, 09:00

Jan%20Willem%20Vermeer's gravatar image

Jan Willem V...
123●1●4●36
accept rate: 0%

3 Answers:
oldestnewestmost voted
0

Here an example how this can be done... 

  PROCEDURE duplicateField
  IS
    l_focus_locator   VARCHAR2(100);
    l_component_name  VARCHAR2(100);
    l_datasource_name VARCHAR2(100);
    l_column_name     VARCHAR2(100);
    l_value_nr        NUMBER;
  BEGIN
    -- Get focused component for the datasource
    -------------------------------------------
    -- NOTE: Add element NAME to the GRID
    l_component_name := api_application.getfocusedcomponent('N');
    --
    -- Get datasource
    -----------------
    l_datasource_name := api_component.getDataSource(l_component_name);
    --
    -- This does not work for the first row
    ---------------------------------------
    l_current_row_id := api_datasource.getrownumber(l_datasource_name);
    --
    IF l_current_row_id > 1 THEN
      --
      -- Get focused component for the column
      ---------------------------------------
      -- NOTE: Add element NAME for the FIELD containing the column name
      l_focus_locator := api_application.getfocusedcomponent('Y');
      --
      -- Extract the column name
      --------------------------
      l_column_name := SUBSTR(l_focus_locator,instr(l_focus_locator,'.') + 1 );
      --
      -- Go to previous row to collect the value above
      ------------------------------------------------
      api_datasource.previousRow(l_datasource_name);
      --
      -- Get value
      ------------
      -- NOTE: We need the datatype! This could be done by adding the type as a text to the element NAME for the field
      l_value_nr := api_datasource.getColumnValueNr(l_datasource_name||'.'||l_column_name);
      --
      -- Go back to the row to copy the value to
      ------------------------------------------
      api_datasource.nextRow(l_datasource_name);
      --
      -- Paste value
      --------------
      api_datasource.setColumnValue(l_datasource_name||'.'||l_column_name,l_value_nr);
      --
      -- For some reason the focus is totally lost, so we have to request it again
      ----------------------------------------------------------------------------
      api_component.requestFocus(l_focus_locator);
    END IF;
  END duplicateField;

This works... However there are two problems.

1) We need to know the datatype for api_datasource.getColumnValueXX. As mentioned we could do a trick with the element NAME in the field, for example NAME="THIS_COLUMN_NAME#DT" for a date field.

2) We need to identify the row above in a more quick and easy manner. When doing previousRow all kinds of actions can be done, for example in my case a number of detail datasources is queried. And offcourse when going back (nextRow) this is done again. These actions make this script slow. Is there an easy way to get the rowid of the row above? I tried getPreviousCurrentRowId but that can be any row i navigated from.

link

answered 29 Sep, 13:51

Jan%20Willem%20Vermeer's gravatar image

Jan Willem V...
123●1●4●36
accept rate: 0%

0

Hi, a generic duplicate field procedure may look like this.

Only thing you have to do is to add elements NAME to the GRID and FIELDS. For the FIELDS you have to indicate the datatype by adding #TX, #NR, #DT, #CL or #BL. For example:

<panel>
  <tableLayout>
    <row>
      <cell hAlign="Full" vAlign="Full">
        <grid name="MY_GRID_NAME" dataSource="MY_DATASOURCE">
          <column name="COL_TEXT_COLUMN" headerLabel="Header text" width="90">
            <textField name="TEXT_COLUMN#TX"  column="TEXT_COLUMN"/>
          </column>
          <column name="COL_DATE_COLUMN" headerLabel="Header date" width="90">
            <dateField name="DATE_COLUMN#DT" dateFormat="DD-MON-YYYY" column="DATE_COLUMN"/>
          </column>
          <column name="COL_NUM_COLUMN" headerLabel="Header number" width="90">
            <dateField name="NUM_COLUMN#NR" column="NUM_COLUMN"/>
          </column>
        </grid>
      </cell>
    </row>
  </tableLayout>
</panel>

The procedure is:

  PROCEDURE duplicateField
  IS
    l_focus_locator      VARCHAR2(100);
    l_component_name     VARCHAR2(100);
    l_datasource_name    VARCHAR2(100);
    l_column_name        VARCHAR2(100);
    l_column_type        VARCHAR2(2);
    l_current_row_number NUMBER;
    l_current_row_id     NUMBER;
    l_row_id             NUMBER;
    l_value_nr           NUMBER;
    l_value_dt           DATE;
    l_value_tx           VARCHAR2(4000);
    l_value_cl CLOB;
    l_value_bl BLOB;
    t_rows_t api_datasource.tt_rows;
  BEGIN
    IF l_debug THEN
      api_debug.log('LIB duplicateField');
    END IF;
    --
    -- Get focused component for the datasource
    -------------------------------------------
    -- NOTE: Add element NAME to the GRID
    l_component_name := api_application.getfocusedcomponent('N');
    --
    -- Get datasource
    -----------------
    l_datasource_name := api_component.getDataSource(l_component_name);
    IF l_datasource_name IS NOT NULL THEN
      --
      -- This does not work for the first row
      ---------------------------------------
      l_current_row_number := api_datasource.getrownumber(l_datasource_name);
      IF l_current_row_number > 1 THEN
        --
        -- Get current row-id
        ---------------------
        l_current_row_id := api_datasource.getCurrentRowId(l_datasource_name);
        --
        -- Get focused component for the column
        ---------------------------------------
        -- NOTE: Add element NAME for the FIELD containing the column name
        l_focus_locator := api_application.getfocusedcomponent('Y');
        --
        -- Extract the column name
        --------------------------
        l_column_name := SUBSTR(l_focus_locator,instr(l_focus_locator,'.') + 1 );
        IF instr(l_column_name,'#TX')    > 0 THEN
          l_column_type                 := 'TX';
          l_column_name                 := REPLACE(l_column_name,'#TX');
        elsif instr(l_column_name,'#NR') > 0 THEN
          l_column_type                 := 'NR';
          l_column_name                 := REPLACE(l_column_name,'#NR');
        elsif instr(l_column_name,'#DT') > 0 THEN
          l_column_type                 := 'DT';
          l_column_name                 := REPLACE(l_column_name,'#DT');
        elsif instr(l_column_name,'#CL') > 0 THEN
          l_column_type                 := 'CL';
          l_column_name                 := REPLACE(l_column_name,'#CL');
        elsif instr(l_column_name,'#BL') > 0 THEN
          l_column_type                 := 'BL';
          l_column_name                 := REPLACE(l_column_name,'#BL');
        ELSE
          l_column_type := '?';
        END IF;
        --
        -- Get all rows
        ---------------
        IF L_COLUMN_TYPE <> '?' THEN
          api_datasource.getRows(l_datasource_name, t_rows_t);
          FOR i IN 1..t_rows_t.count
          LOOP
            --
            -- Determine row-id
            -------------------
            l_row_id := t_rows_t(i)('DS_ROWID').value_nr;
            --
            IF l_row_id = l_current_row_id THEN
              -- Current ID found - paste value
              ---------------------------------
              IF l_column_type = 'TX' THEN
                api_datasource.setColumnValue(l_datasource_name||'.'||l_column_name,l_value_tx);
              elsif l_column_type = 'NR' THEN
                api_datasource.setColumnValue(l_datasource_name||'.'||l_column_name,l_value_nr);
              elsif l_column_type = 'DT' THEN
                api_datasource.setColumnValue(l_datasource_name||'.'||l_column_name,l_value_dt);
              elsif l_column_type = 'CL' THEN
                api_datasource.setColumnValue(l_datasource_name||'.'||l_column_name,l_value_cl);
              ELSE
                api_datasource.setColumnValue(l_datasource_name||'.'||l_column_name,l_value_bl);
              END IF;
              --
              -- Quit loop
              ------------
              EXIT;
            ELSE
              -- Other row - remember value
              -----------------------------
              IF l_column_type = 'TX' THEN
                l_value_tx    := t_rows_t(i)(l_column_name).value_tx;
              elsif l_column_type = 'NR' THEN
                l_value_nr       := t_rows_t(i)(l_column_name).value_nr;
              elsif l_column_type = 'DT' THEN
                l_value_dt       := t_rows_t(i)(l_column_name).value_dt;
              elsif l_column_type = 'CL' THEN
                l_value_cl       := t_rows_t(i)(l_column_name).value_cl;
              ELSE
                l_value_bl := t_rows_t(i)(l_column_name).value_bl;
              END IF;
            END IF;
            --
          END LOOP;
          --
        END IF;
      END IF;
    END IF;
  END duplicateField;
link

answered 03 Oct, 12:56

Jan%20Willem%20Vermeer's gravatar image

Jan Willem V...
123●1●4●36
accept rate: 0%

Hi Jan Willem,

I believe there might be a slightly easier way to do this. I am at Open World all week so I cannot test anything now but did you try to use the api_datasource.getRow API? That returns all the columns for a row in the datasource.

Kind Regards,
Yalim

(03 Oct, 13:12) Yalim ♦♦

Also instead of looking at columns types, can't you just look at if any of the value fields have a not null value and if so simply populate the corresponding column in the new row with that value. Only one of the value fields will have a value.

(03 Oct, 13:14) Yalim ♦♦
0

Hi Yalim,

Indeed there is a shorter way... See below. With this the datatype additions #TX, #NR, #DT... are not needed anymore. So it's easier. For debugging purposes i had used several local variables - they have been removed now.

Only problem left is that this function also works when the users calls it in a field that may not be updated... Is it possible to detect this?

Kind regards, Jan Willem

  PROCEDURE duplicateField
  IS
    l_focus_locator        VARCHAR2(100) := NULL;
    l_component_name       VARCHAR2(100) := NULL;
    l_datasource_name      VARCHAR2(100) := NULL;
    l_column_name          VARCHAR2(100) := NULL;
    l_current_row_number   NUMBER;
    l_preceding_row_number NUMBER;
    t_rows_t api_datasource.tt_rows;
  BEGIN
    -- Get focused component for the datasource
    -------------------------------------------
    -- NOTE: Add element NAME to the GRID
    l_component_name := api_application.getfocusedcomponent('N');
    --
    -- Get datasource
    -----------------
    -- Note: format must be [PanelName].[ComponentName]
    IF LENGTH(SUBSTR(l_component_name,instr(l_component_name,'.')+1)) > 0 THEN
      l_datasource_name  := api_component.getDataSource(l_component_name);
    END IF;
    --
    IF l_datasource_name IS NOT NULL THEN
      --
      -- This does not work for the first row
      ---------------------------------------
      l_current_row_number := api_datasource.getrownumber(l_datasource_name);
      IF l_current_row_number > 1 THEN
        --
        -- Get focused component for the column
        ---------------------------------------
        -- NOTE: Add element NAME for the FIELD containing the column name
        l_focus_locator := api_application.getfocusedcomponent('Y');
        --
        -- Extract the column name
        --------------------------
        l_column_name := SUBSTR(l_focus_locator,instr(l_focus_locator,'.') + 1 );
        --
        IF l_column_name IS NOT NULL THEN
          --
          -- Get all rows
          ---------------
          api_datasource.getRows(l_datasource_name, t_rows_t);
          --
          -- Get preceding row
          --------------------
          l_preceding_row_number := l_current_row_number - 1;
          --
          -- Catch and copy value from previous row
          -----------------------------------------
          IF t_rows_t(l_preceding_row_number)(l_column_name).value_tx IS NOT NULL THEN
            api_datasource.setColumnValue(l_datasource_name||'.'||l_column_name,t_rows_t(l_preceding_row_number)(l_column_name).value_tx);
          elsif t_rows_t(l_preceding_row_number)(l_column_name).value_nr IS NOT NULL THEN
            api_datasource.setColumnValue(l_datasource_name||'.'||l_column_name,t_rows_t(l_preceding_row_number)(l_column_name).value_nr);
          elsif t_rows_t(l_preceding_row_number)(l_column_name).value_dt IS NOT NULL THEN
            api_datasource.setColumnValue(l_datasource_name||'.'||l_column_name,t_rows_t(l_preceding_row_number)(l_column_name).value_dt);
          elsif t_rows_t(l_preceding_row_number)(l_column_name).value_cl IS NOT NULL THEN
            api_datasource.setColumnValue(l_datasource_name||'.'||l_column_name,t_rows_t(l_preceding_row_number)(l_column_name).value_cl);
          elsif t_rows_t(l_preceding_row_number)(l_column_name).value_bl IS NOT NULL THEN
            api_datasource.setColumnValue(l_datasource_name||'.'||l_column_name,t_rows_t(l_preceding_row_number)(l_column_name).value_bl);
          ELSE
            NULL; -- no value in previous row
          END IF;
        END IF;
      END IF;
    END IF;
  EXCEPTION
  WHEN api_exception.e_noCurrentRow THEN
    null;
  END duplicateField;
link

answered 04 Oct, 04:16

Jan%20Willem%20Vermeer's gravatar image

Jan Willem V...
123●1●4●36
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

learn more about Markdown

Tags:

×4
×1

Asked: 29 Sep, 09:00

Seen: 68 times

Last updated: 04 Oct, 04:16

Related questions

How to duplicate a row?

Inserting records

How do i copy/duplicate the value of a column from the row above?


© Copyright Gerger 2017