Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • World
  • Users
  • Groups
Skins
  • Light
  • Cerulean
  • Cosmo
  • Flatly
  • Journal
  • Litera
  • Lumen
  • Lux
  • Materia
  • Minty
  • Morph
  • Pulse
  • Sandstone
  • Simplex
  • Sketchy
  • Spacelab
  • United
  • Yeti
  • Zephyr
  • Dark
  • Cyborg
  • Darkly
  • Quartz
  • Slate
  • Solar
  • Superhero
  • Vapor

  • Default (No Skin)
  • No Skin
Collapse
Code Project
  1. Home
  2. Database & SysAdmin
  3. Database
  4. Calling Oracle Function via Linked Server returns error [modified]

Calling Oracle Function via Linked Server returns error [modified]

Scheduled Pinned Locked Moved Database
databasequestionoraclecomsysadmin
3 Posts 2 Posters 0 Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • P Offline
    P Offline
    Ph ntom
    wrote on last edited by
    #1

    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

    M 1 Reply Last reply
    0
    • P Ph ntom

      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

      M Offline
      M Offline
      Mycroft Holmes
      wrote on last edited by
      #2

      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

      P 1 Reply Last reply
      0
      • M Mycroft Holmes

        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

        P Offline
        P Offline
        Ph ntom
        wrote on last edited by
        #3

        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.

        1 Reply Last reply
        0
        Reply
        • Reply as topic
        Log in to reply
        • Oldest to Newest
        • Newest to Oldest
        • Most Votes


        • Login

        • Don't have an account? Register

        • Login or register to search.
        • First post
          Last post
        0
        • Categories
        • Recent
        • Tags
        • Popular
        • World
        • Users
        • Groups