2 Replies Latest reply on Apr 5, 2012 2:07 PM by stanleycia

    dbms_hs_passthrough.execute_non_query returns 0

    stanleycia
      According to the documentation dbms_hs_passthrough.execute_non_query returns the number of rows affected by the SQL statement in the non-Oracle system.

      My procedure below run without any error

      CRS:=DBMS_HS_PASSTHROUGH.OPEN_CURSOR@MSQL;
      DBMS_HS_PASSTHROUGH.PARSE@MSQL(CRS,
      'INSERT INTO WCUST("cust_code","cust_name","cust_address1","cust_address2","cust_phone1","cust_phone2","cust_fax","cust_email","cust_contact1","cust_contact2",' ||
      '"dest_code","zip_code","flag_pkp","cust_type","cust_npwp","flg_code")' ||
      'VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)');
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@MSQL (CRS,1,TCUSTCODE);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@MSQL (CRS,2,TCUSTNAME);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@MSQL (CRS,3,TCUSTADDRESS1);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@MSQL (CRS,4,TCUSTADDRESS2);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@MSQL (CRS,5,TCUSTPHONE1);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@MSQL (CRS,6,TCUSTPHONE2);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@MSQL (CRS,7,TCUSTFAX);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@MSQL (CRS,8,TCUSTEMAIL);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@MSQL (CRS,9,TCUSTCONTACT1);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@MSQL (CRS,10,TCUSTCONTACT2);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@MSQL (CRS,11,TDESTCODE);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@MSQL (CRS,12,TZIPCODE);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@MSQL (CRS,13,TFLAGPKP);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@MSQL (CRS,14,TCUSTTYPECODE);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@MSQL (CRS,15,TCUSTNPWP);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@MSQL (CRS,16,TFLGCODE);
      RESULT:=DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@MSQL(CRS);
      IF RESULT>0 THEN
      DELETE FROM OSYS_UPDATE_LOG WHERE UPDATE_ID=C.UPDATE_ID;
      ELSE
      DBMS_OUTPUT.PUT_LINE('RESULT=' || RESULT);
      END IF;
      DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@MSQL(CRS);

      and the rows inserted at MSQL successfully, but why the RESULT variable return 0. I thought it should be 1.

      Anyone can help me?