gvsbnarayana
asked on
How to Insert raw data into Oracle raw column?
Hi,
I want to insert/retrieve raw data into a oracle table. How can I do that?
I am using Java/Hibernate/Oracle 10g.
Regards,
Badri.
I want to insert/retrieve raw data into a oracle table. How can I do that?
I am using Java/Hibernate/Oracle 10g.
Regards,
Badri.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
hi
Datatype blob can have 4 gigabytes and raw can have up to 2000 bytes per row.
Use the BLOB datatype to store large binary objects "out of line" inside the database. This means that when a table has a BLOB column, a row of data for that table contains a pointer or a locator to the actual location of the BLOB data (so it is not "in line" with the other column values of the row).
A BLOB variable contains a locator, which then points to the large binary object. BLOBs can be up to four gigabytes in size, and they participate fully in transactions. In other words, any changes you make to a BLOB (via the DBMS_LOB built-in package) can be rolled back or committed along with other outstanding changes in your transaction. BLOB locators cannot, however, span transactions or sessions.
The RAW datatype is used to store binary data or other kinds of raw data, such as a digitized picture or image. A RAW variable has the same maximum length as VARCHAR2 (32767 bytes), which must also be specified when the variable is declared. The difference between RAW and VARCHAR2 is that PL/SQL will not try to interpret raw data. Within the Oracle RDBMS this means that Oracle will not perform character set conversions on RAW data when it is moved from one system (based, for example, on 7-bit ASCII) to another system.
Once again, there is an inconsistency between the PL/SQL maximum length for a RAW variable (32767) and the RDBMS maximum length (255). As a result, you cannot insert more than 255 bytes of your PL/SQL RAW variable's value into a database column. You can, on the other hand, insert the full value of a PL/SQL RAW variable into a column with type LONG RAW, which is a two-gigabyte container for raw data in the database.
Issues
These enhancements are primarily aimed at easing the conversion of LONG and RAW columns to LOBs. The implicit conversions mean that the majority of database code should function correctly after the table columns have been migrated, although it should be revisited to optimize performance.
There are some restrictions on the use of LOBs that should be considered before you start to convert all LONG columns:
Unlike LONGs, LOBs are not allowed in clustered tables.
Migration of replicated tables requires the replicated objects to be manually migrated.
LOBs are not allowed in the UPDATE OF list in update triggers.
Implicit conversions are not allowed in INSTEAD OF triggers.
The ALTER TABLE .. MODIFY syntax required all indexes to be rebuilt manually as it acts like a MOVE.
Domain indexes on LONG columns must be dropped before conversion to LOBs.
please follow
http://www-rohan.sdsu.edu/doc/oracle/server803/A54642_01/ch5.htm
this link to know abt selection of datatypes
regards
Datatype blob can have 4 gigabytes and raw can have up to 2000 bytes per row.
Use the BLOB datatype to store large binary objects "out of line" inside the database. This means that when a table has a BLOB column, a row of data for that table contains a pointer or a locator to the actual location of the BLOB data (so it is not "in line" with the other column values of the row).
A BLOB variable contains a locator, which then points to the large binary object. BLOBs can be up to four gigabytes in size, and they participate fully in transactions. In other words, any changes you make to a BLOB (via the DBMS_LOB built-in package) can be rolled back or committed along with other outstanding changes in your transaction. BLOB locators cannot, however, span transactions or sessions.
The RAW datatype is used to store binary data or other kinds of raw data, such as a digitized picture or image. A RAW variable has the same maximum length as VARCHAR2 (32767 bytes), which must also be specified when the variable is declared. The difference between RAW and VARCHAR2 is that PL/SQL will not try to interpret raw data. Within the Oracle RDBMS this means that Oracle will not perform character set conversions on RAW data when it is moved from one system (based, for example, on 7-bit ASCII) to another system.
Once again, there is an inconsistency between the PL/SQL maximum length for a RAW variable (32767) and the RDBMS maximum length (255). As a result, you cannot insert more than 255 bytes of your PL/SQL RAW variable's value into a database column. You can, on the other hand, insert the full value of a PL/SQL RAW variable into a column with type LONG RAW, which is a two-gigabyte container for raw data in the database.
Issues
These enhancements are primarily aimed at easing the conversion of LONG and RAW columns to LOBs. The implicit conversions mean that the majority of database code should function correctly after the table columns have been migrated, although it should be revisited to optimize performance.
There are some restrictions on the use of LOBs that should be considered before you start to convert all LONG columns:
Unlike LONGs, LOBs are not allowed in clustered tables.
Migration of replicated tables requires the replicated objects to be manually migrated.
LOBs are not allowed in the UPDATE OF list in update triggers.
Implicit conversions are not allowed in INSTEAD OF triggers.
The ALTER TABLE .. MODIFY syntax required all indexes to be rebuilt manually as it acts like a MOVE.
Domain indexes on LONG columns must be dropped before conversion to LOBs.
please follow
http://www-rohan.sdsu.edu/doc/oracle/server803/A54642_01/ch5.htm
this link to know abt selection of datatypes
regards
ASKER
the article was nice. I am not sure whether it is advisable to use BLOB for my purpose or not. The reason is that I won't be having a very huge data for each row. It will be less than 2000 bytes.
The application we are developing demands very high performance. The retrieval will be very low where as there will be around 1000 inserts per minute.
Can you advise me the impact of performance of BLOB over Raw?
Regards,
Badri.