How to Insert raw data into Oracle raw column?

Posted on 2005-04-15
Last Modified: 2012-08-13
  I want to insert/retrieve raw data into a oracle table. How can I do that?
I am using Java/Hibernate/Oracle 10g.
Question by:gvsbnarayana
    LVL 11

    Assisted Solution

    LVL 5

    Accepted Solution

    LVL 8

    Author Comment

    Hi helpneed,
      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?
    LVL 5

    Expert Comment


    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.

    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

    this link to know abt selection of datatypes



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
    Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now