在search_condition的值之后查询Oracle约束 [英] Query Oracle constrain after search_condition's value

问题描述

我想在Oracle SQL中找到一个具有某个search_condition的约束。类似这样：

I want to find a constraint in Oracle SQL that has a certain search_condition. Something like this:

SELECT constraint_name, constraint_type,search_condition
FROM USER_CONSTRAINTS
WHERE table_name ='MYTABLE' AND search_condition = '"myColumn" IS NOT NULL';

问题是我收到Ilegal use of datatype LONG错误。

Problem is i get error "Ilegal use of datatype LONG".

我会喜欢一个工作的替代品。谢谢！

I'd appreciate a working alternative. Thanks!

推荐答案

修改WHERE子句的后半部分如下

Amend the second half of your WHERE clause as follows

SUBSTR(search_condition, 1, 21) = 'whatever you're after'

search_condition是一个LONG数据类型，而是限制了它可以做什么。 SUBSTR的最后一个参数给出了返回的字符串的长度，并根据需要修改。

search_condition is a LONG datatype and that rather limits what you can do with it. the last parameter of the SUBSTR gives the length of the string returned so amend that as needed.

由于我忘记了对WHERE子句的限制，基本上创建了一个PL / SQL函数来执行上述操作，并在WHERE子句中使用它，

Amended as I'd forgotten the restriction on WHERE clauses, basically create a PL/SQL function to do the above and use that in your WHERE clause,

例如

FUNCTION get_long_16(pFormID NUMBER, pSectionItemID NUMBER, pSequence NUMBER)
  RETURN VARCHAR2
  AS
          l_data LONG;
  BEGIN
      SELECT far.text_answer
        INTO l_data
        FROM form_answers_repeating far
       WHERE far.form_id = pFormID
         AND far.section_item_id = pSectionItemID
         AND far.sequence = pSequence;

      RETURN SUBSTR(l_data, 1, 16);
  END;

此处使用....

这篇关于在search_condition的值之后查询Oracle约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！