Writing an IF EXISTS Query in an Oracle Function

Question

I want to be able to have a simple way to check if a query returns anything as an attempt to see if a record exists before I enter it.

So in MS-SQL I would do the following:

IF NOT EXISTS (SELECT * FROM MYTABLE WHERE MYCOLUMN = 'THISVALUE')
BEGIN
//Insert field
END

Easy.

This doesn't work in Oracle and the best alternative I have found is more verbose:

SELECT COUNT(*) INTO TEMPVAR
FROM MYTABLE
WHERE MYCOLUMN = ‘THISVALUE’

IF TEMPVAR = 0
BEGIN
//Insert field
END

So I want to put this into a function so I can do:

IF myPackage.Exists(SELECT * FROM MYTABLE WHERE MYCOLUMN = 'THISVALUE')
BEGIN
//Insert field
END

But here is where I am stuck. How do I execute a query within a case statement when the query has to be generic? I am using EXECUTE IMMEDIATE but no luck.

CASE
WHEN (EXECUTE IMMEDIATE queryString)
THEN 1
ELSE 0
END AS MYVAR

(and then returning that var).

Thanks Damien. I added an example along the lines of myPackage.exists in your post that takes an input of a SELECT COUNT(*) query, but I realize that this may not be sufficient if it a requirement that the exists function accept SELECT * type query strings instead. Can you work with SELECT COUNT(*) as an input or is SELECT * required? Thanks — alexgibbs
– alexgibbs, Commented Apr 20, 2017 at 18:59

alexgibbs · Accepted Answer · 2017-04-20 18:54:31Z

1

You can EXECUTE IMMEDIATE INTO a variable and use that in conditional statements.

An example is below that makes a function whose input is a SELECT COUNT(*) type query string and whose output is TRUE if the count is > 0.

First create a test table:

CREATE TABLE MYTABLE(X NUMBER);
INSERT INTO MYTABLE VALUES (1);

Then create the package:

CREATE PACKAGE MYPACKAGE AS
  FUNCTION DOES_IT_EXIST(QUERY_STRING IN VARCHAR2) RETURN BOOLEAN;
  END MYPACKAGE;
  /

CREATE PACKAGE BODY MYPACKAGE AS
  FUNCTION DOES_IT_EXIST(QUERY_STRING IN VARCHAR2) RETURN BOOLEAN
  IS
    V_ITEM_COUNT NUMBER;
    BEGIN
      EXECUTE IMMEDIATE QUERY_STRING INTO V_ITEM_COUNT;
      RETURN (V_ITEM_COUNT > 0);
      END;

END MYPACKAGE;
/

Then test it:

BEGIN
  IF (MYPACKAGE.DOES_IT_EXIST('SELECT COUNT(*) FROM MYTABLE WHERE X = 1'))
  THEN
    DBMS_OUTPUT.PUT_LINE('1 EXISTS');
  ELSE
    DBMS_OUTPUT.PUT_LINE('1 does not exist');
  END IF;

  CASE WHEN (MYPACKAGE.DOES_IT_EXIST('SELECT COUNT(*) FROM MYTABLE WHERE X = 99'))
  THEN
    DBMS_OUTPUT.PUT_LINE('99 EXISTS');
  ELSE
    DBMS_OUTPUT.PUT_LINE('99 does not exist');
  END CASE;

END;
/

1 EXISTS
99 does not exist

answered Apr 20, 2017 at 18:54

alexgibbs

2,4802 gold badges18 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Damien Over a year ago

Thanks! That's looks like what I needed. I may try and get it to work with SELECT * but SELECT COUNT(*) is very workable too!

vav Over a year ago

in execute immediate you can wrap user query into 'select count(*) from ('||query_string||')', then all user queries would be supported

alexgibbs Over a year ago

Thanks @vav That could provide a clean adapter for SELECT *

alexgibbs Over a year ago

Thanks @Damien vav's comment seems like a succinct adapter between SELECT * and SELECT COUNT(*) if needed.

Damien Over a year ago

Yup! Thanks @vav and @alexgibbs!

Collectives™ on Stack Overflow

Writing an IF EXISTS Query in an Oracle Function

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related