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

How to get client Ip Address using Java HttpServletRequest

-
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 will see how to get IP address from HttpServletRequest.  For the web application which is not running behind the proxy server, we can directly get from the  HttpServletRequest  request object as below remoteAdr = request.getRemoteAddr(); For suppose if your web application is behind a proxy server, load balancer or Cloudfare then you should get client Ip address using the Http request header  X-Forwarded-For  ( XFF) . See below. private String getIpAddress(HttpServletRequest request) { String remoteAdr = "" ; if (request != null ){ remoteAdr = request.getHeader( "X-FORWADED-FOR" ); if (remoteAdr == null || "" .equals(remoteAdr)) { remoteAdr = request.getRemoteAddr();
Read more

How to install Portable JDK in Windows without Admin rights

-
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. Recently i came across the situation where i want to install JDK in the computer without having administrative privileges. Somehow i sought the problem out and want to share the steps that i have followed to do. Prerequisites - Download 7-Zip. Steps to install portable JDK without Administrative Privileges Step 1   Download latest version of JDK from Oracle website. Step 2   Select the downloaded JDK exe and right click on it. From the right click menu, choose 7-Zip ->     'Open archive' . Step 3   After the JDK archive is opened using 7-Zip, inside the folder .rsrc\1033\JAVA_CAB10\111 you        will find tools.zip file. Step 4   Extract tools.zip to your desired JDK directory. Ex: C:\Java\JDK Step 5  Now open windows command prompt
Read more