I am researching options around Usage Reporting and I came a...

[Kristi Carter]

I am researching options around Usage Reporting and I came across the following in multiple articles. This SQL query does not work for me, assuming it is because I need to run this using Oracle instead of SQL Server. Anyone know how to make this same option working using Oracle SQL

select t2.appname,t2.objname,t2.prt_path,t2.lastmodon,t2.lastmodby

from wf_content_revs t1

inner join wf_reposobj t2 on t1.obj_handle = t2.handle

where lower(cast(cast(t1.bcontent as varbinary(max)) as varchar(max))) like %&TAB_NAME.EVAL.%

I get the below message when I run this and it shows the error on the where clause line.

 

Or if you have any other suggestions on how to be able to see the information in the BCONTENT area HUGECLOB data type. I would like to be able to view and also search within that field.

[Toby Mills]

Hi Kristi

I dont have Oracle around to give you a solid answer, but I hope this might be a clue:

check if clob contains string oracle - Stack Overflow

 

You can use DBMS_LOB.INSTR( clob_value, pattern [, offset [, occurrence]] ):

SELECT *

FROM your_table

WHERE DBMS_LOB.INSTR( clob_column, string to match ) > 0;

 

Apparently the dbms_lob functions are the key here.

Assuming were on the right track with the SQL query you have shown, youd alter it kind of like this:

select t2.appname,t2.objname,t2.prt_path,t2.lastmodon,t2.lastmodby

from wf_content_revs t1

inner join wf_reposobj t2 on t1.obj_handle = t2.handle

where DBMS_LOB.INSTR( t1.bcontent, &TAB_NAME.EVAL ) > 0

t1.bcontent as varbinary(max)) as varchar(max))) like %&TAB_NAME.EVAL.%

Thats just a guess - you might want to search Oracle docs on the dbms_lob functions. Im not that familiar with this so Im just going off research on the web.

Also - IBI would prefer you that you dont mess directly with the repository. I think youre fine reading it as long as you dont try to write anything in there.

Let us know if you find a query that works for you.

Thanks

Toby

[Toby Mills]

Hey Kristi

Im looking back at my 8105m machine - it appears the data type when you install with oracle might be varchar2 instead of a RAW for the bcontent column. You might need to alter your WHERE test a little to be more like:

DBMS_LOB.INSTR(t1.bcontent, UTL_RAW.CAST_TO_RAW(&SEARCH_STRING.EVAL), 1, 1) > 0;

Remember youre bypassing the UOA rules / security when youre hitting the repository directly. Just be careful who can hit your reports

Good luck!

Toby

[Pawan Vuppala]

We built WF Search app recently and heres what we used but ours is MSSQL. We actually ended up using this as DB_EXPR to avoid SQL Passthru later.

SELECT

T2.PRT_PATH + / + T2.OBJNAME AS WFCPATH,

T2.OBJNAME,

T2.PRT_PATH,

T1.OBJ_HANDLE,

CAST(CAST(BCONTENT AS VARBINARY(MAX)) AS VARCHAR(MAX)) AS CODE

FROM dbo.WF_CONTENT_REVS T1

INNER JOIN dbo.WF_REPOSOBJ T2

ON T1.OBJ_HANDLE = T2.HANDLE

WHERE PRT_PATH LIKE &DOMAIN_TOKEN|%

AND CAST(CAST(BCONTENT AS VARBINARY(MAX)) AS VARCHAR(MAX)) LIKE %&SEARCH_STRING|% ;

[Kristi Carter]

Thanks everyone for the suggestions, nothing seems to be working yet though.

[Franciscus van Dortmont]

With Oracle try this

ENGINE SQLORA SET DEFAULT_CONNECTION <YOUR WF REPOSITORY DB CONNECTIONNAME>

 

SQL SQLORA

select t2.appname,t2.objname,t2.prt_path,t2.lastmodon,t2.lastmodby

 

from wf_content_revs t1

inner join wf_reposobj t2 on t1.obj_handle = t2.handle

where dbms_lob.instr(t1.bcontent, utl_raw.cast_to_raw('YOUR SEARCH STRING'))>0;

 

TABLE FILE SQLOUT

PRINT *

END

 

Note that this is case sensitive

[Kristi Carter]

Thanks Frans.

I also got this information below from one of our DBAs.

Here are a couple options for you, and a proof that the INSTR function is working.

If you use the TO_CLOB function in Toad, your results grid will show the text (HUGECLOB). If you doble-click that cell, you can view the formatted contents of the cell.

I also shouwed that with the function DBMS_LOB.SUBSTR function, you can return text in the grid, but this is limited to the max VARCHAR2 size of 4000 characters.

select t2.appname

,t2.objname

,t2.prt_path

,t2.lastmodon

,t2.lastmodby

,t1.bcontent blob_val

,to_clob( t1.bcontent ) clob_val

,dbms_lob.substr( to_clob( t1.bcontent ), 4000, 1 ) varchar2_val

,dbms_lob.getlength(t1.bcontent) len

from repos.wf_content_revs t1

inner join repos.wf_reposobj t2

on t1.obj_handle = t2.handle

where dbms_lob.instr( t1.bcontent, utl_raw.cast_to_raw(g_theApp.restoreUserState), 1, 1 ) > 1

[Thomas Stockwell]

Try this, I use this for several of my reports:

utl_raw.cast_to_varchar2(dbms_lob.substr(wf_content_revs.bcontent))

Then you should be able to use LIKE as you would use a varchar.

I also use the following to search without having to limit my search to the first 4000 characters:

dbms_lob.instr(wf_content_revs.bcontent,utl_raw.cast_to_raw(searchString),1,1) > 0