The long type is strongly deprecated in Oracle, but in order to solve the immediate need, it is forced to use a long type, simple ah, direct insert on the line. However, it is not thought that the long type does not support like or directly to_char.
Regret, want to change the long back to the VARCHAR2 type, but can not directly modify, otherwise it will be an error, reminders must be empty data, in order not to empty the data, comprehensive information on the network summarized the following solutions:
The message type in table jivemsg long needs to be replaced by VARCHAR2:
3: Import the data from the table jivemsg into the table JIVEMSG2
This step is critical because the long type cannot be converted directly to VARCHAR2.
So this side uses a function written by someone else to solve.
INSERT into JIVEMSG2 Select Uri,sender,receiver,creationdate,messagetype,messagesize, Long_to_char (rowid, ' ucstar6 ', ' Jivemsg ', ' message '-Fkdomain,version,flags,receivername,sendername from jivemsg msg;
Function:
* * Where In_rowid is a row id,in_owner the account name for the database, In_table_name is the database table name, in_column a long type of table field name for the database * *
CREATE OR REPLACE FUNCTION Long_to_char (In_rowid rowid,in_owner
Varchar,in_table_name varchar,in_column varchar2)
return varchar AS
TEXT_C1 varchar2 (32767);
Sql_cur VARCHAR2 (2000);
--
Begin
Sql_cur: = ' SELECT ' | | in_column| | ' from
'|| in_owner| | '. ' | | in_table_name| | ' WHERE rowID =
'|| Chr (39) | | in_rowid| | Chr (39);
Dbms_output.put_line (sql_cur);
Execute immediate sql_cur into TEXT_C1;
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.