Using define in simple sql statement

Question

Is there a way of defining a section of SQL as a constant for repeated use in a simple oracle SQL statement. In a similar way that can be done in a script and in a similar way to the WITH statement?

Something like

Define meat as "foodtype<>'fruit' and foodtype<>'veg'"

select * from meals where meat

You did not mention that in your question. Anyway, yes you could do it in PL/SQL as I have shown in my answer. But, in plain SQL, NO. — Lalit Kumar B
– Lalit Kumar B, Commented Jan 9, 2015 at 7:34
Where are you running the query from - another client, or from an application? If the latter maybe your application can just manipulate the query string before executing, in Java, C, PHP, even a shell script, etc.? — Alex Poole
– Alex Poole, Commented Jan 9, 2015 at 8:31
My aim is to set up a view which my application can use. If the facility does not exist in plain SQL I thought it would be a relatively simple and useful extension which maybe oracle could consider in the future. — Tony Wolff
– Tony Wolff, Commented Jan 9, 2015 at 8:41

Lalit Kumar B · Accepted Answer · 2015-01-09 07:11:49Z

1

In SQL*Plus you could use DEFINE.

For example,

SQL> define l_str = 'ename = ''SCOTT'''
SQL>
SQL> select empno, deptno from emp where &l_str;
old   1: select empno, deptno from emp where &l_str
new   1: select empno, deptno from emp where ename = 'SCOTT'

     EMPNO     DEPTNO
---------- ----------
      7788         20

SQL>

NOTE DEFINE is a SQL*Plus command. Define sets a user variable or displays its value.

However, not possible in plain SQL as the query won't parse and would throw an error. Since, during parse time, Oracle would expect it to be static, you cannot have it dynamic.

You can use Dynamic SQL in PL/SQL, such that you could have it as a string variable and use it repeatedly in the scope of the session.

For example,

SQL> DECLARE
  2    var_str  VARCHAR2(1000);
  3    var_str1 VARCHAR2(1000);
  4    v_empno emp.empno%type;
  5  BEGIN
  6    var_str  := 'ename = ''SCOTT''';
  7    var_str1 := 'select empno FROM emp WHERE '|| var_str;
  8    EXECUTE IMMEDIATE var_str1 INTO v_empno;
  9
 10    dbms_output.put_line(v_empno);
 11
 12  END;
 13  /
7788

PL/SQL procedure successfully completed.

SQL>

edited Jan 9, 2015 at 7:11

answered Jan 9, 2015 at 7:02

Lalit Kumar B

49.5k13 gold badges105 silver badges132 bronze badges

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

2 Comments

Tony Wolff Over a year ago

I don't need it to be dynamic. I envisage that the first thing that would happen on running the query would be equivalent of search and replace based on the define statement.

Lalit Kumar B Over a year ago

You can use the string variable wherever you need in the procedure scope.

Exhausted · Accepted Answer · 2015-01-09 07:19:40Z

0

As per your question it seems that the condition should be dynamic. It can be acheived in PLSQL Block like below

DECLARE
  v1 varchar2(100) := ' foodtype <> ''fruit'' and foodtype <> ''Veg''';
  mealstyp meals%ROWTYPE; 
BEGIN
  EXECUTE IMMEDIATE 'select * from meals where'||v1 into mealstyp; 
  LOOP
    --your type record
  END LOOP;
END

edited Jan 9, 2015 at 7:19

answered Jan 9, 2015 at 7:01

Exhausted

1,8932 gold badges23 silver badges33 bronze badges

Collectives™ on Stack Overflow

Using define in simple sql statement

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related