How to get data from a LONG data type 1

[cmmrfrds]

Oracle 11G.
I am trying to run this query against the dictionary which has a column of long data type. I want to pull the data out so I can use it. I get an error of illegal use of long data type. Is there a function to pull off the 1st say 100 characters. Toad will interpret the column and display it so there must be some easy way to do this. Thank you for the assistance.

select
table_owner,
table_name,
partition_name,
high_value,
DBMS_LOB.SUBSTR(high_value, 100,1), --column errs.
num_rows,
blocks
from sys.all_tab_partitions
where table_owner = 'SHD'

 

[Thargy]

Sadly, there is no easy way to do this.

Tom Kyte provides a solution, but it involves creating a package to handle the long data type.

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:839298816582

refers.

Regards

T

 

[SantaMufasa]

Cmmrfrds,

Here is a function definition and your original query (slightly modified since I haven't a user "SHD", and the call to my function is simpler than the original) that I believe meet your needs:

CREATE OR REPLACE FUNCTION Partition_High_Value  (owner_in VARCHAR2, table_name_in varchar2, partition_name_in varchar2)  RETURN varchar2 IS
    incoming    varchar2(32767);
    return_hold varchar2(4000);
Begin
    select high_value into incoming from all_tab_partitions
     where table_owner = owner_in
       and partition_name = partition_name_in
       and table_name = table_name_in;
    return_hold := substr(incoming,1,4000);
    return return_hold;
END;
/

Function created.

col hv heading "High_value" format a10
col high_value like hv
col table_owner heading "Table|Owner" format a6
col table_name format a23
col partition_name format a14
select
    table_owner,
    table_name,
    partition_name,
    high_value,
    Partition_High_Value(table_owner,table_name,partition_name) hv, --column errs.
    num_rows,
    blocks
from sys.all_tab_partitions
where table_owner = 'SYSTEM'
/

Table
Owner  TABLE_NAME              PARTITION_NAME High_value High_value   NUM_ROWS     BLOCKS
------ ----------------------- -------------- ---------- ---------- ---------- ----------
SYSTEM LOGSTDBY$APPLY_PROGRESS P0             0          0                   0          0
SYSTEM LOGMNR_DICTSTATE$       P_LESSTHAN100  100        100                 0          0
SYSTEM LOGMNR_DICTIONARY$      P_LESSTHAN100  100        100                 0          0
SYSTEM LOGMNR_OBJ$             P_LESSTHAN100  100        100                 0          0
SYSTEM LOGMNR_USER$            P_LESSTHAN100  100        100                 0          0
SYSTEM LOGMNRC_GTLO            P_LESSTHAN100  100        100                 0          0
SYSTEM LOGMNRC_GTCS            P_LESSTHAN100  100        100                 0          0
SYSTEM LOGMNRC_GSII            P_LESSTHAN100  100        100                 0          0
SYSTEM LOGMNR_TAB$             P_LESSTHAN100  100        100                 0          0
SYSTEM LOGMNR_COL$             P_LESSTHAN100  100        100                 0          0
SYSTEM LOGMNR_ATTRCOL$         P_LESSTHAN100  100        100                 0          0
SYSTEM LOGMNR_TS$              P_LESSTHAN100  100        100                 0          0
SYSTEM LOGMNR_IND$             P_LESSTHAN100  100        100                 0          0
SYSTEM LOGMNR_TABPART$         P_LESSTHAN100  100        100                 0          0
SYSTEM LOGMNR_TABSUBPART$      P_LESSTHAN100  100        100                 0          0
SYSTEM LOGMNR_TABCOMPART$      P_LESSTHAN100  100        100                 0          0
SYSTEM LOGMNR_TYPE$            P_LESSTHAN100  100        100                 0          0
SYSTEM LOGMNR_COLTYPE$         P_LESSTHAN100  100        100                 0          0
SYSTEM LOGMNR_ATTRIBUTE$       P_LESSTHAN100  100        100                 0          0
SYSTEM LOGMNR_LOB$             P_LESSTHAN100  100        100                 0          0
SYSTEM LOGMNR_CDEF$            P_LESSTHAN100  100        100                 0          0
SYSTEM LOGMNR_CCOL$            P_LESSTHAN100  100        100                 0          0
SYSTEM LOGMNR_ICOL$            P_LESSTHAN100  100        100                 0          0
SYSTEM LOGMNR_LOBFRAG$         P_LESSTHAN100  100        100                 0          0
SYSTEM LOGMNR_INDPART$         P_LESSTHAN100  100        100                 0          0
SYSTEM LOGMNR_INDSUBPART$      P_LESSTHAN100  100        100                 0          0
SYSTEM LOGMNR_INDCOMPART$      P_LESSTHAN100  100        100                 0          0

27 rows selected.

You should be able to run both the function and the query with no modifications on your Oracle.

Let us know if you have questions or issues.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[SantaMufasa]

BTW, I don't know what Oracle was thinking when they used a LONG column on HIGH_VALUE...I've never seen a case where HIGH_VALUE is non-numeric...so why on earth would they use a LONG?

In your particular case, CMMRFRDS, It appears that you can just reference HIGH_VALUE in your query without receiving an error.

Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[cmmrfrds]

Thank you Dave, I will try the procedure you wrote. Yes, I can reference the field high_value in the select list, but I need to do some math on the column and that is where I ran into the problem.

 

[SantaMufasa]

Correct...one cannot do a numeric operation on a LONG, even if it contains a valid numeric value, but once the numeric value is in at least a VARCHAR, math is fine.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[cmmrfrds]

Dave, I tested the function and it works great. I need to look at the subpartitions and indexes also. I can makes functions for each, but is there an easy way to change the function to send in the table_name dynamically so that I can use the same function for all the tables? If not, the separate functions will be fine.

Thank you again.

 

[SantaMufasa]

That's a good question, cmmrfrds. Oracle does not allow a PL/SQL (i.e., procedure/function/trigger/et cetera) block to reference a table dynamically (unless you use an "EXECUTE IMMEDIATE" command, which would not buy you much in this case).

What you could do is create a function that contains locally declared functions for each of your possible table references. Then you can pass in table_name parameters, then use "IF"/conditional statements to direct the processing to the appropriate locally defined function.

How does that sound?



Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[sem]

You may utilize old good DBMS_SQL package. It's a bit more cumbersome than already mentioned EXECUTE IMMEDIATE but also a bit more flexible.

Regards, Dima

http://seminihin.narod.ru