In char it allocates the memory space as static where as in
varchar2 it allocates the memory space as dynamic
char is a space mapping function where as in varchar2 it is
not mapping the space it occupies the exact size of the
string
Another difference is accessing a variable declared as char
is faster than, accessing a varchar2.
Friday, October 29, 2010
Monday, October 25, 2010
Data Types in Pl/SQL and SQLA)Scalar Types :- They are Predefined by oracle.
They are classified as
1)Integer
2)Character
3)Date
4)Boolean
B)Composite Types:-They are used mostly in ORDMS(Table,varray).
Record-
%ROWTYPE
In PL/SQL, records are used to group data. A record consists of a number of related
fields in which data values can be stored. The %ROWTYPE attribute provides a record
type that represents a row in a table. The record can store an entire row of data
selected from the table or fetched from a cursor or cursor variable .Even though datatypes of each field is different it considers al of them as a single logical unit.
3)Reference Types:-Used in cursors
4)Lob types:-Used to store huge amount of data
a)BLob-used to store binary data like images,audio .Size is 4gb
b)CLob - used to store character data like files etc Size is 4gb
Benefits and new features of Microsoft Office 2010
1)Office 2010 lets you work how, when, and where you want, letting you get things from a PC, the Web, and even a smartphone.
2)Use Office 2010 virtually anywhere: PC, phone, browser
With Microsoft Office 2010, you can review and do minor editing on Word, Excel, PowerPoint, and OneNote 2010 documents, virtually anywhere, by using Office Web Apps from more locations on more devices.
3)Keep your people productive on the go. Office 2010 lets you access and edit documents stored on a server while you’re offline with SharePoint Workspace 2010, and then automatically syncs these changes when you’re back online. So you stay productive while on the go.
4)Support your employees, regardless of location or device. Office 2010 saves you time and money by helping you deploy and manage Office on the PC, the smartphone, and the Web, all from within familiar Microsoft System Center tools.
5)Bring ideas to life
Create and deliver presentations that help you stand apart from the competition. Office 2010 puts you in the director’s chair, enabling you to create dazzling digital content in PowerPoint 2010 without the need for expensive third-party tools. And you can broadcast your presentation to anyone with a browser, even if they don’t have PowerPoint.
7) Daily Sceduler:- OneNote 2010 pulls together everything from daily sales figures to digital images, so all the information you need is easily accessible. You can also create side notes that stay on your screen as you move between different programs, so you can keep your thoughts organized as you multi-task. This helps you stay organized while saving you time.
Turn intuition into insights to make quicker and more informed business decisions. Excel 2010 provides tools for improved data visualization, which can give you key insights into business processes and tailor messaging and products to best meet customer needs. Whole trends can be conveyed in a single cell with Sparklines. And there are more options in styles and icons in conditional formatting, as well as the ability to highlight specific items like such as “max/min” in a single click.
7) Fast and more Presentive
8)Conversion easy :- Reduce travel costs and spend less on third-party conferencing tools. Office 2010 helps your people save time and money by providing one-click communication through unified communications technology, and real-time document sharing from within Word, PowerPoint, and Excel 2010 without the need to switch applications. This makes virtual meetings more effective so team members can get more done without being in the same room.
9)Instantly share a presentation. Hold spontaneous meetings with customers and partners with PowerPoint 2010. Broadcast Slideshow allows you to present a slideshow entirely through a Web browser—no matter where your audience is at. No need for your customers to have a third-party conferencing tool, or even PowerPoint. All they have to do is click on a link and a browser window opens with your slides.
Does Visual Studio 2010 has nay problems ????
This is a myth ...................
VS 2010 has no problems......................................
Some of the prbmls dat cn be listed are
1)if a project is devleoped in VS 2010 it doesnt wrk in 2008................
This is completly wrong..........just try to copy paste the code..............smething wil nt wrk....but most of the controls work
2)Crystal reports dont work in VS 2010..This is absolutely worng.By default VS 2010 has not given crystal report component.Becoz it is a third part component,after the release of VS 2010 due to some issue it was not available....But now the problem is solved....You cn dwnload crystal report from sap site..for free..You need not do anything....Ur crystal reports starts automatically.
3)ORacle Client not dere in VS 2010....One cn include the VS 2008 orcle client file.
If u hav anymore prbls.......dn u cn ask...anytime
Questions to Solve Oracle SQL(Objectives)
1) A bank gives three types of loans: personal, auto, home. The details of all types of loans are stored in the loan table. However there are some attributes that don’t apply to all kinds of loans. For e.g. car no will be applicable only to auto loan. In this case all non-auto loans will contain null values stored in car no attribute. Which sub entities will u creates to replace optional attributes and specialize this scenario.
A) By creating a subentitiy named auto to loan superentity
B) By creating three subentites named auto, home personal to loan superentity
C) By creating a subentity name personal and home to loan superentity
2) In a university students enroll for different types of courses. James is a student. He enrolls for a course in Philosophy .Identity the entity instances in this scenario?
A) James and Student
B) Philosophy and Course
C) James and Philosophy
D) Student and Course
3) Evaluate this SQL statement:
SELECT e.EMPLOYEE_ID, e.LAST_NAME, e.DEPARTMENT_ID, d.DEPARTMENT_NAME FROM EMP e, DEPARTMENT d WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID;
In the statement, which capabilities of a SELECT statement are performed?
A. Selection, projection, join
B. Difference, projection, join
C. Selection, intersection, join
D. Intersection, projection, join
E. Difference, projection, product
4) Consider a relationship between a vehicle and its types. The truck and car are types of car .The relationship is represented as follows in ERD.Identify the relationship (2 mark)
A) Specialization
B) Generalization
C) Aggregation
D) Inclusion
5) User views are ____ schema.
(A) physical
(B) Conceptual
(C) external
(D) Interface
6) Which two statements about views are true? (Choose two.)
A) A view can be created as read only.
B) A view can be created as a join on two or more tables.
C) A view cannot have an ORDER BY clause in the SELECT statement.
D) A view cannot be created with a GROUP BY clause in the SELECT statement.
E) A view must have aliases defined for the column names in the SELECT statement.
F) A view cannot be created as Read only
7) Which statement describes the ROWID data type?
A. Binary data up to 4 gigabytes.
B. Character data up to 4 gigabytes.
C. Raw binary data of variable length up to 2 gigabytes.
D. Binary data stored in an external file, up to 4 gigabytes.
E. A hexadecimal string representing the unique address of a row in its table.
8) ) What is not an option of SAVE Command in Oracle?(1 mark)
a) Create
b) Replace
c) Append
d) Delete
OR
Which of the following ranking functions provide the true rank based on the values in the column?
A) Row_number()
B) Rank()
C) Dense_Rank ()
9) Which statement about SQL is true?
A. Null values are displayed last in the ascending sequences.
B. Data values are displayed in descending order by default.
C. You cannot specify a column alias in an ORDER BY clause.
D. You cannot sort query results by a column that is not included in the SELECT list.
E. The results are sorted by the first column in the SELECT list, if the ORDER BY Clause is not provided
10) Which two statements are true regarding the ORDER BY clause? (Choose two)
A. The sort is in ascending by order by default.
B. The sort is in descending order by default.
C. The ORDER BY clause must precede the WHERE clause.
D. The ORDER BY clause is executed on the client side.
E. The ORDER BY clause comes last in the SELECT statement.
F. The ORDER BY clause is executed first in the query execution.
7) Consider an Region table which consists of Region_id, Region_name.You need to display details of all Regions where Region_name is Asia. But you are not sure that in which case is the Asia Stored. Which query will you use such that it will list all the employees whose Region_name is Asia
A. Select Region_name FROM regions WHERE Region_name= 'Asia';
B. Select Region_name FROM regions WHERE UPPER (Region_name)= 'Asia';
C. Select Region_name FROM regions WHERE Region_name=UPPER ('Asia');
D. Select Region_name name FROM regions WHERE LOWER (Region_name)= 'Asia';
.
9) Cascada forgot her password. Which command must be executed to set a password for username Cascada?
A. Cascada must execute the command. ALTER USER Cascada PASSOWRD BY lion
B. The DBA must execute the command. ALTER USER Cascada IDENTIFIED BY
Cascada
C. Cascada must execute the command ALTER USER Cascada IDENTIFIED BY Cascada
D. The Cascada must execute the command CHANGE password to Cascada WHERE
"user=Cascada";
E. The DBA must execute the command CHANGE password to Sunrays WHERE
"user=Cascada";
Cursor For Loop
Consider a PL/SQL code to display the empno, ename of employees of department name pr with CURSOR FOR Loop statement.
Solution :-
declare
cursor c1 is select empno,ename,deptnm from emp where deptnm='pr';
rec c1%ROWTYPE;
begin
for rec in c1 loop
dbms_output.put_line(rec.empno||'|'||rec.ename||'|'||rec.deptnm);
end loop;
end;
.
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> /
1|bina|pr
5|biju|pr
4|RHEA|pr
6|kamlesh|pr
10)
Consider a PL/SQL code to display the employee number and name of top 5 highest paid employees with CURSOR FOR LOOP statement.
declare
cursor c1 is select * from (select distinct basicsal,empno,ename from emp order by basicsal desc) where rownum <=5;
rec c1%rowtype;
begin
dbms_output.put_line('empno|ename|basicsal');
for r in c1 loop
dbms_output.put_line(r.empno||'|'||r.ename||'|'||r.basicsal);
end loop;
end;
.
/
SQL> /
empno|ename|basicsal
6|kamlesh|10000
1|bina|6000
5|biju|6000
4|RHEA|1000
What is a Sequence
What is a sequence ?
A sequence is a user created database object that can be shared by mulitple users to generate unique integers.
A typical useage of sequence is to create a primary key value ,which must be unique for each row.
The sequence is generated and incremented by an internal oracle routine.This can be a time saving object because it can reduce the amount of application code needed to write a sequence generating routine.
Sequence numbers are stored and generated independantly of tables.There fore the same sequence can be used for multiple tables.
Syntax:
Create Sequence sequencename
[Increment by n]
[Start with n]
[{Maxvalue n | Nomaxvalue}]
[{Minvalue n | NoMinvalue}]
[{Cycle | NoCycle}]
[{Cache n| Nocache }];
In the above syntax:
Sequencename : is the name of sequence
Increment by n : specifies the interval between the sequence numbers where n is is an integer(If this clause is omitted,the sequence increments by 1)
Start with n : specifie sthe first sequence number to be generated(if this clause is omitted sequence starts with 1)
Maxvalue n : specifies the maxvalue the sequence can generate
Minvalue n : specifies the minimum sequence value
NoMaxvalue:specifies a maximum value of 10^27 for an ascending sequence and -1 for desending sequence(This is default option).
NoMinValue : specifies a minimum value of 1 for ascending sequence and –(10^26) for desending sequence(This is default option)
Cycle | NOCycle :
specifies whether the sequence continues to generate values after reaching its maximum or mimimum value.(NoCycle is default option).
When CYCLE is in effect, then duplicate values can be generated for the sequence.
After an ascending sequence reaches its maximum value it generates its minimum value.
After a descending sequence reaches its minimum value it generates its maximum value.
NoCycle Specifies that values will not be generated for the sequence once the maximum or minimum value for the sequence has been reached. This is the default.
We can create a sequence without using cache as follows becouse by default value of cache is 20.
create sequence e321 increment by 1 maxvalue 40 cycle ;
Cache n|NoCache : specifies how many values oracle server preallocates and keeps in memory(By default oracle server caches 20 values)
CACHE or NO CACHE
Specifies whether to keep some preallocated values in memory for faster access. This is a performance and tuning option.
Specifies the maximum number of sequence values that are preallocated and kept in memory. Preallocating and storing values in the cache reduces synchronous I/O to the log when values are generated for the sequence.
NO CACHE
Specifies that values of the sequence are not to be preallocated. It ensures that there is not a loss of values in the case of a system failure, shutdown or database deactivation. When this option is specified, the values of the sequence are not stored in the cache. In this case, every request for a new value for the sequence results in synchronous I/O to the log.
Example
Create sequence sq1
Increment by 10
Start with 100
Maxvalue 999
NOCache
Nocycle;
Sequence created.
In the abv example we create a sequence sq1 increment by 10 starting value will be 100 then 110 then 120 and so on.This wil continue till 990,since 999 is the mzximum value.
· We can verify our sequence values in the user_sequences data dictinary tables.
Select sequence_name,increment_by,min_value,max_value,last_number from user_sequences;
· Also we can create a sequence for negative values as follows :-
create sequence ac increment by -1 start with 1 maxvalue 5
Sequence created.
The only tricky thing about negative values in sequences is that you may have to explicitly give a MINVALUE or MAXVALUE. Else oracle will give an error.
· Example for creating a sequence using cache and cycle
create sequence k13 increment by 2 maxvalue 6 cycle cache 2;
Here we need to specify the value of cache less than or equal to incremented value.Other wise we get this error : ORA-04013: number to CACHE must be less than one cycle .This is because cache is the memory where oracle stores the value .
· In the below example we increment value of sequence by 3 and maxvalue is 6 .Also memory of cache is 3 .So cache can store only 2 no’s.But we have specified 3.So oracle will give error.
create sequence jsdkj increment by 3 maxvalue 6 cycle cache 3;
create sequence jsdkj increment by 3 maxvalue 6 cycle cache 3
*
ERROR at line 1:
ORA-04013: number to CACHE must be less than one cycle
NextVal and Pseudocolumns :-
· After we create sequence ,it generates sequential numbers for use in our tables.Reference the sequence values by using the nextval and currval pseudocolumns.
· NextVal and Currval Pseudocolumns:-
The nextval pseudocolums is used to extraxt successive sequence numbers from a specified sequence.We must qualify nextval with the sequence name.When we reference sequence.nextval,a new sequence number is generaed and the current sequence number is placed in currval.
The currval pseudocolumns is used to refer to sequence number that the current user has just generated.Nextval must be used to generate a sequence number in the current user’s session before currval can referenced.
When sequence.currval is referenced ,the last value returned to that user’s process is displayed.
Using Sequence :-
Insert into emp values(sq1.nextval,’harsh’);
1 row created.
Subscribe to:
Posts (Atom)