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 Go to next message
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
Re: exceptions were raised when calling procedure in c#.net through ODP for .net [message #77277 is a reply to message #77274] Wed, 19 January 2005 10:03 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
It fails when you create the pkg from c#.net or when you execute it?

Try calling it using explicit format:

FA_ASSETS_TRX.FA_ASSETS_ADDITIONS( p_user_name => 'scott',
p_employee_number => '123', ...)
Previous Topic: Build a webpage connectivity with oracle database using jsp
Next Topic: toplink refuses to install on OraDB 10.1.0.2.0
Goto Forum:
  


Current Time: Thu Mar 28 11:57:16 CDT 2024