E- Learning.

MICROSOFT Windows Longhorn

noreply@blogger.com (Praveen Kumar Bonala) — Mon, 28 Mar 2011 08:28:00 +0530

What is Windows Longhorn?

Longhorn is the codename for the next-generation version of Microsoft's flagship Windows operating system.

IT is the next major desktop Windows release, which follows Windows XP. Windows Longhorn will includes a number of new features including a revised task-based (or "iterative") user interface, an extensible, dock-like, Sidebar, and a SQL Server 2003-based storage engine called WinFS (Windows Future Storage).

Features of Longhorn

• Longhorn will feature a task-based (or "iterative") interface that goes far beyond the task-based interface found today in Windows XP.

• Long Horn will feature WinFS, a new file system based around relational database technology (NTFS will still be available)

• Longhorn will require 3D video hardware to render special effects that will make the screen more photorealistic and deep.

• Longhorn Features Improved Security. Longhorn will optionally include the Palladium security technology Microsoft is developing with Intel and AMD

• Longhorn will include new anti-virus (AV) APIs that will help developers more easily integrate their wares into the base OS.

• Longhorn will include integrated recordable DVD capabilities and will work with every type of recordable DVD format. Digital media enthusiasts will be able to copy video from a digital camcorder directly to recordable DVD, bypassing the system's hard drive entirely, if desired.

• Longhorn will include a new Setup routine that installs the OS in about 15 minutes.

• Longhorn will feature hundreds of new APIs that will let provide access to the new system's features. The Win32 API from previous Windows versions is being replaced by a new .NET-based API called WinFX, for example. It will also feature a new communications and collaboration subsystem, dubbed Indigo.

The visual display in Longhorn is awesome, simply awesome because…..

Longhorn includes...

• an enhanced desktop experience that includes advanced 3D graphics capabilities and driver functionality.

• a new display driver model that will feature radically advanced functionality, stability, and reliability.

• a radically redesigned user interface with a dynamically composed desktop featuring compelling new visual effects like graphically tumbling, rotating, and warped windows.

• hardware accelerated and resolution independent anti-aliased 2D scalable graphics that will expose functionality based on the capabilities of your system's 3D video hardware.

• a rich 3D graphics architecture that is integrated directly into the Windows UI.

.NET Windows Forms Interview Questions

noreply@blogger.com (Praveen Kumar Bonala) — Sun, 27 Mar 2011 17:35:00 +0530

1. Write a simple Windows Forms MessageBox statement.

2. System.Windows.Forms.MessageBox.Show

3. ("Hello, Windows Forms");

4. Can you write a class without specifying namespace? Which namespace does it belong to by default??

Yes, you can, then the class belongs to global namespace which has no name. For commercial products, naturally, you wouldn’t want global namespace.

5. You are designing a GUI application with a window and several widgets on it. The user then resizes the app window and sees a lot of grey space, while the widgets stay in place. What’s the problem? One should use anchoring for correct resizing. Otherwise the default property of a widget on a form is top-left, so it stays at the same location when resized.

6. How can you save the desired properties of Windows Forms application? .config files in .NET are supported through the API to allow storing and retrieving information. They are nothing more than simple XML files, sort of like what .ini files were before for Win32 apps.

7. So how do you retrieve the customized properties of a .NET application from XML .config file? Initialize an instance of AppSettingsReader class. Call the GetValue method of AppSettingsReader class, passing in the name of the property and the type expected. Assign the result to the appropriate variable.

8. Can you automate this process? In Visual Studio yes, use Dynamic Properties for automatic .config creation, storage and retrieval.

9. My progress bar freezes up and dialog window shows blank, when an intensive background process takes over. Yes, you should’ve multi-threaded your GUI, with taskbar and main form being one thread, and the background process being the other.

10. What’s the safest way to deploy a Windows Forms app? Web deployment: the user always downloads the latest version of the code; the program runs within security sandbox, properly written app will not require additional security privileges.

11. Why is it not a good idea to insert code into InitializeComponent method when working with Visual Studio? The designer will likely throw it away; most of the code inside InitializeComponent is auto-generated.

12. What’s the difference between WindowsDefaultLocation and WindowsDefaultBounds? WindowsDefaultLocation tells the form to start up at a location selected by OS, but with internally specified size. WindowsDefaultBounds delegates both size and starting position choices to the OS.

13. What’s the difference between Move and LocationChanged? Resize and SizeChanged? Both methods do the same, Move and Resize are the names adopted from VB to ease migration to C#.

14. How would you create a non-rectangular window, let’s say an ellipse? Create a rectangular form, set the TransparencyKey property to the same value as BackColor, which will effectively make the background of the form transparent. Then set the FormBorderStyle to FormBorderStyle.None, which will remove the contour and contents of the form.

15. How do you create a separator in the Menu Designer? A hyphen ‘-’ would do it. Also, an ampersand ‘&\’ would underline the next letter.

16. How’s anchoring different from docking? Anchoring treats the component as having the absolute size and adjusts its location relative to the parent form. Docking treats the component location as absolute and disregards the component size. So if a status bar must always be at the bottom no matter what, use docking. If a button should be on the top right, but change its position with the form being resized, use anchoring.

.NET Remoting Interview Questions

noreply@blogger.com (Praveen Kumar Bonala) — Sun, 27 Mar 2011 17:34:00 +0530

1. What’s a Windows process? It’s an application that’s running and had been allocated memory.

2. What’s typical about a Windows process in regards to memory allocation? Each process is allocated its own block of available RAM space, no process can access another process’ code or data. If the process crashes, it dies alone without taking the entire OS or a bunch of other applications down.

3. Why do you call it a process? What’s different between process and application in .NET, not common computer usage, terminology? A process is an instance of a running application. An application is an executable on the hard drive or network. There can be numerous processes launched of the same application (5 copies of Word running), but 1 process can run just 1 application.

4. What distributed process frameworks outside .NET do you know? Distributed Computing Environment/Remote Procedure Calls (DEC/RPC), Microsoft Distributed Component Object Model (DCOM), Common Object Request Broker Architecture (CORBA), and Java Remote Method Invocation (RMI).

5. What are possible implementations of distributed applications in .NET? .NET Remoting and ASP.NET Web Services. If we talk about the Framework Class Library, noteworthy classes are in System.Runtime.Remoting and System.Web.Services.

6. When would you use .NET Remoting and when Web services? Use remoting for more efficient exchange of information when you control both ends of the application. Use Web services for open-protocol-based information exchange when you are just a client or a server with the other end belonging to someone else.

7. What’s a proxy of the server object in .NET Remoting? It’s a fake copy of the server object that resides on the client side and behaves as if it was the server. It handles the communication between real server object and the client object. This process is also known as marshaling.

8. What are remotable objects in .NET Remoting? Remotable objects are the objects that can be marshaled across the application domains. You can marshal by value, where a deep copy of the object is created and then passed to the receiver. You can also marshal by reference, where just a reference to an existing object is passed.

9. What are channels in .NET Remoting? Channels represent the objects that transfer the other serialized objects from one application domain to another and from one computer to another, as well as one process to another on the same box. A channel must exist before an object can be transferred.

10. What security measures exist for .NET Remoting in System.Runtime.Remoting? None. Security should be taken care of at the application level. Cryptography and other security techniques can be applied at application or server level.

11. What is a formatter? A formatter is an object that is responsible for encoding and serializing data into messages on one end, and deserializing and decoding messages into data on the other end.

12. Choosing between HTTP and TCP for protocols and Binary and SOAP for formatters, what are the trade-offs? Binary over TCP is the most effiecient, SOAP over HTTP is the most interoperable.

13. What’s SingleCall activation mode used for? If the server object is instantiated for responding to just one single request, the request should be made in SingleCall mode.

14. What’s Singleton activation mode? A single object is instantiated regardless of the number of clients accessing it. Lifetime of this object is determined by lifetime lease.

15. How do you define the lease of the object? By implementing ILease interface when writing the class code.

16. Can you configure a .NET Remoting object via XML file? Yes, via machine.config and application level .config file (or web.config in ASP.NET). Application-level XML settings take precedence over machine.config.

17. How can you automatically generate interface for the remotable object in .NET with Microsoft tools? Use the Soapsuds tool.

C#.NET Interview Questions

noreply@blogger.com (Praveen Kumar Bonala) — Sun, 27 Mar 2011 17:32:00 +0530

1. What’s the implicit name of the parameter that gets passed into the class’ set method? Value, and its datatype depends on whatever variable we’re changing.

2. How do you inherit from a class in C#? Place a colon and then the name of the base class. Notice that it’s double colon in C++.

3. Does C# support multiple inheritance? No, use interfaces instead.

4. When you inherit a protected class-level variable, who is it available to? Classes in the same namespace.

5. Are private class-level variables inherited? Yes, but they are not accessible, so looking at it you can honestly say that they are not inherited. But they are.

6. Describe the accessibility modifier protected internal. It’s available to derived classes and classes within the same Assembly (and naturally from the base class it’s declared in).

7. C# provides a default constructor for me. I write a constructor that takes a string as a parameter, but want to keep the no parameter one. How many constructors should I write? Two. Once you write at least one constructor, C# cancels the freebie constructor, and now you have to write one yourself, even if there’s no implementation in it.

8. What’s the top .NET class that everything is derived from? System.Object.

9. How’s method overriding different from overloading? When overriding, you change the method behavior for a derived class. Overloading simply involves having a method with the same name within the class.

10. What does the keyword virtual mean in the method definition? The method can be over-ridden.

11. Can you declare the override method static while the original method is non-static? No, you can’t, the signature of the virtual method must remain the same, only the keyword virtual is changed to keyword override.

12. Can you override private virtual methods? No, moreover, you cannot access private methods in inherited classes, have to be protected in the base class to allow any sort of access.

13. Can you prevent your class from being inherited and becoming a base class for some other classes? Yes, that’s what keyword sealed in the class definition is for. The developer trying to derive from your class will get a message: cannot inherit from Sealed class WhateverBaseClassName. It’s the same concept as final class in Java.

14. Can you allow class to be inherited, but prevent the method from being over-ridden? Yes, just leave the class public and make the method sealed.

15. What’s an abstract class? A class that cannot be instantiated. A concept in C++ known as pure virtual method. A class that must be inherited and have the methods over-ridden. Essentially, it’s a blueprint for a class without any implementation.

16. When do you absolutely have to declare a class as abstract (as opposed to free-willed educated choice or decision based on UML diagram)? When at least one of the methods in the class is abstract. When the class itself is inherited from an abstract class, but not all base abstract methods have been over-ridden.

17. What’s an interface class? It’s an abstract class with public abstract methods all of which must be implemented in the inherited classes.

18. Why can’t you specify the accessibility modifier for methods inside the interface? They all must be public. Therefore, to prevent you from getting the false impression that you have any freedom of choice, you are not allowed to specify any accessibility, it’s public by default.

19. Can you inherit multiple interfaces? Yes, why not.

20. And if they have conflicting method names? It’s up to you to implement the method inside your own class, so implementation is left entirely up to you. This might cause a problem on a higher-level scale if similarly named methods from different interfaces expect different data, but as far as compiler cares you’re okay.

21. What’s the difference between an interface and abstract class? In the interface all methods must be abstract; in the abstract class some methods can be concrete. In the interface no accessibility modifiers are allowed, which is ok in abstract classes.

22. How can you overload a method? Different parameter data types, different number of parameters, different order of parameters.

23. If a base class has a bunch of overloaded constructors, and an inherited class has another bunch of overloaded constructors, can you enforce a call from an inherited constructor to an arbitrary base constructor? Yes, just place a colon, and then keyword base (parameter list to invoke the appropriate constructor) in the overloaded constructor definition inside the inherited class.

24. What’s the difference between System.String and System.StringBuilder classes? System.String is immutable; System.StringBuilder was designed with the purpose of having a mutable string where a variety of operations can be performed.

25. What’s the advantage of using System.Text.StringBuilder over System.String? StringBuilder is more efficient in the cases, where a lot of manipulation is done to the text. Strings are immutable, so each time it’s being operated on, a new instance is created.

26. Can you store multiple data types in System.Array? No.

27. What’s the difference between the System.Array.CopyTo() and System.Array.Clone()? The first one performs a deep copy of the array, the second one is shallow.

28. How can you sort the elements of the array in descending order? By calling Sort() and then Reverse() methods.

29. What’s the .NET datatype that allows the retrieval of data by a unique key? HashTable.

30. What’s class SortedList underneath? A sorted HashTable.

31. Will finally block get executed if the exception had not occurred? Yes.

32. What’s the C# equivalent of C++ catch (…), which was a catch-all statement for any possible exception? A catch block that catches the exception of type System.Exception. You can also omit the parameter data type in this case and just write catch {}.

33. Can multiple catch blocks be executed? No, once the proper catch code fires off, the control is transferred to the finally block (if there are any), and then whatever follows the finally block.

34. Why is it a bad idea to throw your own exceptions? Well, if at that point you know that an error has occurred, then why not write the proper code to handle that error instead of passing a new Exception object to the catch block? Throwing your own exceptions signifies some design flaws in the project.

35. What’s a delegate? A delegate object encapsulates a reference to a method. In C++ they were referred to as function pointers.

36. What’s a multicast delegate? It’s a delegate that points to and eventually fires off several methods.

37. How’s the DLL Hell problem solved in .NET? Assembly versioning allows the application to specify not only the library it needs to run (which was available under Win32), but also the version of the assembly.

38. What are the ways to deploy an assembly? An MSI installer, a CAB archive, and XCOPY command.

39. What’s a satellite assembly? When you write a multilingual or multi-cultural application in .NET, and want to distribute the core application separately from the localized modules, the localized assemblies that modify the core application are called satellite assemblies.

40. What namespaces are necessary to create a localized application? System.Globalization, System.Resources.

41. What’s the difference between // comments, /* */ comments and /// comments? Single-line, multi-line and XML documentation comments.

42. How do you generate documentation from the C# file commented properly with a command-line compiler? Compile it with a /doc switch.

43. What’s the difference between and XML documentation tag? Single line code example and multiple-line code example.

44. Is XML case-sensitive? Yes, so and are different elements.

45. What debugging tools come with the .NET SDK? CorDBG – command-line debugger, and DbgCLR – graphic debugger. Visual Studio .NET uses the DbgCLR. To use CorDbg, you must compile the original C# file using the /debug switch.

46. What does the This window show in the debugger? It points to the object that’s pointed to by this reference. Object’s instance data is shown.

47. What does assert() do? In debug compilation, assert takes in a Boolean condition as a parameter, and shows the error dialog if the condition is false. The program proceeds without any interruption if the condition is true.

48. What’s the difference between the Debug class and Trace class? Documentation looks the same. Use Debug class for debug builds, use Trace class for both debug and release builds.

49. Why are there five tracing levels in System.Diagnostics.TraceSwitcher? The tracing dumps can be quite verbose and for some applications that are constantly running you run the risk of overloading the machine and the hard drive there. Five levels range from None to Verbose, allowing to fine-tune the tracing activities.

50. Where is the output of TextWriterTraceListener redirected? To the Console or a text file depending on the parameter passed to the constructor.

51. How do you debug an ASP.NET Web application? Attach the aspnet_wp.exe process to the DbgClr debugger.

52. What are three test cases you should go through in unit testing? Positive test cases (correct data, correct output), negative test cases (broken or missing data, proper handling), exception test cases (exceptions are thrown and caught properly).

53. Can you change the value of a variable while debugging a C# application? Yes, if you are debugging via Visual Studio.NET, just go to Immediate window.

54. Explain the three services model (three-tier application). Presentation (UI), business (logic and underlying code) and data (from storage or other sources).

55. What are advantages and disadvantages of Microsoft-provided data provider classes in ADO.NET? SQLServer.NET data provider is high-speed and robust, but requires SQL Server license purchased from Microsoft. OLE-DB.NET is universal for accessing other sources, like Oracle, DB2, Microsoft Access and Informix, but it’s a .NET layer on top of OLE layer, so not the fastest thing in the world. ODBC.NET is a deprecated layer provided for backward compatibility to ODBC engines.

56. What’s the role of the DataReader class in ADO.NET connections? It returns a read-only dataset from the data source when the command is executed.

57. What is the wildcard character in SQL? Let’s say you want to query database with LIKE for all employees whose name starts with La. The wildcard character is %, the proper query with LIKE would involve ‘La%’.

58. Explain ACID rule of thumb for transactions. Transaction must be Atomic (it is one unit of work and does not dependent on previous and following transactions), Consistent (data is either committed or roll back, no “in-between” case where something has been updated and something hasn’t), Isolated (no transaction sees the intermediate results of the current transaction), Durable (the values persist if the data had been committed even if the system crashes right after).

59. What connections does Microsoft SQL Server support? Windows Authentication (via Active Directory) and SQL Server authentication (via Microsoft SQL Server username and passwords).

60. Which one is trusted and which one is untrusted? Windows Authentication is trusted because the username and password are checked with the Active Directory, the SQL Server authentication is untrusted, since SQL Server is the only verifier participating in the transaction.

61. Why would you use untrusted verificaion? Web Services might use it, as well as non-Windows applications.

62. What does the parameter Initial Catalog define inside Connection String? The database name to connect to.

63. What’s the data provider name to connect to Access database? Microsoft.Access.

64. What does Dispose method do with the connection object? Deletes it from the memory.

65. What is a pre-requisite for connection pooling? Multiple processes must agree that they will share the same connection, where every parameter is the same, including the security settings.

ASP.NET Interview Questions

noreply@blogger.com (Praveen Kumar Bonala) — Sun, 27 Mar 2011 17:31:00 +0530

1. Describe the role of inetinfo.exe, aspnet_isapi.dll andaspnet_wp.exe in the page loading process. inetinfo.exe is theMicrosoft IIS server running, handling ASP.NET requests among other things.When an ASP.NET request is received (usually a file with .aspx extension),the ISAPI filter aspnet_isapi.dll takes care of it by passing the request tothe actual worker process aspnet_wp.exe.

2. What’s the difference between Response.Write() andResponse.Output.Write()? The latter one allows you to write formattedoutput.

3. What methods are fired during the page load? Init() - when the pageis instantiated, Load() - when the page is loaded into server memory,PreRender() - the brief moment before the page is displayed to the user asHTML, Unload() - when page finishes loading.

4. Where does the Web page belong in the .NET Framework class hierarchy?System.Web.UI.Page

5. Where do you store the information about the user’s locale? System.Web.UI.Page.Culture

6. What’s the difference between Codebehind="MyCode.aspx.cs" andSrc="MyCode.aspx.cs"? CodeBehind is relevant to Visual Studio.NET only.

7. What’s a bubbled event? When you have a complex control, like DataGrid, writing an event processing routine for each object (cell, button, row, etc.) is quite tedious. The controls can bubble up their eventhandlers, allowing the main DataGrid event handler to take care of its constituents.

8. Suppose you want a certain ASP.NET function executed on MouseOver overa certain button. Where do you add an event handler? It’s the Attributesproperty, the Add function inside that property. So btnSubmit.Attributes.Add("onMouseOver","someClientCode();")

9. What data type does the RangeValidator control support? Integer,String and Date.

10. Explain the differences between Server-side and Client-side code? Server-side code runs on the server. Client-side code runs in the clients’ browser.

11. What type of code (server or client) is found in a Code-Behind class? Server-side code.

12. Should validation (did the user enter a real date) occur server-side or client-side? Why? Client-side. This reduces an additional request to the server to validate the users input.

13. What does the "EnableViewState" property do? Why would I want it on or off? It enables the viewstate on the page. It allows the page to save the users input on a form.

14. What is the difference between Server.Transfer and Response.Redirect? Why would I choose one over the other? Server.Transfer is used to post a form to another page. Response.Redirect is used to redirect the user to another page or site.

15. Can you explain the difference between an ADO.NET Dataset and an ADO Recordset?

• A DataSet can represent an entire relational database in memory, complete with tables, relations, and views.

• A DataSet is designed to work without any continuing connection to the original data source.

• Data in a DataSet is bulk-loaded, rather than being loaded on demand.

• There's no concept of cursor types in a DataSet.

• DataSets have no current record pointer You can use For Each loops to move through the data.

• You can store many edits in a DataSet, and write them to the original data source in a single operation.

• Though the DataSet is universal, other objects in ADO.NET come in different versions for different data sources.

16. Can you give an example of what might be best suited to place in the Application_Start and Session_Start subroutines? This is where you can set the specific variables for the Application and Session objects.

17. If I’m developing an application that must accommodate multiple security levels though secure login and my ASP.NET web application is spanned across three web-servers (using round-robin load balancing) what would be the best approach to maintain login-in state for the users? Maintain the login state security through a database.

18. Can you explain what inheritance is and an example of when you might use it? When you want to inherit (use the functionality of) another class. Base Class Employee. A Manager class could be derived from the Employee base class.

19. Whats an assembly? Assemblies are the building blocks of the .NET framework. Overview of assemblies from MSDN

20. Describe the difference between inline and code behind. Inline code written along side the html in a page. Code-behind is code written in a separate file and referenced by the .aspx page.

21. Explain what a diffgram is, and a good use for one? The DiffGram is one of the two XML formats that you can use to render DataSet object contents to XML. For reading database data to an XML file to be sent to a Web Service.

22. Whats MSIL, and why should my developers need an appreciation of it if at all? MSIL is the Microsoft Intermediate Language. All .NET compatible languages will get converted to MSIL.

23. Which method do you invoke on the DataAdapter control to load your generated dataset with data? The .Fill() method

24. Can you edit data in the Repeater control? No, it just reads the information from its data source

Overview of Indexes

noreply@blogger.com (Praveen Kumar Bonala) — Thu, 3 Mar 2011 18:58:00 +0530

Indexes are optional structures associated with tables and clusters. You can create indexes on one or more columns of a table to speed SQL statement execution on that table. Just as the index in this manual helps you locate information faster than if there were no index, an Oracle index provides a faster access path to table data. Indexes are the primary means of reducing disk I/O when properly used.

You can create many indexes for a table as long as the combination of columns differs for each index. You can create more than one index using the same columns if you specify distinctly different combinations of the columns. For example, the following statements specify valid combinations:

CREATE INDEX employees_idx1 ON employees (last_name, job_id);

CREATE INDEX employees_idx2 ON employees (job_id, last_name);

Oracle provides several indexing schemes, which provide complementary performance functionality:

B-tree indexes

B-tree cluster indexes

Hash cluster indexes

Reverse key indexes

Bitmap indexes

Bitmap join indexes

Oracle also provides support for function-based indexes and domain indexes specific to an application or cartridge.

The absence or presence of an index does not require a change in the wording of any SQL statement. An index is merely a fast access path to the data. It affects only the speed of execution. Given a data value that has been indexed, the index points directly to the location of the rows containing that value.

Indexes are logically and physically independent of the data in the associated table. You can create or drop an index at any time without affecting the base tables or other indexes. If you drop an index, all applications continue to work. However, access of previously indexed data can be slower. Indexes, as independent structures, require storage space.

Oracle automatically maintains and uses indexes after they are created. Oracle automatically reflects changes to data, such as adding new rows, updating rows, or deleting rows, in all relevant indexes with no additional action by users.

Retrieval performance of indexed data remains almost constant, even as new rows are inserted. However, the presence of many indexes on a table decreases the performance of updates, deletes, and inserts, because Oracle must also update the indexes associated with the table.

The optimizer can use an existing index to build another index. This results in a much faster index build.

Unique and Nonunique Indexes

Indexes can be unique or nonunique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns). Nonunique indexes do not impose this restriction on the column values.

Oracle recommends that unique indexes be created explicitly, using CREATE UNIQUE INDEX. Creating unique indexes through a primary key or unique constraint is not guaranteed to create a new index, and the index they create is not guaranteed to be a unique index.

A composite index (also called a concatenated index) is an index that you create on multiple columns in a table. Columns in a composite index can appear in any order and need not be adjacent in the table.

Composite indexes can speed retrieval of data for SELECT statements in which the WHERE clause references all or the leading portion of the columns in the composite index. Therefore, the order of the columns used in the definition is important. Generally, the most commonly accessed or most selective columns go first.

Overview of Synonyms

noreply@blogger.com (Praveen Kumar Bonala) — Thu, 3 Mar 2011 18:57:00 +0530

A synonym is an alias for any table, view, materialized view, sequence, procedure, function, package, type, Java class schema object, user-defined object type, or another synonym. Because a synonym is simply an alias, it requires no storage other than its definition in the data dictionary.

Synonyms are often used for security and convenience. For example, they can do the following:

Mask the name and owner of an object

Provide location transparency for remote objects of a distributed database

Simplify SQL statements for database users

Enable restricted access similar to specialized views when exercising fine-grained access control

You can create both public and private synonyms. A public synonym is owned by the special user group named PUBLIC and every user in a database can access it. A private synonym is in the schema of a specific user who has control over its availability to others.

Synonyms are very useful in both distributed and nondistributed database environments because they hide the identity of the underlying object, including its location in a distributed system. This is advantageous because if the underlying object must be renamed or moved, then only the synonym needs to be redefined. Applications based on the synonym continue to function without modification.

Synonyms can also simplify SQL statements for users in a distributed database system. The following example shows how and why public synonyms are often created by a database administrator to hide the identity of a base table and reduce the complexity of SQL statements. Assume the following:

A table called SALES_DATA is in the schema owned by the user JWARD.

The SELECT privilege for the SALES_DATA table is granted to PUBLIC.

At this point, you have to query the table SALES_DATA with a SQL statement similar to the following:

SELECT * FROM jward.sales_data;

Notice how you must include both the schema that contains the table along with the table name to perform the query.

Assume that the database administrator creates a public synonym with the following SQL statement:

CREATE PUBLIC SYNONYM sales FOR jward.sales_data;

After the public synonym is created, you can query the table SALES_DATA with a simple SQL statement:

SELECT * FROM sales;

Notice that the public synonym SALES hides the name of the table SALES_DATA and the name of the schema that contains the table.

Overview of the Sequence Generator

noreply@blogger.com (Praveen Kumar Bonala) — Thu, 3 Mar 2011 18:54:00 +0530

The sequence generator provides a sequential series of numbers. The sequence generator is especially useful in multiuser environments for generating unique sequential numbers without the overhead of disk I/O or transaction locking. For example, assume two users are simultaneously inserting new employee rows into the employees table. By using a sequence to generate unique employee numbers for the employee_id column, neither user has to wait for the other to enter the next available employee number. The sequence automatically generates the correct values for each user.

Therefore, the sequence generator reduces serialization where the statements of two transactions must generate sequential numbers at the same time. By avoiding the serialization that results when multiple users wait for each other to generate and use a sequence number, the sequence generator improves transaction throughput, and a user's wait is considerably shorter.

Sequence numbers are Oracle integers of up to 38 digits defined in the database. A sequence definition indicates general information, such as the following:

The name of the sequence

Whether the sequence ascends or descends

The interval between numbers

Whether Oracle should cache sets of generated sequence numbers in memory

Oracle stores the definitions of all sequences for a particular database as rows in a single data dictionary table in the SYSTEM tablespace. Therefore, all sequence definitions are always available, because the SYSTEM tablespace is always online.

Sequence numbers are used by SQL statements that reference the sequence. You can issue a statement to generate a new sequence number or use the current sequence number. After a statement in a user's session generates a sequence number, the particular sequence number is available only to that session. Each user that references a sequence has access to the current sequence number.

Sequence numbers are generated independently of tables. Therefore, the same sequence generator can be used for more than one table. Sequence number generation is useful to generate unique primary keys for your data automatically and to coordinate keys across multiple rows or tables. Individual sequence numbers can be skipped if they were generated and used in a transaction that was ultimately rolled back. Applications can make provisions to catch and reuse these sequence numbers, if desired.

Caution:

If your application can never lose sequence numbers, then you cannot use Oracle sequences, and you may choose to store sequence numbers in database tables. Be careful when implementing sequence generators using database tables. Even in a single instance configuration, for a high rate of sequence values generation, a performance overhead is associated with the cost of locking the row that stores the sequence value.

Define Constraints on Views

noreply@blogger.com (Praveen Kumar Bonala) — Thu, 3 Mar 2011 18:53:00 +0530

Data warehousing applications recognize multidimensional data in the Oracle database by identifying Referential Integrity (RI) constraints in the relational schema. RI constraints represent primary and foreign key relationships among tables. By querying the Oracle data dictionary, applications can recognize RI constraints and therefore recognize the multidimensional data in the database. In some environments, database administrators, for schema complexity or security reasons, define views on fact and dimension tables. Oracle provides the ability to constrain views. By allowing constraint definitions between views, database administrators can propagate base table constraints to the views, thereby allowing applications to recognize multidimensional data even in a restricted environment.

Only logical constraints, that is, constraints that are declarative and not enforced by Oracle, can be defined on views. The purpose of these constraints is not to enforce any business rules but to identify multidimensional data. The following constraints can be defined on views:

Primary key constraint

Unique constraint

Referential Integrity constraint

Given that view constraints are declarative, DISABLE, NOVALIDATE is the only valid state for a view constraint. However, the RELY or NORELY state is also allowed, because constraints on views may be used to enable more sophisticated query rewrites; a view constraint in the RELY state allows query rewrites to occur when the rewrite integrity level is set to trusted mode.

Refresh Materialized Views

Oracle maintains the data in materialized views by refreshing them after changes are made to their master tables. The refresh method can be incremental (fast refresh) or complete. For materialized views that use the fast refresh method, a materialized view log or direct loader log keeps a record of changes to the master tables.

Materialized views can be refreshed either on demand or at regular time intervals. Alternatively, materialized views in the same database as their master tables can be refreshed whenever a transaction commits its changes to the master tables.

Materialized View Logs

A materialized view log is a schema object that records changes to a master table's data so that a materialized view defined on the master table can be refreshed incrementally.

Each materialized view log is associated with a single master table. The materialized view log resides in the same database and schema as its master table.

Overview of Materialized Views

noreply@blogger.com (Praveen Kumar Bonala) — Thu, 3 Mar 2011 18:51:00 +0530

Materialized views are schema objects that can be used to summarize, compute, replicate, and distribute data. They are suitable in various computing environments such as data warehousing, decision support, and distributed or mobile computing:

In data warehouses, materialized views are used to compute and store aggregated data such as sums and averages. Materialized views in these environments are typically referred to as summaries because they store summarized data. They can also be used to compute joins with or without aggregations. If compatibility is set to Oracle9i or higher, then materialized views can be used for queries that include filter selections.

The optimizer can use materialized views to improve query performance by automatically recognizing when a materialized view can and should be used to satisfy a request. The optimizer transparently rewrites the request to use the materialized view. Queries are then directed to the materialized view and not to the underlying detail tables or views.

In distributed environments, materialized views are used to replicate data at distributed sites and synchronize updates done at several sites with conflict resolution methods. The materialized views as replicas provide local access to data that otherwise has to be accessed from remote sites.

In mobile computing environments, materialized views are used to download a subset of data from central servers to mobile clients, with periodic refreshes from the central servers and propagation of updates by clients back to the central servers.

Materialized views are similar to indexes in several ways:

They consume storage space.

They must be refreshed when the data in their master tables changes.

They improve the performance of SQL execution when they are used for query rewrites.

Their existence is transparent to SQL applications and users.

Unlike indexes, materialized views can be accessed directly using a SELECT statement. Depending on the types of refresh that are required, they can also be accessed directly in an INSERT, UPDATE, or DELETE statement.

A materialized view can be partitioned. You can define a materialized view on a partitioned table and one or more indexes on the materialized view.

how to perform a join.

noreply@blogger.com (Praveen Kumar Bonala) — Thu, 3 Mar 2011 18:49:00 +0530

Present the data in a different perspective from that of the base table

For example, the columns of a view can be renamed without affecting the tables on which the view is based.

Isolate applications from changes in definitions of base tables

For example, if a view's defining query references three columns of a four column table, and a fifth column is added to the table, then the view's definition is not affected, and all applications using the view are not affected.

Express a query that cannot be expressed without using a view

For example, a view can be defined that joins a GROUP BY view with a table, or a view can be defined that joins a UNION view with a table.

Save complex queries

For example, a query can perform extensive calculations with table information. By saving this query as a view, you can perform the calculations each time the view is queried.

Mechanics of Views

Oracle stores a view's definition in the data dictionary as the text of the query that defines the view. When you reference a view in a SQL statement, Oracle:

Merges the statement that references the view with the query that defines the view

Parses the merged statement in a shared SQL area

Executes the statement

Oracle parses a statement that references a view in a new shared SQL area only if no existing shared SQL area contains a similar statement. Therefore, you get the benefit of reduced memory use associated with shared SQL when you use views.

Globalization Support Parameters in Views

When Oracle evaluates views containing string literals or SQL functions that have globalization support parameters as arguments (such as TO_CHAR, TO_DATE, and TO_NUMBER), Oracle takes default values for these parameters from the globalization support parameters for the session. You can override these default values by specifying globalization support parameters explicitly in the view definition.

Dependencies and Views

Because a view is defined by a query that references other objects (tables, materialized views, or other views), a view depends on the referenced objects. Oracle automatically handles the dependencies for views. For example, if you drop a base table of a view and then create it again, Oracle determines whether the new base table is acceptable to the existing definition of the view.

Updatable Join Views

A join view is defined as a view that has more than one table or view in its FROM clause (a join) and that does not use any of these clauses: DISTINCT, aggregation, GROUP BY, START WITH, CONNECT BY, ROWNUM, and set operations (UNION ALL, INTERSECT, and so on).

An updatable join view is a join view that involves two or more base tables or views, where UPDATE, INSERT, and DELETE operations are permitted. The data dictionary views ALL_UPDATABLE_COLUMNS, DBA_UPDATABLE_COLUMNS, and USER_UPDATABLE_COLUMNS contain information that indicates which of the view columns are updatable. In order to be inherently updatable, a view cannot contain any of the following constructs:

A set operator

A DISTINCT operator

An aggregate or analytic function

A GROUP BY, ORDER BY, CONNECT BY, or START WITH clause

A collection expression in a SELECT list

A subquery in a SELECT list

Joins (with some exceptions)

Views that are not updatable can be modified using INSTEAD OF triggers.

Object Views

In the Oracle object-relational database, an object view let you retrieve, update, insert, and delete relational data as if it was stored as an object type. You can also define views with columns that are object datatypes, such as objects, REFs, and collections (nested tables and VARRAYs).

Inline Views

An inline view is not a schema object. It is a subquery with an alias (correlation name) that you can use like a view within a SQL statement.

Overview of Views

noreply@blogger.com (Praveen Kumar Bonala) — Thu, 3 Mar 2011 18:47:00 +0530

A view is a tailored presentation of the data contained in one or more tables or other views. A view takes the output of a query and treats it as a table. Therefore, a view can be thought of as a stored query or a virtual table. You can use views in most places where a table can be used. For example, the employees table has several columns and numerous rows of information. If you want users to see only five of these columns or only specific rows, then you can create a view of that table for other users to access. Because views are derived from tables, they have many similarities. For example, you can define views with up to 1000 columns, just like a table. You can query views, and with some restrictions you can update, insert into, and delete from views. All operations performed on a view actually affect data in some base table of the view and are subject to the integrity constraints and triggers of the base tables. You cannot explicitly define triggers on views, but you can define them for the underlying base tables referenced by the view. Oracle does support definition of logical constraints on views.

How Views are Stored
Unlike a table, a view is not allocated any storage space, nor does a view actually contain data. Rather, a view is defined by a query that extracts or derives data from the tables that the view references. These tables are called base tables. Base tables can in turn be actual tables or can be views themselves (including materialized views). Because a view is based on other objects, a view requires no storage other than storage for the definition of the view (the stored query) in the data dictionary.

How Views Are Used
Views provide a means to present a different representation of the data that resides within the base tables. Views are very powerful because they let you tailor the presentation of data to different types of users. Views are often used to: Provide an additional level of table security by restricting access to a predetermined set of rows or columns of a table.

Hide data complexity
For example, a single view can be defined with a join, which is a collection of related columns or rows in multiple tables. However, the view hides the fact that this information actually originates from several tables.
Simplify statements for the user For example, views allow users to select information from multiple tables without actually

Foreign Key Constraint

noreply@blogger.com (Praveen Kumar Bonala) — Thu, 3 Mar 2011 17:28:00 +0530

Whenever two tables contain one or more common columns, you can enforce the relationship between the tables through a referential integrity constraint with a foreign key. A foreign key requires that all column values in the child table exist in the parent table. The table that includes the foreign key is called the dependent or child table. The table that is referenced is called the parent table. An example of a foreign key constraint is when the department column of the employees table (child) must contain a department ID that exists in the departments table (parent).

Foreign keys can be made up of multiple columns. Such a composite foreign key must reference a composite primary or unique key of the exact same structure, with the same number of columns and the same datatypes. Because composite primary and unique keys are limited to 32 columns, a composite foreign key is also limited to 32

columns. You must use the same datatype for corresponding columns in the parent and child tables. The column names do not need to match. For performance purposes, you might want to add an index to the columns you define

in a child table when adding a foreign key constraint. Oracle Database XE does not do this for you automatically.

When you create a foreign key constraint on a table, you can specify the action to take when rows are deleted in the referenced (parent) table. These actions include:

■ Disallow Delete - Blocks the delete of rows from the referenced table when there are dependent rows in the table.

■ Cascade Delete - Deletes the dependent rows from the table when the corresponding parent table row is deleted from the referenced table.

■ Null on Delete - Sets the foreign key column values in the table to null values when the corresponding table row is deleted from the referenced table.

Unique Constraint and Primary Key Constraint

noreply@blogger.com (Praveen Kumar Bonala) — Thu, 3 Mar 2011 17:26:00 +0530

Unique Constraint
A unique constraint requires that every value in a column be unique. That is, no two rows can have duplicate values in a specified column or combination of columns. Choose columns for unique constraints carefully. The purpose of these constraints is different from that of primary keys. Unique key constraints are appropriate for any column where duplicate values are not allowed. Primary keys identify each row of the table uniquely, and typically contain values that have no significance other than being unique. In the employees table, the email column has a unique key constraint because it is important that the e-mail address for each employee is unique. Note that the email column has a NOT NULL constraint.Some examples of good unique keys include:

■ An employee social security number, where the primary key might be the employee number

■ A truck license plate number, where the primary key might be the truck number

■ A customer phone number, consisting of the two columns area_code and local_phone, where the primary key might be the customer number

■ A department name and location, where the primary key might be the department number Primary Key Constraint A primary key requires that a column (or combination of columns) be the unique identifier of the row and ensures that no duplicate rows exist. A primary key column cannot contain NULL values. Each table can have only one primary key. Use the following guidelines when selecting a primary key:

■ Whenever practical, create a sequence number generator to generate unique numeric values for your primary key values. Choose a column whose data values are unique, because the purpose of a primary

key is to uniquely identify each row of the table.

■ Choose a column whose data values are never changed. A primary key value is only used to identify a row in the table, and its data should never be used for any other purpose. Therefore, primary key values should rarely or never be changed. Choose a column that does not contain any null values. A PRIMARY KEY

constraint, by definition, does not allow any row to contain a null value in any column that is part of the primary key.

■ Choose a column that is short and numeric. Short primary keys are easy to type.

■ Minimize your use of composite primary keys. A composite primary key constraint applies to more than one column. Although composite primary keys are allowed, they do not satisfy all of the other recommendations. For example, composite primary key values are long and cannot be assigned by sequence

numbers.

NOT NULL Constraint

noreply@blogger.com (Praveen Kumar Bonala) — Thu, 3 Mar 2011 17:22:00 +0530

The NOT NULL constraint is a column-level constraint that requires that the column must contain a value whenever a row is inserted or updated. The NOT NULL constraint must be defined as part of the column definition. Use a NOT NULL constraint when the data is required for the integrity of the database. For example, if all employees must belong to a specific department, then the column that contains the department identifier should be defined with a NOT NULL constraint. On the other hand, do not define a column as NOT NULL if the data might be unknown or might not exist when rows are added or changed, for example, the second, optional line in a mailing address.

A primary key constraint automatically adds a NOT NULL constraint to the columns included in the primary key, in addition to enforcing uniqueness among the values.

Check Constraint

A check constraint requires that a column (or combination of columns) satisfies a condition for every row in the table. A check constraint must be a Boolean expression that is evaluated using the column value about to be inserted or updated to the row. Use check constraints when you need to enforce integrity rules based on logical

expressions, such as comparisons. Never use CHECK constraints when any of the other types of integrity constraints can provide the necessary checking. Examples of check constraints include the following:

■ A check constraint on employee salaries so that no salary value is less than 0.

■ A check constraint on department locations so that only the locations Boston, New York, and Dallas are allowed.

■ A check constraint on the salary and commissions columns to prevent the commission from being larger than the salary.

Ensuring Data Integrity in Tables With Constraints

noreply@blogger.com (Praveen Kumar Bonala) — Thu, 3 Mar 2011 17:19:00 +0530

Ensuring Data Integrity in Tables With Constraints

With Oracle Database XE, you can define integrity constraints to enforce business rules on data in your tables to preserve the integrity of the data. Business rules specify conditions and relationships that must always be true, or must always be false. For example, in a table containing employee data, the employee e-mail column must be

unique. Similarly, in this table you cannot have two employees with the same employee ID. When an integrity constraint applies to a table, all data in the table must conform to the corresponding rule. When you issue a SQL statement that inserts or modifies data in the table, Oracle Database XE ensures that the new data satisfies the integrity constraint, without the need to do any checking within your program. Any attempt to insert, update, or remove a row that violates a constraint results in an error, and the statement is rolled back. Likewise, any attempt to apply a new constraint to a populated table also results in an error if any existing row violates the new constraint. Constraints can be created and, in most cases, modified with a number of different status values. The options include enabled or disabled, which determine if the constraint is checked when rows are added, modified, or removed; and deferred or immediate, which cause constraint validation to occur at the end of a transaction or at the end of a statement, respectively. You can enforce rules by defining integrity constraints more reliably than by adding logic to your application. Oracle Database XE can check that all the data in a table obeys an integrity constraint faster than an application can. Constraints can be defined at the column level or at the table level:

■ Column-level constraints are syntactically defined where the column to which the constraint applies is defined. These constraints determine what values are valid in the column. When creating a table with Object Browser, the only constraint defined at the column level is the NOT NULL constraint, which requires that a value is included in this column for every row in the table. Table-level constraints are syntactically defined at the end of the table definition and apply to the entire table. With Object Browser, you can create primary key,

foreign key, unique, and check constraints.

Column Default Value

You can define default values that are values that are automatically stored in the column whenever a new row is inserted without a value being provided for the column. When you define a column with a default value, any new rows inserted into the table store the default value unless the row contains an alternate value for the column. Assign default values to columns that contain a typical value. For example, in the employees table, if most employees work in the sales department, then the default value for the department_id column can be set to the ID of the sales department. Depending on your business rules, you might use default values to represent zero or FALSE, or leave the default values as NULL to signify an unknown value. Default values can be defined using any literal, or almost any expression including SYSDATE,

which is a SQL function that returns the current date.

CREATE TABLE personal_info (

employee_id NUMBER(6,0) NOT NULL,

birth_date DATE NOT NULL,

social_security_id VARCHAR2(12) NOT NULL,

marital_status VARCHAR2(10),

dependents_claimed NUMBER(2,0) DEFAULT 1,

contact_name VARCHAR2(45) NOT NULL,

contact_phone VARCHAR2(20) NOT NULL,

contact_address VARCHAR2(80) NOT NULL

);

Datatypes

noreply@blogger.com (Praveen Kumar Bonala) — Thu, 3 Mar 2011 17:15:00 +0530

Using Datatypes

A datatype associates a fixed set of properties with values that are used in a column of a table or in an argument of a procedure or function. The properties of datatypes cause Oracle Database XE to treat values of one datatype differently from values of another datatype. For example, Oracle Database XE can use the addition operator on values of numeric datatypes, but not with values of some other datatypes.

The datatypes supported by Oracle Database Express Edition include:

■ Character datatypes

■ Numeric datatypes

■ Date and time (date-time) datatypes

■ Large Object (LOB) datatypes

When you create a table, you must specify a datatype for each of its columns to define the nature of the data to be stored in the column. For example, a column defined as a DATE datatype cannot accept the value February 29 (except for a leap year) or the values 2 or SHOE. When specifying a datatype, you can also indicate the longest value that can be placed in the column. In most cases, you only need columns of NUMBER, VARCHAR2, and DATE datatypes to create a definition of a table.

What Are the Character Datatypes?

You can use the following SQL datatypes to store character (alphanumeric) data:

■ The VARCHAR2 datatype stores variable-length character literals. When creating a VARCHAR2 column in a table, you must specify a string length between 1 and 4000 bytes for the VARCHAR2 column. Set the size to the maximum number of characters to be stored in the column. For example, a column to hold

the last name of employees can be restricted to 25 bytes by defining it as

VARCHAR2(25).

For each row, Oracle Database XE stores each value in the column as a variable-length field unless a value exceeds the column's maximum length, in which case Oracle returns an error. Using VARCHAR2 saves on space used by the table. For most cases where you need to store character data, you would use the

VARCHAR2 datatype.

■ The CHAR datatype stores fixed-length character literals. When creating a CHAR column in a table, you must specify a string length between 1 and 2000 bytes for the CHAR column. For each row, Oracle Database XE stores each value in the column as a fixed-length field. If the value of the character data is

less than specified length of the column, then the value is blank-padded to the fixed length. If a value is too large, Oracle Database XE returns an error.

■ NCHAR and NVARCHAR2 datatypes store only Unicode character data.

The NVARCHAR2 datatype stores variable-length Unicode character literals. The NCHAR datatype stores fixed-length Unicode character literals Choosing Between the Character Datatypes. When deciding which datatype to use for a column that will store character data in a table, consider the following:

■ Space usage

To store data more efficiently, use the VARCHAR2 datatype. The CHAR datatype adds blanks to maintain a fixed column length for all column values, whereas the VARCHAR2 datatype does not add extra blanks.

■ Comparison semantics

Use the CHAR datatype when trailing blanks are not important in string comparisons. Use the VARCHAR2 datatype when trailing blanks are important in string comparisons.

■ Future compatibility

The CHAR and VARCHAR2 datatypes are fully supported.

What Are the Numeric Datatypes?

The following SQL datatypes store numeric data:

■ NUMBER

■ BINARY_FLOAT

■ BINARY_DOUBLE

Use the NUMBER datatype to store integers and real numbers in a fixed-point or floating-point format. Numbers using this datatype are guaranteed to be portable among different Oracle database platforms. For nearly all cases where you need to store numeric data, you would use the NUMBER datatype. When defining numeric data, you can use the precision option to set the maximum number of digits in the number, and the scale option to define how many of the digits are to the right of the decimal point. For example, a field to hold the salary of an employee can be defined as NUMBER(8,2), providing 6 digits for the primary unit of currency (dollars, pounds,marks, and so on) and two digits for the secondary unit (cents, pennies, pfennigs, and so on).

Oracle Database XE provides the numeric BINARY_FLOAT and BINARY_DOUBLE datatypes exclusively for floating-point numbers. They support all of the basic functionality provided by the NUMBER datatype. However, while the NUMBER datatype uses decimal precision, BINARY_FLOAT and BINARY_DOUBLE datatypes use binary precision. This enables faster arithmetic calculations and usually reduces storage requirements.

Using the NUMBER Datatype

The NUMBER datatype stores zero as well as positive and negative fixed numbers with absolute values from 1.0 x 10-130 to (but not including) 1.0 x 10126. If you specify an arithmetic expression whose value has an absolute value greater than or equal to 1.0 x 10126, then Oracle Database XE returns an error.

The NUMBER datatype can be specified with a precision (p) and a scale (s) designator. Precision is the total number of significant decimal digits, where the most significant digit is the left-most, nonzero digit, and the least significant digit is the right-most, known digit. Scale is the number of digits from the decimal point to the least.

You can specify a NUMBER datatype as follows:

■ NUMBER(p) for an integer

This represents a fixed-point number with precision p and scale 0, and is equivalent to NUMBER(p,0).

■ NUMBER(p, s) for a fixed-point number

This explicitly specifies the precision (p) and scale (s). It is good practice to specify the scale and precision of a fixed-point number column for extra integrity checking on input. Specifying scale and precision does not force all values to a fixed length. If a value exceeds the precision, then Oracle Database XE returns an error. If a value exceeds the scale, then Oracle Database XE rounds it.

■ NUMBER for a floating-point number

The absence of precision and scale designators specifies the maximum range and precision for an Oracle number.

Storage of Scale and Precision

Actual Data Specified As Stored As 123.8915 NUMBER 123.8915 123.8915 NUMBER(3) 124

123.8915 NUMBER(4,1) 123.9

123.8915 NUMBER(5,2) 123.89

123.8915 NUMBER(6,3) 123.892

123.8915 NUMBER(7,4) 123.8915

1.238915e2 NUMBER(7,4) 123.8915

Using Floating-Point Number Formats

The BINARY_FLOAT and BINARY_DOUBLE datatypes store floating-point data in the 32-bit IEEE 754 format and the double precision 64-bit IEEE 754 format respectively.

Compared to the Oracle NUMBER datatype, arithmetic operations on floating-point data are usually faster for BINARY_FLOAT and BINARY_DOUBLE. High-precision values require less space when stored as BINARY_FLOAT and BINARY_DOUBLE datatypes.

The BINARY_FLOAT datatype has a maximum positive value equal to 3.40282E+38F and the minimum positive value equal to 1.17549E-38F. The BINARY_DOUBLE datatype has a maximum positive value equal to

1.79769313486231E+308 and the minimum positive value equal to

2.22507485850720E-308.Storing Date and Time Data Oracle Database XE stores dates in its own internal format that corresponds to century, year, month, day, hour, minute, and second. For input and output of dates, the standard Oracle Database XE default date format is DD-MON-RR. The RR date-time format element enables you store 20th century dates in the 21st century by specifying only the last two digits of the year. Time is stored in a 24-hour format as HH24:MI:SS. Oracle Database Express Edition provides various SQL functions to calculate and convert date-time data.

Using DATE and TIMESTAMP Datatypes

Oracle Database supports the following date and time (date-time) datatypes:

■ DATE

■ TIMESTAMP

■ TIMESTAMP WITH TIME ZONE

■ TIMESTAMP WITH LOCAL TIME ZONE

DATE and TIMESTAMP Examples

Datatype Example

DATE 09-DEC-05

TIMESTAMP 09-DEC-05 02.05.49.000000 PM

TIMESTAMP WITH TIME ZONE 09-DEC-05 02.05.49.000000 PM -08:00

TIMESTAMP WITH LOCAL TIME ZONE 09-DEC-05 02.05.49.000000 PM

Using the DATE Datatype Use the DATE datatype to store point-in-time values (dates and times) in a table. For example, a column to hold the date that an employee is hired can by defined as a DATE datatype. An application that specifies the time for a job might also use the DATE datatype. For most cases where you need to store date data, you would use the DATE datatype. DATE columns are automatically formatted by Oracle Database XE to include a date and time component. The DATE datatype stores the century, year, month, day, hours, minutes, and seconds. The valid date range is from January 1, 4712 BC to December 31,

9999 AD. Although both the date and time are stored in a date column, by default, the date portion is automatically displayed for you, when retrieving date data. However, Oracle Database Express Edition enables you great flexibility in how you can display your dates and times.

Using the TIMESTAMP Datatype Use the TIMESTAMP datatype to store values that are precise to fractional seconds. An application that must decide which of two events occurred first might use TIMESTAMP. Using the TIMESTAMP WITH TIME ZONE Datatype Because the TIMESTAMP WITH TIME ZONE datatype can also store time zone information, it is particularly suited for recording date information that must be gathered or coordinated across geographic regions. Using the TIMESTAMP WITH LOCAL TIME ZONE Datatype Use the TIMESTAMP WITH LOCAL TIME ZONE datatype when the time zone is not significant. For example, you might use it in an application that schedules teleconferences, where participants each see the start and end times for their own time zone.

The TIMESTAMP WITH LOCAL TIME ZONE datatype is appropriate for two-tier applications in which you want to display dates and times that use the time zone of the client system. It is generally inappropriate in three-tier applications because data displayed in a Web browser is formatted according to the time zone of the Web server, not the time zone of the browser. The Web server is the database client, so its local time

is used.

Storing Large Objects

Large Objects (LOBs) are a set of datatypes that are designed to hold large amounts of data. A LOB can hold up to a maximum size ranging from 8 terabytes to 128 terabytes depending on how your database is configured. Storing data in LOBs enables you to access and manipulate the data efficiently in your application.

The BLOB, CLOB, and NCLOB datatypes are internal LOB datatypes and are stored in the database. The BFILE datatype is the only external LOB datatype and is stored in an operating system file, outside the database.

Managing Tables

Tables are the basic unit of data storage in an Oracle database. They hold all user-accessible data. A table is a two-dimensional object made up of columns and rows. For example, the employees table includes (vertical) columns called employee_id, first_name, and last_name. Each (horizontal) row in the table contains a value for employee name and ID number. The most common type of table in an Oracle database is a relational table.

Database Objects

noreply@blogger.com (Praveen Kumar Bonala) — Thu, 3 Mar 2011 17:00:00 +0530

You need to create database objects before you start developing your application. These database objects are primarily used to store and organize the data that your application manipulates. These databases objects include tables, indexes, views, sequences and synonyms. When creating some database objects, you need to specify a datatype for the data that is used by the object. When you create a table, you must specify a datatype for each of its columns. A datatype associates a fixed set of properties with the values that can be used in a column, or in an argument of a procedure or function. Each column value and constant in a SQL statement has a datatype, which is associated with a specific storage format, constraints, and a valid range of values. The most common data-types are character, numeric, and date.

Overview of Managing Objects

You need to create tables, indexes, and possibly other database objects in a schema before you start developing your application. A schema is a collection of database objects. A schema is owned by a database user and has the same name as that user, such as the HR schema. Schema objects are logical structures created by users. Objects

can define areas of the database to hold data, such as tables, or can consist of just a definition, such as views.

Tables are the basic database objects and contain all the user data. When creating a table, it is important that you define that data that you want to store in the table. You need to specify the datatype of the data and any restrictions on the range of values.

Database Objects for Your Application

Some object types have many more management options than others, but most have a number of similarities. Every object in the database belongs to just one schema and has a unique name within that schema. Therefore, when you create an object, you must ensure it is in the schema where you intend to store it. Generally, you place all of the

objects that belong to a single application in the same schema. A database object name must abide by certain rules. For example, object names cannot be longer than 30 bytes and must begin with a letter. If you attempt to create an object with a name that violates any of these rules, then Oracle Database XE raises an error.

Overview of SQL Command Line

noreply@blogger.com (Praveen Kumar Bonala) — Thu, 3 Mar 2011 16:54:00 +0530

SQL Command Line (SQL*Plus) is a command-line tool for accessing Oracle Database XE. It enables you to enter and run SQL, PL/SQL, and SQL*Plus commands and statements to:
■ Query, insert, and update data
■ Execute PL/SQL procedures
■ Examine table and object definitions
■ Develop and run batch scripts
■ Perform database administration

You can use SQL Command Line to generate reports interactively, to generate reports
as batch processes, and to write the results to a text file, to a screen, or to an HTML file for browsing on the Internet.

Starting and Exiting SQL Command Line

To start SQL Command Line from the operating-system command prompt, enter the
following:
sqlplus
When prompted, enter the username and password of the user account (schema) that you want to access in the local database. For example, enter HR for the username and my_hr_password for the password when prompted. You can also include the username and password when you start SQL Command Line.
For example:
sqlplus hr/my_hr_password
If you want to connect to a database running on a remote system, you need to include a connect string when starting SQL Command Line. For example:
sqlplus hr/my_hr_password@host_computer_name
After you have started SQL Command Line, the SQL> prompt displays as follows:
SQL>
At the SQL> prompt, you can enter SQL statements.
When you want to exit SQL Command Line, enter EXIT at the SQL prompt, as follows:
SQL> EXIT

Displaying Help With SQL Command Line

To display a list of Help topics for SQL Command Line, enter HELP INDEX at the SQL
prompt as follows:
SQL> HELP INDEX
From the list of SQL Command Line Help topics, you can display Help about an individual topic by entering HELP with a topic name. For example, the following displays Help about the SQL Command Line COLUMN command, which enables you to format column output:
SQL> HELP COLUMN

Entering and Executing SQL Statements and Commands

To enter and execute SQL statements or commands, enter the statement or command at the SQL prompt. At the end of a SQL statement, put a semi-colon (;) and then press the Enter key to execute the statement. For example:
SQL> SELECT * FROM employees;
If the statement does not fit on one line, enter the first line and press the Enter key. Continue entering lines, and terminate the last line with a semi-colon (;). For example:
SQL> SELECT employee_id, first_name, last_name
2 FROM employees
3 WHERE employee_id >= 105 AND employee_id <= 110; The output from the previous SELECT statement is similar to: EMPLOYEE_ID FIRST_NAME LAST_NAME ----------- -------------------- ----------------------- 105 David Austin 106 Valli Pataballa 107 Diana Lorentz 108 Nancy Greenberg 109 Daniel Faviet 110 John Chen 6 rows selected. Note that a terminating semi-colon (;) is optional with SQL Command Line commands, such as DESCRIBE o r SET, but required with SQL statements. SQL Command Line DESCRIBE Command SQL Command Line provides the DESCRIBE command to display a description of a database object. For example, the following displays the structure of the employees table. This description is useful when constructing SQL statements that manipulate the employees table. SQL> DESCRIBE employees
Name Null? Type
---------------------------------------- -------- ------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
SQL Command Line SET Commands
The SQL Command Line SET commands can be used to specify various SQL
Command Line settings, such as the format of the output from SQL SELECT
statements. For example, the following SET commands specify the number of lines for each page and the number of characters for each line in the output:
SQL> SET PAGESIZE 200
SQL> SET LINESIZE 140
To enable output from PL/SQL blocks with DBMS_OUTPUT.PUT_LINE, use the
following:
SQL> SET SERVEROUTPUT ON
To view all the settings, enter the following at the SQL prompt:
SQL> SHOW ALL
Running Scripts From SQL Command Line
You can use a text editor to create SQL Command Line script files that contain
SQL*Plus, SQL, and PL/SQL statements. For consistency, use the .sql extension for
the script file name.
A SQL script file is executed with a START or @ command. For example, in a Windows
environment, you can execute a SQL script as follows:
SQL> @c:\my_scripts\my_sql_script.sql
A SQL script file can be executed in a Linux environment as follows:
SQL> START /home/cjones/my_scripts/my_sql_script.sql
You can use SET ECHO ON to cause a script to echo each statement that is executed. You can use SET TERMOUT OFF to prevent the script output from displaying on the screen. When running a script, you need to include the full path name unless the script is located in the directory from which SQL Command Line was started, or the script is located in the default script location specified by the SQLPATH environment variable.
Spooling From SQL Command Line The SPOOL command can be used to direct the output from SQL Command Line to a disk file, which enables you to save the output for future review.
To start spooling the output to an operating system file, you enter the SPOOL
command followed by a file name. For example SQL> SPOOL my_log_file.log
If you want to append the output to an existing file:
SQL> SPOOL my_log_file.log APPEND
To stop spooling and close a file, enter the following:
SQL> SPOOL OFF
Using Variables With SQL Command Line
You can create queries that use variables to make SELECT statements more flexible. You can define the variable before running a SQL statement, or you specify that the statement prompts for a variable value at the time that the SQL statement is run. When using a variable in a SQL statement, the variable name must be begin with an ampersand (&).
Prompting for a Variable Value in a Query
You can use & to identify a variable that you want to define dynamically
Prompting for a Variable Value in SQL Command Line
-- prompt for employee_id in a query, you need to enter a valid ID such as 125
SELECT employee_id, last_name, job_id FROM employees
WHERE employee_id = &employee_id;
When you run the previous SELECT statement, the output is similar to:
Enter value for employee_id: 125
...
EMPLOYEE_ID LAST_NAME JOB_ID
----------- ------------------------- ----------
125 Nayer ST_CLERK
Reusing a Variable Value in a Query
You can use && to identify a variable that you want to define dynamically multiple times, but only want to prompt the user once.
&&column_name variable causes the SQL statement to prompt for a value when the statement is executed. The value that is entered is substituted for all remaining occurrences of &&column_name in the SQL statement.
Defining a Variable Value for a Query
-- define a variable value for a query as follows
DEFINE job_id = "ST_CLERK"
-- run a query using the defined value for job_id (ST_CLERK)
SELECT employee_id, last_name FROM employees WHERE job_id = '&job_id';

Overview of SQL

noreply@blogger.com (Praveen Kumar Bonala) — Thu, 3 Mar 2011 16:44:00 +0530

SQL is nonprocedural language for accessing a database. You run SQL statements commands to perform various tasks, such as retrieving data from tables in Oracle Database XE. The SQL language automatically handles how to navigate the database and perform the desired task. All database operations are performed using SQL statements. With SQL statements you can perform the following:

■ Query, insert, and update data in tables

■ Format, perform calculations on, store, and print from query results

■ Examine table and object definitions

Oracle SQL statements are divided into several categories:

■ Data Manipulation Language (DML) statements These statements query, insert, update, and delete data in tables.

■ Transaction Control statements These statements commit or roll back the processing of transactions. A group of changes that you make is referred to as a transaction.

■ Data Definition Language (DDL) statements These statements create, alter, and drop database objects.

A statement consists partially of SQL reserved words, which have special meaning in SQL and cannot be used for any other purpose. For example, SELECT and UPDATE are reserved words and cannot be used as table names.

List of the SQL reserved words

ACCESS, ADD*, ALL*, ALTER*, AND*, ANY*, AS*, ASC*, AUDIT

BETWEEN*, BY*, CHAR*, CHECK*, CLUSTER, COLUMN, COMMENT, COMPRESS, CONNECT*, CREATE*, CURRENT*

DATE*, DECIMAL*, DEFAULT*, DELETE*, DESC*, DISTINCT*, DROP*

ELSE*, EXCLUSIVE, EXISTS

FILE, FLOAT*, FOR*, FROM*

GRANT*, GROUP*

HAVING*

IDENTIFIED, IMMEDIATE*, IN*, INCREMENT, INDEX, INITIAL, INSERT*, INTERSECT*, INTO*, IS*

LEVEL*, LIKE*, LOCK, LONG

MAXEXTENTS, MINUS, MLSLABEL, MODE, MODIFY

NOAUDIT, NOCOMPRESS, NOT*, NOWAIT, NULL*, NUMBER

OF*, OFFLINE, ON*, ONLINE, OPTION*, OR*, ORDER*

PCTREE, PRIOR*, PRIVLEGES*, PUBLIC*

RAW, RENAME, RESOURCE, REVOKE*, ROW, ROWID, ROWNUM, ROWS*

SSELECT*, SESSION*, SET*, SHARE, SIZE*, SMALLINT*, START, SUCCESSFUL, SYNONYM, SYSDATE

TABLE*, THEN*, TO*, TRIGGER

UID, UNION*, UNIQUE*, UPDATE*, USER*

VALIDATE, VALUES*, VARCHAR*, VARCHAR2, VIEW*

WHENEVER*, WHERE, WITH*

Microsoft Management Console has stopped working.

noreply@blogger.com (Praveen Kumar Bonala) — Mon, 28 Feb 2011 17:24:00 +0530

Hiii I am Working on windows server 2008 32-bit standard edition. When i am trying to open Server Manager i am getting error Microsoft Management Console has stopped working. and evend error is APPcrash. i did maximum troubleshooting like installed .net framework 3.5 and activated windows, all services are running and hardware compability. Even i googled for the same if any of you know solution please write in comment or send mail to praveenbonala@gmail.com
Thank you....

How to do post installation steps

noreply@blogger.com (Praveen Kumar Bonala) — Fri, 18 Feb 2011 20:27:00 +0530

1. Command To set administrator password C:\users\admin> Net user administrator yourpasswordhere. 2. Command to configure network interface: By default it will take IP from DHCP. If you dont have DHCP and you want to configure manually use netsh command To list all network interfaces use following command C:\users\admin>netsh interface ipv4 show interfaces. To assign Ip use following commnad C:\users\admin>netsh interface ipv4 set address name=2 source=static address=192.168.1.1 mask=255.255.255.0 gateway=192.168.1.100 values are taken for example please give apropriate values. 3. Command to Activating server Once trial period expired you need to activate windows. To activate windows run following command. C:\users\admin>Slmgr.vbs -at0 4. command to rename computer; C:\users\admin> Wmic computersystem where name=" %computername%" rename name="your name here" this command should results in a return value=0 to indicate sucessfull rename. 5. Command to join computer in domain: C:\users\admin>Netdom Join %computername% /domain:practice.com /userd:admin /passwordd:* Note: If you dont want to expose password here then type * it will prompt for password which you can give your password securly. 6. Configure Automatic Updates: To enable Automatic updates manually C:\users\admin> CScript Scregedit.wsf /AU 4 To Turnoff Automatic updates C:\users\admin>CScript Sregedit.wsf /AU 1 7. To Enable Remote Administration C:\users\admin>SCregedit.wsf /AR 0

Post installation tasks in windows server 2008 core installation

noreply@blogger.com (Praveen Kumar Bonala) — Fri, 18 Feb 2011 20:17:00 +0530

As we discussed earlier core installation is great feature for windows server 2008 it is very help full for who need to install ADC where system admin is not present. It is much more secure because it comes only with command prompt no GUI. It support only sevel roles listed below.... 1. Active directory domain service. 2. Active directory light weight directory services. 3. File server. 4. DHCP server. 5. DNS server. 6. Print server. 7. Streaming media server. In Windows server 2008 you need to do following Post- Installation Tasks

Set Admin Password
Configure your network interface
Activate server
Rename server and join it into domain
configure automatic updates
Enable remote administration
Configure Windows Firewall

Installing Windows Server 2008.

noreply@blogger.com (Praveen Kumar Bonala) — Fri, 18 Feb 2011 19:57:00 +0530

Installing windows server 2008 just like installing other operating systems but windows server 2008 reduces user interaction while installing. It is just like unattended installation. At starting you just give inputs after windows server 2008 never ask user to give inputs like all other operation systems. Installation of windows server 2008 contains following steps. 1. First of all you should require DVD drive and make it as your first boot device. 2. After loading all files first it will ask for media like language , time and currency. keyboard. 3. Next you will get installation screen, in this screen you will get install now option and repair option for repair your windows server 2008, and know more about installation option. 4. Next it prompts for product key you can skip this step 5. In this step it will prompt for opetaing system selection. One important thing to tell here is in all other os you will need seperate boot disk for different types of version but in windows server 2008 all versions will come in only one boot disk. Like Enterprise edition, standard edition , core installation or full installation. You can select desired os edition here. next you need to agree licence agriment. 6. Next you will get installation types like advanced and custom and in which partiotion you want to install. 7. once you selected partition and start installation again you no need to give any inputs till installation completes. Installation process contains following steps

Copying files
Extracting files
Installing features
Installing Updates
Completing Installation

8. After sucessfull installation it will prompt for set administrator password.

All above steps included in installing Windows server 2008.

Here one more thing i want to discuss with you people...basicall all earlier version of operating system uses NTLDR to load os but in windwos server 2008 it uses BCD instead of NTLDR. BCD stands for Boot Configuration Data

Introduction To Windows server 2008

noreply@blogger.com (Praveen Kumar Bonala) — Fri, 18 Feb 2011 19:44:00 +0530

After 2003 server sucess Microsoft introduced Windows server 2008. Windows server 2008 comes with many advanced features to ease up the system administrators task. First of all we can see what are the minimum system requirements for Windows server 2008. Processor : Minimum - 1 GHz Recomonded - 2 GHz Optimal - 3 GHZ or faster Itanium 2 processor required. Memory : Minimum - 512MB Recomonded - 1 GB Optimal - 2 GB for full Installation 1GB for core installation(Will Discuss further regarding core installation) Disk Space : Min. - 8 GB Recomonded - 40 GB for full, 10 GB for core Optimal - 80 GB for full, 40 GB for Core. Drive: DVD drive. All these above are minimum system requirements to install windows server 2008. When installing windows server 2008, we have two choices 1. Full installation : Just like all remaining operating systems. 2. Core installation: This is new concept introduced in windows server 2008. If you install core os it doesn't include a GUI or any other unneeded services. It only installs key roles like Active Directory, DNS, DHCP..... After installing core installation you will not get GUI instead it gets only command prompt.