ORA-24338: statement handle not executed

From: <sangu_rao_at_yahoo.co.in>
Date: 13 Dec 2006 02:39:21 -0800
Message-ID: <1166006361.871628.105210@j72g2000cwa.googlegroups.com>

Hi,
I am getting the above error when the C++ application is executing the Oracle stored procedure. The SP contains few IN parameters and one OUT parameter which is SYS_REFCURSOR.

The code for the sp is: Could you please suggest what might have gone wrong. When i am executing from the sqlPlus prompt i am able to execute it without any issues.

CREATE OR REPLACE PROCEDURE Archive_GetFileLocation (

v_Inum IN TBLCALLS.INUM%TYPE,
v_Filetype IN FILETYPE.FILETYPEEXT%TYPE,
v_ServerName IN STORAGEDEVICE.MACHINENAME%TYPE,
v_LocalDeviceNames IN VARCHAR2,
v_RemoteDeviceNames IN VARCHAR2,
v_Ref OUT SYS_REFCURSOR --This REF Cursor will ouput ther parameters to

Archiver
)AS

v_ItemID MEDIAITEM.ITEMID%TYPE;
v_FileStoreID FILESTORE.FILESTOREID%TYPE;
v_ContainerID FILESTORE.CONTAINERFILESTOREID%TYPE;

MediaID FILESTORE.MEDIAID%TYPE;
TarLocation FILESTORE.FILENAME%TYPE;
FileLength FILESTORE.LENGTH_%TYPE;
FileOffset FILESTORE.OFFSET%TYPE;
v_MediaTypeID PHYSICALMEDIA.MEDIATYPEID%TYPE; v_MediaType MediaType.MediaTypeName%TYPE;

Dev_OffSet INT;
Dev_Len INT;
Dev_Names_Local VARCHAR2(2000) := v_LocalDeviceNames;
Dev_Names_Remote VARCHAR2(2000) := v_RemoteDeviceNames;
Dev_Sub_Name VARCHAR2(2000);
Dev_Sub_Name_Qote VARCHAR2(2000);

Temp_Check NUMBER(1);

BEGIN

	--Onyx 58503: Will find the ItemID from the INUM value.
	v_ItemID := INUMTOGUID(v_Inum);
	-- find the most recent instance of the file on media currently
associated with the server
	-- the same file may appear multiple times, so select only the most
recent!
	IF LENGTH(v_LocalDeviceNames) > 0 THEN


		--Onyx 58503: This Loop will extract each DeviceName from the Comma

delimited string and populates to a Global Temporary table.


		--If the Device_Names string desn't have the Comma seperated string

then it will fail.
		LOOP
			Dev_OffSet := INSTR(Dev_Names_Local,',',1,1);
			IF Dev_OffSet = 0 THEN
			   INSERT INTO TEMP_DEVNAMES
VALUES(SUBSTR(Dev_Names_Local,2,LENGTH(Dev_Names_Local) - 2));
			   EXIT;
			END IF;
			Dev_Sub_Name_Qote := SUBSTR(Dev_Names_Local,1,Dev_Offset - 1);
			Dev_Len := LENGTH(Dev_Sub_Name_Qote);
			Dev_Sub_Name := SUBSTR(Dev_Sub_Name_Qote,2,Dev_Len - 2);
			Dev_Names_Local := SUBSTR(Dev_Names_Local,Dev_Len + 2);
			INSERT INTO TEMP_DEVNAMES VALUES(Dev_Sub_Name);
		END LOOP;
		BEGIN
			SELECT MediaItemFileStore.FileStoreID,
			FileStore.ContainerFileStoreID,
			FileStore.Length_,
			FileStore.offset,
			MediaItem.MediaID,
			PhysicalMedia.MediaTypeID
			INTO v_FileStoreID,
			v_ContainerID,
			FileLength,
			FileOffset,
			MediaID,
			v_MediaTypeID
			FROM MediaItem,MediaItemFileStore, FileStore, StorageDevice,
PhysicalMedia
			WHERE
			StorageDevice.StorageDeviceName IN (SELECT * FROM TEMP_DEVNAMES) AND

-- This statement will check whether any of the LocalDeviceName are are matching with the StorageDeviceName

			StorageDevice.MachineName = v_ServerName AND
			PhysicalMedia.StorageDeviceID = StorageDevice.StorageDeviceID AND
			PhysicalMedia.MediaID = MediaItem.MediaID AND
			MediaItem.ItemID = v_ItemID AND
			MediaItemFileStore.ItemID = v_ItemID AND --Modified this to check

for v_ItemID since checking for v_ItemID and MediaItemID doesn't make any difference.

			MediaItemFileStore.MediaID = MediaItem.MediaID AND
			MediaItemFileStore.MediaItemId = MediaItem.MediaItemId AND
			FileStore.MovedOffMediaDateTime IS NULL AND
			FileStore.FileStoreID = MediaItemFileStore.FileStoreID AND
			FileStore.FileTypeID IN (SELECT FileTypeID FROM FileType WHERE

FileTypeExt = v_Filetype) AND ROWNUM <= 1 --Enusers only one row will be selected out of the set of rows.

			ORDER BY FileStore.LastModifiedDateTime DESC;
			EXCEPTION
			WHEN NO_DATA_FOUND THEN
			NULL;
		END;
	END IF;
	IF (v_FileStoreID IS NULL) AND LENGTH(v_RemoteDeviceNames) > 0 THEN --
not found
		COMMIT;
		LOOP
			Dev_OffSet := INSTR(Dev_Names_Remote,',',1,1);
			IF Dev_OffSet = 0 THEN
				INSERT INTO TEMP_DEVNAMES
VALUES(SUBSTR(Dev_Names_Remote,2,LENGTH(Dev_Names_Remote) - 2));
				EXIT;
			END IF;
			Dev_Sub_Name_Qote := SUBSTR(Dev_Names_Remote,1,Dev_Offset - 1);
			Dev_Len := LENGTH(Dev_Sub_Name_Qote);
			Dev_Sub_Name := SUBSTR(Dev_Sub_Name_Qote,2,Dev_Len - 2);
			Dev_Names_Remote := SUBSTR(Dev_Names_Remote,Dev_Len + 2);
			INSERT INTO TEMP_DEVNAMES VALUES(Dev_Sub_Name);
		END LOOP;
		BEGIN
			SELECT MediaItemFileStore.FileStoreID,
			FileStore.ContainerFileStoreID,
			FileStore.Length_,
			FileStore.offset,
			MediaItem.MediaID,
			PhysicalMedia.MediaTypeId
			INTO v_FileStoreID,
			v_ContainerID,
			FileLength,
			FileOffset,
			MediaID,
			v_MediaTypeId
			FROM MediaItem,MediaItemFileStore, FileStore, StorageDevice,
PhysicalMedia
			WHERE
			StorageDevice.StorageDeviceName IN (SELECT * FROM TEMP_DEVNAMES) AND

-- This statement will check whether any of the LocalDeviceName are are matching with the StorageDeviceName

			StorageDevice.MachineName = v_ServerName AND
			PhysicalMedia.MediaID  = StorageDevice.LocalPath AND
			PhysicalMedia.MediaID = MediaItem.MediaID AND
			MediaItem.ItemID = v_ItemID AND
			MediaItemFileStore.ItemID = v_ItemID AND --Modified this to check

for v_ItemID since checking for v_ItemID and MediaItemID doesn't make any difference.

			MediaItemFileStore.MediaID = MediaItem.MediaID AND
			MediaItemFileStore.MediaItemId = MediaItem.MediaItemId AND
			FileStore.MovedOffMediaDateTime IS NULL AND
			FileStore.FileStoreID = MediaItemFileStore.FileStoreID AND
			FileStore.FileTypeID IN (SELECT FileTypeID FROM FileType WHERE
FileTypeExt = v_Filetype) AND
			MediaItem.MediaID IN (SELECT MediaId FROM PhysicalMedia WHERE

StorageDeviceID IN (SELECT StorageDeviceID from StorageDevice WHERE MachineName = v_ServerName)) AND

                        ROWNUM <= 1 --Enusers only one row will be selected out of the set of rows.

			ORDER BY LastModifiedDateTime DESC;
			EXCEPTION WHEN NO_DATA_FOUND THEN
			NULL;
		END;
	END IF;
	IF v_FileStoreID IS NOT NULL THEN


		-- now that we have settled on a target FileStoreID, need to pull

some


		-- information about the parent TAR and media.

		BEGIN
			SELECT FileStore.Filename INTO TarLocation FROM FileStore WHERE
FileStoreID = v_ContainerID;
			EXCEPTION
			WHEN NO_DATA_FOUND THEN
			NULL;
		END;


		-- obtain the media type and return that as well; Archiver needs to

know if this is a remote media
		BEGIN
			SELECT MediaType.MediaTypeName INTO v_MediaType FROM MediaType WHERE
MediaType.MediaTypeID = v_MediaTypeID;
			EXCEPTION
			WHEN NO_DATA_FOUND THEN
			NULL;
		END;


		-- This table is a temporary which is intended to store the values

that are going to be returned to the Archiver. This table is created since Archiver is

• not working if we pass these values directly to Ref Cursor by selecting from DULA table. That is the reason we require this table. INSERT INTO Temp_Archive_GetFileLocation (MediaID,TarLocation,FileOffSet,FileLength,MediaType) VALUES (mediaid,tarLocation,fileLength,fileOffset,v_MediaType); OPEN v_Ref FOR SELECT MediaID,TarLocation,FileOffSet,FileLength,MediaType FROM Temp_Archive_GetFileLocation;
  --Delete the entries from the temp table since it should be empty for
  the next time. DELETE FROM Temp_Archive_GetFileLocation; END IF; END Archive_GetFileLocation; /

Received on Wed Dec 13 2006 - 04:39:21 CST