Home » SQL & PL/SQL » SQL & PL/SQL » Best way of long to long column insertion in sql
Best way of long to long column insertion in sql [message #324029] Fri, 30 May 2008 06:02 Go to next message
kaushikcse_b0613
Messages: 3
Registered: May 2008
Junior Member
Hi

Please help me in finding a way to copy LONG TO LONG Column insertion in one single sql query


I have some values in the table TBL_NEWS which have one and only one column of LONG data type
CREATE TABLE TBL_NEWS(NEWS LONG);
/
I created one more table for taking backup of TBL_NEWS
CREATE TABLE TMP_LONG(TMP_NEWS LONG);
/

Now i have to insert the records from TBL_NEWS TO TMP_LONG

AS we cannot use this statement
INSERT INTO TMP_LONG AS SELECT * FROM TBL_NEWS.

I made use of DBMS_METADATA_UTIL.LONG2VARCHAR

LOGGED IN SYS:
Executed the package DBMSMETU from rdbms/admin area bothe package and package body....

GRANT Execute on DBMS_METADATA_UTIL TO SCHEMA_NAME;

LOGGED IN SCHEMA WHERE THE LONG TABLES EXIST:

now i tried this way:

INSERT INTO TMP_LONG
(SELECT sys.dbms_metadata_util.long2varchar(4000,'TBL_NEWS','NEWS',rowid));

this insert succeeded but the values from LONG column are not inserted into the
TMP_LONG Table's TMP_NEWS Column.

I tried this way again:
INSERT INTO TMP_LONG
(SELECT sys.dbms_metadata_util.long2varchar(length =>4000,tab =>'TBL_NEWS',col =>'NEWS',row =>rowid));

Still the LONG Column data not get inserted into other LONG COlumn.


Kindly let me know the way i can insert a long column data into other long column data.
I cannot write a procedure/function to handle this..using local long variables
as this insert being happened using execute immediate.


Please guide me with any sql utility or function to long to long insertion at query level.


Thanks in Advance,
Kaushik B.




Re: Best way of long to long column insertion in sql [message #324032 is a reply to message #324029] Fri, 30 May 2008 06:08 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#i54330

Long datatype is deprecated and it is supported only for backward compatibility. For more information about alternative see above.

Regards

Raj
Re: Best way of long to long column insertion in sql [message #324038 is a reply to message #324029] Fri, 30 May 2008 06:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Personally, I'd use BLOB/CLOB instead of LONG.

However, pl/Sql is your friend here:
SQL> create table tmp_long_1 (col_1 number, col_2 long);

Table created.

SQL> 
SQL> create table tmp_long_2 (col_1 number, col_2 long);

Table created.

SQL> 
SQL> insert into tmp_long_2 values (1,rpad('A',5000));

1 row created.

SQL> insert into tmp_long_2 values (1,rpad('A',40000));

1 row created.

SQL> 
SQL> begin
  2    for rec in (select col_1,col_2 from tmp_long_2) loop
  3      insert into tmp_long_1 (col_1,col_2) values (rec.col_1,rec.col_2);
  4    end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> 
SQL> select count(*) from tmp_long_2;

  COUNT(*)
----------
         2
Re: Best way of long to long column insertion in sql [message #324041 is a reply to message #324038] Fri, 30 May 2008 06:26 Go to previous messageGo to next message
kaushikcse_b0613
Messages: 3
Registered: May 2008
Junior Member


Hi,

Thanks for the response...

Tha application i work on is very older...
and as i said earlier...i cannot use the pl/sql
as this insertion happening dynamically
l_str :='INSERT INTO TMP_LONG
(SELECT sys.dbms_metadata_util.long2varchar(length =>4000,tab =>'TBL_NEWS',col =>'NEWS',row =>rowid))';
EXECUTE_IMMEDIATE l_str;

And i cannot use any pl/sql tables or records to handle this..

all i can do is find any sql utility that can convert the long to varchar internally and insert into another long column.

Thanks in Advance.
Kaushik B.
Re: Best way of long to long column insertion in sql [message #324051 is a reply to message #324041] Fri, 30 May 2008 07:39 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
and as i said earlier...i cannot use the pl/sql

Quote:
l_str :='INSERT INTO TMP_LONG
(SELECT sys.dbms_metadata_util.long2varchar(length =>4000,tab =>'TBL_NEWS',col =>'NEWS',row =>rowid))';
EXECUTE_IMMEDIATE l_str;


Don't you think these two statements are contradicting each other or am I missing something here.

Have a look at the discussion mentioned in this link.

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

Regards

Raj

[EDIT:] Link added

[Updated on: Fri, 30 May 2008 07:42]

Report message to a moderator

Re: Best way of long to long column insertion in sql [message #324053 is a reply to message #324041] Fri, 30 May 2008 08:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Seeing as you're using Pl/Sql at the moment, I choose to ignore you protestations that you can't use Pl/Sql.

Why not replace that piece of Dynamic SQL with a piece of Pl/Sql that looks a lot like this:

BEGIN
  FOR rec IN (SELECT news FROM tbl_news WHERE <however you identify the row) LOOP
    INSERT INTO tmp_long VALUES rec.news;
  END LOOP;
END;
/


That's got to be better than using undocumented oracle internal packages, plus this will work when the field is longer than 4000 chrs.

Do you have any rows with a LONG > 4000 chrs?
Re: Best way of long to long column insertion in sql [message #324055 is a reply to message #324053] Fri, 30 May 2008 08:17 Go to previous message
kaushikcse_b0613
Messages: 3
Registered: May 2008
Junior Member
Hi,

Thanks for the response...

Please let me know whether long to long column insertion can be happened using any built in packages /functions in sql query itself.

As i wrote the construction of the insert script and execute immediate are not happening one after the other
it is between huge validations...


We have a packaged procedure assigned to a job
for removing unnecessary data(Purging) after business validations.

This package will construct the inserts scripts taking the structures of the tables along with filter conditions and dynamically executes the query

There are only three or four tables out of 3500 tables...
So, I am checking whether this is possible to handle the long columns with a deviation from regular workflow only in the insert script (sql level).

The architecture of the workflow making me to restrict this deviation only in a sql query...
like changing a part of entire query for long columns....

Kindly let me know if it is not clear

Thanks in advance.

Kaushik B.

[Updated on: Fri, 30 May 2008 08:21]

Report message to a moderator

Previous Topic: email program
Next Topic: Oracle PL/SQL Collection
Goto Forum:
  


Current Time: Fri Dec 09 04:14:40 CST 2016

Total time taken to generate the page: 0.06395 seconds