OraFAQ Forum: Forms » call associative array in oracle procedure using oracle forms

Home » Developer & Programmer » Forms » call associative array in oracle procedure using oracle forms (forms 6i)

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

call associative array in oracle procedure using oracle forms [message #583059]

Thu, 25 April 2013 04:24

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.

Report message to a moderator

Re: call associative array in oracle procedure using oracle forms [message #583069 is a reply to message #583059]

Thu, 25 April 2013 06:12

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Show us the forms code you are using and the error you are getting

Report message to a moderator

Re: call associative array in oracle procedure using oracle forms [message #583073 is a reply to message #583069]

Thu, 25 April 2013 07:23

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.

Report message to a moderator

Re: call associative array in oracle procedure using oracle forms [message #583074 is a reply to message #583073]

Thu, 25 April 2013 07:38

cookiemonster
Messages: 13920
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;

Report message to a moderator

Re: call associative array in oracle procedure using oracle forms [message #583114 is a reply to message #583074]

Fri, 26 April 2013 00:06

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.

Report message to a moderator

Re: call associative array in oracle procedure using oracle forms [message #583117 is a reply to message #583114]

Fri, 26 April 2013 00:54

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

So initialize it before you reference it in your code.

Report message to a moderator

Re: call associative array in oracle procedure using oracle forms [message #583132 is a reply to message #583117]

Fri, 26 April 2013 02:31

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.

Report message to a moderator

Re: call associative array in oracle procedure using oracle forms [message #583133 is a reply to message #583132]

Fri, 26 April 2013 02:48

cookiemonster
Messages: 13920
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.

Report message to a moderator

Re: call associative array in oracle procedure using oracle forms [message #583137 is a reply to message #583133]

Fri, 26 April 2013 04:07

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

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

which error?
and shouldn't it be:

i_ldata  := PK_EXCEL_TO_DB.LIST_ROUTE();

Report message to a moderator

Re: call associative array in oracle procedure using oracle forms [message #583202 is a reply to message #583148]

Sat, 27 April 2013 01:02

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

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Your form code doesn't appear to actually populate the array with anything.

Report message to a moderator

Re: call associative array in oracle procedure using oracle forms [message #583271 is a reply to message #583219]

Mon, 29 April 2013 01:37

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

Report message to a moderator

Re: call associative array in oracle procedure using oracle forms [message #584597 is a reply to message #583219]

Thu, 16 May 2013 00:16

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.

Report message to a moderator

Re: call associative array in oracle procedure using oracle forms [message #584604 is a reply to message #584597]

Thu, 16 May 2013 02:33

cookiemonster
Messages: 13920
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.

Report message to a moderator

Previous Topic:	Opening PDF/Images/Text Files In Forms
Next Topic:	dispaly pdf file thru forms & extract blob from DB thru forms (merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 16:02:10 CDT 2024