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?
 
Jinesh KamdarConnect With a Mentor Commented:
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
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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
 
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
All Courses

From novice to tech pro — start learning today.