Copy BLOB data from ome oracle table to another in the same database

I tried to Copy BLOB data from GBEN_BLOB1(table) to GBEN_BLOB2(table) in the same database. Passing the column lists through the procedure's parameters and i get the following error message:

ERROR MESSAGE:
PL/SQL:ORA-01732 data manipulation operation not legal on this view



SQL> DESC GBEN_BLOB1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FILE_ID                                   NOT NULL NUMBER
 FILE_NAME                                          VARCHAR2(256)
 FILE_CONTENT_TYPE                         NOT NULL VARCHAR2(256)
 FILE_DATA                                          BLOB


SQL> DESC GBEN_BLOB2;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FILE_ID                                   NOT NULL NUMBER
 FILE_NAME                                          VARCHAR2(256)
 FILE_CONTENT_TYPE                         NOT NULL VARCHAR2(256)
 FILE_DATA                                          BLOB



=======BeginCopy BLOB procedure======
CREATE OR REPLACE PROCEDURE GBEN_COPY_BLOB
(
--P_table1 IN VARCHAR2,
--P_table2 IN VARCHAR2,
P_column1 IN VARCHAR2,
P_column2 IN VARCHAR2,
P_column3 IN VARCHAR2,
P_column4_blob IN VARCHAR2,

P_column5 IN VARCHAR2,
P_column6 IN VARCHAR2,
P_column7 IN VARCHAR2,
P_column8_blob IN VARCHAR2
)
 
IS

--v_table1 VARCHAR2(50);
--v_table2 VARCHAR2(50);
v_column1  VARCHAR2(50);
v_column2  VARCHAR2(50);
v_column3  VARCHAR2(50);
v_column4_blob VARCHAR2(50);

v_column5  VARCHAR2(50);
v_column6  VARCHAR2(50);
v_column7  VARCHAR2(50);
v_column8_blob VARCHAR2(50);

BEGIN
--v_table1 := P_table1;
--v_table2 := P_table2;

v_column1 := P_column1;
v_column2 := P_column2;
v_column3 := P_column3;
v_column4_blob := P_column4_blob;

v_column5 := P_column5;
v_column6 := P_column6;
v_column7 := P_column7;
v_column8_blob := P_column8_blob;

INSERT INTO APPS.gben_blob2(v_column1,v_column2,v_column3,v_column4_blob) SELECT v_column5,v_column6,v_column7,v_column8_blob FROM APPS.gben_blob1;

COMMIT;

end Gben_Copy_Blob;
=======End Copy BLOB procedure======

Olugbnega OyeneyeDeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SmilingPixieCommented:
This could be a couple of things....

Do you have the definition of the views themselves available from the data dictionary?   One possibility is that the underlying view is complex and therefore direct DML statements can't be issued.  A second possibility is that the view has been defined as read only.  A third option is that there are additional columns in the underlying table used by the view that have not null constraints on them and that can prevent inserts from happening into the view.

Have you tried just doing a simple insert into the view outside of PL/SQL?
0
Olugbnega OyeneyeDeveloperAuthor Commented:
I tried the following procedure and it worked(copied the data) excellently. The thing is I want to pass the columns as parameters in the procedure.

===begin====
CREATE OR REPLACE PROCEDURE Gben_Copy_Blob iS
BEGIN

INSERT INTO gben_blob2 SELECT * FROM gben_blob1;

end Gben_Copy_Blob;
=========end======
0
SmilingPixieCommented:
Is there a reason you are defining the BLOB parameters as VACHAR2 rather than as BLOBs?  

try p_col8_blob  BLOB in defining the parameters....
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jinesh KamdarCommented:
Ok, then try something like this.
CREATE OR REPLACE PROCEDURE Gben_Copy_Blob
(filename_yn IN BOOLEAN, file_data_yn IN BOOLEAN)
IS
BEGIN
 
INSERT INTO gben_blob2
SELECT file_id,
       DECODE(filename_yn, TRUE, file_name, NULL),
       file_content_type,
       DECODE(filedata_yn, TRUE, file_data, NULL)
FROM gben_blob1;
 
END Gben_Copy_Blob;

Open in new window

0
Jinesh KamdarCommented:
Re-pasting with corrections.
CREATE OR REPLACE PROCEDURE Gben_Copy_Blob
(copy_filename_flag IN BOOLEAN, copy_filedata_flag IN BOOLEAN)
IS
BEGIN
 
INSERT INTO gben_blob2
SELECT file_id,
       DECODE(copy_filename_flag, TRUE, file_name, NULL),
       file_content_type,
       DECODE(copy_filedata_flag, TRUE, file_data, NULL)
FROM gben_blob1;
 
END Gben_Copy_Blob;

Open in new window

0
Olugbnega OyeneyeDeveloperAuthor Commented:
It did not compile successfully, it gave the following error message.

ERROR Message:  PL/SQL: ORA-00904: "TRUE": invalid identifier
Line 10 Column 34


============begin========
CREATE OR REPLACE PROCEDURE Gben_Copy_Blob
(copy_filename_flag IN BOOLEAN, copy_filedata_flag IN BOOLEAN)
IS
BEGIN
 
INSERT INTO gben_blob2
SELECT file_id,
       DECODE(copy_filename_flag, TRUE, file_name, NULL),
       file_content_type,
       DECODE(copy_filedata_flag, TRUE, file_data, NULL) --ERROR here on column 34
FROM gben_blob1;
 
END Gben_Copy_Blob;
=========end========
0
Jinesh KamdarCommented:
Thats strange! I didn't know we cannot use BOOLEAN comparison in PL/SQL DECODE statements!!
Lets just use a VARCHAR2 instead.
CREATE OR REPLACE PROCEDURE Gben_Copy_Blob
(copy_filename_flag IN VARCHAR2, copy_filedata_flag IN VARCHAR2)
IS
BEGIN
 
INSERT INTO gben_blob2
SELECT file_id,
       DECODE(copy_filename_flag, 'Y', file_name, NULL),
       file_content_type,
       DECODE(copy_filedata_flag, 'Y', file_data, NULL)
FROM gben_blob1;
 
END Gben_Copy_Blob;

Open in new window

0
Olugbnega OyeneyeDeveloperAuthor Commented:
Its only file_id column and file_content_type column that copied the data in each row

The other 2 columns:
1. The file_name column returned no data (returned null) in all the rows
2. The file_data column does not display/copy any image.


0
Jinesh KamdarCommented:
What did u pass the parameters to the SP as? If u want the file_name and file_data columns to be copied over as well, then uou should be calling the SP something like this -

BEGIN
Gben_Copy_Blob('Y', 'Y');
END;
0
Jinesh KamdarCommented:
Also, ur SP does not include COMMIT / ROLLBACK! Added it below.
CREATE OR REPLACE PROCEDURE Gben_Copy_Blob
(copy_filename_flag IN VARCHAR2, copy_filedata_flag IN VARCHAR2)
IS
BEGIN
 
INSERT INTO gben_blob2
SELECT file_id,
       DECODE(copy_filename_flag, 'Y', file_name, NULL),
       file_content_type,
       DECODE(copy_filedata_flag, 'Y', file_data, NULL)
FROM gben_blob1;
 
COMMIT;
 
EXCEPTION
 
WHEN OTHERS THEN
     ROLLBACK;
     DBMS_OUTPUT.PUT_LINE(SQLERRM);
 
END Gben_Copy_Blob;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Olugbnega OyeneyeDeveloperAuthor Commented:
This is how i ran the procedure:
---------begin--------
SQL> execute gben_copy_blob('FILE_NAME','FILE_DATA');
PL/SQL procedure successfully completed.
-------end---------

no error message but:
Its only file_id column and file_content_type column that copied the data in each row

The other 2 columns:
1. The file_name column returned no data (returned null) in all the rows
2. The file_data column does not display/copy any image.
0
Jinesh KamdarCommented:
As noted in my previous post, that's an incorrect way to call the procedure. They way it has been coded, you will have to call it the way I did.

SQL> SET SERVEROUT ON
SQL> execute gben_copy_blob('Y','Y');
PL/SQL procedure successfully completed.
0
Olugbnega OyeneyeDeveloperAuthor Commented:
I tried this and it copied all the data:
--------begin-------

SQL> BEGIN
  2  Gben_Copy_Blob('Y', 'Y');
  3  END;
  4  /
--------end-------

but how then will i pass any parameter(column name) at run time?
0
Jinesh KamdarCommented:
Why would u want to pass column names at run-time if ur tables are pre-created and u already know their column-names at design-time ?? If u wanna pass column-names at run-time, then u wud hv to make use of dynamic SQL which will surely affect performance! Are u sure u wanna pass column-names at run-time ??
0
Olugbnega OyeneyeDeveloperAuthor Commented:
The code is working just fine now...
You are so right...
Thank you.
0
Olugbnega OyeneyeDeveloperAuthor Commented:
Thank you. you are great!
0
Jinesh KamdarCommented:
Glad to be of help :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.