exists

[chrisrlong]

I've posted this before, but here it is again.

CREATE OR REPLACE FUNCTION Exists_sf
/*****************************************************************************
**	FUNCTION:	Exists_sf
**
**	DESC:	This function allows one to do an IF Exists_sf(),
**		much like one can do in SQLServer. Since the statement is a string,
**		single quotes must be 'doubled' and binds are not presently handled.
**		Still a fairly useful piece of functionality.
**
**	USAGE:

	DECLARE
		l_TABLE_NAME	VARCHAR2(30);
	BEGIN
		l_TABLE_NAME	:=	'XAPLCN_DEBUG';
		IF ( Exists_sf('
					SELECT
						1
					FROM
						USER_TABLES
					WHERE
						TABLE_NAME	=	'''||
						l_TABLE_NAME||''''
					)
			)
		THEN
			DBMS_OUTPUT.PUT_LINE ('Does exist');
		ELSE
			DBMS_OUTPUT.PUT_LINE ('Does not exist');
		END IF;
	END;

**
**	HISTORY:	NAME						DATE		COMMENT
**				--------------------	--------	------------------------------------
**				Christopher R. Long	04/2001	Initial Code
**
*****************************************************************************/
	(
	i_SQL			IN			VARCHAR2	
	)
RETURN
	BOOLEAN
IS
	-- ---------------
	-- Local Variables 
	-- ---------------
	l_SQL				VARCHAR2(32767);
	l_Exists_Flg	CHAR(1)			;

BEGIN

	l_SQL :=	'
		SELECT
				-- Exists_sf
			COUNT(*)
		FROM
			DUAL
		WHERE
			EXISTS
				(
				'||i_SQL||'
				)	';

	EXECUTE IMMEDIATE
		l_SQL
	INTO
		l_Exists_Flg	;

	RETURN l_Exists_Flg = 1;
 
END Exists_sf;

Remember, a 3-char tab makes everything line up nicely.
- Chris