1 2 Previous Next 16 Replies Latest reply: Apr 24, 2013 12:14 PM by Andreas Weiden RSS

    map excel columns on oracle forms and insert it into database

    sam8682
      Hello,

      I have task. I am using oracle forms 6i. I want to import excel data to oracle forms(its common task using ole2 package). But this time I want to map the columns i.e my database table having 5 columns. and the excel file is having 2 or 3 columns then i suppose to map those columns and accordingly insert it to my table.
      So far i have import column heading of excel to oracle forms, then i've provide list item for mapping each column
      so that user can map excel column to database columns. Now I am confuse how to write the code so that selected columns should get inserted into database.

      somebody please help me for the same.

      Thank you.
        • 1. Re: map excel columns on oracle forms and insert it into database
          tony.g
          Hi

          I think that we are a bit confused with your description of the problem.

          Can you state again more clearly exactly what it is you are wanting to achieve.

          regards
          Tony
          • 2. Re: map excel columns on oracle forms and insert it into database
            Andreas Weiden
            I think you will have to use dynamic sql to implement that. I would go the following approach:
            -Built a record with attribute1 - attributeXX
            -Build up an associative array with on that record.
            -Build up a list with the column-names for each attribute.
            -Pass the data to a db-procedure.
            -Build an INSERT-Statement as a string, execute it using EXECUTE IMMEDIATE, passing the array-data in the using clause.
            • 3. Re: map excel columns on oracle forms and insert it into database
              sam8682
              Thanks for reply Tony
              I have table with columns id, name, location, address, plan
              in those columns i need to insert records form excel.
              user having a excel with 3 columns col1, col2, col3.
              on the form i've fetch column headers of excel and in front of that i've provide database column list , so user can match excel column with database column. e.g

              COL1 --> list value of database column
              COL2 -->     list value of database column
              COL3 -->     list value of database column

              Once user map those column i want to insert those values into my database table (table with columns id, name, location, address, plan). and i am confuse about this code.

              Thanks again.
              Sam
              • 4. Re: map excel columns on oracle forms and insert it into database
                sam8682
                Thanks Andreas Weiden

                i will try accordingly get back to u soon.

                Thanks
                Sam

                Edited by: sam8682 on Apr 12, 2013 10:34 AM
                • 5. Re: map excel columns on oracle forms and insert it into database
                  sam8682
                  Hello Andreas Weiden,
                  sorry for late reply
                  as you suggest, i've done first three steps.
                  for step no 4 i.e- Pass the data to a db-procedure., should i use ole2 package for that?
                  or should i write some procedure. (This might be simple logic question, but i am not able to judge)

                  Thanks
                  Sam
                  • 6. Re: map excel columns on oracle forms and insert it into database
                    Andreas Weiden
                    You need to read the data somehow from excel, e.g. using ole2 and you have to pass it to a db-procedure afterwards.
                    • 7. Re: map excel columns on oracle forms and insert it into database
                      sam8682
                      Thanks for reply

                      Andreas Weiden
                      • 8. Re: map excel columns on oracle forms and insert it into database
                        sam8682
                        hello Andreas Weiden,

                        I am able to read data from excel using ole2 . How to pass it to db procedure? i am not getting it.

                        Thanks
                        Sam
                        • 9. Re: map excel columns on oracle forms and insert it into database
                          Andreas Weiden
                          Create a db-package and in its spec create something like
                          CREATE OR REPLACE PACKAGE PK_EXCEL_TO_DB IS
                          
                            TYPE tKeyValue IS RECORD (
                              vcFieldName VARCHAR2(255),
                              vcValue     VARCHAR2(4000)
                            );
                          
                            TYPE tDataList IS TABLE OF tKeyValue;
                          
                            PROCEDURE PR_DO_INSERT(i_lData IN tDataList);
                          
                          END;
                          
                          CREATE OR REPLACE PACKAGE BODY PK_EXCEL_TO_DB IS
                          
                            PROCEDURE PR_DO_INSERT(i_lData IN tDataList) IS
                              vcColumns VARCHAR2(32000);
                              vcValues    VARCHAR2(32000);
                            BEGIN
                              FOR i IN 1..i_ldata.COUNT LOOP
                                vcColumns:=vcColumns || ',' || i_ldata(i).vcFieldName;
                                vcValues:=vcValues|| ',''' || i_ldata(i).vcValue || '''';
                              END LOOP;
                              EXECUTE IMMEDIATE 'INSERT INTO TABLE (' || SUBSTR(vcColumns, 2) || ') VALUES (' || SUBSTR(vcValues,2) || ')';
                            END;
                          END;
                          Not tested and just a draft (need to take care of dates, ' in Varchars, Numbers. If a lot of data is involved a different insert-mechanisms with Bind-variable my be better.
                          • 12. Re: map excel columns on oracle forms and insert it into database
                            sam8682
                            hello Andreas,
                            Sorry for late reply. I am working as per your solution. Its possible for me to read data from excel using ole2 package.
                            I've also create db package as per your code. I know how to call procedure using form but how to call package i don't know.
                            I am trying several times but i am getting error.
                            following code i am using

                            PROCEDURE get_excel IS
                            APPLICATION OLE2.OBJ_TYPE;
                                 WORKBOOKS OLE2.OBJ_TYPE;
                                 WORKBOOK OLE2.OBJ_TYPE;
                                 WORKSHEETS OLE2.OBJ_TYPE;
                                 WORKSHEET OLE2.OBJ_TYPE;
                                 CELL OLE2.OBJ_TYPE;
                                 
                                 CTR NUMBER(12);
                                 COLS NUMBER(2);

                                 CELLVALUE VARCHAR2(89);
                                 C_ROUTE VARCHAR2(255);
                                 V_ROUTE VARCHAR2(1000);
                                 C_TRNDATE VARCHAR2(255);
                                 V_TRNDATE VARCHAR2(1000);
                                 FILENAME VARCHAR2(500);
                                 v_path                     varchar2(1000):=:path;
                                 ARGS OLE2.OBJ_TYPE;
                                 TEMP_REC TEMP%ROWTYPE;

                            BEGIN          
                                           filename := V_PATH;--GET_FILE_NAME('c:\', File_Filter=>'Excel Files (*.xls)|*.xls|'); -- to pick the file
                                           APPLICATION := OLE2.CREATE_OBJ('EXCEL.APPLICATION');
                                           OLE2.SET_PROPERTY(APPLICATION,'VISIBLE','FALSE');
                                           WORKBOOKS := OLE2.GET_OBJ_PROPERTY(APPLICATION, 'WORKBOOKS');
                                           ARGS := OLE2.CREATE_ARGLIST;
                                           OLE2.ADD_ARG(ARGS, FILENAME);
                                           WORKBOOK := OLE2.GET_OBJ_PROPERTY(WORKBOOKS,'OPEN',ARGS);
                                           OLE2.DESTROY_ARGLIST(ARGS);
                                           ARGS := OLE2.CREATE_ARGLIST;
                                           OLE2.ADD_ARG(ARGS,'Sheet1');
                                           WORKSHEET := OLE2.GET_OBJ_PROPERTY (WORKBOOK,'WORKSHEETS',ARGS);
                                           OLE2.DESTROY_ARGLIST(ARGS);
                                           ctr := 2; --row number
                                           cols := 1; -- column number
                                           GO_BLOCK('BLOCK3');
                                           FIRST_RECORD;
                                           LOOP
                                           -----------------------COLUMN1-------------------------------------
                                           ARGS := OLE2.CREATE_ARGLIST;
                                           OLE2.ADD_ARG(ARGS,CTR); ---reading row
                                           OLE2.ADD_ARG(ARGS,1);
                                           CELL := OLE2.GET_OBJ_PROPERTY(WORKSHEET,'CELLS',ARGS);
                                           OLE2.DESTROY_ARGLIST(ARGS);
                                           ARGS := OLE2.CREATE_ARGLIST;
                                           V_ROUTE := OLE2.GET_CHAR_PROPERTY(CELL,'TEXT');
                                           
                                           ARGS := OLE2.CREATE_ARGLIST;
                                           OLE2.ADD_ARG(ARGS,COLS); --reading column
                                           OLE2.ADD_ARG(ARGS,1);
                                           CELL := OLE2.GET_OBJ_PROPERTY(WORKSHEET,'CELLS',ARGS);
                                           OLE2.DESTROY_ARGLIST(ARGS);
                                           ARGS := OLE2.CREATE_ARGLIST;
                                           C_ROUTE := OLE2.GET_CHAR_PROPERTY(CELL,'TEXT');

                                           -----------------------COLUMN2-------------------------------------
                                           ARGS := OLE2.CREATE_ARGLIST;
                                           OLE2.ADD_ARG(ARGS,CTR);
                                           OLE2.ADD_ARG(ARGS,2);
                                           CELL := OLE2.GET_OBJ_PROPERTY(WORKSHEET,'CELLS',ARGS);
                                           OLE2.DESTROY_ARGLIST(ARGS);
                                           ARGS := OLE2.CREATE_ARGLIST;
                                           V_TRNDATE:= OLE2.GET_CHAR_PROPERTY(CELL,'TEXT');
                                           :D2:=V_TRNDATE;
                                           
                                           ARGS := OLE2.CREATE_ARGLIST;
                                           OLE2.ADD_ARG(ARGS,COLS);
                                           OLE2.ADD_ARG(ARGS,2);
                                           CELL := OLE2.GET_OBJ_PROPERTY(WORKSHEET,'CELLS',ARGS);
                                           OLE2.DESTROY_ARGLIST(ARGS);
                                           ARGS := OLE2.CREATE_ARGLIST;
                                           C_TRNDATE:= OLE2.GET_CHAR_PROPERTY(CELL,'TEXT');
                                           
                            EXIT WHEN length(V_Route) = 0 or length(V_Route) is null;

                            EXECUTE PK_EXCEL_TO_DB(C_ROUTE,V_ROUTE, C_TRNDATE, V_TRNDATE);

                            /* ERROR 103
                            Encountered the symbol 'PK_EXCEL_TO_DB" when expectiong one of the following
                            :=.(@%;
                            The symbol ":=" was substitued for "PK_EXCEL_TO_DB" to continue.*/

                            TEMP_REC := PK_EXCEL_TO_DB(C_ROUTE,V_ROUTE, C_TRNDATE, V_TRNDATE);

                            /*Error 222
                            no function with name 'PK_EXCEL_TO_DB' exists in this scope;*/

                            ctr := ctr + 1;
                            cols := 1;

                            END LOOP;
                            OLE2.INVOKE(APPLICATION,'QUIT');

                            OLE2.RELEASE_OBJ(CELL);
                            OLE2.RELEASE_OBJ(WORKSHEET);
                            OLE2.RELEASE_OBJ(WORKBOOK);
                            OLE2.RELEASE_OBJ(WORKBOOKS);
                            OLE2.RELEASE_OBJ(APPLICATION);

                            exception
                            WHEN OTHERS THEN
                            MESSAGE(sqlerrm);
                            END;
                            /

                            PLEASE HELP

                            Edited by: sam8682 on Apr 23, 2013 10:11 AM
                            • 13. Re: map excel columns on oracle forms and insert it into database
                              sam8682
                              Since package contains dynamic sql I don't know how to execute this package from oracle from?
                              I am able to read data from excel. I just want to pass it to the database package.

                              Please help.
                              • 14. Re: map excel columns on oracle forms and insert it into database
                                sam8682
                                Since package contains dynamic sql I don't know how to execute this package from oracle from?
                                I am able to read data from excel. I just want to pass it to the database package.

                                Please help.
                                1 2 Previous Next