Home » Developer & Programmer » Forms » call associative array in oracle procedure using oracle forms (forms 6i)
call associative array in oracle procedure using oracle forms [message #583059] Thu, 25 April 2013 04:24 Go to next message
sameer_da
Messages: 74
Registered: April 2013
Location: India
Member
HELLO,
I've one package in which one record is created. associative array is craeted on that record.
create procedure on associative array.
using forms 6i i want to call this procedue.(package_name.procedure_name(paramerters)).
but my problem is what paramerter shuld i provide to excute the procedure?
like PK_EXCEL_TO_DB.PR_DO_INSERT(LIST_ROUTE); but i am getting error while doing this.

CREATE OR REPLACE PACKAGE PK_EXCEL_TO_DB IS

	TYPE ROUTE IS RECORD (COL_ROUTE VARCHAR2(255), VAL_ROUTE VARCHAR2(4000));

	TYPE LIST_ROUTE IS TABLE OF ROUTE;

	PROCEDURE PR_DO_INSERT(i_lData IN LIST_ROUTE);
END;

CREATE OR REPLACE PACKAGE BODY PK_EXCEL_TO_DB IS

	PROCEDURE PR_DO_INSERT(i_lData IN LIST_ROUTE) IS
                  ROUTE_NAME VARCHAR2(32000);
	          ROTUE_VALUE VARCHAR2(32000);
BEGIN
	FOR i IN 1..i_ldata.COUNT LOOP
		   ROUTE_NAME :=ROUTE_NAME || ',' || i_ldata(i).ROUTE_NAME ;
		   ROTUE_VALUE:=ROTUE_VALUE|| ',''' || i_ldata(i).ROTUE_VALUE || '''';
	END LOOP;
EXECUTE IMMEDIATE 'INSERT INTO ROUTE_DTL (' || SUBSTR(ROUTE_NAME, 2) || ') VALUES (' ||                         SUBSTR(ROTUE_VALUE,2) || ')';
END;
END;

please suggest something, i know it must be a simple thing but i am not getting it.

Regards
Sameer.
Re: call associative array in oracle procedure using oracle forms [message #583069 is a reply to message #583059] Thu, 25 April 2013 06:12 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Show us the forms code you are using and the error you are getting
Re: call associative array in oracle procedure using oracle forms [message #583073 is a reply to message #583069] Thu, 25 April 2013 07:23 Go to previous messageGo to next message
sameer_da
Messages: 74
Registered: April 2013
Location: India
Member
Thanks for quick reply cookiemonster,
Actually i am importing excel to oracle table by column mapping, for this purpose i've created procedure(previous one). I am using ole2 package, and i want to pass output of ole2 package to 'PK_EXCEL_TO_DB ' package. ole2 code is given below.
[size=1]
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);

	FILENAME   VARCHAR2(500);
	v_path    varchar2(1000):=:path;
BEGIN
		
			:progress:='Please wait...';
			SYNCHRONIZE;
			filename := V_PATH;
			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,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');

		       :C1:=:CM1;
		   
		  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');
			:D1:=V_ROUTE;		
			
EXIT WHEN length(V_Route) = 0 or length(V_Route) is null; 
PK_EXCEL_TO_DB.PR_DO_INSERT(LIST_ROUTE)
ctr := ctr + 1; 
		cols := 1;
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;
[/size]


Thanks again.

Regards
Sameer.
Re: call associative array in oracle procedure using oracle forms [message #583074 is a reply to message #583073] Thu, 25 April 2013 07:38 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
You haven't declared LIST_ROUTE in your forms code. So forms has no idea what it's supposed to be. Something like this in the declare:

list_route   PK_EXCEL_TO_DB.list_route;
Re: call associative array in oracle procedure using oracle forms [message #583114 is a reply to message #583074] Fri, 26 April 2013 00:06 Go to previous messageGo to next message
sameer_da
Messages: 74
Registered: April 2013
Location: India
Member
Thanks a lot cookiemonster,
my form gets compiled. but when i run the form i get the error..
       ORA-06531 reference to uninitialized collection


Thanks again
Regards
Sameer.
Re: call associative array in oracle procedure using oracle forms [message #583117 is a reply to message #583114] Fri, 26 April 2013 00:54 Go to previous messageGo to next message
Littlefoot
Messages: 19633
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So initialize it before you reference it in your code.
Re: call associative array in oracle procedure using oracle forms [message #583132 is a reply to message #583117] Fri, 26 April 2013 02:31 Go to previous messageGo to next message
sameer_da
Messages: 74
Registered: April 2013
Location: India
Member
Thanks for prompt reply cookiemonster,

when i initialize it declaration part of SPECIFICATION
i_ldata  list_route:= list_route();

 error:
variable or constants initialization may not refer to function declare in the same package


when i initialize it declaration section of BODY
i_ldata  list_route:= list_route();

error
duplicate fields in records, tabl or argument list are not permitted

is this correct one or i am doing something wrong? i am confuse.

Thanks again
Sameer.
Re: call associative array in oracle procedure using oracle forms [message #583133 is a reply to message #583132] Fri, 26 April 2013 02:48 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Since it's an in parameter you need to initialise it in the form code not the package.
Re: call associative array in oracle procedure using oracle forms [message #583137 is a reply to message #583133] Fri, 26 April 2013 04:07 Go to previous messageGo to next message
sameer_da
Messages: 74
Registered: April 2013
Location: India
Member
Hello cookiemonster,
I've written following code in ole2 package.
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);--row
	COLS       NUMBER(2);--column
	CELLVALUE  VARCHAR2(89);
	C_ROUTE    VARCHAR2(255);
	V_ROUTE    VARCHAR2(1000);

	FILENAME   VARCHAR2(500);
	v_path    varchar2(1000):=:path;
       
        i_ldata PK_EXCEL_TO_DB.tDataList
    --also tried 	i_ldata PK_EXCEL_TO_DB.tDataLis:=PK_EXCEL_TO_DB.tDataList();
BEGIN
	i_ldata  := PK_EXCEL_TO_DB.tDatalist();
....
.....

END;


Still getting same error.

Thanks & Regardes
Sameer.

[Updated on: Fri, 26 April 2013 04:08]

Report message to a moderator

Re: call associative array in oracle procedure using oracle forms [message #583148 is a reply to message #583137] Fri, 26 April 2013 05:51 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
which error?
and shouldn't it be:
i_ldata  := PK_EXCEL_TO_DB.LIST_ROUTE();
Re: call associative array in oracle procedure using oracle forms [message #583202 is a reply to message #583148] Sat, 27 April 2013 01:02 Go to previous messageGo to next message
sameer_da
Messages: 74
Registered: April 2013
Location: India
Member
Sorry for late reply
I still getting same error. in the procedure problem is at this line
 FOR i IN 1..i_ldata.COUNT LOOP

ORA-06531: Referance to uninitialized collection ORA-06512: PK_EXCEL_TO_DB LINE NO...


Thanks
Sameer.

[Updated on: Sat, 27 April 2013 01:26]

Report message to a moderator

Re: call associative array in oracle procedure using oracle forms [message #583219 is a reply to message #583202] Sat, 27 April 2013 14:10 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Your form code doesn't appear to actually populate the array with anything.
Re: call associative array in oracle procedure using oracle forms [message #583271 is a reply to message #583219] Mon, 29 April 2013 01:37 Go to previous messageGo to next message
sameer_da
Messages: 74
Registered: April 2013
Location: India
Member
Sorry for late reply cookiemonster,
Yes your right, array is not populated, i am working on the same thing. Trying to find
solution for same. Code is ok i don't understand why its happening.

Regards
Sameer
Re: call associative array in oracle procedure using oracle forms [message #584597 is a reply to message #583219] Thu, 16 May 2013 00:16 Go to previous messageGo to next message
sameer_da
Messages: 74
Registered: April 2013
Location: India
Member
Sorry for late rely, I've modify my code which is given below. every thing working properly, but no records are saved to oracle database table. please help me.
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;

I don't understand why records are not inserted into table.
Thanks & Regards
Sameer.
Re: call associative array in oracle procedure using oracle forms [message #584604 is a reply to message #584597] Thu, 16 May 2013 02:33 Go to previous message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
First off - commit in forms only does anything if forms thinks some of the datablock items have changed. I'm guessing that's not the case here, so you need to put the standard.commit back in.
If it still doesn't work then I would assume the insert is erroring out.
Previous Topic: To open word document/excel/pdf from Oracle forms
Next Topic: dispaly pdf file thru forms & extract blob from DB thru forms (merged)
Goto Forum:
  


Current Time: Mon Sep 22 10:06:07 CDT 2014

Total time taken to generate the page: 0.15861 seconds