Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> ORA-24338: statement handle not executed

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 Received on Wed Dec 13 2006 - 04:39:21 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US