Home » Developer & Programmer » Forms » how to call database package from oracle 6i forms (forms 6i)
how to call database package from oracle 6i forms [message #582891] Tue, 23 April 2013 04:05 Go to next message
sameer_da
Messages: 74
Registered: April 2013
Location: India
Member
Hii all,

I've created a database package which is having record type and one procedure. I want to execute or call this package from oracle 6i form. please tell me how to do this.

Thanks
Sameer. Sad
Re: how to call database package from oracle 6i forms [message #582892 is a reply to message #582891] Tue, 23 April 2013 04:16 Go to previous messageGo to next message
Littlefoot
Messages: 19693
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You can't execute a package, but you can execute its procedure. How? As usual:
package_name.procedure_name(list_of_parameters);
Re: how to call database package from oracle 6i forms [message #582901 is a reply to message #582891] Tue, 23 April 2013 05:09 Go to previous messageGo to next message
sameer_da
Messages: 74
Registered: April 2013
Location: India
Member
Thanks for quick replay.
Actually I've created a database package, which is like this

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;

Now I want to pass values from my form to Record in the package. Please tell me how can i do this. Once the values are passed then procedure will get execute.

Thanks
Sameer
Re: how to call database package from oracle 6i forms [message #582904 is a reply to message #582901] Tue, 23 April 2013 05:29 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

2/
"INSERT INTO TABLE"
Your table name is TABLE? And it is static while the column names are not? very strange.
TABLE is a reserved word, you cannot use it as an identifier.

Regards
Michel
Re: how to call database package from oracle 6i forms [message #582912 is a reply to message #582904] Tue, 23 April 2013 05:52 Go to previous messageGo to next message
sameer_da
Messages: 74
Registered: April 2013
Location: India
Member
Thanks for quick replay.
Actually I've created a database package, which is like this
[CODE]
CREATE OR REPLACE PACKAGE PK_EXCEL_TO_DB IS

TYPE tKeyValue IS RECORD (V_ROUTE VARCHAR2(255), ROUTE_VAL 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
COL_NAME VARCHAR2(32000);
COL_VALUE VARCHAR2(32000);
BEGIN
FOR i IN 1..i_ldata.COUNT LOOP
COL_NAME :=COL_NAME || ',' || i_ldata(i).COL_NAME ;
COL_VALUE:=COL_VALUE|| ',''' || i_ldata(i).COL_VALUE || '''';
END LOOP;
EXECUTE IMMEDIATE 'INSERT INTO ROUTE_DTL (' || SUBSTR(COL_NAME, 2) || ') VALUES (' || SUBSTR(COL_VALUE,2) || ')';
END;
END;

[CODE]
Now I want to pass values from my form to Record in the package. Please tell me how can i do this. Once the values are passed then procedure will get execute.

Thanks
Sameer
Re: how to call database package from oracle 6i forms [message #582921 is a reply to message #582912] Tue, 23 April 2013 07:18 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't carefully read the link, and format does not mean just add code tags but also indent the code.
Do you really code with all lines starting at column 1?

Regards
Michel
Re: how to call database package from oracle 6i forms [message #582924 is a reply to message #582921] Tue, 23 April 2013 07:40 Go to previous messageGo to next message
sameer_da
Messages: 74
Registered: April 2013
Location: India
Member
hello Michel,

Sorry I've tried for better format but in preview i am getting the same format.

So i am attaching my code, please find the attachment of the same.

Regards
Sameer
  • Attachment: code.txt
    (Size: 0.67KB, Downloaded 93 times)
Re: how to call database package from oracle 6i forms [message #582967 is a reply to message #582921] Tue, 23 April 2013 23:21 Go to previous message
sameer_da
Messages: 74
Registered: April 2013
Location: India
Member
Hii Michel,
Now i got it. Its my query
CREATE OR REPLACE PACKAGE PK_EXCEL_TO_DB IS
	TYPE tKeyValue IS RECORD (V_ROUTE VARCHAR2(255), 
                                  ROUTE_VAL 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
	COL_NAME VARCHAR2(32000);
	COL_VALUE VARCHAR2(32000);
BEGIN
	FOR i IN 1..i_ldata.COUNT LOOP
	COL_NAME :=COL_NAME || ',' || i_ldata(i).COL_NAME ;
	COL_VALUE:=COL_VALUE|| ',''' || i_ldata(i).COL_VALUE || '''';
	END LOOP;
EXECUTE IMMEDIATE 'INSERT INTO ROUTE_DTL (' || SUBSTR(COL_NAME, 2) || ') VALUES (' || SUBSTR(COL_VALUE,2) || ')';
END;
END;

[Updated on: Wed, 24 April 2013 07:58] by Moderator

Report message to a moderator

Previous Topic: oracle.forms.fd.sound
Next Topic: .fmx in oracle 10g
Goto Forum:
  


Current Time: Thu Oct 23 09:41:58 CDT 2014

Total time taken to generate the page: 0.09582 seconds