Pasting Excel-data to a forms-block
There was a thread in the Forms-forum these days dealing with Copy&Paste-problems, especially with the question if its possible to copy whole rows into a forms-block. As this is structured
data and. of course, forms doesn't know how do deal with that structured data "automatically". But there is a way to deal with that problem using standard PL/SQL-methods.
Lets say that some manager has entered data about new employees in an excel-sheet. And now the manager wants to transfer that data to the "legay-system"without having to type everything again. So
i use a tabular form based on the EMPLOYEES-table from the demo-schema.
How to do it
When you have a closer look about what gets pasted into the clipboard when you have copied some rows and columns from excel. The data is pure "textdata", each row is delimited by a CHR(10).
In each row, the different cell-values are separated by a CHR(9). So. if we know the stucture of the excel-data, its quite easy to slice it down to the differents rows and columns and transfer
the data to forms-items.
As we have to do some "special work" on pasting, i start the pasting-action using a button.
Here's the code behing the "Paste"-Button
DECLARE vcBuffer VARCHAR2(32000); vcLine VARCHAR2(32000); VARCHAR2(4000); FUNCTION FK_SPLIT(io_vcText IN OUT VARCHAR2, i_vcSplitChar IN VARCHAR2) RETURN VARCHAR2 IS iPos PLS_INTEGER:=NVL(INSTR(io_vcText, i_vcSplitChar),0); vcResult VARCHAR2(4000); BEGIN IF iPos>0 THEN vcResult:=SUBSTR(io_vcText, 1, iPos-1); io_vcText:=SUBSTR(io_vcText, iPos+1); ELSE vcResult:=io_vcText; io_vcText:=NULL; END IF; RETURN vcResult; END; BEGIN GO_ITEM('BL_CONTROL.IT_PASTE'); PASTE_REGION; GO_BLOCK('EMPLOYEES'); vcBuffer:=:BL_CONTROL.IT_PASTE; LOOP EXIT WHEN vcBuffer IS NULL; -- Next line vcLine:=FK_SPLIT(vcBuffer, CHR(10)); IF :SYSTEM.RECORD_STATUS!='NEW' THEN CREATE_RECORD; END IF; :EMPLOYEES.EMPLOYEE_ID:=TO_NUMBER(FK_SPLIT(vcLine, CHR(9))); :EMPLOYEES.FIRST_NAME:=FK_SPLIT(vcLine,CHR(9)); :EMPLOYEES.LAST_NAME:=FK_SPLIT(vcLine, CHR(9)); :EMPLOYEES.EMAIL:=FK_SPLIT(vcLine, CHR(9)); :EMPLOYEES.HIRE_DATE:=TO_DATE(FK_SPLIT(vcLine, CHR(9)), 'DD.MM.YYYY'); END LOOP; END; END;
Of course, this dummy-Item :BL_CONTROL.IT_PASTE on the canvas could be made to have a size of (0,0) in a "real" application.