Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.4k views
in Technique[技术] by (71.8m points)

oracle - How to use a package constant in SQL SELECT statement?

How can I use a package variable in a simple SELECT query statement in Oracle?

Something like

SELECT * FROM MyTable WHERE TypeId = MyPackage.MY_TYPE

Is it possible at all or only when using PL/SQL (use SELECT within BEGIN/END)?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

You can't.

For a public package variable to be used in a SQL statement, you have to write a wrapper function to expose the value to the outside world:

SQL> create package my_constants_pkg
  2  as
  3    max_number constant number(2) := 42;
  4  end my_constants_pkg;
  5  /

Package created.

SQL> with t as
  2  ( select 10 x from dual union all
  3    select 50 from dual
  4  )
  5  select x
  6    from t
  7   where x < my_constants_pkg.max_number
  8  /
 where x < my_constants_pkg.max_number
           *
ERROR at line 7:
ORA-06553: PLS-221: 'MAX_NUMBER' is not a procedure or is undefined

Create a wrapper function:

SQL> create or replace package my_constants_pkg
  2  as
  3    function max_number return number;
  4  end my_constants_pkg;
  5  /

Package created.

SQL> create package body my_constants_pkg
  2  as
  3    cn_max_number constant number(2) := 42
  4    ;
  5    function max_number return number
  6    is
  7    begin
  8      return cn_max_number;
  9    end max_number
 10    ;
 11  end my_constants_pkg;
 12  /

Package body created.

And now it works:

SQL> with t as
  2  ( select 10 x from dual union all
  3    select 50 from dual
  4  )
  5  select x
  6    from t
  7   where x < my_constants_pkg.max_number()
  8  /

         X
----------
        10

1 row selected.

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...