在search_condition的值之后查询Oracle约束

发布于 2024-12-08 07:55:22 字数 299 浏览 0 评论 0原文

我想在 Oracle SQL 中找到具有特定 search_condition 的约束。像这样的事情：

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

问题是我收到错误“非法使用数据类型 LONG”。

我希望有一个可行的替代方案。谢谢！

原文

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';

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

I'd appreciate a working alternative. Thanks!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

躲猫猫 2024-12-15 07:55:22

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

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

search_condition 是 LONG 数据类型，这相当限制了您可以用它做什么。 SUBSTR 的最后一个参数给出了返回的字符串的长度，因此请根据需要进行修改。

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

例如

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;

，如此处使用的...

Amend the second half of your WHERE clause as follows

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

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.

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,

For example

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;

As used here....

回复收藏 0 原文