0

I am working on a project that deal with ORACLE DATABASE. My project has two functionality, one is fetching content of table into a flat file aka backup and second one is take that flat file to restore that data back to blank table having same structure aka restore.

I can backup and restore oracle database data for all datatype object except RAW|LONG RAW. I have an issue regarding it. for example I backup the database data into flat file in the from of string. like

1,"Name", "Class",12.00

Now if any table has RAW or LONG RAW datatype then at the time of insertion into table it may be inserted from any datatype to raw type using RAWTOHEX method. To understand the case better we takes two table for now.

  1. one table say TBLNUM and their column ID NUMBER, RAWINT RAW(2000)

  2. second table say TBLCHAR and their column ID NUMBER, RAWCHAR RAW(2000)

So we insert the data into TBLNUM for the num value to raw using numbers only and TBLCHAR for string only.

I am not very much known about this type. But I can get back the inserted data as following ways If I insert number for RAW column by RAWTOHEX(1), it saved the 1 in the form of 'C102' and I can get back 1 using following method utl_raw.cast_to_number('C102')

Same I can do for STRING RAWTOHEX('7D') -> 3744 and get back that by ult_raw.cast_to_varchar2('3744') -> 7D

first, please correct me if I am wrong second if I am correct then my question is how can I identify from flat file that the given RAW column value is created from INTEGER VALUE OR STRING VALUE

I want to find the solution, using that I can insert the same data back to different database table using that backup file. And data should be inserted as it was in source table from where I did create flat file.

Tej Kiran
  • 2,218
  • 5
  • 21
  • 42
  • Why are you writing your own tools to do this - what's wrong with the built-in export and import functionality? Either data pump, or from a client like SQL Developer? – Alex Poole Mar 21 '16 at 09:57
  • The tool can export different kind of database data. It could be ORACLE, SQL Server, Postgres etc. So we are using apache metamodel to archive and restore the data. Help me that how can I handle it ? – Tej Kiran Mar 21 '16 at 11:32
  • OK, but I'm still not sure I understand your problem. If you have a RAW column why does it matter what it contains? Just write the RAW data to your file, and read it back, as it is. Why would you do any conversion? – Alex Poole Mar 21 '16 at 12:05

1 Answers1

0

I think you should not change the value of raw data type when you backup the database(don't use cast_to_number and cast_to_varchar2). So store the raw data as hex string and then insert the hex string to destination. When you select raw data in sql developer it represented as hex string and to insert raw data to database, your script must contain the hex string:

insert into table1 (num_col, varchar2_col1,varchar2_col2,raw_col)
  values(1, 'Name', 'Class', 'C102')

If your program want to extract the raw data from database you must get the column value as byte[] and write it as hex string in the file.

Mostafa Vatanpour
  • 1,328
  • 13
  • 18