Home » Developer & Programmer » Forms » Error 201 identifier must be declare (oracle forms)
Error 201 identifier must be declare [message #584526] |
Wed, 15 May 2013 01:08 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/71e7ed14bc76446bef38666d8e833174?s=64&d=mm&r=g) |
sameer_da
Messages: 74 Registered: April 2013 Location: India
|
Member |
|
|
[MERGED by LF]
Hii,
I am importing excel data to oracle table by column mapping using forms 6i. I am using ole2 package and i also created one package.which is given below. My code gets compiled, I am unable to insert record to the table.Values are passed to the procedure, i am able to see the values while debugging, but all values are seen in record 1 (as looping is there) all other remaining records are empty.i.e records are changing but seen only in record 1 and all other records are empty.I also get message 'DATA INSERTED INTO THE TABLE' but when i check it with sql, the table is empty.
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;
l_sql varchar2(32767);
i_ldata PK_EXCEL_TO_DB.tDataList:=PK_EXCEL_TO_DB.tDataList();
tDataList PK_EXCEL_TO_DB.tDataList;
rowcounter number;
BEGIN
:progress:='Please wait...';
SYNCHRONIZE;
--------------INITIATE EXCEL APPLICATION---------------------------
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');
----------------GET WORKBOOKS FROM EXCEL APPLICATION---------------
WORKBOOKS := OLE2.GET_OBJ_PROPERTY(APPLICATION, 'WORKBOOKS');
----------------OPEN REQUIRED WORKBOOK-----------------------------
ARGS := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(ARGS, FILENAME);
WORKBOOK := OLE2.GET_OBJ_PROPERTY(WORKBOOKS,'OPEN',ARGS);
OLE2.DESTROY_ARGLIST(ARGS);
----------------OPEN REQUIRED WORKSHEET---------------------------
ARGS := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(ARGS,'Sheet1');
WORKSHEET := OLE2.GET_OBJ_PROPERTY (WORKBOOK,'WORKSHEETS',ARGS);
OLE2.DESTROY_ARGLIST(ARGS);
----------------GET CELL VALUE-------------------------------------
ctr := 2; --row number
cols := 1; -- column number
FIRST_RECORD;
LOOP
i_ldata.extend(1);
rowcounter:= i_ldata.count;
-----------------------COLUMN1----------------------------------
ARGS := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(ARGS,COLS); --COLS
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');
i_ldata(rowcounter).CROUTE:=C_ROUTE;
ARGS := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(ARGS,CTR);
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');
i_ldata(rowcounter).VROUTE:=V_ROUTE;
-----------------------COLUMN2----------------------------------
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');
i_ldata(rowcounter).CTRNDATE:=C_TRNDATE;
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');
i_ldata(rowcounter).vtrndate:=v_trndate;
EXIT WHEN length(V_Route) = 0 or length(V_Route) is null;
ctr := ctr + 1;
cols := 1;
END LOOP;
PK_EXCEL_TO_DB.PR_DO_INSERT(i_ldata);
:progress:='EXCEL READING IS DONE...';
----------------CLOSE THE EXCEL SHEET AFTER READING--------------
OLE2.INVOKE(APPLICATION,'QUIT');
-----------------RELEASE ALL OBJECTS
OLE2.RELEASE_OBJ(CELL);
OLE2.RELEASE_OBJ(WORKSHEET);
OLE2.RELEASE_OBJ(WORKBOOK);
OLE2.RELEASE_OBJ(WORKBOOKS);
OLE2.RELEASE_OBJ(APPLICATION);
:PROGRESS := 'DATA INSERTED INTO THE TABLE '; SYNCHRONIZE;
SYNCHRONIZE;
exception
WHEN OTHERS THEN
MESSAGE(sqlerrm);
END
Package specification
PACKAGE PK_EXCEL_TO_DB IS
TYPE tKeyValue IS RECORD (
CROUTE VARCHAR2(255),
VROUTE VARCHAR2(1000),
CTRNDATE VARCHAR2(255),
VTRNDATE VARCHAR2(1000));
TYPE tDataList IS TABLE OF tKeyValue;
--i_lData tDataList;
PROCEDURE PR_DO_INSERT(i_lData IN tDataList);
END;
Package Body
PACKAGE BODY PK_EXCEL_TO_DB IS
PROCEDURE PR_DO_INSERT(i_lData IN tDataList) IS
--vcColumns VARCHAR2(255);
-- vcValues VARCHAR2(1000);
C_ROUTE VARCHAR2(255);
V_ROUTE VARCHAR2(1000);
C_TRNDATE VARCHAR2(255);
V_TRNDATE VARCHAR2(1000);
BEGIN
FOR i IN 1..i_ldata.count LOOP
C_ROUTE:=C_ROUTE || ',' || i_ldata(i).CROUTE;
V_ROUTE:=V_ROUTE|| ',''' || i_ldata(i).VROUTE || '''';
C_TRNDATE :=C_TRNDATE || ',' || i_ldata(i).CTRNDATE ;
V_TRNDATE :=V_TRNDATE || ',''' || i_ldata(i).VTRNDATE || '''';
END LOOP;
FORMS_DDL('INSERT INTO TEMP2 (' || SUBSTR(C_ROUTE, 2) || ', ' || SUBSTR(C_TRNDATE, 2)|| ',)
VALUES (' || SUBSTR(V_ROUTE,2) || ' , ' || SUBSTR(V_TRNDATE,2) ||');
---STANDARD.COMMIT;
COMMIT;
END;
END;
Regards
Sameer
[Updated on: Mon, 27 May 2013 06:23] by Moderator Report message to a moderator
|
|
|
Error 201 identifier must be declare [message #585533 is a reply to message #584526] |
Mon, 27 May 2013 05:08 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/71e7ed14bc76446bef38666d8e833174?s=64&d=mm&r=g) |
sameer_da
Messages: 74 Registered: April 2013 Location: India
|
Member |
|
|
hello I've following code,
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);
C_TTIME VARCHAR2(255);
V_TTIME VARCHAR2(1000);
C_TID VARCHAR2(255);
V_TID VARCHAR2(1000);
i PLS_INTEGER:=0;
vcColumn1 VARCHAR2(30);
vcColumn2 VARCHAR2(30);
vcColumn3 VARCHAR2(30);
vcColumn4 VARCHAR2(30);
FILENAME VARCHAR2(500);
v_path varchar2(1000):=:path;
ARGS OLE2.OBJ_TYPE;
l_sql varchar2(32767);
i_ldata PK_EXCEL_TO_DB.tDataList:=PK_EXCEL_TO_DB.tDataList();
rowcounter number;
BEGIN
GO_BLOCK('BLK_MAIN');
FIRST_RECORD;
LOOP
i:=i+1;
IF i=1 THEN
vcColumn1:=:BLK_MAIN.TAB_COL1;
ELSIF i=2 THEN
vcColumn2:=:BLK_MAIN.TAB_COL2;
END IF;
NEXT_RECORD;
END LOOP;
-- extract your column-values here
:progress:='Please wait...';
SYNCHRONIZE;
--------------INITIATE EXCEL APPLICATION---------------------------
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');
----------------GET WORKBOOKS FROM EXCEL APPLICATION---------------
WORKBOOKS := OLE2.GET_OBJ_PROPERTY(APPLICATION, 'WORKBOOKS');
----------------OPEN REQUIRED WORKBOOK-----------------------------
ARGS := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(ARGS, FILENAME);
WORKBOOK := OLE2.GET_OBJ_PROPERTY(WORKBOOKS,'OPEN',ARGS);
OLE2.DESTROY_ARGLIST(ARGS);
----------------OPEN REQUIRED WORKSHEET---------------------------
ARGS := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(ARGS,'Sheet1');
WORKSHEET := OLE2.GET_OBJ_PROPERTY (WORKBOOK,'WORKSHEETS',ARGS);
OLE2.DESTROY_ARGLIST(ARGS);
----------------GET CELL VALUE-------------------------------------
ctr := 2; --row number
cols := 1; -- column number
--FIRST_RECORD;
LOOP
i_ldata.extend(1);
rowcounter:=i_ldata.count;
-----------------------COLUMN1-------------------------------------
ARGS := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(ARGS,CTR);
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');
i_ldata(rowcounter).VROUTE:=V_ROUTE;
-----------------------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');
i_ldata(rowcounter).VTRNDATE:=V_TRNDATE;
EXIT WHEN length(V_Route) = 0 or length(V_Route) is null;
ctr := ctr + 1;
cols := 1;
PK_EXCEL_TO_DB.PR_DO_INSERT(vcColumn1,vcColumn2,I_lData);
END LOOP;
:progress:='EXCEL READING IS DONE...';
----------------CLOSE THE EXCEL SHEET AFTER READING--------------
OLE2.INVOKE(APPLICATION,'QUIT');
-----------------RELEASE ALL OBJECTS
OLE2.RELEASE_OBJ(CELL);
OLE2.RELEASE_OBJ(WORKSHEET);
OLE2.RELEASE_OBJ(WORKBOOK);
OLE2.RELEASE_OBJ(WORKBOOKS);
OLE2.RELEASE_OBJ(APPLICATION);
:PROGRESS := 'DATA INSERTED INTO THE TABLE '; SYNCHRONIZE;
SYNCHRONIZE;
--exception
--WHEN OTHERS THEN
--MESSAGE(sqlerrm);
-- now call the insertion-procedure
END;
And package specification and body
PACKAGE PK_EXCEL_TO_DB IS
TYPE tKeyValue IS RECORD (
VROUTE VARCHAR2(1000),
VTRNDATE VARCHAR2(1000),
VTTIME VARCHAR2(1000),
VTID VARCHAR2(1000));
TYPE tDataList IS TABLE OF tKeyValue;
PROCEDURE PR_DO_INSERT(i_vcColumn1 IN VARCHAR2,i_vcColumn2 IN VARCHAR2,i_vcColumn3 IN VARCHAR2,i_vcColumn4 IN VARCHAR2,i_lData IN tDataList);
END;
PACKAGE BODY PK_EXCEL_TO_DB IS
PROCEDURE PR_DO_INSERT(i_vcColumn1 IN VARCHAR2,i_vcColumn2 IN VARCHAR2,i_vcColumn3 IN VARCHAR2,i_vcColumn4 IN VARCHAR2,i_lData IN tDataList) IS
vcInsert VARCHAR2(3500);
BEGIN
FOR i IN 1..i_lData.COUNT LOOP
vcInsert :='INSERT INTO TEST (' || vcColumn1 || ', ' || vcColumn2 || ', ' || vcColumn3 || ', '|| vcColumn4 || ')' ||
' VALUES (' || ''''||i_ldata(i).VROUTE ||''''|| ', ' ||''''|| i_ldata(i).VTRNDATE ||''''|| ', ' ||''''|| i_ldata(i).VTTIME ||''''|| ',' || ''''||i_ldata(i).VTID ||'''' || ')';
FORMS_DDL(vcInsert);
END LOOP;
STANDARD.COMMIT;
END;
END;
In the insert statement of package body, I am getting error
Error 201 'vcColumn1 must be declare'
According to me code is ok.
please help me to solve the problem.
Thanks in advance
Sameer
|
|
|
|
|
|
|
|
Re: Error 201 identifier must be declare [message #585590 is a reply to message #585584] |
Tue, 28 May 2013 02:26 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
This:
EXIT WHEN length(:BLK_MAIN.TAB_COL1) = 0 or length(:BLK_MAIN.TAB_COL1) is null;
is simply a log winded way of writing this:
EXIT WHEN :BLK_MAIN.TAB_COL1 is null;
Your first loop doesn't make any obvious sense. Why you getting the first col from the 1st record, 2nd col from 2nd record etc?
|
|
|
|
|
Re: Error 201 identifier must be declare [message #585609 is a reply to message #585599] |
Tue, 28 May 2013 05:52 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
And how do those 28 rows in the table correspond to the 8 rows in the spreadsheet?
It's very, very hard to debug stuff you can't run yourself.
It's also very hard to debug code that can't work:
PROCEDURE PR_DO_INSERT(i_vcColumn1 IN VARCHAR2,i_vcColumn2 IN VARCHAR2,i_vcColumn3 IN VARCHAR2,i_vcColumn4 IN VARCHAR2,i_lData IN tDataList) IS
PK_EXCEL_TO_DB.PR_DO_INSERT(vcColumn1,vcColumn2,I_lData);
The above don't match up. One of them must be wrong.
|
|
|
Re: Error 201 identifier must be declare [message #585610 is a reply to message #585609] |
Tue, 28 May 2013 06:11 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/71e7ed14bc76446bef38666d8e833174?s=64&d=mm&r=g) |
sameer_da
Messages: 74 Registered: April 2013 Location: India
|
Member |
|
|
Thanks for reply,
I also don't know why it is happening like that.
and there is no problem in the code, just to reduce the length of code I've given only two column instead of 4 columns.so my original code contains.
PROCEDURE PR_DO_INSERT(i_vcColumn1 IN VARCHAR2,i_vcColumn2 IN VARCHAR2,i_vcColumn3 IN VARCHAR2,i_vcColumn4 IN VARCHAR2,i_lData IN tDataList) IS
PK_EXCEL_TO_DB.PR_DO_INSERT(vcColumn1,vcColumn2,vcColumn3, vcColumn4, I_lData);
I am getting the same result. If anybody could help me i upload form and excel file and script.(I've already done that but nobody is replying). Anyways I've to do it.
Thanks again
Sameer
|
|
|
Re: Error 201 identifier must be declare [message #585613 is a reply to message #585610] |
Tue, 28 May 2013 06:26 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
It would help if you answered the first question in my previous post.
It would also help if you posted the exact code your running with nothing taken out, for all we know the bit causing the problem is one of the bits you've removed before posting here.
|
|
|
Re: Error 201 identifier must be declare [message #585621 is a reply to message #585613] |
Tue, 28 May 2013 07:26 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/71e7ed14bc76446bef38666d8e833174?s=64&d=mm&r=g) |
sameer_da
Messages: 74 Registered: April 2013 Location: India
|
Member |
|
|
hello cookiemonster,
Thanks for your reply.
From my previous posts your know what i am trying to achieve.
her is my full code.
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);
C_TTIME VARCHAR2(255);
V_TTIME VARCHAR2(1000);
C_TID VARCHAR2(255);
V_TID VARCHAR2(1000);
i PLS_INTEGER:=0;
vcColumn1 VARCHAR2(30);
vcColumn2 VARCHAR2(30);
vcColumn3 VARCHAR2(30);
vcColumn4 VARCHAR2(30);
FILENAME VARCHAR2(500);
v_path varchar2(1000):=:path;
ARGS OLE2.OBJ_TYPE;
l_sql varchar2(32767);
i_ldata PK_EXCEL_TO_DB.tDataList:=PK_EXCEL_TO_DB.tDataList();
rowcounter number;
BEGIN
GO_BLOCK('BLK_MAIN');
FIRST_RECORD;
LOOP
i:=i+1;
IF i=1 THEN
vcColumn1:=:BLK_MAIN.TAB_COL1; -- i_ldata(rowcounter).CROUTE:=C_ROUTE;
ELSIF i=2 THEN
vcColumn2:=:BLK_MAIN.TAB_COL2;
ELSIF i=3 THEN
vcColumn3:=:BLK_MAIN.TAB_COL3;
ELSIF i=4 THEN
vcColumn4:=:BLK_MAIN.TAB_COL4;
--END IF;
ELSIF :SYSTEM.LAST_RECORD = 'TRUE' THEN
EXIT;
ELSE
NEXT_RECORD;
END IF;
--EXIT WHEN :BLK_MAIN.TAB_COL1 is null;
END LOOP;
--extract your column-values here
:progress:='Please wait...';
SYNCHRONIZE;
--------------INITIATE EXCEL APPLICATION---------------------------
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');
----------------GET WORKBOOKS FROM EXCEL APPLICATION---------------
WORKBOOKS := OLE2.GET_OBJ_PROPERTY(APPLICATION, 'WORKBOOKS');
----------------OPEN REQUIRED WORKBOOK-----------------------------
ARGS := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(ARGS, FILENAME);
WORKBOOK := OLE2.GET_OBJ_PROPERTY(WORKBOOKS,'OPEN',ARGS);
OLE2.DESTROY_ARGLIST(ARGS);
----------------OPEN REQUIRED WORKSHEET---------------------------
ARGS := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(ARGS,'Sheet1');
WORKSHEET := OLE2.GET_OBJ_PROPERTY (WORKBOOK,'WORKSHEETS',ARGS);
OLE2.DESTROY_ARGLIST(ARGS);
----------------GET CELL VALUE-------------------------------------
ctr := 2; --row number
cols := 1; -- column number
--FIRST_RECORD;
LOOP
i_ldata.extend(1);
rowcounter:=i_ldata.count;
-----------------------COLUMN1-------------------------------------
ARGS := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(ARGS,CTR);
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');
i_ldata(rowcounter).VROUTE:=V_ROUTE;
-----------------------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');
i_ldata(rowcounter).VTRNDATE:=V_TRNDATE;
-----------------------COLUMN3-------------------------------------
ARGS := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(ARGS,CTR);
OLE2.ADD_ARG(ARGS,3);
CELL := OLE2.GET_OBJ_PROPERTY(WORKSHEET,'CELLS',ARGS);
OLE2.DESTROY_ARGLIST(ARGS);
ARGS := OLE2.CREATE_ARGLIST;
V_TTIME := OLE2.GET_CHAR_PROPERTY(CELL,'TEXT');
i_ldata(rowcounter).vttime:=v_ttime;
-----------------------COLUMN4-------------------------------------
ARGS := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(ARGS,CTR);
OLE2.ADD_ARG(ARGS,4);
CELL := OLE2.GET_OBJ_PROPERTY(WORKSHEET,'CELLS',ARGS);
OLE2.DESTROY_ARGLIST(ARGS);
ARGS := OLE2.CREATE_ARGLIST;
V_TID := OLE2.GET_CHAR_PROPERTY(CELL,'TEXT');
i_ldata(rowcounter).vtid:=v_tid;
EXIT WHEN length(V_Route) = 0 or length(V_Route) is null;
ctr := ctr + 1;
cols := 1;
-- now call the insertion-procedure
PK_EXCEL_TO_DB.PR_DO_INSERT(vcColumn1,vcColumn2,vcColumn3,vcColumn4,I_lData);
END LOOP;
:progress:='EXCEL READING IS DONE...';
----------------CLOSE THE EXCEL SHEET AFTER READING--------------
OLE2.INVOKE(APPLICATION,'QUIT');
-----------------RELEASE ALL OBJECTS
OLE2.RELEASE_OBJ(CELL);
OLE2.RELEASE_OBJ(WORKSHEET);
OLE2.RELEASE_OBJ(WORKBOOK);
OLE2.RELEASE_OBJ(WORKBOOKS);
OLE2.RELEASE_OBJ(APPLICATION);
:PROGRESS := 'DATA INSERTED INTO THE TABLE '; SYNCHRONIZE;
SYNCHRONIZE;
--exception
--WHEN OTHERS THEN
--MESSAGE(sqlerrm);
END;
package specification.
PACKAGE PK_EXCEL_TO_DB IS
TYPE tKeyValue IS RECORD (
VROUTE VARCHAR2(1000),
VTRNDATE VARCHAR2(1000),
VTTIME VARCHAR2(1000),
VTID VARCHAR2(1000));
TYPE tDataList IS TABLE OF tKeyValue;
PROCEDURE PR_DO_INSERT(i_vcColumn1 IN VARCHAR2,i_vcColumn2 IN VARCHAR2,i_vcColumn3 IN VARCHAR2,i_vcColumn4 IN VARCHAR2,i_lData IN tDataList);
END;
package body
PACKAGE BODY PK_EXCEL_TO_DB IS
PROCEDURE PR_DO_INSERT(i_vcColumn1 IN VARCHAR2,i_vcColumn2 IN VARCHAR2,i_vcColumn3 IN VARCHAR2,i_vcColumn4 IN VARCHAR2,i_lData IN tDataList) IS
vcInsert VARCHAR2(3500);
BEGIN
FOR i IN 1..i_lData.COUNT LOOP
:BLK_MAIN.T4:= vcInsert;
vcInsert :='INSERT INTO TEST (' || i_vcColumn1 || ', ' || i_vcColumn2 || ', ' ||i_vcColumn3 || ', '|| i_vcColumn4 || ')' ||
' VALUES (' || ''''||i_ldata(i).VROUTE ||''''|| ', ' ||''''|| i_ldata(i).VTRNDATE ||''''|| ', ' ||''''|| i_ldata(i).VTTIME ||''''|| ',' || ''''||i_ldata(i).VTID ||'''' || ')';
FORMS_DDL(vcInsert);
END LOOP;
STANDARD.COMMIT;
END;
END;
and your question :28 rows in the table correspond to the 8 rows in the spreadsheet?
I am searching answer for the same.
Thanks again
Regards
Sameer.
|
|
|
Re: Error 201 identifier must be declare [message #585628 is a reply to message #585621] |
Tue, 28 May 2013 07:58 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
sameer_da wrote on Tue, 28 May 2013 13:26
and your question :28 rows in the table correspond to the 8 rows in the spreadsheet?
I am searching answer for the same.
Are all 28 rows identical?
Are they all different?
Is each of the spreadsheet rows replicated multiple times in the table?
Ie. how does the input map to the output? If you tell us that we may be able to work out the problem.
|
|
|
Re: Error 201 identifier must be declare [message #585629 is a reply to message #585590] |
Tue, 28 May 2013 08:00 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
And I still don't understand the reason for this:
cookiemonster wrote on Tue, 28 May 2013 08:26
Your first loop doesn't make any obvious sense. Why you getting the first col from the 1st record, 2nd col from 2nd record etc?
|
|
|
|
Re: Error 201 identifier must be declare [message #585682 is a reply to message #585629] |
Wed, 29 May 2013 01:55 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/71e7ed14bc76446bef38666d8e833174?s=64&d=mm&r=g) |
sameer_da
Messages: 74 Registered: April 2013 Location: India
|
Member |
|
|
Hello cookiemonster,
I've modified my code,
Declare
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);
C_TTIME VARCHAR2(255);
V_TTIME VARCHAR2(1000);
C_TID VARCHAR2(255);
V_TID VARCHAR2(1000);
i PLS_INTEGER:=0;
vcColumn1 VARCHAR2(30);
vcColumn2 VARCHAR2(30);
vcColumn3 VARCHAR2(30);
vcColumn4 VARCHAR2(30);
FILENAME VARCHAR2(500);
v_path varchar2(1000):=:path;
ARGS OLE2.OBJ_TYPE;
l_sql varchar2(32767);
i_ldata PK_EXCEL_TO_DB.tDataList:=PK_EXCEL_TO_DB.tDataList();
rowcounter number;
BEGIN
GO_BLOCK('BLK_MAIN');
vcColumn1:=:BLK_MAIN.TAB_COL1; -- i_ldata(rowcounter).CROUTE:=C_ROUTE;
vcColumn2:=:BLK_MAIN.TAB_COL2;
vcColumn3:=:BLK_MAIN.TAB_COL3;
vcColumn4:=:BLK_MAIN.TAB_COL4;
--extract your column-values here
:progress:='Please wait...';
SYNCHRONIZE;
--------------INITIATE EXCEL APPLICATION---------------------------
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');
----------------GET WORKBOOKS FROM EXCEL APPLICATION---------------
WORKBOOKS := OLE2.GET_OBJ_PROPERTY(APPLICATION, 'WORKBOOKS');
----------------OPEN REQUIRED WORKBOOK-----------------------------
ARGS := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(ARGS, FILENAME);
WORKBOOK := OLE2.GET_OBJ_PROPERTY(WORKBOOKS,'OPEN',ARGS);
OLE2.DESTROY_ARGLIST(ARGS);
----------------OPEN REQUIRED WORKSHEET---------------------------
ARGS := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(ARGS,'Sheet1');
WORKSHEET := OLE2.GET_OBJ_PROPERTY (WORKBOOK,'WORKSHEETS',ARGS);
OLE2.DESTROY_ARGLIST(ARGS);
----------------GET CELL VALUE-------------------------------------
ctr := 2; --row number
cols := 1; -- column number
--FIRST_RECORD;
LOOP
i_ldata.extend(1);
rowcounter:=i_ldata.count;
-----------------------COLUMN1-------------------------------------
ARGS := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(ARGS,CTR);
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');
i_ldata(rowcounter).VROUTE:=V_ROUTE;
-----------------------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');
i_ldata(rowcounter).VTRNDATE:=V_TRNDATE;
-----------------------COLUMN3-------------------------------------
ARGS := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(ARGS,CTR);
OLE2.ADD_ARG(ARGS,3);
CELL := OLE2.GET_OBJ_PROPERTY(WORKSHEET,'CELLS',ARGS);
OLE2.DESTROY_ARGLIST(ARGS);
ARGS := OLE2.CREATE_ARGLIST;
V_TTIME := OLE2.GET_CHAR_PROPERTY(CELL,'TEXT');
i_ldata(rowcounter).vttime:=v_ttime;
-----------------------COLUMN4-------------------------------------
ARGS := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(ARGS,CTR);
OLE2.ADD_ARG(ARGS,4);
CELL := OLE2.GET_OBJ_PROPERTY(WORKSHEET,'CELLS',ARGS);
OLE2.DESTROY_ARGLIST(ARGS);
ARGS := OLE2.CREATE_ARGLIST;
V_TID := OLE2.GET_CHAR_PROPERTY(CELL,'TEXT');
i_ldata(rowcounter).vtid:=v_tid;
EXIT WHEN length(V_Route) = 0 or length(V_Route) is null;
ctr := ctr + 1;
cols := 1;
-- now call the insertion-procedure
PK_EXCEL_TO_DB.PR_DO_INSERT(vcColumn1,vcColumn2,vcColumn3,vcColumn4,I_lData);
END LOOP;
:progress:='EXCEL READING IS DONE...';
----------------CLOSE THE EXCEL SHEET AFTER READING--------------
OLE2.INVOKE(APPLICATION,'QUIT');
-----------------RELEASE ALL OBJECTS
OLE2.RELEASE_OBJ(CELL);
OLE2.RELEASE_OBJ(WORKSHEET);
OLE2.RELEASE_OBJ(WORKBOOK);
OLE2.RELEASE_OBJ(WORKBOOKS);
OLE2.RELEASE_OBJ(APPLICATION);
:PROGRESS := 'DATA INSERTED INTO THE TABLE '; SYNCHRONIZE;
SYNCHRONIZE;
--exception
--WHEN OTHERS THEN
--MESSAGE(sqlerrm);
END;
I've also made some changes in the procedure. (I know its not proper way of design).
PACKAGE PK_EXCEL_TO_DB IS
TYPE tKeyValue IS RECORD (
-- CROUTE VARCHAR2(255),
-- CTRNDATE VARCHAR2(255),
-- CTTIME VARCHAR2(255),
-- CTID VARCHAR2(255),
VROUTE VARCHAR2(1000),
VTRNDATE VARCHAR2(1000),
VTTIME VARCHAR2(1000),
VTID VARCHAR2(1000));
TYPE tDataList IS TABLE OF tKeyValue;
PROCEDURE PR_DO_INSERT(i_vcColumn1 IN VARCHAR2,i_vcColumn2 IN VARCHAR2,i_vcColumn3 IN VARCHAR2,i_vcColumn4 IN VARCHAR2,i_lData IN tDataList);
END;
package body is
PACKAGE BODY PK_EXCEL_TO_DB IS
PROCEDURE PR_DO_INSERT(i_vcColumn1 IN VARCHAR2,i_vcColumn2 IN VARCHAR2,i_vcColumn3 IN VARCHAR2,i_vcColumn4 IN VARCHAR2,i_lData IN tDataList) IS
vcInsert VARCHAR2(3500);
i INTEGER:=0;
BEGIN
--FOR i IN 1..i_lData.COUNT LOOP
--END LOOP;
i:=i_lData.COUNT;
:BLK_MAIN.T4:= vcInsert;
vcInsert :='INSERT INTO TEST (' || i_vcColumn1 || ', ' || i_vcColumn2 || ', ' ||i_vcColumn3 || ', '|| i_vcColumn4 || ')' ||
' VALUES (' || ''''||i_ldata(i).VROUTE ||''''|| ', ' ||''''|| i_ldata(i).VTRNDATE ||''''|| ', ' ||''''|| i_ldata(i).VTTIME ||''''|| ',' || ''''||i_ldata(i).VTID ||'''' || ')';
FORMS_DDL(vcInsert);
--END LOOP;
STANDARD.COMMIT;
END;
END;
and its working porperly. But my main problem remains same i.e my column heading became dynamic but records are still static.
i.e
Excel Column Table column
----------------------------------
Route Route
Tdate date
Ttime time
id id
In above case record get inserted into table
But when sequence of excel changes then
i.e
Excel Column Table column
----------------------------------
Tdate date
Route Route
id id
Ttime time
Records are no inserted into table.
Could you please help me in that.
Thanks again.
Sameer.
|
|
|
Re: Error 201 identifier must be declare [message #585689 is a reply to message #585670] |
Wed, 29 May 2013 03:02 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
sameer_da wrote on Wed, 29 May 2013 05:23Hello cookiemonster,
ok why i am getting first col from the 1st record, 2nd col from 2nd record etc?
I've have display all column heading of excel on the form. Then in-front of each column heading of excel i've provide list item which contains table column heading. i am asking user to map his excel column with table column, one the user map's the column I am handling it with first loop of the procedure.
That's why i've created this loop.
Thanks
Sam.
I still don't understand. Why don't you have all the column headings in a single row?
Or a single column across multiple rows?
A screen shot would probably help.
|
|
|
|
|
|
|
|
|
|
|
Re: Error 201 identifier must be declare [message #585727 is a reply to message #585726] |
Wed, 29 May 2013 06:51 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
sameer_da wrote on Wed, 29 May 2013 12:30Yes Values are displayed in the vcInsert and values are correct but there is some problem in the insert statement.
But you're not sure what the problem is? And you haven't posted the insert here so we can't tell what it is either.
sameer_da wrote on Wed, 29 May 2013 12:30
Since record are shown 28 times and expected 8 time only I've modify the insert statement code (which is not according to good programming practice)
Define good pratice in this case.
sameer_da wrote on Wed, 29 May 2013 12:30
. If required i can provide the code.
Everyone's life would be simpler if you always assumed that it is required for you to provide the code you're using that you want us to help fix.
sameer_da wrote on Wed, 29 May 2013 12:30
The condition is
Excel column table column
----------------------------------------
Route Route
date tdate
time ttime
tid id
in above case data get inserted
when situation is like this
Excel column table column
--------------------------------------------
id id
time ttime
date tdate
route route
in this case data is not inserted into table.
So how do the insert statements differ in each case?
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 07 21:05:21 CDT 2024
|