CODEWITHSUNDEEP
oracle-database
user1429080
user1429080

Reputation: 9166

Convert LONG to varchar in 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
')

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.

How can select the whole TRIGGER_BODY field?

Upvotes: 3

Views: 7226

Answers (2)

Andreas Covidiot
Andreas Covidiot

Reputation: 4755

In case you like to have it a little easier to read an execute parts of it, I transformed the version from user1429080 using literal escape syntax using § and [/] for escaping the nested strings:

select * from openquery(SERVERLINKNAME, q'§   
    select *
    from
    xmltable( '/ROWSET/ROW' passing dbms_xmlgen.getXMLType(q'[
        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)
    )
§')

And just in case you want to inspect the code in sql result views (that often support displaying only single CHR(13) as visual line feeds) this is very useful:

select  translate( trigger_body, CHR(10)||CHR(13), CHR(13)||CHR(13) ) as body
from xmltable( 
  '/ROWSET/ROW' passing dbms_xmlgen.getXMLType(
    q'[
      select TRIGGER_BODY from ALL_TRIGGERS
      where TRIGGER_NAME='<MY_TRIG_NAME>'  -- or any other condition
    ]')
  columns TRIGGER_BODY varchar2(4000))

Upvotes: 1

user1429080
user1429080

Reputation: 9166

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.

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

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

Upvotes: 4

Related Questions