Question and answers related to Sql queries

-

Hello, In this post, questions about oracle Sql would be placed with answers. From day to day this post will be updated with question and answers. I have got few questions today to post and hence i did.

What would be the default size of varchar2 in oracle?

The default size of varchar2 in oracle is 4k and the default value of varchar2 is 32k when it is being used as input parameter in stored procedure or a function.

What would be the recommended size of database column to store IP address?

There are two types of IP address. One is IPv4 and another one is IPv6. Each of the numbers between the periods in an IP address range between 0-255 which occupies one byte each. so the total recommended size for IPv4 addresses is 4*8 = 32 characters

But IPv6 addresses are 128 bits as opposed to 32 bits (IPv4 addresses). IPV6 addresses are usually written as 8 groups of 4 hex digits seperated by colon (2001:0db8:85a3:0000:0000:8a2e:0370:7334.
So 39 characters is recommended for IPv6 addresses. But there is a caveat here. For IPv4 mapped IPv6 addresses, the String can be longer than 39 characters.

Example:

IPv6 (39 characters) :
abcd:abcd:abcd:abcd:abcd:abcd:abcd:abcd

IPv4-mapped IPv6 (45 characters) :
abcd:abcd:abcd:abcd:abcd:abcd:abcd:abcd:190.167.150.181

The last 32 bits which correspond to IPv4 address need upto 15 characters. So the correct maximum IPv6 varchar size can be 45.

Change the settings for Date Format and Timestamp formats in Oracle Sql Developer

Without the proper settings that were described below, the time will not be shown in any of the Date columns inside the tables of a database in sql developer. Instead it only shows Date. 

Follow the below steps

1) Open Sql developer
2) Go to Tools -> Preferences -> Database -> NLS.
3) By default under Timestamp Format and Date Format, it holds the value RRRR-MM-DD HH24:MI:SSXFF. Remove XFF in the last. Finally it should be RRRR-MM-DD HH24:MI:SS. 
4) Click on OK
5) Query the table. Now you could see time along with date in the columns

Please Note: Please post your comments, the comments could be whatever. They can be a question or question with an answer. But this post is limited only for Sql related queries.
If whatever you post in the comment box is related to sql queries, i will update the post accordingly.

Comments

Post a Comment

Popular posts from this blog

Bash - Execute Pl/Sql script from Shell script

-
Hi, I am Malathi Boggavarapu working at Volvo Group and i live in Gothenburg, Sweden. I have been working on Java since several years and had vast experience and knowledge across various technologies. In this post we see how to build a bash script which executes pl/sql script, retrieve data and send it to graphite server using NetCat. We also go through various basic bash commands such as do/while, nc and also we use bash functions too. NetCat is used to connect to the servers and stream out the data to them. You can visit wiki to get basic understanding about it.  https://en.wikipedia.org/wiki/Netcat To understand what graphite is visit the following link https://graphiteapp.org/ Now let's start with pl/sql script to retrieve data inorder to send it to graphite server. Pl/sql script A table holds the size of each table exists in the database every 24hrs. The query is to get the latest record of each table and it's size and some other columns too. ...
Read more

Gradle Fundamentals

-
Image
Hi, I am Malathi Boggavarapu working at Volvo Group and i live in Gothenburg, Sweden. I have been working on Java since several years and had vast experience and knowledge across various technologies. This post teach you the fundamentals of Gradle. So let's get started to know it's definition first and go through different concepts of Gradle. What is Gradle?  Build by convention Gradle is often called build by convention. Like Maven, gradle has a bunch of building conventions that we can follow when we do the build . However these conventions are easily overridable. We will talk more about it later. Groovy DSL Gradle is written in java but the build language DSL (domain specific language) is written in Groovy. This language makes it very easy to configure our builds. So we are not writing an XML, we write in a language specific to our build. Supports dependencies Just like Maven support dependencies, Gradle also supports dependencies. Supports ...
Read more

Load Balancing using Spring Cloud Netflix Ribbon

-
Image
Hi, I am Malathi Boggavarapu working at Volvo Group and i live in Gothenburg, Sweden. I have been working on Java since several years and had vast experience and knowledge across various technologies. This post teaches you about how to acheive Client side LoadBalancing using SpringCloud Netflix Ribbon Api. So let's jump start into the details. First we learn what is meant by LaodBalancing and the differences between Client side and Server side Load Balancing and also we see which one is the best fit for MicroServices. What is Load Balancing Simply put, Load balancing is a way to improve distribution of work load across multiple computing resources -Wikipedia The most important thing you need to know is what role does Load balancing plays in cloud native architecture. It would be very clear if we understand the difference between load balancing in cloud and non-cloud native architecture. In non-cloud native application, we go from having multiple instances...
Read more