Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> ORA-24338: statement handle not executed
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 toArchiver
v_ItemID MEDIAITEM.ITEMID%TYPE; v_FileStoreID FILESTORE.FILESTOREID%TYPE; v_ContainerID FILESTORE.CONTAINERFILESTOREID%TYPE;MediaID FILESTORE.MEDIAID%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);
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-- This statement will check whether any of the LocalDeviceName are are matching with the StorageDeviceName
--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
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 checkfor 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 WHEREFileTypeExt = 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 checkfor 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 WHEREStorageDeviceID 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 THENthat are going to be returned to the Archiver. This table is created since Archiver is
-- 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
![]() |
![]() |