在Oracle中将LONG转换为varchar [英] Convert LONG to varchar in Oracle

问题描述

我正在用一堆旧解决方案进行清理.作为清理的一部分，我正在研究从Oracle数据库中删除一些旧触发器.触发器最初是由我的同事设计的，并由第三方顾问安装到位.除了通过我可以访问的Sql Server中的server link之外，我没有对Oracle数据库的任何直接访问.

I'm doing some cleaning up in a bunch of old solutions. As part of the cleanup, I'm looking at removing some old triggers from an Oracle database. The triggers were originally designed by colleagues of mine, and put in place by a third party consultant. I don't have any direct access to the Oracle database except through a server link from a Sql Server where I do have access.

所以我列出了这样的触发器:

So I'm listing the triggers like this:

select * from openquery(SERVERLINKNAME, '
    select *
    from ALL_TRIGGERS
    where owner like ''%OURUSERNAME%''
    order by trigger_name
')

可以，但是问题是ALL_TRIGGERS中的TRIGGER_BODY字段是LONG类型，并且该字段中的数据在Oracle服务器和我的结果集.因此，我只能从此列中看到前100个字符.

This works ok, but the problem is that the TRIGGER_BODY field from ALL_TRIGGERS is of type LONG, and the data in the field gets cut off at some point between the Oracle server and my SSMS resultset. So I can only see the first 100 chars from this column.

如何选择整个TRIGGER_BODY字段?

推荐答案

在Google中搜索oracle convert long to varchar会得到很多结果，其中许多建议使用函数，(临时)表等.所有这些都不在我的具体情况是一个问题，因为不允许在Oracle数据库/服务器中创建任何对象.

Searching through Google for oracle convert long to varchar gives quite a few results, many of which suggests using functions, (temporary) tables etc. All of these are out of the question in my specific case since I'm not allowed to create any objects in the Oracle database/server.

我终于找到了可以针对用例进行修改的示例.该示例来自此页面，有人自称Sayan Malakshinov.修改了他的样本后，我最终得到了这个:

I did finally find a sample that I was able to modify for my use case. The sample is from this page, by someone calling himself Sayan Malakshinov. After modifying his sample, I ended up with this:

select * from openquery(SERVERLINKNAME, '
    select *
    from
    xmltable( ''/ROWSET/ROW'' passing dbms_xmlgen.getXMLType(''
        select
            trigger_name,
            TRIGGER_BODY
        from ALL_TRIGGERS
        where TRIGGER_BODY is not null
            and owner = ''''OURUSERNAME''''
    '')
    columns
        trigger_name varchar2(80),
        TRIGGER_BODY varchar2(4000)
    )
')

这省略了ALL_TRIGGERS中的某些列，但是我得到了整个触发器主体(因为没有一个触发器的长度超过4000个字符).

This omits some columns from ALL_TRIGGERS but I get the whole trigger body (since none of the triggers are longer than 4000 chars).

这篇关于在Oracle中将LONG转换为varchar的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！