Char or Varchar in oracle

Normally most of the time I use varchar in oracle. But for no reason that day I used char. And the result is clear tonight. varchar is in fact variable character. And char is just character. So if you use char no matter how many char you fill in the field but always you will get the length of data in the field as you declared during the table declaration. so in many cases you can not check the that char data against any var char data and also char data if the char field declaration length is not same. So if you are not really sure about the length of your data char type is not a wise type in oracle.

Comparing Two Tables: Highlighting the Differences

I was looking for this for long time about two weeks. At last I got this. But the bad luck is really bad. I cant understand this. May be helpful for you

Creating table from another table...

Its about creating table in ORACLE. Surely how to create a table is not an issue here.

 

I was trying to create a table from another table with this query.

 

            create table table_2 as (select * from table_1);

 

This will work fine. It will create a table and copy all data from table 1 to table 2. This may not be desired everywhere. Sometimes you may need and empty table with the same schema. That is also possible in this way. Just add a where clause which selects no values. Like the following

 

            create table table_2 as (select * from table_1 where table_1.fiels_1 = ‘la bla bla’);

 

I was facing a different problem. The originating table has primary key and few foreign keys. These were not being incorporated to the new table. This is very much obvious because most of the keys have data dependency. So if oracle wants to put the constraints on the new table, it will take a lot of time to create the new table due to a brut force data analysis.

 

So during creating table keep this in mind that the constraints will not be incorporated in this way.

 

 

Parameter naming problem in oracle function & Procedure..

                  Oracle Procedure and Functions are very much effective, developing N-tier application. Both of them can take parameter and function can return a value and procedure cant return. Here I got some problem with the naming system of parameters of functions & procedures.

                   Suppose the function has a parameter named employee_ID and you are working on a table where a column name is also employee_ID. And now suppose you have a query like this in your function.

select employee_name from employee_table where employee_id = employee_id;


Your intension may be to compare the column of the table to the parameter. Ok lets make it more clear to Oracle. Suppose your query is like the following now

select employee_name from employee_table et where et.employee_id = employee_id;


Still no way. You may be expecting only one row but you will get all the rows of your table. this query in fact will work just like following

select employee_name from employee_table et where et.employee_id = et.employee_id;




So here it is clear why you will get all the rows.

So what I want to say here is, It is always safe to use different name for parameters like param_employee_id so that oracle cant be confused with other names used in your tables. 

 

Normally when we develop an application with a RAD style, we merely look for a very planned DB design. But when the application development has been finished now you may need to look @ your ERD. Pl SQL Developer has a very good feature to reverse engineering the ERD. It is very simple to use




Just select the new diagram window. Now select the the tables you want to put in your ERD, then just drag them onto the digram space. You will get the view with all of your tables relation and key constrains. Most interesting is this you can do about all sorts of DB operation from this diagram. Just click right to get more information.

This is a very beginner level advice for writing insert query. Instead of using in this way
insert into table1 values ('data1', 'data2')

always mention the field name for which you are inserting values

insert into table1 (field1, field2) values ('data1', 'data2')

This way it is ensured that whatever changes you may do on your table, if the columns exists you insert with the old queries.

Within last few days I was stuck with my this mistake and it took a whole night to change all the queries of my application according to the new schema though just a new column has been added..

Now I am inserting huge amount of DATA in ORACLE. Its about 6 million rows. I am using JAVA for chunked insert. So that I can commit after a while. Using JAVA threads improved the performance a lot. Using one where clause you can make chunks of the whole data for insertion then just use threads to inserts few chunks of data at a time. It will decrease the load on the DB also. Because it will not need to increase the memory size for cached data for the insertion data. By the way I will go for a procedure very soon...

At the very first time I was thinking of doing some JAVA ode to do this...

But it is really simple...


you can just use PL/SQL developer to do this..

If your table contains any CLOB/BLOB type field then just open your table in data editable mode in PL/SQL. and select the Image/ File tab...


and you will get all you need...