OraFAQ Forum: Forms » Error 201 identifier must be declare

Home » Developer & Programmer » Forms » Error 201 identifier must be declare (oracle forms)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Error 201 identifier must be declare [message #584526]

Wed, 15 May 2013 01:08

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