Calling Oracle Function via Linked Server returns error [modified]
-
I am trying to call Oracle function via Linked Server, while executing it return the error. Can some one tell what is going wrong with this code, keep in mind that the parameter passed are of exactly same type defined in Oracle function except the date type parameter which I am passing as string because there is no equivalent. -- returned error -- OLE DB provider "MSDAORA" for linked server "ERPUDEV" returned message "ORA-06550: line 1, column 18: PLS-00382: expression is of wrong type ORA-06550: line 1, column 7: PL/SQL: Statement ignored -- calling part -- DECLARE @source_code VARCHAR(30) DECLARE @source_lineid INT DECLARE @feeder_itemcode VARCHAR(40) DECLARE @sales_price INT DECLARE @fdr_org_code VARCHAR(10) DECLARE @fdr_subinv_code VARCHAR(10) DECLARE @txn_qty INT DECLARE @txn_date VARCHAR(30) DECLARE @feeder_txntype VARCHAR(30) DECLARE @fdr_subinvcode VARCHAR(10) DECLARE @fdr_trnsubinvcode VARCHAR(10) DECLARE @item_desc VARCHAR(10) DECLARE @ErrMsg VARCHAR(100) DECLARE @RetVal INT SET @source_code = 'SSP-SALES' SET @source_lineid = 8728 SET @txn_no = '15059' SET @txn_no2 = '29193' SET @feeder_itemcode = 'SSP1032' SET @sales_price = 12 SET @fdr_org_code = 'S' SET @fdr_subinvcode = 'S-SALES' SET @txn_qty = 1 SET @txn_date = '13/MAR/2010' SET @feeder_txntype = 'S-SALES_ISSUE' SET @fdr_trnsubinvcode = '' EXEC ('BEGIN ? := xx_eginv_f_dfs_erp_insert(?,?,?,?,?,?,?,?,?,?,?,?);END;', @RetVal,@source_code, @source_lineid, @txn_no, @feeder_itemcode, @sales_price,@fdr_org_code, @fdr_subinvcode, @txn_qty, @txn_date, @feeder_txntype, @fdr_trnsubinvcode,@ErrMsg OUTPUT) AT ERPUDEV
The Phantom.
modified on Sunday, March 14, 2010 8:19 AM
-
I am trying to call Oracle function via Linked Server, while executing it return the error. Can some one tell what is going wrong with this code, keep in mind that the parameter passed are of exactly same type defined in Oracle function except the date type parameter which I am passing as string because there is no equivalent. -- returned error -- OLE DB provider "MSDAORA" for linked server "ERPUDEV" returned message "ORA-06550: line 1, column 18: PLS-00382: expression is of wrong type ORA-06550: line 1, column 7: PL/SQL: Statement ignored -- calling part -- DECLARE @source_code VARCHAR(30) DECLARE @source_lineid INT DECLARE @feeder_itemcode VARCHAR(40) DECLARE @sales_price INT DECLARE @fdr_org_code VARCHAR(10) DECLARE @fdr_subinv_code VARCHAR(10) DECLARE @txn_qty INT DECLARE @txn_date VARCHAR(30) DECLARE @feeder_txntype VARCHAR(30) DECLARE @fdr_subinvcode VARCHAR(10) DECLARE @fdr_trnsubinvcode VARCHAR(10) DECLARE @item_desc VARCHAR(10) DECLARE @ErrMsg VARCHAR(100) DECLARE @RetVal INT SET @source_code = 'SSP-SALES' SET @source_lineid = 8728 SET @txn_no = '15059' SET @txn_no2 = '29193' SET @feeder_itemcode = 'SSP1032' SET @sales_price = 12 SET @fdr_org_code = 'S' SET @fdr_subinvcode = 'S-SALES' SET @txn_qty = 1 SET @txn_date = '13/MAR/2010' SET @feeder_txntype = 'S-SALES_ISSUE' SET @fdr_trnsubinvcode = '' EXEC ('BEGIN ? := xx_eginv_f_dfs_erp_insert(?,?,?,?,?,?,?,?,?,?,?,?);END;', @RetVal,@source_code, @source_lineid, @txn_no, @feeder_itemcode, @sales_price,@fdr_org_code, @fdr_subinvcode, @txn_qty, @txn_date, @feeder_txntype, @fdr_trnsubinvcode,@ErrMsg OUTPUT) AT ERPUDEV
The Phantom.
modified on Sunday, March 14, 2010 8:19 AM
So have you tried different date formats, I would try 'yyyy-mm-dd' as the second most unambiguous format, you have tried the first, Oracle may not like characters in the data and spits the dummy. Note this is not a solution, just another avenue to try. I have not used Oracle in a decade or more.
Never underestimate the power of human stupidity RAH
-
So have you tried different date formats, I would try 'yyyy-mm-dd' as the second most unambiguous format, you have tried the first, Oracle may not like characters in the data and spits the dummy. Note this is not a solution, just another avenue to try. I have not used Oracle in a decade or more.
Never underestimate the power of human stupidity RAH
Thanks for the comments. A little more to understand the problem, I'v found that the error message was because of the returned type boolean (@RetVal). -- oracle implementation --====================== CREATE OR REPLACE FUNCTION TEST_ERP_INSERT ( P_SOURCE_CODE IN VARCHAR2 , P_SOURCE_LINE_ID IN NUMBER , P_TRANSACTION_NUMBER IN VARCHAR2 , P_FEEDER_ITEM_CODE IN VARCHAR2 , P_SALES_PRICE IN NUMBER , P_FEEDER_ORGANIZATION_CODE IN VARCHAR2 , P_FEEDER_SUBINVENTORY_CODE IN VARCHAR2 , P_TRANSACTION_QUANTITY IN NUMBER , P_TRANSACTION_DATE IN VARCHAR2 , P_FEEDER_TRANSACTION_TYPE IN VARCHAR2 , P_FEEDER_TRANSFER_SUBINV_CODE IN VARCHAR2 , p_errmesg OUT VARCHAR2 ) RETURN BOOLEAN IS BEGIN RETURN 1; -- EXCEPTION -- WHEN OTHERS THEN -- p_errmesg := 'Error:' || SQLERRM; -- RETURN 0; END; -- calling from sql server --======================== DECLARE @ErrMsg VARCHAR(1000) DECLARE @RetVal SMALLINT EXEC ('BEGIN ? := TEST_ERP_INSERT(?,?,?,?,?,?,?,?,?,?,?,?);END;', @RetVal,'SSP-SALES', 8728, '15059', 'SSP1032', 12,'S', 'S-SALES', 1, '13/MAR/2010', 'S-SALES_ISSUE', '', @ErrMsg OUTPUT) AT ERPUSTG; Any idea? I can't change the returned type because its a different system.
The Phantom.