Cannot insert data when there is "select ... into..." in stored procedure

From: <cherryparadise001_at_gmail.com>
Date: 4 Jan 2007 02:58:09 -0800
Message-ID: <1167908289.603934.281040@31g2000cwt.googlegroups.com>

Hi All,

I am doing database programming using VB.Net and Oracle DB 8.1.7. when there is command "select ...into..." in the following procedure, it doesn't insert anything and also no error return to the application. When I comment those command, it does insert records even they are null.
What's wrong with my select statement?
I can select value when I run the command in sqlplus. Anyone can have idea?

Thanks in advanced.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROCEDURE INSERT_TEMPLATE_DETAIL(P_TEMPLATE_CODE IN VARCHAR2,
									P_LINE_TYPE IN VARCHAR2,
									P_SEQUENCE IN NUMBER,
									P_ORGANIZATION_CODE IN VARCHAR2,--P_ORGANIZATION_ID IN NUMBER,
									P_SUBINVENTORY_CODE IN VARCHAR2,
									P_ITEM_CODE IN VARCHAR2,--P_ITEM_ID IN FLOAT,
									P_DESC IN VARCHAR2,
									P_CREATED_BY IN NUMBER,
									P_LAST_UPD_BY IN NUMBER,
									P_UOM IN VARCHAR2,
									P_REVISION IN VARCHAR2,
									P_DELIVERY_LOCATION_CODE IN VARCHAR2)
IS--P_DELIVERY_LOCATION_ID IN NUMBER) IS
	P_ORGANIZATION_ID MKL_TEMPLATE_DTL.ORGANIZATION_ID%TYPE := NULL;
	P_ITEM_ID MKL_TEMPLATE_DTL.ITEM_ID%TYPE := 0;
	P_DELIVERY_LOCATION_ID MKL_TEMPLATE_DTL.DELIVERY_LOCATION_ID%TYPE :=
NULL;
	BEGIN

  IF P_ORGANIZATION_CODE = '' OR P_ORGANIZATION_CODE = NULL THEN
	  P_ORGANIZATION_ID := NULL;
  ELSE
  	SELECT ORGANIZATION_ID INTO P_ORGANIZATION_ID FROM

ORG_ORGANIZATION_DEFINITIONS WHERE ORGANIZATION_CODE = P_ORGANIZATION_CODE;
  END IF;
  IF P_ITEM_CODE = '' OR P_ITEM_CODE = NULL THEN     P_ITEM_ID := NULL;
  ELSE
          SELECT INVENTORY_ITEM_ID INTO P_ITEM_ID FROM MTL_SYSTEM_ITEMS WHERE SEGMENT1 = P_ITEM_CODE;
  END IF;
  IF P_DELIVERY_LOCATION_CODE = '' OR P_DELIVERY_LOCATION_CODE = NULL THEN
          P_DELIVERY_LOCATION_ID := NULL;
  ELSE
                SELECT LOCATION_ID INTO P_DELIVERY_LOCATION_ID FROM PO_LOCATIONS_VAL_V WHERE LOCATION_CODE = P_DELIVERY_LOCATION_CODE;   END IF;   INSERT INTO MKL_TEMPLATE_DTL
(MKL_TEMPLATE_DTL,

			TEMPLATE_CODE,
			LINE_TYPE,
			"SEQUENCE",
			ORGANIZATION_ID,
			SUBINVENTORY_CODE,
			ITEM_ID,
			"DESC",
			CREATED_BY,
			CREATED_DATE,
			LAST_UPD_BY,
			LAST_UPD_DATE,
			UOM,
			REVISION,
			DELIVERY_LOCATION_ID)
			VALUES


			(MKL_TEMPLATE_DTL_SEQ.NEXTVAL,

			P_TEMPLATE_CODE,
			P_LINE_TYPE,
			P_SEQUENCE,
 		  P_ORGANIZATION_ID,
			P_SUBINVENTORY_CODE,
			P_ITEM_ID,
			P_DESC,
			P_CREATED_BY,
			sysdate,
			P_LAST_UPD_BY,
			sysdate,
			P_UOM,
			P_REVISION,
			P_DELIVERY_LOCATION_ID);
	END;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Cherry Received on Thu Jan 04 2007 - 04:58:09 CST