QUESTION: sir, in oracle 10g column i want to insert a data like this MH/12/10/22. which datatype i have to use and what is the correct syntex.

ANSWER: Hi Sanyog,

You need to know what the table name and the structure that you wish to insert into. A table structure is made up columns, each with a determined format. If you are looking into insert mixed used information, VARCHAR is the best format to use. Data best practices suggest that you create a column for each of the data types (it makes it easier to search on). So if you are able to break up MH from (presumably) the date 12/10/22 you can use a VARCAHR and a DATE format.

In a report you can easily concatenate both columns using a SELECT statement (the "||" is the syntax for concatenation).

Here's a reference of all the supported Oracle data types:
The original Oracle 10gR2 data type documentation is available here:

I hope this helps you.


---------- FOLLOW-UP ----------

QUESTION: sir, i know that varchar is allow to insert numeric and char both type of data and my column data type is varchar. sir i want to insert data in 'cc/nn/c/n' this format where c is char and n is numeric. is it possible. can i use check constraint or any other way is possible. sir, kindly give me syntax for this. thanks

Hi Sanyog,

This make the following assumption:

1. That you are storing the value in a column as a VARCHAR field.
2. You want the data stored to always be in the format cc/nn/c/n, where c is a CHAR and N is a NUMBER datatype.
3. (FACT) Check Constraints is used to compare, not for data type validation (Reference:

If you are building an application, it would be enforced within your application code (.net, JAVA, C#, C, etc.).

However if you are building an INSERT SQL statement, you may have to use PL/SQL to enforce this format rule. This isn't recommended as you will need to (a) collect the data; (b) parse the data by the PL/SQL, return a validation back to the user if it is not in the correct format. This could result in a latency delay.

If you are using this as part of a bulk data load (e.g. batch insert), then this would be practical.

You would need to parse the "string" into the different character and number parts. As you are parsing, if the value is not in the correct format, the PL/SQL would reject it. NOTE that a number is an acceptable character as well!!

References: How to parse a String/value

I hope this helps.



All Answers

Answers by Expert:

Ask Experts


Peter Choi


I am a senior Oracle DBA, PeopleSoft HCM specialist and Project Manager with 15+ years experience. I have been working with PeopleSoft (HRMS/HCM 5, 7, 7.x and 8.9, 9.x), Oracle RDBMS (7.3 - 11gR2) on various Unix and MS-Windows platforms, and some Oracle Application Server (9i - 11gR1). I also have experience with the configuration and administration of PeopleSoft's Internet Architecture (PIA) and the Oracle 11g Fusion Middleware and Oracle Business Intelligence (OBI).

©2017 All rights reserved.