Databsae Knowledge - ORACLE and SQL Server

Get All Table Rows and Table Size in SQL Server

noreply@blogger.com (Kiran Upadhyay) — Wed, 11 Mar 2015 07:41:00 +0000

SELECT t.NAME AS TableName,s.Name AS SchemaName,p.rows AS RowCounts,SUM(a.total_pages) * 8 AS TotalSpaceKB,SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255
GROUP BY t.Name, s.Name, p.Rows
ORDER BY RowCounts desc

SQL Server - Difference between clustered and a non-clustered index

noreply@blogger.com (Kiran Upadhyay) — Sun, 26 Oct 2014 08:46:00 +0000

What are the difference between clustered and a non-clustered index?

1). A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

2). A non clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

Read XML into SQL Database

noreply@blogger.com (Kiran Upadhyay) — Mon, 09 Dec 2013 10:52:00 +0000

Declare @xml XML
set @xml = (select cast(c1 as xml) from OPENROWSET (BULK 'XMLpath.xml',SINGLE_BLOB) as T1(c1))
insert into TableName (Col1,Col2,Col3)
SELECT A.Column1,A.Column2,A.Column3 FROM
(SELECT
Tbl.Col.value('@XmlTag', 'varchar(200)') as Col1,
Tbl.Col.value('@XmlTag', 'datetime') as Col2,
Tbl.Col.value('@XmlTag', 'varchar(2000)') as Col3
FROM @xml.nodes('//XMLNode') Tbl(Col) ) A

jQuery Mobile 1.4.0

noreply@blogger.com (Kiran Upadhyay) — Fri, 25 Oct 2013 07:04:00 +0000

The jQuery Mobile team is excited to announce the first release candidate for 1.4.0. For this new version of the jQuery Mobile framework we focused on performance improvements, reviewing widgets as well as a new default theme and SVG icons. Some of the new features in 1.4 are a flip switch widget, a generic filter widget named “filterable”, popups with arrows, tooltips for sliders and we integrated the tabs widget from jQuery UI.
See the changelog below for the key changes, and have a look at the jQuery Mobile 1.4.0 Beta 1 announcement to find out about all highlights of this new version of jQuery Mobile.

Demos | Key changes | Download & CDN

Read More

Difference between WHERE clause and HAVING clause

noreply@blogger.com (Kiran Upadhyay) — Sun, 20 Oct 2013 10:11:00 +0000

WHERE and HAVING both filters out records based on one or more conditions.

The difference is,

WHERE clause can only be applied on a static non-aggregated column whereas we will need to use HAVING for aggregated columns.

To understand this, consider this example.

Suppose we want to see only those departments where department ID is greater than 3. There is no aggregation operation and the condition needs to be applied on a static field. We will use WHERE clause here:

SELECT * FROM DEPT WHERE ID > 3

Next, suppose we want to see only those Departments where Average salary is greater than 80. Here the condition is associated with a non-static aggregated information which is “average of salary”. We will need to use HAVING clause here:

SELECT dept.name DEPARTMENT, avg(emp.sal) AVG_SAL
FROM DEPT dept, EMPLOYEE emp
WHERE dept.id = emp.dept_id (+)
GROUP BY dept.name
HAVING AVG(emp.sal) > 80

What is the difference between inner and outer join?.

noreply@blogger.com (Kiran Upadhyay) — Sun, 20 Oct 2013 10:09:00 +0000

Inner Join

Inner join is the most common type of Join which is used to combine the rows from two tables and create a result set containing only such records that are present in both the tables based on the joining condition

SELECT dept.name DEPARTMENT, emp.name EMPLOYEE
FROM DEPT dept, EMPLOYEE emp
WHERE emp.dept_id = dept.id

Outer Join

Outer Join, on the other hand, will return matching rows from both tables as well as any unmatched rows from one or both the tables (based on whether it is single outer or full outer join respectively).

Notice in our record set that there is no employee in the department 5 (Logistics). Because of this if we perform inner join, then Department 5 does not appear in the above result. However in the below query we perform an outer join (dept left outer join emp), and we can see this department.

SELECT dept.name DEPARTMENT, emp.name EMPLOYEE
FROM DEPT dept, EMPLOYEE emp
WHERE dept.id = emp.dept_id (+)

Pivot In SQL Query

noreply@blogger.com (Kiran Upadhyay) — Sun, 29 Sep 2013 07:54:00 +0000

We use pivot queries when we need to transform data from row-level to columnar data.

Pivot query help us to generate an interactive table that quickly combines and compares large amounts of data. We can rotate its rows and columns to see different summaries of the source data, and we can display the details for areas of interest at a glance. It also help us to generate Multidimensional reporting.

SELECT *
FROM (
SELECT
year(invoiceDate) as [year],left(datename(month,invoicedate),3)as [month],
InvoiceAmount as Amount
FROM Invoice
) as s
PIVOT
(
SUM(Amount)
FOR [month] IN (jan, feb, mar, apr,
may, jun, jul, aug, sep, oct, nov, dec)
)AS pivot

SQL Wildcards

noreply@blogger.com (Kiran Upadhyay) — Mon, 16 Sep 2013 13:53:00 +0000

A wildcard character can be used to substitute for any other character(s) in a string.

Using the SQL % Wildcard

The following SQL statement selects all customers with a City starting with "ber":

SELECT * FROM table_Name
WHERE column_Name LIKE 'ber%';

Using the SQL _ Wildcard

The following SQL statement selects all customers with a City starting with any character, followed by "erlin":

SELECT * FROM table_Name
WHERE column_Name LIKE '_erlin';

Using the SQL [charlist] Wildcard

The following SQL statement selects all customers with a City starting with "b", "s", or "p":

SELECT * FROM table_Name
WHERE column_Name LIKE '[bsp]%';

The following SQL statement selects all customers with a City starting with "a", "b", or "c":

SELECT * FROM table_Name
WHERE column_Name LIKE '[a-c]%';

The following SQL statement selects all customers with a City NOT starting with "b", "s", or "p":

SELECT * FROM table_Name
WHERE column_Name LIKE '[!bsp]%';

Slid.es: Create Easy And Beautiful Presentations In Two Dimensions

noreply@blogger.com (Kiran Upadhyay) — Fri, 09 Aug 2013 04:48:00 +0000

Tools like Microsoft PowerPoint or Apple’s Keynote put an amazingly powerful toolset into hands that make presentations. The key is realising that you should only need a minute portion of those tools. Not every line of text needs to whirl into view with CGI bravado. Not every image needs to glow and throw a shadow. It’s fun to put these effects in, but what looks cool and engaging is often distracting instead. If you want to engage your audience, focus on the speaker and use the presentation to support what you’re saying. Simple keywords and images get you further than big blocks of text and headache-inducing effects.
Part of making professional presentations comes from focusing on key points like that. Another part comes from using the right tool for the job. PowerPoint is an amazing piece of software, but it gives you twenty degrees of freedom. Fifteen of those lead to disaster. Slid.es is a free online PowerPoint alternative to make great presentations. Simply beautiful and beautifully simple.

Slid.es

Slid.es is a simple web app to make presentations. It’s simple to use and easy to make beautiful presentations with. Think Google Docs, but with a minimalist look and its own approach. All of this freely available, although you can upgrade to a Pro version to get more storage and private sharing.

Getting started is very easy. Just click the big Get Started button on the home page and create a new account. If you don’t want to busy yourself with creating another account from scratch, sign in with your Facebook or Google account instead.

After signing in, Slid.es let’s you know that you don’t have any decks. A deck, in case you’re wondering, is almost the same as a presentation. There are some differences, though.

Decks: Making Multidimensional Presentations

After creating a new deck in Slid.es, you’ll notice something peculiar. Both to the right and below the currently selected slide are buttons to create new slides. Pressing these will effectively add a new slide to the right, or to the bottom of the current one. This means you’re able to create a presentation in two dimensions!

This is better illustrated with an example. I’ve created a small mockup presentation in Slid.es, making use of both dimensions. By selecting Arrange from the sidebar menu, you get a good overview of the relationships between different slides.
In the example below, we start off with a title slide. Going down brings up an overview of the subject. Going right takes you to a new chapter. Similar to a book, you can view the vertical stacks as chapters in an all-compassing presentation, or you could create a deck of related slideshows, each in a separate stack.

How you use it is up to you. Personally, I like the “chapter-y” feel of it. However, you could also simply work in a single direction and make a simple slideshow; front to back.
Navigating works by using the arrow keys, or the virtual directional pad in the lower right corner of the presentation. Note that you don’t skip the entire stack by moving right. Slid.es first draws the different parts of the current slide, regardless of whether you go down or right. Decks created with a free account are public.

Creating Appealing Slides

Before getting started, take a look at the Style menu in the sidebar. Here you can tweak the global style settings of your slides. What you change here, and also at any later stage, affects your entire deck. In this menu, you’ll be able to select a colour scheme, font, transition animation between two slides, and a transition animation when you change the background in a slide.

Now, let’s create our first slide. You’ll notice there are no clickable areas set apart as is usual in these presentation suites (e.g. “Click to add title.”). Instead, there’s just a single canvas. Heading 1 is selected in the top toolbar, so you can just start typing to enter a title at the top of your slide. Press Enter, and Slid.es switches to a Paragraph style automatically.
You can change everything involving typesetting in the top toolbar, although generally you’ll only have to change the text style occasionally. This is also where you’ll introduce new bullet lists, change text colour, add links and insert images into your slides.

Just like the top toolbar tweaks properties of the content in the slide, the right toolbar tweaks the slides themselves. Here you can change the background colour or image, make items appear sequentially during your presentation, position elements freely and change the slide’s HTML code (in that order).

Making items appear on a slide sequentially is incredibly simple. Start by clicking the lightning shaft icon in the right toolbar. Now, click on items in your slide to turn them into fragments. Every fragment is an item that only appears on your screen after you’ve stepped through them. There’s no tweaking the order or animation, though. Make sure to preview the slideshow before you finish.

Sharing The Presentation

Select Share in the left sidebar when you’re done to start spreading those slides around. There are two ways to share your slides. You can send people a link, or embed the slides on a website. (If you’ll be giving a presentation in person, this won’t be necessary.)

Behind the link, you’ll find an embed of your presentation. In the lower left corner are buttons to enter full screen or open the presentation in a separate window. Additionally, below the presentation, you’ll find social sharing links and a comments section to engage with your audience.

Take a look at this sample presentation in action. It’s a minimal presentation, but suffices to show off Slid.es features and how easy these presentations are to navigate.

Announcing jQuery Mobile 1.4.0 Alpha

noreply@blogger.com (Kiran Upadhyay) — Wed, 31 Jul 2013 11:46:00 +0000

The jQuery Mobile team is thrilled to announce the 1.4.0 Alpha release. For this release we focused on performance improvements and reviewing widgets. We also introduced a new default theme and SVG icons. Some of the new features that come with this release are a flip switch widget, a generic filter widget named “filterable”, popups with arrows, tooltips for sliders and we integrated the tabs widget from jQuery UI.

Performance

To improve performance we reduced DOM manipulation. Generation of inner markup for elements styled as butons has been completely removed. In many cases the framework just adds classes to the native element during enhancement and we even reduced the amount of classes that are added by the framework.

Theme inheritance

One of the biggest changes is the way theme inheritance works. In previous versions we used JavaScript to find the nearest parent element with a theme and added theme classes to all elements. This has been replaced by a pure CSS solution where the level of specificity of the selector determines what theme (swatch) is applied. In almost all cases the default for option theme has been removed and widgets get the same theme as their container or page via CSS.

New default theme

This was also a good time to switch to a new default theme with a flat, more modern, design. The number of swatches has been reduced from five to two; a light “A” swatch and a dark “B” swatch. We will update the ThemeRoller soon so you can create your own themes for 1.4.

SVG icons

Not only the theme is new. A big thank you to Glyphish for creating a complete new icon set for jQuery Mobile! These are vector-based SVG icons, but we included a fallback to external PNG icons on browsers that don’t support inline SVG. We are also going to provide additional stylesheets, each with different icon CSS (inline SVG, data-uri PNG, and external PNG) that can be used with the full Grunticon solution.

Read More

What is SSRS and Why SSRS is asked for in many Job Opening?

noreply@blogger.com (Kiran Upadhyay) — Mon, 22 Jul 2013 06:19:00 +0000

This example is from the Beginning SSRS by Kathi Kallenberger. Supporting files are available with a free download from the www.Joes2Pros.com web site.

This will be a 5 day blog post in getting started with SSRS. Today will show the importance of SSRS in the business.

Why is SSRS asked for in so many job openings?

If you talk to an SSRS expert it’s very clear to them exactly why companies really need this invention and how it saves time and adds business value. You don’t have to be an SSRS expert to know its value or to start using it. For example you don’t have to be an airline pilot to know the usefulness of modern transportation. Even the people who don’t know how to run SSRS but need the reports can tell you why that is needed. This blog post will go into why SSRS is an important invention by showing how it improves the usage of information in your company.

Before SSRS there has always been a need for a company to benefit from the use of its own information. Excel spreadsheets have been a popular way to do this for a long time. With SSRS you can still use this solution and gain many other options too.

A friend of mine told me a story about doing database work in the 90s for a major company and how he wished SSRS was available back then. The Vice President of the marketing channel would often come to him just before an important meeting with the board of directors. He often needed to show how certain product sales were performing over time. All this information was in the database so it was my friend’s job to get the information out and organized into a medium the VP could use. This medium was usually Excel. The VP often had meetings all over the world where he showcased this Excel report.

The solution to get the VP to him anywhere he was in the world was an Excel file attached to an e-mail. This worked pretty well but with some drawbacks. One time my friend sent the wrong file in the e-mail. A few minutes later my friend realized his mistake and sent another frantic e-mail to VP. This one was saying to ignore the last e-mail and use this newer one. Would the VP see the correct e-mail in time?

If SSRS had been available, my friend could have created a solution that let the VP run the report any time he wished. The report could have been published to the company intranet where the VP could run it from any of the offices he happened to be traveling to that month. There is a fair amount of work up front to develop and publish the report, but once that work is completed, the report can be reused as many times as needed. My friend could even be on vacation for the first day of the monthly and the VP can get his real-time report.

Not only could the report show the most recent data, the VP could choose to view reports of previous months with just a few clicks. The deployed SSRS is user friendly, and can also be configured to protect reports from being run by the wrong people.

Tomorrow’s Post

Tomorrow’s blog post will show how to know if you already have SSRS installed.

If you want to learn SSRS in easy to simple words – I strongly recommend you to get Beginning SSRS book from Joes 2 Pros.

Source

Announcing jQuery Mobile 1.3.2

noreply@blogger.com (Kiran Upadhyay) — Mon, 22 Jul 2013 05:00:00 +0000

The jQuery Mobile team is happy to announce 1.3.2. This is the second maintenance release for 1.3 and contains fixes throughout the library. Try it now!

Demos & docs | Download & CDN | Change log

Download

CDN-Hosted JavaScript:

Uncompressed: jquery.mobile-1.3.2.js (useful for debugging)
Minified: jquery.mobile-1.3.2.min.js (full library, ready to deploy)

CDN-Hosted CSS:

Uncompressed with Default theme: jquery.mobile-1.3.2.css (useful for debugging)
Minified with Default theme: jquery.mobile-1.3.2.min.css (full library, ready to deploy)
Uncompressed structure without a theme: jquery.mobile-1.3.2.css (useful for theme development)
Minified structure without a theme: jquery.mobile-1.3.2.min.css (full library, ready to deploy)

Copy-and-Paste Snippet for CDN-hosted files (recommended):

<link rel="stylesheet" href="http://code.jquery.com/mobile/1.3.2/jquery.mobile-1.3.2.min.css" /> <script src="http://code.jquery.com/jquery-1.9.1.min.js"></script> <script src="http://code.jquery.com/mobile/1.3.2/jquery.mobile-1.3.2.min.js"></script>

ZIP File:
If you want to host the files yourself you can download a zip of all the files:

Zip File: jquery.mobile-1.3.2.zip (JavaScript, CSS, and images)

Microsoft CDN hosted jQuery Mobile files:

http://www.asp.net/ajaxLibrary/CDN.ashx

Fork jQuery Mobile on GitHub
https://github.com/jquery/jquery-mobile

Read More

Difference Between INNER JOIN and JOIN

noreply@blogger.com (Kiran Upadhyay) — Wed, 17 Jul 2013 04:56:00 +0000

Query using INNER JOIN

SELECT * FROM Table1 INNER JOIN Table2 ON Table1.Column1 = Table2.Column1

Query using JOIN

SELECT * FROM Table1 JOIN Table2 ON Table1.Column1 = Table2.Column1

The question is what is the difference between above two syntax.

Here is the answer – They are equal to each other. There is absolutely no difference between them. They are equal in performance as well as implementation. JOIN is actually shorter version of INNER JOIN.

Personally I prefer to write INNER JOIN because it is much cleaner to read and it avoids any confusion if there is related to JOIN. For example if users had written INNER JOIN instead of JOIN there would have been no confusion in mind and hence there was no need to have original question.

Source

Delete duplicate rows in SQL Server and Oracle

noreply@blogger.com (Kiran Upadhyay) — Wed, 10 Jul 2013 10:47:00 +0000

 SQL SERVER

DELETE FROM Table1 WHERE ID NOT IN
(  SELECT MAX(ID) FROM Table1
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3 )


WITH CTE (COl1,Col2, DuplicateCount)
AS
(
SELECT COl1,Col2,
ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount
FROM TableName
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
GO



AND In ORACLE


DELETE FROM TABLE1

WHERE ROWID NOT IN (SELECT   MIN (ROWID)
FROM TABLE1
GROUP BY COLUMN1, COLUMN2, COLUMN3, COLUMN4);

Count of the number of records in a SQL Table

noreply@blogger.com (Kiran Upadhyay) — Fri, 17 May 2013 12:35:00 +0000

Name 3 ways to get an accurate count of the number of records in a SQL Table?

SELECT * FROM TableName

SELECT COUNT(*) FROM TableName

SELECT rows FROM sysindexes WHERE id = OBJECT_ID('TableName') AND indid < 2

Super fast Database Copying/Cloning

noreply@blogger.com (Kiran Upadhyay) — Sat, 06 Apr 2013 14:57:00 +0000

A database cloning procedure is especially useful for the DBA who wants to give his developers a full-sized TEST and DEV instance by cloning the PROD instance into the development server areas.

This Oracle clone procedure can be use to quickly migrate a system from one UNIX server to another. It clones the Oracle database and this Oracle cloning procedures is often the fastest way to copy a Oracle database.

STEP 1: On the old system, go into SQL*Plus, sign on as SYSDBA and issue: “alter database backup controlfile to trace”. This will put the create database syntax in the trace file directory. The trace keyword tells oracle to generate a script containing a create controlfile command and store it in the trace directory identified in the user_dump_dest parameter of the init.ora file. It will look something like this:

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS

NOARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 2

MAXDATAFILES 240

MAXINSTANCES 1

MAXLOGHISTORY 113

LOGFILE

GROUP 1 ('/u03/oradata/oldlsq/log1a.dbf',

'/u03/oradata/olslsq/log1b.dbf') SIZE 30M,

GROUP 2 ('/u04/oradata/oldlsq/log2a.dbf',

'/u04/oradata/oldlsq/log2b.dbf') SIZE 30M

DATAFILE

'/u01/oradata/oldlsq/system01.dbf',

'/u01/oradata/oldlsq/mydatabase.dbf'

;

# Recovery is required if any of the datafiles are restored

# backups, or if the last shutdown was not normal or immediate.

RECOVER DATABASE

# Database can now be opened normally.

ALTER DATABASE OPEN;

STEP 2: Shutdown the old database

STEP 3: Copy all data files into the new directories on the new server. You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server.

rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq

rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq

rcp /u03/oradata/oldlsq/* newhost:/u03/oradata/newlsq

rcp /u04/oradata/oldlsq/* newhost:/u04/oradata/newlsq

STEP 4: Copy and Edit the Control file – Using the output syntax from STEP 1, modify the controlfile creation script by changing the following:

Old:

CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS

New:

CREATE CONTROLFILE SET DATABASE "NEWLSQ" RESETLOGS

STEP 5: Remove the “recover database” and “alter database open” syntax

# Recovery is required if any of the datafiles are restored

# backups, or if the last shutdown was not normal or immediate.

RECOVER DATABASE

# Database can now be opened normally.

ALTER DATABASE OPEN;

STEP 6: Re-names of the data files names that have changed.

Save as db_create_controlfile.sql.

Old:

DATAFILE

'/u01/oradata/oldlsq/system01.dbf',

'/u01/oradata/oldlsq/mydatabase.dbf'

New:

DATAFILE

'/u01/oradata/newlsq/system01.dbf',

'/u01/oradata/newlsq/mydatabase.dbf'

STEP 7: Create the bdump, udump and cdump directories

cd $DBA/admin

mkdir newlsq

cd newlsq

mkdir bdump

mkdir udump

mkdir cdump

mkdir pfile

STEP 8: Copy-over the old init.ora file

rcp $DBA/admin/olslsq/pfile/*.ora newhost:/u01/oracle/admin/newlsq/pfile

STEP 9: Start the new database

@db_create_controlfile.sql

STEP 10: Place the new database in archivelog mode

Generate Data Dictionary from SQL Server

noreply@blogger.com (Kiran Upadhyay) — Sat, 06 Apr 2013 14:54:00 +0000

Introduction

This tip shows how to find out table description and dependency with other tables.

Using the code

In any SQL Server database (any version of SQL Server) just create a Stored Procedure [spGenerateDBDictionary] that is given as attachment, and exec [spGenerateDBDictionary]. Then the procedure will return tables information with table name, data, attributes, data types, IsNullable info, primary key, foreign key constraints, any reference with another table, and the details description, but remember that the description will come from the description field where you give any description at the time of table creation.

CREATE proc [dbo].[spGenerateDBDictionary]
AS
BEGIN
select a.name [Table],b.name [Attribute],c.name [DataType],b.isnullable [Allow Nulls?],CASE WHEN
d.name is null THEN 0 ELSE 1 END [PKey?],
CASE WHEN e.parent_object_id is null THEN 0 ELSE 1 END [FKey?],CASE WHEN e.parent_object_id
is null THEN '-' ELSE g.name END [Ref Table],
CASE WHEN h.value is null THEN '-' ELSE h.value END [Description]
from sysobjects as a
join syscolumns as b on a.id = b.id
join systypes as c on b.xtype = c.xtype
left join (SELECT so.id,sc.colid,sc.name
     FROM    syscolumns sc
     JOIN sysobjects so ON so.id = sc.id
     JOIN sysindexkeys si ON so.id = si.id
                   and sc.colid = si.colid
     WHERE si.indid = 1) d on a.id = d.id and b.colid = d.colid
left join sys.foreign_key_columns as e on a.id = e.parent_object_id and b.colid = e.parent_column_id
left join sys.objects as g on e.referenced_object_id = g.object_id
left join sys.extended_properties as h on a.id = h.major_id and b.colid = h.minor_id
where a.type = 'U' order by a.name
END

How to format datetime & date in Sql Server

noreply@blogger.com (Kiran Upadhyay) — Mon, 14 Jan 2013 08:38:00 +0000

SELECT convert(varchar, getdate(), 100) – mon dd yyyy hh:mmAM (or PM)
                                      – Oct 2 2008 11:01AM
SELECT convert(varchar, getdate(), 101) – mm/dd/yyyy - 10/02/2008
SELECT convert(varchar, getdate(), 102) – yyyy.mm.dd – 2008.10.02
SELECT convert(varchar, getdate(), 103) – dd/mm/yyyy
SELECT convert(varchar, getdate(), 104) – dd.mm.yyyy
SELECT convert(varchar, getdate(), 105) – dd-mm-yyyy
SELECT convert(varchar, getdate(), 106) – dd mon yyyy
SELECT convert(varchar, getdate(), 107) – mon dd, yyyy
SELECT convert(varchar, getdate(), 108) – hh:mm:ss
SELECT convert(varchar, getdate(), 109) – mon dd yyyy hh:mm:ss:mmmAM (or PM)
                                      – Oct 2 2008 11:02:44:013AM
SELECT convert(varchar, getdate(), 110) – mm-dd-yyyy
SELECT convert(varchar, getdate(), 111) – yyyy/mm/dd
SELECT convert(varchar, getdate(), 112) – yyyymmdd
SELECT convert(varchar, getdate(), 113) – dd mon yyyy hh:mm:ss:mmm
                                      – 02 Oct 2008 11:02:07:577
SELECT convert(varchar, getdate(), 114) – hh:mm:ss:mmm(24h)
SELECT convert(varchar, getdate(), 120) – yyyy-mm-dd hh:mm:ss(24h)
SELECT convert(varchar, getdate(), 121) – yyyy-mm-dd hh:mm:ss.mmm
SELECT convert(varchar, getdate(), 126) – yyyy-mm-ddThh:mm:ss.mmm
                                      – 2008-10-02T10:52:47.513
– SQL create different date styles with t-sql string functions
SELECT replace(convert(varchar, getdate(), 111), ‘/’, ‘ ‘) – yyyy mm dd
SELECT convert(varchar(7), getdate(), 126)                 – yyyy-mm
SELECT right(convert(varchar, getdate(), 106), 8)          – mon yyyy

Type of Exception in Pl / Sql

noreply@blogger.com (Kiran Upadhyay) — Wed, 06 Oct 2010 15:26:00 +0000

CURSOR_ALREADY_OPEN ~ You tried to OPEN a cursor that was already OPEN. You must CLOSE a cursor before you try to OPEN or re-OPEN it.

DUP_VAL_ON_INDEX ~ Your INSERT or UPDATE statement attempted to store duplicate values in a column or columns in a row which is restricted by a unique index.

INVALID_CURSOR ~ You made reference to a cursor that did not exist. This usually happens when you try to FETCH from a cursor or CLOSE a cursor before that cursor is OPENed.

INVALID_NUMBER ~ PL/SQL executes a SQL statement that cannot convert a character string successfully to a number. This exception is different from the VALUE_ERROR exception, as it is raised only from within a SQL statement.

LOGIN_DENIED ~ Your program tried to log onto the Oracle RDBMS with an invalid username-password combination. This exception is usually encountered when you embed PL/SQL in a 3GL language.

NO_DATA_FOUND ~ This exception is raised in three different scenarios:
(1) You executed a SELECT INTO statement (implicit cursor) that returned no rows.
(2) You referenced an uninitialized row in a local PL/SQL table.
(3) You read past end of file with UTL_FILE package.

NOT_LOGGED_ON ~ Your program tried to execute a call to the database (usually with a DML statement) before it had logged into the Oracle RDBMS.

PROGRAM_ERROR ~ PL/SQL encounters an internal problem. The message text usually also tells you to "Contact Oracle Support."

STORAGE_ERROR ~ Your program ran out of memory or memory was in some way corrupted.

TIMEOUT_ON_RESOURCE ~ A timeout occurred in the RDBMS while waiting for a resource.

TOO_MANY_ROWS ~ A SELECT INTO statement returned more than one row. A SELECT INTO can return only one row; if your SQL statement returns more than one row you should place the SELECT statement in an explicit CURSOR declaration and FETCH from that cursor one row at a time.

TRANSACTION_BACKED_OUT ~ The remote part of a transaction is rolled back, either with an explicit ROLLBACK command or as the result of some other action.

VALUE_ERROR ~ PL/SQL raises the VALUE_ERROR whenever it encounters an error having to do with the conversion, truncation, or invalid constraining of numeric and character data. This is a very general and common exception. If this same type of error is encountered in a SQL DML statement within a PL/SQL block, then the INVALID_NUMBER exception is raised.

ZERO_DIVIDE ~ Your program tried to divide by zero.

Managing Backup & Recovery

noreply@blogger.com (Kiran Upadhyay) — Wed, 06 Oct 2010 15:23:00 +0000

What are the different methods of backing up oracle database ?
- Logical Backups
- Cold Backups
- Hot Backups (Archive log)

What is a logical backup ?
Logical backup involves reading a set of databse records and writing them into a file. Export utility is used for taking backup and Import utility is used to recover from backup.

What is cold backup ? What are the elements of it ?
Cold backup is taking backup of all physical files after normal shutdown of database. We need to take.
- All Data files.
- All Control files.
- All on-line redo log files.
- The init.ora file (Optional)

What are the different kind of export backups ?
Full back - Complete database
Incremental - Only affected tables from last incremental date/full backup date.
Cumulative backup - Only affected table from the last cumulative date/full backup date.

What is hot backup and how it can be taken ?
Taking backup of archive log files when database is open. For this the ARCHIVELOG mode should be enabled. The following files need to be backed up.
All data files. All Archive log, redo log files. All control files.

What is the use of FILE option in EXP command ?
To give the export file name.

What is the use of COMPRESS option in EXP command ?
Flag to indicate whether export should compress fragmented segments into single extents.

What is the use of GRANT option in EXP command ?
A flag to indicate whether grants on databse objects will be exported or not. Value is 'Y' or 'N'.

What is the use of INDEXES option in EXP command ?
A flag to indicate whether indexes on tables will be exported.

What is the use of ROWS option in EXP command ?
Flag to indicate whether table rows should be exported. If 'N' only DDL statements for the databse objects will be created.

What is the use of CONSTRAINTS option in EXP command ?
A flag to indicate whether constraints on table need to be exported.

What is the use of FULL option in EXP command ?
A flag to indicate whether full databse export should be performed.

What is the use of OWNER option in EXP command ?
List of table accounts should be exported.

What is the use of TABLES option in EXP command ?
List of tables should be exported.

What is the use of RECORD LENGTH option in EXP command ?
Record length in bytes.

What is the use of INCTYPE option in EXP command ?
Type export should be performed COMPLETE,CUMULATIVE,INCREMENTAL.

What is the use of RECORD option in EXP command ?
For Incremental exports, the flag indirects whether a record will be stores data dictionary tables recording the export.

What is the use of PARFILE option in EXP command ?
Name of the parameter file to be passed for export.

What is the use of PARFILE option in EXP command ?
Name of the parameter file to be passed for export.

What is the use of ANALYSE ( Ver 7) option in EXP command ?
A flag to indicate whether statistical information about the exported objects should be written to export dump file.

How to convert a report into excel sheet

noreply@blogger.com (Kiran Upadhyay) — Tue, 05 Oct 2010 16:46:00 +0000

PROCEDURE U_1ButtonAction is
CURSOR cur is
SELECT A.EMP_CODE,A.EMP_TITLE ||' '||A.EMP_FNAME ||' '||A.EMP_LNAME EMP_NAME,A.EMP_DEPT,A.EMP_TYPE,EMP_DOJ
FROM BPAYT030 A WHERE A.EMP_TYPE = DECODE(:P_EMPTYPE,'A',A.EMP_TYPE,:P_EMPTYPE)
AND A.EMP_DEPT = DECODE(:P_EMPDEPT,'A',A.EMP_DEPT,:P_EMPDEPT) AND EMP_STATUS ='Y';
--AND A.JOB_TITLE = DECODE(:P_JOB,'A',A.EMP_DEPT,:P_JOB)
V_OUTFILE TEXT_IO.FILE_TYPE;
V_OUTSTRING    VARCHAR2(1000);
L_FILENAME     VARCHAR2(100);
L_SR           NUMBER:=0;
L_DESC         VARCHAR2(200);
L_DESC1        VARCHAR2(200);
BEGIN
L_FILENAME := 'C:'||'EMPLOYEE LIST'||TO_CHAR(SYSDATE,'DDMMRR')||'.CSV';
V_outfile := text_io.fopen(L_FILENAME,'W');
V_outstring := ','||','||'EMPLOYEE TYPE/DEPARTMENT WISE DETAILS';
TEXT_IO.PUT_LINE(V_OUTFILE, V_OUTSTRING);
V_outstring :='';
TEXT_IO.PUT_LINE(V_OUTFILE, V_OUTSTRING);
V_outstring := :P_COMP;
TEXT_IO.PUT_LINE(V_OUTFILE, V_OUTSTRING);
--EXCEL FILE HEADS
V_outstring :='EMPLOYEE TYPE'||','||'DEPARTMENT'||','||'CODE'||','||'DOJ' ||','||'EMPLOYEE NAME';
TEXT_IO.PUT_LINE(V_OUTFILE, V_OUTSTRING);
FOR REC IN CUR LOOP
SELECT CODE_DESC INTO L_DESC FROM BCOMT011 WHERE TYPE_CODE = REC.EMP_TYPE;
SELECT CODE_DESC INTO L_DESC1 FROM BCOMT011 WHERE TYPE_CODE = REC.EMP_DEPT;
--DATA IN EXCEL
V_outstring := L_DESC||','||L_DESC1||','||REC.EMP_CODE||','||EC.EMP_DOJ||','||REC.EMP_NAME;
TEXT_IO.PUT_LINE(V_OUTFILE, V_OUTSTRING);
END LOOP;
text_io.fclose(v_outfile);
SRW.MESSAGE(100,'File '||L_FILENAME|| ' Generated Successfully....');
EXCEPTION
WHEN OTHERS THEN
SRW.MESSAGE('1',SQLERRM);
text_io.fclose(v_outfile);
END;

What is a stored procedure ?

noreply@blogger.com (Kiran Upadhyay) — Tue, 05 Oct 2010 16:29:00 +0000

A stored procedure is a subprogram that is stored in the database as a PCODE and thus called as a standalone schema object.

a) It can be invoked directly from any calling environments, or other subprograms.

b) It is similar to any subprogram that accepts parameters and performs an action. It may or may not return a value. This attribute differentiates it from another subprogram type called FUNCTION.

c) It can be nested

d) Is created by using the syntax CREATE OR REPLACE

e) This stored procedure can be seen as an object in the User_Objects and is different from the procedures that are created in packages.

Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ? Why ?

noreply@blogger.com (Kiran Upadhyay) — Tue, 05 Oct 2010 16:27:00 +0000

Generally In triggers you can't use TCL commands.
But you can use TCL commands in Autonomous Triggers.
You can declare a trigger as Autonomous by providing PRAGMA AUTONOMOUS_TRANSACTION in the beginning of the trigger.
At a same time you have to end your trigger with commit/rollback.
You can use these type of triggers to maintain log details of a table.

What are the cursor attributes used in PL/SQL?

noreply@blogger.com (Kiran Upadhyay) — Tue, 05 Oct 2010 16:16:00 +0000

For Implicit Cursors:-
Found - Returns TRUE If the last DML Statement affect atleast 1 row otherwise FALSE.
NotFound - Returns TRUE If last DML Statement doesn't affect atleast 1 row otherwise FALSE.
Rowcount - Returns number of rows affected by the last DML statement.
Isopen - Returns TRUE if the cursor is open otherwise FALSE. Its always returns FALSE incase of implicit cursor. Because after executing the DML statement Oracle Server automatically close the cursor.

For Explicit Cursors:-
Found - Returns TRUE if the Last fetch returns a Row otherwise FALSE.
NotFound - Returns TRUE if the last fetch doesn't return any row otherwise FALSE.
Rowcount - Returns number of rows returned so far from the active set.
Isopen - Returns TRUE if the cursor is open otherwise FALSE.

What is a cursor ? Why Cursor is required ?

noreply@blogger.com (Kiran Upadhyay) — Tue, 05 Oct 2010 16:15:00 +0000

CURSOR
Whenever a sql statement is issued at that time oracle server will allocate some memory area to execute and parse that sql statement. That data area is called as cursor.

Two types : IMPLICIT EXPLICIT

IMPLICIT
-----------
(1) Called as SQL.
(2) Never user CURSOR keyword explicitly
(3) Memory allocation and program controll will done automatically
(4) Returns or updates or deletes only one record.
(5) Attributes : SQL FOUND SQL NOTFOUND

EXPLICIT
-----------
(1) called as cursors
(2) Uses CURSOR keyword explicitly
(3) Memory allocation and program control thru the cursor written.
(4) Returns or updates or deletes multiple records.
(5) Attributes : SQL FOUND SQL NOTFOUND ISOPEN ROWCOUNT