Home » Fusion Middleware & Colab Suite » Weblogic & Application Server » exceptions were raised when calling procedure in c#.net through ODP for .net
| exceptions were raised when calling procedure in c#.net through ODP for .net [message #77274] |
Fri, 14 January 2005 22:41  |
Jonathan
Messages: 10
Registered: May 2002
|
Junior Member |
|
|
When called in the sql*plus environment, the procedrue worked well. But when in .net, it raised exceptions as follows:
a non-numeric character found where a numeric was expected.
the stored procedure is as below:
-------------------------------------------------------
CREATE OR REPLACE PACKAGE FA_ASSETS_TRX AUTHID CURRENT_USER AS
procedure FA_ASSETS_ADDITIONS( p_user_name IN VARCHAR2,
p_employee_number IN VARCHAR2,
p_invoice_number IN VARCHAR2,
p_po_number IN VARCHAR2,
p_vendor_number IN VARCHAR2,
p_asset_number IN VARCHAR2,
p_asset_description IN VARCHAR2,
p_category IN VARCHAR2,
p_tag_number IN VARCHAR2 DEFAULT NULL,
p_serial_number IN VARCHAR2 DEFAULT NULL,
p_asset_key IN VARCHAR2 DEFAULT NULL,
p_asset_type IN VARCHAR2 DEFAULT 'CAPITALIZED',
p_units_assigned IN VARCHAR2 DEFAULT '1',
p_cost IN VARCHAR2,
p_dpis IN VARCHAR2,
p_expense IN VARCHAR2,
p_location IN VARCHAR2,
p_book_type_code IN VARCHAR2 DEFAULT 'IMJ CORPORATE',
x_mesg OUT VARCHAR2
);
END FA_ASSETS_TRX;
/
CREATE OR REPLACE PACKAGE BODY FA_ASSETS_TRX AS
l_return_status VARCHAR2(1);
l_mesg_count NUMBER := 0;
l_mesg_len NUMBER;
l_mesg VARCHAR2(4000);
procedure FA_ASSETS_ADDITIONS( p_user_name IN VARCHAR2,
p_employee_number IN VARCHAR2,
p_invoice_number IN VARCHAR2,
p_po_number IN VARCHAR2,
p_vendor_number IN VARCHAR2,
p_asset_number IN VARCHAR2,
p_asset_description IN VARCHAR2,
p_category IN VARCHAR2,
p_tag_number IN VARCHAR2 DEFAULT NULL,
p_serial_number IN VARCHAR2 DEFAULT NULL,
p_asset_key IN VARCHAR2,
p_asset_type IN VARCHAR2 DEFAULT 'CAPITALIZED',
p_units_assigned IN VARCHAR2 DEFAULT '1',
p_cost IN VARCHAR2,
p_dpis IN VARCHAR2,
p_expense IN VARCHAR2,
p_location IN VARCHAR2,
p_book_type_code IN VARCHAR2 DEFAULT 'IMJ CORPORATE',
x_mesg OUT VARCHAR2
) AS
l_trans_rec FA_API_TYPES.trans_rec_type;
l_dist_trans_rec FA_API_TYPES.trans_rec_type;
l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
l_asset_desc_rec FA_API_TYPES.asset_desc_rec_type;
l_asset_cat_rec FA_API_TYPES.asset_cat_rec_type;
l_asset_type_rec FA_API_TYPES.asset_type_rec_type;
l_asset_hierarchy_rec FA_API_TYPES.asset_hierarchy_rec_type;
l_asset_fin_rec FA_API_TYPES.asset_fin_rec_type;
l_asset_deprn_rec FA_API_TYPES.asset_deprn_rec_type;
l_asset_dist_rec FA_API_TYPES.asset_dist_rec_type;
l_asset_dist_tbl FA_API_TYPES.asset_dist_tbl_type;
l_inv_rec FA_API_TYPES.inv_rec_type;
l_inv_tbl FA_API_TYPES.inv_tbl_type;
l_inv_rate_tbl FA_API_TYPES.inv_rate_tbl_type;
v_min_date Date;
v_max_date Date;
v_user_id NUMBER(15);
v_assigned_to NUMBER(10);
v_po_vendor_id NUMBER;
v_category_id NUMBER(15);
v_expense_ccid NUMBER(15);
v_location_ccid NUMBER(15);
v_asset_key_ccid NUMBER;
v_asset_key VARCHAR2(1);
v_dpis Date;
v_units_assigned NUMBER(15);
v_cost NUMBER(15);
program_exit EXCEPTION;
--=========initialize parameters==================--
BEGIN
fnd_profile.put('PRINT_BUG','Y');
FA_SRVR_MSG.Init_Server_Message;
FND_MSG_PUB.INITIALIZE;
FA_DEBUG_PKG.Initialize;
--======the main information ===---
--desc info
l_asset_desc_rec.asset_number := 'IMJ040149-10';
l_asset_desc_rec.description := 'APPS';
--l_asset_desc_rec.tag_number := p_tag_number;
--l_asset_desc_rec.serial_number := p_serial_number;
l_asset_desc_rec.asset_key_ccid := 4;
--category info
l_asset_cat_rec.category_id := 12;
--type info
l_asset_type_rec.asset_type := 'CAPITALIZED';
--finance info
l_asset_fin_rec.date_placed_in_service :=
TO_DATE('2004-11-19','YYYY-MM-DD');
l_asset_fin_rec.cost := 50000;--v_cost;
l_asset_fin_rec.original_cost := l_asset_fin_rec.cost;
l_asset_fin_rec.depreciate_flag := 'YES';
l_asset_fin_rec.deprn_method_code := 'STL';
l_asset_fin_rec.life_in_months := 72;
--deprn info
--l_asset_deprn_rec.ytd_deprn := 500;
--l_asset_deprn_rec.deprn_reserve := 500;
--l_asset_deprn_rec.bonus_ytd_deprn := 0;
--l_asset_deprn_rec.bonus_deprn_reserve := 0;
--book/transaction info
l_asset_hdr_rec.book_type_code := 'IMJ CORPORATE';
l_trans_rec.transaction_date_entered :=
l_asset_fin_rec.date_placed_in_service;
l_trans_rec.who_info.created_by := -1;
l_trans_rec.who_info.last_updated_by := -1;
--distribution info
l_asset_dist_rec.units_assigned := 1;--v_units_assigned;
l_asset_dist_rec.expense_ccid := 23559;--v_expense_ccid;
l_asset_dist_rec.location_ccid := 20;--v_location_ccid;
--l_asset_dist_rec.assigned_to := v_assigned_to;
l_asset_dist_rec.transaction_units := l_asset_dist_rec.units_assigned;
l_asset_dist_tbl(1) := l_asset_dist_rec;
--l_asset_dist_tbl(2) := l_asset_dist_rec;
--l_asset_desc_rec.asset_number :=
--l_asset_desc_rec.property_type_code := 'REAL';
--l_asset_desc_rec.property_1245_1250_code := '1245';
--l_asset_desc_rec.in_use_flag := 'YES';
--l_asset_desc_rec.owned_leased := 'OWNED';
--l_asset_desc_rec.new_used := 'NEW';
--l_asset_desc_rec.inventorial := 'YES';
--l_asset_desc_rec.manufacturer_name :=
--l_asset_desc_rec.serial_number :=
--l_asset_desc_rec.model_number :=
--l_asset_desc_rec.tag_number :=
--l_asset_desc_rec.parent_asset_id :=
--l_asset_desc_rec.warranty_id :=
--l_asset_desc_rec.lease_id :=
-- For tax addition, will need existing asset_id
--l_asset_hdr_rec.asset_id :=
--l_asset_fin_rec.salvage_value :=
--l_asset_fin_rec.unrevalued_cost :=
--l_asset_fin_rec.short_fiscal_year_flag :=
--l_asset_fin_rec.conversion_date :=
--l_asset_fin_rec.orig_deprn_start_date :=
--l_asset_fin_rec.unit_of_measure :=
--l_asset_deprn_rec.reval_deprn_reserve :=
--l_asset_deprn_rec.reval_amortization_basis :=
-- Accept amort start date for amortize NBV additions
-- l_trans_rec.amortization_start_date :=
--
--=========================call the api=======-------
-- call the api
fa_addition_pub.do_addition
(p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_mesg_count,
x_msg_data => l_mesg,
p_calling_fn => 'FA_ASSETS_TRX.FA_ASSETS_ADDITIONS',
px_trans_rec => l_trans_rec,
px_dist_trans_rec => l_dist_trans_rec,
px_asset_hdr_rec => l_asset_hdr_rec,
px_asset_desc_rec => l_asset_desc_rec,
px_asset_type_rec => l_asset_type_rec,
px_asset_cat_rec => l_asset_cat_rec,
px_asset_hierarchy_rec => l_asset_hierarchy_rec,
px_asset_fin_rec => l_asset_fin_rec,
px_asset_deprn_rec => l_asset_deprn_rec,
px_asset_dist_tbl => l_asset_dist_tbl,
px_inv_tbl => l_inv_tbl,
px_inv_rate_tbl => l_inv_rate_tbl
);
IF (l_return_status = 'E') THEN
l_mesg := 'At least one error occured!';
END IF;
IF (l_return_status = 'U') THEN
l_mesg := 'Unexpected error occured!'||CHR(10)||'Asset Addition
FAILED!';
END IF;
if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
l_mesg_count := fnd_msg_pub.count_msg;
if l_mesg_count > 0 then
l_mesg := chr(10) || substr(fnd_msg_pub.get
(fnd_msg_pub.G_FIRST, fnd_api.G_FALSE),
1, 512);
for i in 1..(l_mesg_count-1) loop
l_mesg := l_mesg || chr(10) ||
substr(fnd_msg_pub.get
(fnd_msg_pub.G_NEXT,
fnd_api.G_FALSE), 1, 512);
end loop;
x_mesg := l_mesg;
fnd_msg_pub.delete_msg();
/*l_mesg_len := length(l_mesg);
for i in 1..ceil(l_mesg_len/255) loop
dbms_output.put_line(substr(l_mesg, ((i*255)-254), 255));
end loop;*/
end if;
else
l_mesg := 'ADDITION WITH SUCCCESS'
||CHR(10)||'THANSACTION_ID = '||
to_char(l_trans_rec.transaction_header_id)
||CHR(10)||'ASSET_ID = '|| to_char(l_asset_hdr_rec.asset_id)
||CHR(10)||'ASSET_NUM = '||l_asset_desc_rec.asset_number;
x_mesg := l_mesg;
--dbms_output.put_line(l_mesg);
end if;
EXCEPTION
when program_exit then
l_mesg := l_mesg||' ! '||'Assets Addition FAILED';
x_mesg := l_mesg;
--dbms_output.put_line(l_mesg);
when value_error then
l_mesg := 'character string buffer too small';
l_mesg := l_mesg||' ! '||'Assets Addition FAILED';
x_mesg := l_mesg;
--dbms_output.put_line(l_mesg);
when others then
l_mesg := SQLERRM;
l_mesg := l_mesg||' ! '||'Assets Addition FAILED';
x_mesg := l_mesg;
--dbms_output.put_line(l_mesg);
END FA_ASSETS_ADDITIONS;
END FA_ASSETS_TRX;
/
=======================================================--.net code
this.oracleConnection1.ConnectionString="Data Source=vis;user
id=apps;password=apps;enlist=false";
this.oracleCommand1.CommandText="FA_ASSETS_TRX.FA_ASSETS_ADDITIONS";
this.oracleCommand1.CommandType=CommandType.StoredProcedure;
Oracle.DataAccess.Client.OracleParameter objParam1=new
Oracle.DataAccess.Client.OracleParameter("p_user_name",Oracle.DataAccess.Client.OracleDbType.Varchar2,100);
objParam1.Value=this.TextBox1.Text;
Oracle.DataAccess.Client.OracleParameter objParam2=new
Oracle.DataAccess.Client.OracleParameter("p_employee_number",Oracle.DataAccess.Client.OracleDbType.Varchar2,30);
objParam2.Value=this.TextBox2.Text;
Oracle.DataAccess.Client.OracleParameter objParam3=new
Oracle.DataAccess.Client.OracleParameter("p_invoice_number",Oracle.DataAccess.Client.OracleDbType.Varchar2,50);
objParam3.Value=this.TextBox3.Text;
Oracle.DataAccess.Client.OracleParameter objParam4=new
Oracle.DataAccess.Client.OracleParameter("p_po_number",Oracle.DataAccess.Client.OracleDbType.Varchar2,20);
objParam4.Value=this.TextBox4.Text;
Oracle.DataAccess.Client.OracleParameter objParam5=new
Oracle.DataAccess.Client.OracleParameter("p_vendor_number",Oracle.DataAccess.Client.OracleDbType.Varchar2,30);
objParam5.Value=this.TextBox5.Text;
Oracle.DataAccess.Client.OracleParameter objParam6=new
Oracle.DataAccess.Client.OracleParameter("p_asset_number",Oracle.DataAccess.Client.OracleDbType.Varchar2,15);
objParam6.Value=this.TextBox6.Text;
Oracle.DataAccess.Client.OracleParameter objParam7=new
Oracle.DataAccess.Client.OracleParameter("p_asset_description",Oracle.DataAccess.Client.OracleDbType.Varchar2,80);
objParam7.Value=this.TextBox7.Text;
Oracle.DataAccess.Client.OracleParameter objParam8=new
Oracle.DataAccess.Client.OracleParameter("p_category",Oracle.DataAccess.Client.OracleDbType.Varchar2,20);
objParam8.Value=this.TextBox8.Text.ToString().Trim();
Oracle.DataAccess.Client.OracleParameter objParam9=new
Oracle.DataAccess.Client.OracleParameter("p_tag_number",Oracle.DataAccess.Client.OracleDbType.Varchar2,15);
objParam9.Value=this.TextBox9.Text;
Oracle.DataAccess.Client.OracleParameter objParam10=new
Oracle.DataAccess.Client.OracleParameter("p_serial_number",Oracle.DataAccess.Client.OracleDbType.Varchar2,15);
objParam10.Value=this.TextBox10.Text;
Oracle.DataAccess.Client.OracleParameter objParam11=new
Oracle.DataAccess.Client.OracleParameter("p_asset_key",Oracle.DataAccess.Client.OracleDbType.Varchar2,1);
objParam11.Value=this.TextBox11.Text;
Oracle.DataAccess.Client.OracleParameter objParam12=new
Oracle.DataAccess.Client.OracleParameter("p_asset_type",Oracle.DataAccess.Client.OracleDbType.Varchar2,15);
objParam12.Value=this.TextBox12.Text;
Oracle.DataAccess.Client.OracleParameter objParam13=new
Oracle.DataAccess.Client.OracleParameter("p_units_assigned",Oracle.DataAccess.Client.OracleDbType.Varchar2,15);
objParam13.Value=this.TextBox13.Text;
Oracle.DataAccess.Client.OracleParameter objParam14=new
Oracle.DataAccess.Client.OracleParameter("p_cost",Oracle.DataAccess.Client.OracleDbType.Varchar2,15);
objParam14.Value=this.TextBox14.Text;
Oracle.DataAccess.Client.OracleParameter objParam15=new
Oracle.DataAccess.Client.OracleParameter("p_dpis",Oracle.DataAccess.Client.OracleDbType.Varchar2,20);
objParam15.Value=this.TextBox15.Text;
Oracle.DataAccess.Client.OracleParameter objParam16=new
Oracle.DataAccess.Client.OracleParameter("p_expense",Oracle.DataAccess.Client.OracleDbType.Varchar2,60);
objParam16.Value=this.TextBox16.Text;
Oracle.DataAccess.Client.OracleParameter objParam17=new
Oracle.DataAccess.Client.OracleParameter("p_location",Oracle.DataAccess.Client.OracleDbType.Varchar2,15);
objParam17.Value=this.TextBox17.Text;
Oracle.DataAccess.Client.OracleParameter objParam18=new
Oracle.DataAccess.Client.OracleParameter("p_book_type_code",Oracle.DataAccess.Client.OracleDbType.Varchar2,15);
objParam18.Value=this.TextBox18.Text;
Oracle.DataAccess.Client.OracleParameter objParam19=new
Oracle.DataAccess.Client.OracleParameter("x_mesg",Oracle.DataAccess.Client.OracleDbType.Varchar2,4000);
objParam19.Direction=ParameterDirection.Output;
this.oracleCommand1.Parameters.Add(objParam1);
this.oracleCommand1.Parameters.Add(objParam2);
this.oracleCommand1.Parameters.Add(objParam3);
this.oracleCommand1.Parameters.Add(objParam4);
this.oracleCommand1.Parameters.Add(objParam5);
this.oracleCommand1.Parameters.Add(objParam6);
this.oracleCommand1.Parameters.Add(objParam7);
this.oracleCommand1.Parameters.Add(objParam8);
this.oracleCommand1.Parameters.Add(objParam9);
this.oracleCommand1.Parameters.Add(objParam10);
this.oracleCommand1.Parameters.Add(objParam11);
this.oracleCommand1.Parameters.Add(objParam12);
this.oracleCommand1.Parameters.Add(objParam13);
this.oracleCommand1.Parameters.Add(objParam14);
this.oracleCommand1.Parameters.Add(objParam15);
this.oracleCommand1.Parameters.Add(objParam16);
this.oracleCommand1.Parameters.Add(objParam17);
this.oracleCommand1.Parameters.Add(objParam18);
this.oracleCommand1.Parameters.Add(objParam19);
=======================================================
and exception is what I mentioned above.
I'm pulling my hair out and the boss is screaming............please help
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Nov 22 08:56:09 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
