Procedures in Oracle

ORACLE PROCEDURE

A procedure is a schema object that consists of a set of SQL statements and other PL/SQL constructs, grouped together, stored in the database.it can be executed as a unit to solve a specific problem or perform a set of related tasks. Procedures permit the caller to provide parameters that can be input only, output only, or input and output values.

Procedures and functions in oracle are identical except that functions always return a single value to the caller but procedures can return many values

The syntax for a procedure is:
CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name];

Eg :

create or replace procedure acc_bal

(

loancd in varchar2,

accno in number,

bal out number

)

is

ln_bal number;

tr_bal number;

begin

select nvl(sum(decode(type,'C',-1*amount,amount)),0) into ln_bal from loan_tran_dtl where loan_no=loancd and account_no=accno;

select nvl(sum(decode(type,'C',-1*amount,amount)),0) into tr_bal from tran_detail where ref_id=loancd and

account_no=accno;

bal:=ln_bal+tr_bal;

end;

BOOT

Boot

The process by which a computer is started up and its operating system kernel is loaded into RAM is called the boot, or bootstrap, process. The basic steps are the:

1. Execute a hardware self-test.
2. Look in a predefined place for the boot sector and load this code.
3. Execute the boot sector program to load other programs.
4. Execute these programs to load still other programs or to configure the operating environment.
5. Repeat the previous step as often as dictated by the programs being loaded and by their initialization code.

BOOTCONF.SYS

It is configurations file that specifies how a diskless workstation can boot the operating system in order to access the network.

Boot ROM

It is a ROM (read-only memory) chip used in diskless workstations to enable these machines to boot and connect to a network.

BIOS -BASIC INPUT/OUTPUT SYSTEM

BIOS (Basic Input/Output System)

The BIOS (Basic Input/Output System) is a collection of services on a ROM. It enables hardware and software, operating systems and applications, and also applications and users to communicate with each other. The BIOS services are loaded automatically into specific addresses. Also these services should always be accessible. BIOS services are updated and expanded to handle newer devices and greater demands. When you replace the ROM chip in your computer with an upgrade chip you will get a new BIOS. Like the standard BIOS, BIOS extensions are also implemented on a ROM chip located on a Mother Board or an expansion board.

Interview Questions

Common Interview Questions and Answers:

How long would you expect to work for us if hired?

You can answer like: I'd like it to be a long time Or As long as we both feel I'm doing a good job.

2. Explain how you would be an asset to this organization.

This question gives you a chance to highlight your best points as they relate to the position being discussed. Give a little advance thought to this relationship.

3. Why should we hire you?

Point out how your assets meet the goals of the organization. Do not mention any other candidates to make a comparison.

4. What is your greatest strength?

Say positive answers. You can say: your ability to prioritize, your problem solving skills, the ability to work under great pressure, leadership skills, professional skills, positive attitude etc.

5. What is more important to you: the money or the work?

You have to say Money is always important, but the work is the most important. There is no better answer to say.

Antivirus Program

Anti-Virus Program

It is used for detecting or removing a computer virus. This program looks for suspicious activity, such as attempts to intercept a BIOS or other low-level call, unnecessary disk access, and attempts to format or delete files. Sometimes the anti-virus program detects a pattern characteristic of a particular virus. Some anti-virus programs are TSR (terminate-and-stay-resident) programs, which monitor computer activity constantly, looking for indications of a virus. In some cases, these types of programs can be extremely annoying and very processor intensive. So users have been known to remove an anti-virus TSR program from memory out of frustration. Other anti-virus programs are intended to be run periodically. When they are run, the

Programs look for the tell-tale signs (known as signatures) of particular viruses and these programs are minimally disruptive. That is their effectiveness is directly proportional to the frequency with which they are used. Antivirus programs must be updated regularly because the coding for computer viruses is constantly changing. Also it is very important to test every new release of anti-virus programs thoroughly. Some antivirus programs uses a large amount of working memory.

ODBC -OPEN DATABASE CONNECTIVITY

Open Database Connectivity (ODBC)

ODBC provides a common Application Programming Interface (API) to underlying database systems. ODBC communicates with the database through a library driver. Depending on the database being used, a networking driver may be required to connect to a remote database.

ODBC structure.

The unique feature of ODBC is that none of its functions are database-vendor specific. That is, you can use the same code to perform queries against a Microsoft Access table or an Informix database with little or no modification. ODBC has developed into a standard adopted into many products, including Visual Basic, Visual C++, FoxPro. Using ODBC will be more portable but slower than using the Oracle7 or Sybase libraries.

Common Interview Questions

Common Interview Questions and Answers:

1. What have you done to improve your knowledge in the last year?

Try to include improvement activities that relate to the job

2. Are you applying for other jobs?

Be honest. But keep the focus on this job and what you can do for this organization. Anything else is a distraction.

3. Why do you want to work for this organization?

The answer is based on the research you had done about the organization.. Try to relate it to your long-term career goals.

4. Do you know anyone who works for us?

Be aware of the policy on relatives working for the organization before the interview. This can affect your answer even though they asked about friends not relatives. Be careful to mention a friend.

5. What kind of salary do you need?

It is a tough question. If you are confident you can tell what you are expected. Other wise you can ask the range of salary they are giving for this post. Or say that it can depend on the details of the job.

6. Are you a team player?

You are, of course, a team player. Be sure to have examples ready. This should shows your positive attitude.

Silverlight

Silverlight

It is a crosss-platform implementation of the .NET framework for building and delivering of the next generation media experiences and Rich Interactive Applications(RIA) for the web.it runs in almost all popular browsers like IE. mozilla firefox, Opera etc. the plugin required is very small and hence installed very quickly. In this different technologies are combined into a single platform which allows you to select programming language and the tools .

Silverlight aims to compete with Adobe Flash and the presentation components of Ajax. It also competes with Sun Microsystems' JavaFX.

2 major versions of Silverlight are :

Silverlight 1.0 and Silverlight 2.0( previously referred to as version 1.1).

Silverlight 1.0 : consists of the core presentation framework, which is responsible for UI, interactivity and user input, basic UI controls, graphics and animation, media playback, DRM support, and DOM integration.

Silverlight 2.0 : includes a version of the .NET Framework, with the full Common Language Runtime as .NET Framework 3.0; so it can execute any . Multiple instances of the CoreCLR included in Silverlight can be hosted in one process. markup file (.xaml file) can be augmented by code-behind code, written in any .NET language, which contains the programming logic.

JAVA

Java (programming language)

It is a programming language originally developed by James Gosling at Sun Microsystems .It is released in 1995 as a core component of Sun Microsystems' Java platform. Java Programming is based on object oriented concepts. It derives much of its syntax from C and C++. The java programs are compiled to bytecode. This bytecode can run on JAVA VIRTUAL MACHINE regardless of the architecture of the computer.

Java Platform

Java is portable. That is same java program can run in different machines regardless of the computer architecture with JVM. This is achieved by compiling the java program into java bytecode which are analogous to machine code. These bytecodes are interpreted by a virtual machine written specifically for host hardware. End-users commonly use a Java Runtime Environment (JRE) installed on their own machine for standalone Java applications, or in a Web browser for Java applets. A major benefit of using bytecode is porting.

Common Interview Questions and Answers

Common Interview Questions and Answers:

I am giving you some commonly asking interview questions and answers. Read carefully and face the interview very confidently.
1. Tell me about yourself :
This is very commonly asked question. So it is better to prepare a short note for it. Try to include work related matters. That is what you have done, what you can do etc related to the position you are interviewing for.
2. Why did you leave your last job?
Say positive reasons regardless of the situations. You can tell, to get a better opportunity to do something special etc.
3. What experience do you have in this field?
Speak about your experience that relate to the position you are applying for.
4. Do you consider yourself successful?
For this question the answer should always be ‘YES’ and you have to tell the reason briefly. You can tell your achieved goals and the set goals.
5. What do co-workers say about you?
Be prepared with a quote or two from co-workers. It should always positive.
6. What do you know about this organization?
You must do a research about the organization before attending the interview. Find out where they have been and where they are going, current issues of the organization etc

10 TIPS FOR INTERVIEW

10TIPS FOR INTERVIEW

1. Do your homework. Know about the company and improve your knowledge about the company. This will help you in interview

2. Know where you’re going. Know where is the interview place. Keep address and phone numbers of the office with you. This will help to save your time and avoids unnecessary stress.

3. Look the part. Wear neat dress with professional look.Your clothing should be neat, pressed, and professional looking. Make sure to have a fresh haircut and clean, manicured nails.

4. Rehearse beforehand. Prepare answers to common questions the interviewer is likely to ask. If possible conduct a mock interview with a trusted friend as practice.

5. Secure your references. Find at least three key people — former supervisors, colleagues, or instructors — who are willing to serve as your professional references. Before interview be sure to secure their permission ,and be certain that they will speak highly of you when an employer contact him.

6. Arrive early. Be sure to arrive the interview place at least 15 minutes before the interview. Then announce yourself to the receptionist to let him or her know that you have arrived and that you have an appointment. Don’t forget to turn your cell phone off so it doesn’t ring during your meeting.

7. Bring necessary documentation. Prepare a checklist of documents that you will need for the interview, and make sure that you have taken them in your briefcase before leaving home. Don’t forget to take extra copies of your resume, a passport, driver’s license, Social Security card, or portfolio of writing samples or other professional work.

8. Sell yourself. The interview is your chance to shine. Develop a 25-second sales pitch that sings your praises. In business this is called an “elevator speech,” a compelling overview of why you? that can be recited in the time it takes to ride the elevator. IThis should include your strengths, your abilities, your knowledge and what sets you uniquely apart from other applicants.

9. Don’t neglect to ask questions. Based on your earlier research about the company, ask how the responsibilities of the open position relate to the company’s goals and plans for the future. Interviewers are impressed by candidates who show that they are knowledgeable about the organization.

10. Follow up. After the interview, don’t forget to send a handwritten note or friendly email. The email includes thanking the interviewer for his or her time and consideration, as well as restating your interest and commitment to the position. If you don’t hear anything after one week, you can call to politely inquire when they will be making a final decision.

Seven Manthras of Success

SEVEN MANTRAS FOR SUCCESS

1. Communication Skills – Communication is very important in an organization. So the we should be master in both verbal as well as written.

2. Influencing Abilities - we should have leadership power. This includes both persuading and negotiating skills.persuation means convincing and encouraging people to work when a hard situation occurs. It is a very powerful tool in conflicting situations in professional and personal life.

3. Managing Skills – this term means getting things done. That is managing things,people and yourself.managing yourself means managing your time, money etc .

4. Problem Solving – this means to know how to recognize ,define and solve problems. . Effective problem solving skills will help to become the backbone of any team, which in turn will help in career prospects.

5. Creative Thinking – the ability to generate new ideas .that is we have to use both part of the brain effectively. Watch everything around the world very carefully to get new ideas.

6. Social Skills – this means the ability to relate peole, having inslights, helping others and facilitating. Interdependence has a great value in today’s life.

7. Managing Money- develop the ability to manage money efficiently.

Computer Science Seminar Topic

SPINS -Security Protocol For Sensor Network

Abstract

Wireless sensor networks will be widely deployed in the near future. Many researched are doing on making these networks feasible and useful. Sensor networks are identified as being useful in different domains to include battlefield and perimeter defense. So we have to give high attention to security.

A suite of security protocols optimized for sensor networks: SPINS. SPINS has two secure building blocks: SNEP and _TESLA. SNEP the following important baseline security primitives: data confidentiality, two-party data authentication, and evidence of data freshness. Providing efficient broadcast authentication, which is an important mechanism for sensor networks is a hard problem._TESLA provides authenticated broadcast for severely resource-constrained environments.

Oracle Functions

ORACLE FUNCTIONS

·         SIGN(n)  :     positive = 1, zero = 0, negative = -1 

·         SIN(n)    :    Sine of n in Radians 

·         SINH(n)  :     Hyperbolic Sine of n in Radians

·         SOUNDEX(char)  : It returns a char value representing the sound of the words 

·         SQRT(n)   :   to find  Square Root of a number

                                  Eg : sqrt(4)=2 

·         STDDEV([DISTINCT] n) :  Standard deviation of n 

·         SUBSTR(char, s[,l]) : A substring of char, starting at character s, length l 

·         SUBSTRB(char, s[,l]) : A substring of char, starting at character s, length l. The same as SUBSTR, except that 's', 'l' and the return value are expressed in bytes, use for double-byte char sets 

·         SUM([DISTINCT] n) :  Sum of values of n, ignoring NULLs 

·         SYS_CONTEXT('namespace','attribute_name') :  Examine the package associated with the context namespace .

              Possible attributes are: NLS_TERRITORY, NLS_CURRENCY, NLS_CALENDAR ,              NLS_DATE_FORMAT, NLS_DATE_LANGUAGE, NLS_SORT, SESSION_USER, CURRENT_USER, CURRENT SCHEMAID,SESSION_USERID, CURRENT_USERID 

·         SYS_CONTEXT ('USERENV','IP_ADDRESS') 

·         SYS_GUID()  :  Returns a globally unique identifier (16 byte RAW value) 

·         SYSDATE   :    The current system date & time

·         TAN(n)      :  Tangent of n in Radians

·         TANH(n)     :  Hyperbolic tangent of n in Radians

·         TO_CHAR  :     Convert to character String

                               Eg :to_char(to_date(‘1/sep/2009’),’MON’)  =sep 

·         TO_DATE  :     Convert to date value 

·         TO_LOB(long)  : Convert LONG values to CLOB or NCLOB values  or convert LONG RAW values to BLOB values.  Use only as part of an "INSERT INTO ... SELECT..." subquery.  

·         TO_MULTI_BYTE(char) :  Convert single-byte char to multi-byte char. 

·         TO_NUMBER   :  Convert to numeric format

·         TO_SINGLE_BYTE(char) :  Convert multi-byte char to single-byte char.

·         TRANSLATE('char','search_str','replace_str') :Replace every occurrence of search_str with replace_str  unlike REPLACE() if replace_str is NULL the function returns NULL 

·         TRANSLATE (text USING charset) :  Convert text into a specific character set. Use this instead of CONVERT() if either the input or output datatype  is NCHAR or NVARCHAR2. 

·         TRIM(LEADING|TRAILING|BOTH trim_char FROM trim_source) : Returns trim_source as a VARCHAR2 with leading/trailing items removed.  trim_char defaults to a space ' ' but may be numeric or char 'A' 

·         TRUNC(i,d)  :  i truncated to d decimal places (d defaults to 0)

·         TRUNC(date,fmt) :  date truncated to nearest fmt

·         UID  :         User id - unique number

·         UPPER(char)  :  Returns Chars in uppercase 

·         USER      :    Returns the current Username 

·         USERENV('option') : Can return any of the options: ENTRYID, SESSIONID, TERMINAL, LANGUAGE, ISDBA, LANG, INSTANCE, CLIENT_INFO 

·         VALUE(correlation_variable) : Return the object instance for a row of an object table as associated with the correlation_variable 

·         VARIANCE([DISTINCT] n) : Variance of n, ignoring NULLs

·         VSIZE(expr)  :  Value Size - returns the number of bytes used by each row of expr.

Oracle Functions

ORACLE FUNCTIONS
·         LAST_DAY(date) : Returns the last day of month in Date
                   Eg : last_day('23/sep/2009')  = 9/30/2009
 ·         LEAST(expression, expression...) :  Returns the smallest in a list of expressions
                   Eg : least (2,4,3)=2
 ·         LENGTH(char)  :  Returns the number of characters in char
                   Eg : length(‘abc’)=3
 ·         LENGTHB(char) :  Returns the number of bytes in char                
·         LIKE wildcard/value :    Wildcards are [% = any chars] [ _ = any one char]
                                                   Where 'x' LIKE 'ajith%' [will find 'Ajithraj']
                                                   Where 'x' LIKE 'ajith_' [will find 'Ajitha']
·         LN(n)      :    Natural Log of n, where n>0
·         LOG(b,n)    :  log of n, base b
            ·         LOWER(char)  :  Returns character string in lowercase
                                              Eg : lower(‘Lima’) = lima
·         LPAD(char, n[,PadChar]) :    Left Pad char with n spaces [or PadChars]
                                  Eg : lpad('abc',5,0) = 00abc
·         LTRIM(char[,set]) : Left Trim char - remove leading spaces [or char set]
·         MAKE_REF(table,key) :  Create a REF to a row of an OBJECT view/table
·         MAX([DISTINCT] expr) :  Maximum value returned by expr
·         MIN([DISTINCT] expr) :   Minimum value returned by expr
·         MOD(x,y)  :    Remainder of x divided by y
·         MONTHS_BETWEEN(end_date, start_date) : Number of months between the 2 dates (integer)
·         NEW_TIME(date, zone1, zone2) :   Convert between GMT and US time zones 
·         NEXT_DAY(date,day_of_week) :   '12-OCT-01','Monday' will return the next Mon after 12 Oct
·         NLS_CHARSET_DECL_LEN (bytecount,charset) :  Returns the declaration width (no of chars) of an NCHAR column
·         NLS_CHARSET_ID(varchars) :  Returns the char set ID given a charset name 
·         NLS_CHARSET_NAME(charset_id) :  Returns the char set name given a charset id
·         NLS_INITCAP(char[,'NLS_SORT = sort_sequence']) :  Returns char in Initial Caps, using an NLS sort_sequence  either the session default or specified directly
·         NLS_LOWER(char[,'NLS_SORT = sort_sequence']) : Returns char in lower case, using an NLS sort_sequence  either the session default or specified directly
·         NLSSORT(char[,'NLS_SORT = sort_sequence']) :  Return the string of bytes used to sort char, using an NLS sort_sequence  either the session default or specified directly 
·         NLS_UPPER(char[,'NLS_SORT = sort_sequence']) :  Returns char in UPPER case, using an NLS sort_sequence  either the session default or specified directly
·         NVL(expression, value_if_null) : If expression in the function is null, returns value_if_null; if expression is not null, returns expression.  
·         PERCENT_RANK : Calculate the percent rank of a value in a group.
·         POWER(m,n)  :  m raised to the nth power
·         RANK   :       Calculate the rank of a value in a group
·         RAWTOHEX(raw) : Convert  a raw to a character value containing its hex equivalent
·         REF(table_alias) : Returns a REF value for an object instance .The table alias (correlation variable) is associated with one row of an object table or an object view in an SQL statement. 
·         REFTOHEX(ref)  : Convert ref (object type) to a char value containing its hex equivalent. 
·         REPLACE(char, search_str[, replace_str]) :  ANSI alternative to decode() Replace every occurrence of search_str  with replace_str, replace_str defaults to null.
·         ROUND(n,d)  :  n rounded to d decimal places (d defaults to 0)
                                              Eg : round(3.5345,2)=3.53
·         ROUND(date,fmt) : date rounded to fmt
·         ROWIDTOCHAR(rowid) :  Convert a rowid value to VARCHAR2
·         ROW_NUMBER   : It assign a unique number to each row of the results.  
·         RPAD(char, n[,PadChar]) :   Right Pad char with n spaces [or PadChars]
·         RTRIM(char[,set]) :  Right Trim char - remove trailing spaces [or char set]