wuyisky
- 浏览: 273160 次
- 性别:
- 来自: 南京
-
文章分类
最新评论
-
Java_zhou：
...
Oracle自定义函数 -
wmj007：
SELECT USER_TAB_COLS.TABLE_NAME ...
Oracle 查询字段详细信息 -
avi9111：
怎么可以个人有一个MQ？ 咁威的
使用c#操作IBM WebSphere MQ -
chouchouzzj：
8个小时。。。让我想起了世界时和北京时之间的差距，MQ存在时区 ...
使用c#操作IBM WebSphere MQ
Long to Varchar2 conversion
You Asked
Hi,
Thanks for your earlier responses....
See, i have one more problem, like i want to retrive the first 4000 characters of the
long datatype, with out using the pl sql code. i just wrote a function like
//
create or replace
function getlong1( p_rowid in rowid)
return varchar2
as
l_data long;
begin
select remarks into l_data from cr_claimheader where rowid = p_rowid;
return substr( l_data, 1, 4000 );
end;
//
to get the first 4000 chars of remarks i am calling this function like
//
select getlong1(rowid) from cr_claimheader where headersrno = 4005
//
Fine, this is working fine when i have the data with less than 4000 chars. in the
particular remarks (long datatype) column.
suppose if the length of data increase means, it returns just null...
how can i solve this problem in a simple manner...
Its very urgent...
Thanks and bye
regards
Periasamy
and we said...
In 8i and up, this will work:
ops$tkyte@ORA8I.WORLD> create or replace function getlong( p_tname in varchar2,
2 p_cname in varchar2,
3 p_rowid in rowid ) return varchar2
4 as
5 l_cursor integer default dbms_sql.open_cursor;
6 l_n number;
7 l_long_val varchar2(4000);
8 l_long_len number;
9 l_buflen number := 4000;
10 l_curpos number := 0;
11 begin
12 dbms_sql.parse( l_cursor,
13 'select ' || p_cname || ' from ' || p_tname ||
' where rowid = :x',
14 dbms_sql.native );
15 dbms_sql.bind_variable( l_cursor, ':x', p_rowid );
16
17 dbms_sql.define_column_long(l_cursor, 1);
18 l_n := dbms_sql.execute(l_cursor);
19
20 if (dbms_sql.fetch_rows(l_cursor)>0)
21 then
22 dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos ,
23 l_long_val, l_long_len );
24 end if;
25 dbms_sql.close_cursor(l_cursor);
26 return l_long_val;
27 end getlong;
28 /
Function created.
ops$tkyte@ORA8I.WORLD>
ops$tkyte@ORA8I.WORLD> drop table t;
Table dropped.
ops$tkyte@ORA8I.WORLD> create table t ( x long );
Table created.
ops$tkyte@ORA8I.WORLD>
ops$tkyte@ORA8I.WORLD> declare
2 data long default rpad( '*', 32000, '*' );
3 begin
4 insert into t values ( data );
5 end;
6 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA8I.WORLD>
ops$tkyte@ORA8I.WORLD> select getlong('T', 'X', rowid) from t;
GETLONG('T','X',ROWID)
------------------------------------------------------------------------------------------
-----------------------------------------
**************************************************.....
 Thanks a lot June 21, 2001 - 3pm Central time zone Reviewer:Periasamy Subramaniyanfrom Bulgaria
We planned to do some alteration... very good timely help... Thanks a lot...  How does this work... September 20, 2002 - 5am Central time zone Reviewer:Subhrajyoti Paulfrom Mumbai, India
Tom,
why does this happen...
pts@ptsmig.world> create table t1
2 (x int, y long);
Table created.
pts@ptsmig.world> create table t2
2 (x int, y varchar2(4000));
Table created.
pts@ptsmig.world> insert into t1 values(1,'abcde');
1 row created.
pts@ptsmig.world> commit;
Commit complete.
pts@ptsmig.world> insert into t2 select * from t1;
insert into t2 select * from t1
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
pts@ptsmig.world> begin
2 for a in (select * from t1) loop
3 insert into t2 values(a.x, a.y);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
pts@ptsmig.world> select * from t2;
X
----------
Y
--------------------------------------------------------------------------------
1
abcde
When I am doing a Select-Insert, the datatype conversion gives an error but when i am using values
clause, it works fine... Why does this happen...?
Followup September 20, 2002 - 8am Central time zone: because the documentation says it will? LONGS cannot be used in an insert into. Put more then 32k in the long and it'll fail miserably in plsql as well. USE CLOBS, do not, repeat, do not, use a long. But Tom, what happens in this case, why PL/SQL succeeds in this case... September 20, 2002 - 8am Central time zone Reviewer:Subhrajyoti Paul
Tom,
I have a table which was created with a long column and has around 1 M records in it... I want
to modify the column type to long. In this process, I am saving the data into temp table with
varchar2 column and modifying the main table. So, I have to do the conversion.
I used the loop to do that.
I just wanted to know why this failed in PURE SQL while it worked in PL/SQL loop....
Thanks
Correction in the last review September 20, 2002 - 8am Central time zone Reviewer:Subhrajyoti Paul
Sorry, I want to modify the column type to VARCHAR2, not long as I wrongly wrote in the previous review... Excellent work February 5, 2003 - 7am Central time zone Reviewer:Chandra S.Reddyfrom India
Great stuff from very great Tom. Is this againest to the coding Standards. February 11, 2003 - 5am Central time zone Reviewer:Sindhufrom Alaska
Tom, Thanks for the code you provided. In one situation I have to send a value from LONG datatyped column to application through Stored Proc as a OUT parameter. SQL> desc t_comment Name Null? Type ----------------------------------------------------- ----- T_NUMBER NOT NULL CHAR(13) USER_COMMENT LONG In a stored Proc I implemented like. procedure sp_xx(in_cTNo IN NUMBER, l_var OUT VARCHAR2) IS select sf_pc_cmt_FNC(in_cTNo) into l_var from t_comment; end; the function "sf_pc_cmt_FNC" is exactly look like the code snippet you provided. But when I implemented this snippent, My client refused to accept this.Below are the client's comments. <<< his comments >>> " I think that there is a misunderstand information for " item #10:Use Oracle Packages and functions for generalized common routines frequently executed" in the Guideline for Oracle Database Application Development for PL/SQL Stored Procedures document. The dbms_sql package can be directory access by the store procedure if the code related to the reference tables and it is not heavy accessing by the applications. We have to discuss the correct the usage of the dbms_sql package. " <<<<>>>> The item#10 is below. . Use Oracle packages and functions for generalized common routines frequently executed since code is efficient. Also, use user-written packages and functions for application shared code. Before you write a user-written package or function, first verify it doesnt already exist with Oracle. One good reason to do this is because when Oracle upgrades to a new release they upgrade their own packages and functions for you. If you write them yourself, you may have more maintenance when converting to a new release. < item #10 over...> Is Oracle providing any package(DBMS_?) for this conversion. Please let me know the way to resolve this and why client is refused to accept this SP. Thanks in advance. Followup February 11, 2003 - 8am Central time zone: I don't understand this at all -- sorry? What exactly is their issue? My routine returns just the first 4000 characters of a long --regardless of how long the long is. IF your longs are all 32k or less your code should be: is l_tmp long; begin select long_col into l_tmp from t_comment where ...; return substr( l_tmp, 1, 4000 ); end; if they EXCEED 32k for just one row -- you have to use dbms_sql in plsql  Converting Long to Varchar2 with minimal redo/undo. June 24, 2003 - 12am Central time zone Reviewer:A readerfrom Japan
Hi Tom, What method would you suggest for converting a long column to varchar2. Our table has about
20 million rows in it. We want to generate least number of archive logs for this. Sqlplus copy
command and PL/SQL insert cannot be used as we cannot use APPEND hint with them(We tried creating a
new table and then inserting into it,we had planed to rename the original table.We plan to create
index's with nologging after the table is created.)
The orginal table and the new table are as given below.
CREATE TABLE AD_MESSAGES
(MSG_NUM NUMBER(5) DEFAULT 0 NOT NULL
,MSG_CONTENT LONG NOT NULL
,MSG_TS DATE NOT NULL
,AD_DOC_DOC_NUM NUMBER(5) NOT NULL
,AD_DOC_ADC_CONTAINER_NUM NUMBER(12) NOT NULL
,AML_MSG_LEVEL VARCHAR2(10) NOT NULL
)
TABLESPACE AD_MESSAGES
/
CREATE TABLE TEMP_AD_MESSAGES
(MSG_NUM NUMBER(5) DEFAULT 0 NOT NULL
,MSG_CONTENT VARCHAR2(4000) NOT NULL
,MSG_TS DATE NOT NULL
,AD_DOC_DOC_NUM NUMBER(5) NOT NULL
,AD_DOC_ADC_CONTAINER_NUM NUMBER(12) NOT NULL
,AML_MSG_LEVEL VARCHAR2(10) NOT NULL
)
TABLESPACE AD_MESSAGE
/
ALTER TABLE TEMP_AD_MESSAGES NOLOGGING
/
DECLARE
CURSOR cur_msg IS
SELECT msg_num
,msg_content
,msg_ts
,ad_doc_doc_num
,ad_doc_adc_container_num
,aml_msg_level
FROM AD_MESSAGES;
--
ln_msg_num TEMP_AD_MESSAGES.MSG_NUM%TYPE;
lc_msg_content TEMP_AD_MESSAGES.MSG_CONTENT%TYPE;
lc_msg_ts TEMP_AD_MESSAGES.MSG_TS%TYPE;
ln_doc_num TEMP_AD_MESSAGES.AD_DOC_DOC_NUM%TYPE;
ln_container_num TEMP_AD_MESSAGES.AD_DOC_ADC_CONTAINER_NUM%TYPE;
lc_msg_lvl TEMP_AD_MESSAGES.AML_MSG_LEVEL%TYPE;
--
ln_count NUMBER(20) := 0;
ln_step NUMBER(10) := 0;
BEGIN
ln_step := 1;
OPEN cur_msg;
LOOP
ln_step := 2;
FETCH cur_msg INTO ln_msg_num,lc_msg_content,lc_msg_ts,ln_doc_num,ln_container_num,lc_msg_lvl;
EXIT WHEN cur_msg%NOTFOUND;
ln_step := 3;
INSERT /*+ APPEND */ INTO TEMP_AD_MESSAGES
(MSG_NUM
,MSG_CONTENT
,MSG_TS
,AD_DOC_DOC_NUM
,AD_DOC_ADC_CONTAINER_NUM
,AML_MSG_LEVEL)
VALUES
(ln_msg_num
,lc_msg_content
,lc_msg_ts
,ln_doc_num
,ln_container_num
,lc_msg_lvl);
ln_step := 4;
IF ln_count > 100000 THEN
COMMIT;
ln_count := 0;
END IF;
ln_step := 5;
ln_count := ln_count + 1;
END LOOP;
ln_step := 6;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line('Error in script. '||SQLERRM ||'ln_step '||ln_step);
END;
This is generating heavy archive logs, as APPEND is ignored with 'values' clause of insert.
What would be your suggestion for this ?
Thanks,
Followup June 24, 2003 - 7am Central time zone: there will be no way to do this without unloading the data and then using sqlldr direct path to reload it or writing an OCI program in C using the direct path apis. create table as select --> won't work, longs. insert /*+ append */ as select --> won't work, longs. copy -> won't work, conventional path. code you write -> won't work, conventional path. so, maybe you can unload to a flat file (see asktom.oracle.com/~tkyte for unloaders) and reload with sqlldr use a function to return a varchar2 June 24, 2003 - 9am Central time zone Reviewer:A readerfrom Japan
Hi Tom, Do you see any problem, if we create a function which takes a rowid parameter and then selects the long column into a varchar2 variable from the rowid and returns this varchar2 variable. After this we just use this function in the insert statement. For eg. insert /*+ APPEND */ into TEMP_AD_MESSAGES select MSG_NUM ,my_to_varchar_func(rowid),MSG_TS ,AD_DOC_DOC_NUM,AD_DOC_ADC_CONTAINER_NUM,AML_MSG_LEVEL from ad_messages; We know that our long field has less than 2000 bytes of length. In test case atleast its appearing to use direct mode insert. Thanks, Followup June 25, 2003 - 9am Central time zone: It would seem to me that in order to use append, you must be worried about gobs of UNDO. If you are worried about gobs of undo -- you must have TONS of data. If you have tons of data, calling a function once per row is going to be slow, I'd rather deal with the UNDO. How to deal with long for this query June 24, 2003 - 6pm Central time zone Reviewer:Yongfrom CA, USA
Tom,
Search_condition is long type in Oracle data dictionary. How can I construct a query to get the
following results without set a condition on a long type
SQL> select constraint_name from user_constraints
2 where search_condition not like '%NOT NULL';
where search_condition not like '%NOT NULL'
*
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG
Followup June 25, 2003 - 11am Central time zone: ops$tkyte@ORA920LAP> create or replace function get_search_condition( p_cons_name in varchar2 ) return varchar2 2 authid current_user 3 is 4 l_search_condition user_constraints.search_condition%type; 5 begin 6 select search_condition into l_search_condition 7 from user_constraints 8 where constraint_name = p_cons_name; 9 10 return l_search_condition; 11 end; 12 / Function created. ops$tkyte@ORA920LAP> ops$tkyte@ORA920LAP> select constraint_name 2 from user_constraints 3 where get_search_condition(constraint_name) like '%NOT NULL%'; CONSTRAINT_NAME ------------------------------ SYS_C004792 SYS_C004794 SYS_C004181 .... Tom, Great! thanks, I get error when I try to use your function January 15, 2004 - 3pm Central time zone Reviewer:Sonali Kelkarfrom waltham, ma
When I do this I get error
create or replace function get_text( p_VIEW_NAME in varchar2 )
return varchar2
is
l_text user_views.text%type;
begin
select text into l_text
from user_views
where view_name = p_VIEW_NAME;
return l_text;
end;
/
show errors
select view_name
from user_views
where get_text(view_name) like '%Company_ID%';
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "xyz.GET_TEXT", line 10
Followup January 16, 2004 - 12am Central time zone: well -- a major issue with this is that varchar2 is always limited to 4,000 characters in SQL --
returning a view > 4,000 characters is going to blow up here.
You would have to:
where do_a_like_on_view_text( view_name, '%Company_ID%' ) > 0
and code more like this:
is
return_val number := 0;
begin
for x in ( select text ... )
loop
if ( x.text like p_inputs )
then
return_val :=1;
end if;
end loop;
return return_val;
end;
how to convert from long datatype to varchar April 21, 2004 - 4am Central time zone Reviewer:diwakarfrom Malaysia
if i want to put like in long data type it gives error so how to do it... thanks Followup April 21, 2004 - 8pm Central time zone: look up one review where I show you that you need to use a function.  CONVERSION FROM LONG TO VARCHAR@ April 21, 2004 - 11pm Central time zone Reviewer:diwakarfrom Malaysia
Hi,
I have seen ur function but it also gives the following error:-
select distinct trigger_name from user_triggers
where FNC_getlong(trigger_name,'%BATCH%') > 0;
where FNC_getlong(trigger_name,'%BATCH%') > 0
*
ERROR at line 2:
ORA-20001: ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SEALINER_PH2B.FNC_GETLONG", line 20
ORA-06512: at line 1
As u can see i'm still getting error
and the function is
CREATE OR REPLACE FUNCTION Fnc_Getlong( p_tRG_name IN VARCHAR2,p_inputs VARCHAR2)
RETURN NUMBER
IS
return_val NUMBER := 0;
BEGIN
FOR x IN ( SELECT trigger_body FROM user_triggers WHERE trigger_name =p_tRG_name )
LOOP
IF ( x.trigger_body LIKE p_inputs )
THEN
return_val :=1;
END IF;
END LOOP;
RETURN return_val;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,SQLERRM);
END;
/
can u pls help me in this
Thanks in Advance
 conversion from long to varchar2 April 22, 2004 - 10pm Central time zone Reviewer:diwakar
Yes it is greater than 32K Followup April 23, 2004 - 11am Central time zone: see above: ..... IF your longs are all 32k or less your code should be: is l_tmp long; begin select long_col into l_tmp from t_comment where ...; return substr( l_tmp, 1, 4000 ); end; if they EXCEED 32k for just one row -- you have to use dbms_sql in plsql ................ however if you have need to search a long, you really ought be looking at a text index: create index t_idx on t(long_column) indextype ctxsys.context; and then select * from t where contains( long_column, 'foobar' ) > 0; Awesome April 15, 2005 - 8pm Central time zone Reviewer:Paul Ramosfrom Santa Barbara, CA USA
Your team rules!!! This site is the cure for the common headache. convert LONG to Varchar2 - THANKS! May 11, 2005 - 1am Central time zone Reviewer:A.Bozrikovfrom Lemesos, Cyprus
I had to convert a dozen of tables having LONG columns (for no reason, as all strings in LONG columns were shorter than 2k!) to VARCHAR2 columns. Tom Kyte's suggestions to others were very helphul, thanks! I did not even have to ask anything. Hi The procedure you used to find search_condition in user_constrains works fine. Is there a way to build a generic procedure/function to search long columns? Such as select * from user_views where search_long(text) like '%XXX%' select * from user_constraints where search_long(search_condition) like '%XXX%' THANKS Followup July 15, 2005 - 8am Central time zone: Here is an excerpt from the book I'm working on now that deals with this question:
<quote>
A question that arises frequently however is - what about the data dictionary in Oracle? It is
littered with LONG columns and this makes using them (the dictionary columns) problematic. For
example, to find all VIEWS that contain the text 'HELLO' is not possible:
ops$tkyte@ORA10G> select *
2 from all_views
3 where text like '%HELLO%';
where text like '%HELLO%'
*
ERROR at line 3:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG
This is not limited to just the ALL_VIEWS view, there are many views:
ops$tkyte@ORA10G> select table_name, column_name
2 from dba_tab_columns
3 where data_type in ( 'LONG', 'LONG RAW' )
4 and owner = 'SYS'
5 and table_name like 'DBA%';
TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
DBA_VIEWS TEXT
DBA_TRIGGERS TRIGGER_BODY
DBA_TAB_SUBPARTITIONS HIGH_VALUE
DBA_TAB_PARTITIONS HIGH_VALUE
DBA_TAB_COLUMNS DATA_DEFAULT
DBA_TAB_COLS DATA_DEFAULT
DBA_SUMMARY_AGGREGATES MEASURE
DBA_SUMMARIES QUERY
DBA_SUBPARTITION_TEMPLATES HIGH_BOUND
DBA_SQLTUNE_PLANS OTHER
DBA_SNAPSHOTS QUERY
DBA_REGISTERED_SNAPSHOTS QUERY_TXT
DBA_REGISTERED_MVIEWS QUERY_TXT
DBA_OUTLINES SQL_TEXT
DBA_NESTED_TABLE_COLS DATA_DEFAULT
DBA_MVIEW_ANALYSIS QUERY
DBA_MVIEW_AGGREGATES MEASURE
DBA_MVIEWS QUERY
DBA_IND_SUBPARTITIONS HIGH_VALUE
DBA_IND_PARTITIONS HIGH_VALUE
DBA_IND_EXPRESSIONS COLUMN_EXPRESSION
DBA_CONSTRAINTS SEARCH_CONDITION
DBA_CLUSTER_HASH_EXPRESSIONS HASH_EXPRESSION
23 rows selected.
that are affected by this. So, what is the solution? If you want to make use of these columns in
SQL - we'll need to convert them to a SQL friendly type. We can use a user defined function for
doing so. This demonstrates how to accomplish a "long substr" function that will allow you to
effectively convert any 4000 bytes of a LONG type into a varchar2, for use with SQL. When we are
done, we'll be able to query:
ops$tkyte@ORA10G> select *
2 from (
3 select owner, view_name,
4 long_help.substr_of( 'select text
5 from dba_views
6 where owner = :owner
7 and view_name = :view_name',
8 1, 4000,
9 'owner', owner,
10 'view_name', view_name ) substr_of_view_text
11 from dba_views
12 where owner = user
13 )
14 where upper(substr_of_view_text) like '%INNER%'
15 /
Meaning, we converted the first 4000 bytes of the VIEW_TEXT column from LONG to a VARCHAR2 and can
now use a predicate on it. Using the same technique, you would be able to implement your own
instr, like and such for LONG types as well. In this book, I'll only be demonstrating how to get
the substring of a LONG type.
The package we will implement has the following specification:
ops$tkyte@ORA10G> create or replace package long_help
2 authid current_user
3 as
4 function substr_of
5 ( p_query in varchar2,
6 p_from in number,
7 p_for in number,
8 p_name1 in varchar2 default NULL,
9 p_bind1 in varchar2 default NULL,
10 p_name2 in varchar2 default NULL,
11 p_bind2 in varchar2 default NULL,
12 p_name3 in varchar2 default NULL,
13 p_bind3 in varchar2 default NULL,
14 p_name4 in varchar2 default NULL,
15 p_bind4 in varchar2 default NULL )
16 return varchar2;
17 end;
18 /
Package created.
Note that one line 2, we've specified AUTHID CURRENT_USER. This makes the package run as the
invoker, with all roles and grants in place. This is important for two reasons. Firstly, we'd
like the database security to no be subverted - this package will only return substrings of columns
you (the invoker) is allowed to see. Secondly, we'd like to install this package once in the
database and have its functionality available for all to use - using invokers rights allows us to
do that. If we used the default security model of PLSQL, definer rights, the package would run
with the privileges of the owner of the package. Meaning it would only be able to see data the
owner of the package could see - which does not include the set of data the invoker is allowed to
see.
The concept behind the function SUBSTR_OF is to take a query that selects at most one row and one
column - the LONG value we are interested in. SUBSTR_OF will parse that query if needed, bind any
inputs to it and fetch the results programmatically, returning the necessary piece of the LONG
value.
The package body, the implementation, begins with two global variables. The G_CURSOR variable
holds a persistent cursor open for the duration of our session. This is to avoid having to
repeatedly open and close the cursor and to avoid parsing SQL more than we need to. The second
global variable, G_QUERY, is used to remember the text of the last SQL query we've parsed in this
package. As long as the query remains constant, we'll just parse it once. So, even if we query
5,000 rows in a query - as long as the SQL query we pass to this function doesn't change, we'll
only have one parse call:
ops$tkyte@ORA10G> create or replace package body long_help
2 as
3
4 g_cursor number := dbms_sql.open_cursor;
5 g_query varchar2(32765);
6
Next in this package is a private function, BIND_VARIABLE, that we'll use to bind inputs passed to
us by the caller. We implemented this as a separate private procedure only to make life easier -
we want to bind only when the input name is NOT NULL. Rather than perform that check 4 times in
the code for each input parameter - we do it once in this procedure:
7 procedure bind_variable( p_name in varchar2, p_value in varchar2 )
8 is
9 begin
10 if ( p_name is not null )
11 then
12 dbms_sql.bind_variable( g_cursor, p_name, p_value );
13 end if;
14 end;
15
Next is the actual implementation of SUBSTR_OF in the package body, it begins with function
declaration from the package specification and the declaration for some local variables. L_BUFFER
will be used to return the value and L_BUFFER_LEN will be used to hold the length returned by an
Oracle supplied function:
16
17 function substr_of
18 ( p_query in varchar2,
19 p_from in number,
20 p_for in number,
21 p_name1 in varchar2 default NULL,
22 p_bind1 in varchar2 default NULL,
23 p_name2 in varchar2 default NULL,
24 p_bind2 in varchar2 default NULL,
25 p_name3 in varchar2 default NULL,
26 p_bind3 in varchar2 default NULL,
27 p_name4 in varchar2 default NULL,
28 p_bind4 in varchar2 default NULL )
29 return varchar2
30 as
31 l_buffer varchar2(4000);
32 l_buffer_len number;
33 begin
Now, the first thing our code does is a sanity check on the P_FROM and P_FOR inputs. P_FROM must
be a number greater than or equal to 1 and P_FOR must be between 1 and 4000 - just like the
built-in function SUBSTR:
34 if ( nvl(p_from,0) <= 0 )
35 then
36 raise_application_error
37 (-20002, 'From must be >= 1 (positive numbers)' );
38 end if;
39 if ( nvl(p_for,0) not between 1 and 4000 )
40 then
41 raise_application_error
42 (-20003, 'For must be between 1 and 4000' );
43 end if;
44
Next, we'll check to see if we are getting a new query that needs to be parsed. If the last query
we parsed is the same as the current query - we can skip this step. It is very important to note
that on line 47 we are verifying that the P_QUERY passed to us is just a SELECT - we will use this
package only to execute SQL SELECT statements, this check validates that for us:
45 if ( p_query <> g_query or g_query is NULL )
46 then
47 if ( upper(trim(nvl(p_query,'x'))) not like 'SELECT%')
48 then
49 raise_application_error
50 (-20001, 'This must be a select only' );
51 end if;
52 dbms_sql.parse( g_cursor, p_query, dbms_sql.native );
53 g_query := p_query;
54 end if;
Now we are ready to bind the inputs to this query. Any non-NULL names that were passed to us will
be "bound" to the query so when we execute it, it finds the right row:
55 bind_variable( p_name1, p_bind1 );
56 bind_variable( p_name2, p_bind2 );
57 bind_variable( p_name3, p_bind3 );
58 bind_variable( p_name4, p_bind4 );
59
And now we are ready to execute the query and fetch the row. Then using
DBMS_SQL.COLUMN_VALUE_LONG, we extract the necessary substring of the long and return it:
60 dbms_sql.define_column_long(g_cursor, 1);
61 if (dbms_sql.execute_and_fetch(g_cursor)>0)
62 then
63 dbms_sql.column_value_long
64 (g_cursor, 1, p_for, p_from-1,
65 l_buffer, l_buffer_len );
66 end if;
67 return l_buffer;
68 end substr_of;
69
70 end;
71 /
Package body created.
And that is it, you should be able to use that package against any legacy LONG column in your
database, allowing you to perform many "where clause" operations that were not possible before, for
example, to find all partitions in your schema such that the HIGH_VALUE has the year 2003 in it:
ops$tkyte@ORA10G> select *
2 from (
3 select table_owner, table_name, partition_name,
4 long_help.substr_of
5 ( 'select high_value
6 from all_tab_partitions
7 where table_owner = :o
8 and table_name = :n
9 and partition_name = :p',
10 1, 4000,
11 'o', table_owner,
12 'n', table_name,
13 'p', partition_name ) high_value
14 from all_tab_partitions
15 where table_name = 'T'
16 and table_owner = user
17 )
18 where high_value like '%2003%'
19 /
TABLE_OWN TABLE PARTIT HIGH_VALUE
--------- ----- ------ ------------------------------
OPS$TKYTE T PART1 TO_DATE(' 2003-03-13 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIAN')
OPS$TKYTE T PART2 TO_DATE(' 2003-03-14 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIAN')
Using this same technique - that of processing the result of a query that returns a single row with
a single LONG column in a function - you can implement your own INSTR, LIKE and so on as needed.
This implementation works well on the LONG type but will not work on LONG RAW types. LONG RAWs are
not piecewise accessible (there is no COLUMN_VALUE_LONG_RAW function in DBMS_SQL). Fortunately,
this is not too serious of a restriction since LONG RAWs are not used in the dictionary and the
need to "substring" so you can search on it is rare. If you do have a need to do so however, you
will not be using PLSQL unless the LONG RAW is 32k or less, there is simply no method for dealing
with LONG RAWS over 32k in PLSQL itself. Java, C, C++, Visual Basic or some other language would
have to be used.
Another approach would be to temporarily convert the LONG or LONG RAW into a CLOB or BLOB using the
TO_LOB built in function and a global temporary table. Your PLSQL procedure could:
Insert into global_temp_table ( blob_column )
select to_lob(long_raw_column) from t where
This would work well in an application that occasionally needed to work with a single LONG RAW
value - you would not want to be continuously doing that however due to the amount of work
involved. If you find yourself needing to resort to this technique frequently, you would
definitely convert the LONG RAW to a BLOB once and be done with it.
</quote>
Updating a value in a long column November 18, 2005 - 1pm Central time zone Reviewer:Ajums T Tfrom Mumbai, India
Hi Tom, We have a table with a long column datatype. In this long column datatype there are several instances of the word "Bombay". We have to replace all instances of "Bombay" with "Mumbai". There are over 15000 such records and its painful to do this one at a time through the front-end. Can we write a PL/SQL block to do this at one go? We are using Oracle 8.1.7. I forgot to mention that earlier. Regards, Ajums TT November 19, 2005 - 12pm Central time zone Reviewer:Ajums TTfrom mumbai, india
The longs are about 5 pages of text in some of the rows. In others where its less than 4000 bytes, I wrote a piece of code in VB and that worked for them. For this 5 pages of text, there are 3 rows that still need to be updated. Followup November 19, 2005 - 1pm Central time zone: If "5 pages" is less then 32k - you can use plsql.
begin
for x in ( select * from t )
loop
x.long_col := replace( x.long_col, .... )
update t set long_col = x.long_col where ....
end loop;
I've no idea what "5 pages" is though.
What is 5 pages. November 19, 2005 - 1pm Central time zone Reviewer:Ajums TTfrom Mumbai, India
5 pages is a printout on A4 size paper. I am certain that one of the rows has crossed 32 K in size. Followup November 19, 2005 - 2pm Central time zone: that still doesn't tell me how big 5 pages is :) but if they are over 32k in size, you need something beyond SQL and PLSQL to deal with them. worked for 4 rows November 19, 2005 - 4pm Central time zone Reviewer:Ajums TTfrom Mumbai, India
Hi Tom, Your technique has updated all but 1 row in my table. I am certain its more than 32 K of data in this particular row. If I need something beyond Pl /SQL or SQL to fix this, where should I be looking at? I have been having a lot of sleepless nights for some time now. Regards, Ajums TT Followup November 19, 2005 - 8pm Central time zone: look to the application that puts it into the database in the first place??? what is it written in. Explicitly checking for SELECT instead of pragma restrict_references(WNDS' November 20, 2005 - 4am Central time zone Reviewer:Jagjeet Singhfrom Delhi, India
Hi, in Long_help package you are checking explicitly checking whether this text is "SELECT" or not. Any specific reason for not using Pragma restrict_reference(....,WNDS); Js Followup November 20, 2005 - 8am Central time zone: because you haven't needed to use restrict references for many releases, so I don't bother. Varchar to Nvarachar migration November 22, 2005 - 12am Central time zone Reviewer:thirumaranfrom INDIA
Hi Tom, Is it possible to move data from a varchar2 datatype in Oracle 9i solaris Server to an NVARACHAR2 oracle 10g R2 Windows 2003 server datatype. Oracle 9i is using the default oracle character set whereas the proposed Oracle 10g R2 Database character set will be AL32UTF8 sets and the National character set will be UTF 16 while creating the database. Thanks in adv Thirumaran Thanks in adv Thirumaran. Char to Nvarchar conversion November 22, 2005 - 2am Central time zone Reviewer:Banufrom INDIA
Dear Tom, 1) Can i move my Table columns data from Char to NVarchar datatype. Examples are available only form CHAR to NCHAR . 2) What is the difference between unicode units VS Bytes. i am not clear with the text from this site: http://www.cs.umb.edu/cs634/ora9idocs/server.920/a96529/ch2.htm#104327 Oracle9i Database Globalization Support Guide Release 2 (9.2) Part Number A96529-01 "When the NCHAR character set is AL16UTF16, ename can hold up to 10 Unicode code units. When the NCHAR character set is AL16UTF16, ename can hold up to 20 bytes." please illustrate with examples. 3)i have a DB with western alphabets(9i) when i move data to a DB which has different Database character set(AL32UTF8) & National character set(UTF-16) what are the impacts ? please guide me on this. regards Banu Followup November 22, 2005 - 8am Central time zone: 1) yes. 2) basically it is saying that unicode takes one OR MORE bytes to store a single character, it is a multi-byte encoding scheme. when you say "varchar2(20)", you get 20 bytes - it might be able to hold 20 characters, then again - maybe not. 3) you'll be going from single byte per character to multi-byte. You might find that 40 characters no longer fits in the varchar2(40) which is 40 bytes as 40 characters might need 80, 120, 160 or even more bytes. converting LONG to NUMBER datatype November 25, 2005 - 6am Central time zone Reviewer:Suvendufrom Bangalore, INDIA
Hi Tom,
Here my problem with high_value column in user_tab_partitions not with the query. If my query is
written wrong way, could you please correct me here.
The scenarion is like:
When I'm getting low range value partition to an existing partition table, I wants find out where
this new partition going to fit in between existing two partitions and after getting it, I need to
:
Split the highest bound partition with new partition key value.
To find out same here is my query, but BETWEEN ..AND .. clause going fail on LONG column
high_value, I tried with utl_raw package relating this thread along with CAST( high_value AS
NUMBER) option too, but not getting solved the problem.
Could you, please provide any good option to do same, if any and to solve the problem here too?
SQL> select lower.partition_name,
lower.high_value,
higher.partition_name,
higher.high_value
from (select partition_name, high_value from user_tab_partitions
where table_name='FACT_TAB') lower,
(select partition_name, high_value from user_tab_partitions
where table_name='FACT_TAB') higher
where lower.partition_name=higher.partition_name
and 1117324802 between lower.high_value and higher.high_value;
ORA-00997: illegal use of LONG datatype
It's in Oracle 9.2 on HP Unix.
Thanking you a lot for your kind consideartion to my question.
Regards,
Suvendu
Followup November 25, 2005 - 10am Central time zone: http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:839298816582#44365156010493 10g DATE datatypes vs. 8i DATE datatypes March 15, 2006 - 6pm Central time zone Reviewer:Sinan Topuzfrom NYC USA
Tom,
The following package was working well on 8i. I migrated it to 10g and I got an error message
"ORA-00932:inconsistent datatypes: expected - got -".
Could you explain me what this means please?
Thanks in advance.
Sinan
VIEW
----
Name Type
--------------------- -------------
SH_HEADER_ID VARCHAR2(30)
SH_DATE DATE
SH_SERIAL VARCHAR2(2)
SH_NUMBER VARCHAR2(30)
SH_STATUS VARCHAR2(2)
SH_TYPE VARCHAR2(3)
SH_CUST_ID VARCHAR2(30)
SH_CUST_PHONE VARCHAR2(30)
SH_PO_NUMBER VARCHAR2(30)
SH_DEPT_CODE VARCHAR2(10)
SH_STORE_CODE VARCHAR2(10)
SH_TERMS_CODE VARCHAR2(5)
SH_SHIPMENT_TYPE VARCHAR2(30)
SH_SHIPTO_ATTN VARCHAR2(100)
SH_SHIPTO VARCHAR2(100)
SH_SHIPTO_ADDRESS1 VARCHAR2(100)
SH_SHIPTO_ADDRESS2 VARCHAR2(100)
SH_SHIPTO_CITY VARCHAR2(100)
SH_SHIPTO_STATE VARCHAR2(5)
SH_SHIPTO_ZIP VARCHAR2(15)
SH_SHIPTO_COUNTRY VARCHAR2(30)
SH_SHIPTO_PHONE VARCHAR2(30)
SH_SHIPTO_FAX VARCHAR2(30)
SH_BILLTO VARCHAR2(100)
SH_BILLTO_ADDRESS1 VARCHAR2(100)
SH_BILLTO_ADDRESS2 VARCHAR2(100)
SH_BILLTO_CITY VARCHAR2(100)
SH_BILLTO_STATE VARCHAR2(5)
SH_BILLTO_ZIP VARCHAR2(15)
SH_BILLTO_COUNTRY VARCHAR2(30)
SH_BILLTO_PHONE VARCHAR2(30)
SH_BILLTO_FAX VARCHAR2(30)
SH_SALESPERSON VARCHAR2(30)
SH_STARTDATE DATE
SH_CANCELDATE DATE
SH_SPECIALINSTRUCTION VARCHAR2(254)
SH_ENTEREDBY VARCHAR2(30)
SH_ENTERED_DATE DATE
SH_UPDATEDBY VARCHAR2(30)
SH_UPDATED_DATE DATE
SH_BFVAT_AMOUNT NUMBER
SH_VAT_AMOUNT NUMBER
SH_NET_AMOUNT NUMBER
SH_CCARDNO VARCHAR2(30)
SH_CCARDEXP DATE
SH_CCARDNAME VARCHAR2(60)
CR_CUST_CODE VARCHAR2(30)
CR_CUST_NAME VARCHAR2(100)
CR_EMAIL VARCHAR2(254)
CR_PHONE VARCHAR2(30)
CR_FAX VARCHAR2(30)
SP_NAME VARCHAR2(100)
SP_ADDRESS1 VARCHAR2(100)
SP_ADDRESS2 VARCHAR2(100)
SP_CITY VARCHAR2(100)
SP_ZIP VARCHAR2(15)
SP_COUNTRY VARCHAR2(30)
SP_PHONE1 VARCHAR2(30)
SP_PHONE2 VARCHAR2(30)
SP_PAGER VARCHAR2(30)
SP_FAX VARCHAR2(30)
SP_EMAIL VARCHAR2(254)
The error occurs when the cursor is opened because if I comment the cursor loop, I don't get the
error message.
Could this be something related to DATE formats???
CREATE OR REPLACE PACKAGE sl_view
AS
PROCEDURE orderlist(
wsid IN VARCHAR2
,pfind IN VARCHAR2 DEFAULT ''
,pbeg_date IN VARCHAR2 DEFAULT to_char(sysdate-15, 'MM/DD/YYYY')
,pend_date IN VARCHAR2 DEFAULT to_char(sysdate, 'MM/DD/YYYY')
,pslsperson IN VARCHAR2 DEFAULT ''
,pstatus IN VARCHAR2 DEFAULT ''
,pfilter IN VARCHAR2 DEFAULT ''
,pgn_beg IN NUMBER DEFAULT 1
);
END sl_view;
/
CREATE OR REPLACE PACKAGE BODY sl_view
AS
/*
LIST ORDERS
*/
PROCEDURE orderlist(
wsid IN VARCHAR2
,pfind IN VARCHAR2 DEFAULT ''
,pbeg_date IN VARCHAR2 DEFAULT to_char(sysdate-15, 'MM/DD/YYYY')
,pend_date IN VARCHAR2 DEFAULT to_char(sysdate, 'MM/DD/YYYY')
,pslsperson IN VARCHAR2 DEFAULT ''
,pstatus IN VARCHAR2 DEFAULT ''
,pfilter IN VARCHAR2 DEFAULT ''
,pgn_beg IN NUMBER DEFAULT 1
) IS
CURSOR csid IS
SELECT SD_SID, SD_USER_CODE, SD_SERVER, SD_LOGIN_DATE, SD_EXP_DATE, SD_IP
FROM SL_SESSION
WHERE SD_SID = wsid ;
RSID CSID%ROWTYPE;
max_line_page integer;
SQ varchar2(5000);
SQW varchar2(5000);
max_page_num integer;
P_FIRST number;
P_LAST number;
F_PAGE integer;
Z_MAXNUM VARCHAR2(25);
max_var number;
page_write_count integer;
TYPE typ1 IS REF CURSOR;
ref_cr typ1;
v_SH_HEADER_ID SL_SLSORDERS.SH_HEADER_ID %TYPE;
v_SH_DATE SL_SLSORDERS.SH_DATE %TYPE;
v_SH_SERIAL SL_SLSORDERS.SH_SERIAL %TYPE;
v_SH_NUMBER SL_SLSORDERS.SH_NUMBER %TYPE;
v_SH_STATUS SL_SLSORDERS.SH_STATUS %TYPE;
v_SH_TYPE SL_SLSORDERS.SH_TYPE %TYPE;
v_SH_CUST_ID SL_SLSORDERS.SH_CUST_ID %TYPE;
v_SH_PO_NUMBER SL_SLSORDERS.SH_PO_NUMBER %TYPE;
v_SH_DEPT_CODE SL_SLSORDERS.SH_DEPT_CODE %TYPE;
v_SH_STORE_CODE SL_SLSORDERS.SH_STORE_CODE %TYPE;
v_SH_TERMS_CODE SL_SLSORDERS.SH_TERMS_CODE %TYPE;
v_SH_SHIPMENT_TYPE SL_SLSORDERS.SH_SHIPMENT_TYPE %TYPE;
v_SH_SALESPERSON SL_SLSORDERS.SH_SALESPERSON %TYPE;
v_SH_STARTDATE SL_SLSORDERS.SH_STARTDATE %TYPE;
v_SH_CANCELDATE SL_SLSORDERS.SH_CANCELDATE %TYPE;
v_SH_ENTEREDBY SL_SLSORDERS.SH_ENTEREDBY %TYPE;
v_SH_ENTERED_DATE SL_SLSORDERS.SH_ENTERED_DATE %TYPE;
v_SH_UPDATEDBY SL_SLSORDERS.SH_UPDATEDBY %TYPE;
v_SH_UPDATED_DATE SL_SLSORDERS.SH_UPDATED_DATE %TYPE;
v_SH_BFVAT_AMOUNT SL_SLSORDERS.SH_BFVAT_AMOUNT %TYPE;
v_SH_VAT_AMOUNT SL_SLSORDERS.SH_VAT_AMOUNT %TYPE;
v_SH_NET_AMOUNT SL_SLSORDERS.SH_NET_AMOUNT %TYPE;
v_CR_CUST_CODE SL_SLSORDERS.CR_CUST_CODE %TYPE;
v_CR_CUST_NAME SL_SLSORDERS.CR_CUST_NAME %TYPE;
v_SP_NAME SL_SLSORDERS.SP_NAME %TYPE;
beg_date DATE;
end_date DATE;
ERR1 EXCEPTION;
ERR_MSG VARCHAR2(250);
pSID SL_SESSION.SD_SID%TYPE;
BEGIN
pSID := wsid;
sl_security_check.checkit(pSID);
max_line_page := TO_NUMBER(get_lookupvalue('SYSTEM', 'MAX_PAGE_LINE'));
OPEN CSID;
FETCH CSID INTO RS
|
相关推荐
### VARCHAR与VARCHAR2之间的联系和区别 在数据库设计与应用中，正确理解并选择合适的数据类型对于确保数据的准确存储及高效查询至关重要。在Oracle数据库中，`VARCHAR`与`VARCHAR2`是两种常见的用于存储可变长度...
### Oracle中的VARCHAR2(BYTE)与VARCHAR2(CHAR)区别详解 #### 一、引言 在Oracle数据库中，`VARCHAR2`是最常用的字符数据类型之一，用于存储变长的字符串。然而，在定义`VARCHAR2`类型时，可以选择指定长度为`BYTE`...
今天我们将深入探讨三种常见的字符串类型：`char`、`varchar`和`varchar2`，它们在不同的数据库系统中有着微妙的区别。 1. **char类型** `char`是一种固定长度的字符串类型，无论实际存储的数据是否填满指定长度，...
### Oracle CHAR, VARCHAR, VARCHAR2 的区别与使用方法 在 Oracle 数据库中，字符串类型是极为常见的数据类型之一，主要用于存储文本数据。其中最常用的三种类型包括：`CHAR`, `VARCHAR`, 和 `VARCHAR2`。这三种...
Oracle 中 char 和 varchar2 的区别 Oracle 中 char 和 varchar2 是两种常用的字符串数据类型，它们之间的区别是很多开发者经常忽视的。下面我们将详细分析 Oracle 中 char 和 varchar2 的区别。 首先，char 是定...
标题"long_to_char.zip_LONG_TO_CHAR_long_long to char"暗示了我们讨论的主题是关于将`LONG`类型（在某些数据库系统中也可能表示为`BIGINT`或`INTEGER`）的字段转换为`CHAR`类型的字段。这种转换可能出于多种原因，...
Oracle SQL 函数进行BLOB转换Varchar2
然而，实际上MySQL的`VARCHAR`类型在存储时会额外使用1到2个字节来记录字符串的长度，这意味着即使使用UTF-8编码，`VARCHAR(50)`仍能存储50个英文字符或16个中文字符。 在MySQL 5.1及以上版本中，`VARCHAR`的最大...
在Oracle数据库中，VARCHAR2()和NVARCHAR2()都是用于存储可变长度的字符串数据类型，但它们之间存在显著的差异，主要涉及到字符编码和存储方式。 首先，VARCHAR2()类型是Oracle数据库中最常见的字符串类型，它允许...
varchar(4000)表示最大能存储4000个字符，而varchar(MAX)则可以存储最多2^31-1个字符，即大约2GB的数据。 在实际应用中，当Python需要与SQL Server交互，特别是涉及大数据量的varchar(MAX)字段时，可能会出现一些...
本文主要探讨的是两种常见字符串类型：`CHAR`和`VARCHAR2`，它们在使用上有何不同，并且会涉及一些相关的Oracle数据库功能。 首先，`CHAR`是定长字符串类型，这意味着当你声明一个`CHAR(10)`字段时，无论你存储的...
本文主要讨论了int、char和varchar这三种常见数据类型在性能上的差异。通常，这些差异在无索引和有索引的情况下的表现会有所不同。 首先，从无索引的全表扫描角度来看，测试结果显示int和bigint（即i8）在查询性能...
2. varchar 转 date 使用 to_date 函数可以将 varchar 类型转换为 date 类型。例如： ```sql select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss') from dual; ``` 这将字符串形式的日期和时间转换为 ...
例如，LONG 类型主要用于不需要作字符串搜索的长串数据，如果要进行字符搜索就要用 varchar2 类型。此外，LONG 数据类型的使用中，要受限于磁盘的大小。 在使用 LONG 数据类型时，还需要注意一些限制。例如，一个表...
Range分区：最为常用，基于属于一个给定连续区间的列值，把多行分配给分区。最常见的是基于时间字段. 基于分区的列最好是整型，如果日期型的可以使用函数转换为整型。varchar日期字段分区sql demo
在MySQL数据库中，`CHAR`和`VARCHAR`是两种常见的字符串数据类型，它们在存储和处理数据时具有不同的效率特点。理解这两种类型的差异对于优化数据库性能至关重要。 `CHAR`是一种固定长度的数据类型，这意味着无论...
- `LONG VARCHAR`：用于存储非常长的字符串，最大长度取决于可用内存。 4. **二进制类型**： - `BINARY(n)`：用于存储固定长度的二进制数据，n为最大字节数。 - `VARBINARY(n)`：用于存储可变长度的二进制数据，...
如果你有一个VARCHAR2类型的字段`ma_datetime`，包含形如'2022-01-01 12:30:45'的日期字符串，你可以先用`TO_DATE()`将其转换为DATE类型，然后再使用`TO_CHAR()`转换回VARCHAR2类型，但此时你可以自定义输出的日期...
2. 字符长度的相似性：如果字段的长度大部分时间相近，如电话号码，即使长度较长，使用CHAR可能更合适，因为长度变化不大，浪费的空间相对有限。 3. 空间效率：如果字段长度差异大，经常存储较短的字符串，VARCHAR能...