Sunday, February 26, 2012

Cursors in PL/SQL



There are two types of cursors in PL SQL
1) Implicit cursor
2) Explicit cursor

Implicit cursors:

These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. They are also created when a SELECT statement that returns just one row is executed.

Explicit cursors:

They must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row.

The attributes of the cursors

The attributes of the implicit cursors can return information about DML and DDL execution commands such as INSERT, UPDATE, DELETE, SELECT INTO, COMMIT or ROLLBACK.

% FOUND

Until SQL data manipulation is executed, the attribute % FOUND is NULL. So, % FOUND is TRUE if the command type of INSERT, UPDATE, or DELETE affects one or more records from the database or SELECT INTO returns one or more recordings.

% ISOPEN

Oracle closes the cursor automatically after execution of commands. As a result,% ISOPEN becomes FALSE.

% NOTFOUND

% NOTFOUND is the opposite attribute of % FOUND.
% NOTFOUND is TRUE if the INSERT, UPDATE, or DELETE your registration does not affect any of the database or SELECT INTO does not return any registration. Otherwise it is FALSE.

% ROWCOUNT

% ROWCOUNT returns the number of records affected by one of the commands that INSERT, UPDATE, or DELETE, or the number of records affected by the SELECT INTO. % ROWCOUNT is 0 if the command INSERT, UPDATE, or DELETE does not affect any registration, or SELECT INTO does not return any record.



For Example: Consider the PL/SQL Block that uses implicit cursor attributes as shown below:

DECLARE var_rows number(5);
BEGIN
UPDATE employee
SET salary = salary + 1000;
IF SQL%NOTFOUND THEN
dbms_output.put_line('None of the salaries where updated');
ELSIF SQL%FOUND THEN
var_rows := SQL%ROWCOUNT;
dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');
END IF;
END;
Read more

Saturday, February 25, 2012

PL/SQL FOR and WHILE LOOP / Iterative Statements



Introduction to PL/SQL LOOP Statement

PL/SQL LOOP is an iterative control structure that allows you to execute a sequence of statements repeatedly.

    There are three types of PL/SQL loop Statements
  • Simple Loop

  • While Loop

  • For Loop



Type 1) Simple Loop
A Simple Loop is used when a set of statements is to be executed at least once before the loop terminates. An EXIT condition must be specified in the loop, otherwise the loop will get into an infinite number of iterations. When the EXIT condition is satisfied the process exits from the loop.

Syntax :

LOOP
statements;
EXIT;
{or EXIT WHEN condition;}
END LOOP;


Type 2) While Loop
A WHILE LOOP is used when a set of statements has to be executed as long as a condition is true. The condition is evaluated at the beginning of each iteration. The iteration continues until the condition becomes false.

Syntax :

WHILE
LOOP statements;
END LOOP;


3) FOR Loop
A FOR LOOP is used to execute a set of statements for a predetermined number of times. Iteration occurs between the start and end integer values given. The counter is always incremented by 1. The loop exits when the counter reachs the value of the end integer.

Syntax :

FOR counter IN value1..value2
LOOP statements;
END LOOP;


value1 - Start integer value.
value2 - End integer value.
Read more

PL/SQL IF ELSE statement / Conditional statements



Conditional Statements in PL/SQL

The PL/SQL IF statement allows you to execute a sequence of statements conditionally. The IF statements evaluate a condition. The condition can be anything that evaluates to a logical true or false such as comparison expression or combination of multiple comparison expressions. The programming constructs are similar to how you use in programming languages like Java and C++.


IF THEN ELSE STATEMENT

Type 1)

IF condition
THEN
statement 1;
ELSE
statement 2;
END IF;


Type 2)

IF condition 1
THEN
statement 1;
statement 2;
ELSIF condtion2 THEN
statement 3;
ELSE
statement 4;
END IF


Type 3)

IF condition 1
THEN
statement 1;
statement 2;
ELSIF condtion2 THEN
statement 3;
ELSE
statement 4;
END IF;


Type 4)

IF condition1 THEN
ELSE
IF condition2 THEN
statement1;
END IF;
ELSIF condition3 THEN
statement2;
END IF;
Read more

PL/SQL Records



What is a PL/SQL Record

A PL/SQL record is a composite data structure that is a group of related data stored in fields. Each field in the Pl/SQL record has its own name and data type.

The General Syntax to define a composite datatype is:

TYPE record_type_name IS RECORD
(first_col_name column_datatype,
second_col_name column_datatype, ...);

record_type_name – it is the name of the composite type you want to define.
first_col_name, second_col_name, etc.,- it is the names the fields/columns within the record.
column_datatype defines the scalar datatype of the fields.

Declaring Table-based Record

To declare a table-based record you use a table name with %ROWTYPE attribute. The fields of the PL/SQL record has the same name and data type corresponding to the column of the table. The following illustrates table-based record declaration:

DECLARE
table_based_record table_name%ROWTYPE;
Read more

Friday, February 24, 2012

PL/SQL Variables and Constants



PL/SQL Variables

Variables can be any SQL data type such as CHAR, DATE, or NUMBER, or a type of date PL/SQL such as BOOLEAN or PLS_INTEGER.

These are placeholders that store the values that can change through the PL/SQL Block.
--------------------------------------------------------------------------------
Naming rules for Variables in PL SQL:
--------------------------------------------------------------------------------
The variable name must be less than 31 characters.

The starting of a variable must be an ASCII letter. It can be either lowercase or uppercase.

A variable name can contain numbers, underscore, and dollar sign characters followed by the first character.

Make them meaningful to understand to make it easier to maintain in the future.
---------------------------------------------------------------------------------

PL/SQL Variable Declaration


The General Syntax to declare a variable is:

variable_name datatype [NOT NULL := value ];

variable_name is the name of the variable.
datatype is a valid PL/SQL datatype.
NOT NULL is an optional specification on the variable.
value or DEFAULT valueis also an optional specification, where you can initialize a variable.
Each variable declaration is a separate statement and must be terminated by a semicolon.


1. Declaring Variables in PL/SQL

DECLARE
part_no NUMBER (6);
part_name VARCHAR2 (20);
in_stock BOOLEAN;
part_price NUMBER (6.2);
part_desc VARCHAR2 (50);

2. Assigning variables using the := operator

DECLARE
hours_worked NUMBER: = 40;
hourly_salary NUMBER: = 22.50;
bonus NUMBER: = 150;
country VARCHAR2 (128);
counter NUMBER: = 0;
done BOOLEAN;
emp_rec1 employees% ROWTYPE;
emp_rec2 employees% ROWTYPE;
BEGIN
wages: = (hours_worked * hourly_salary) + bonus;
country: = ' Italy ';
country: = UPPER (' Spain ');
done: = (counter > 100);
emp_rec1.: first_name = ' John ';
emp_rec1. last_name: = ' Davis ';
emp_rec1: = emp_rec2;
END;

3. Assigning values to a variable using Select Into

DECLARE
bonus NUMBER (8,2);
emp_id NUMBER (6): = 100;
BEGIN
SELECT salary * 0.10 INTO bonus FROM employees
WHERE employee_id = emp_id;
END;

4. Assigning Value to a Variable as a parameter of a subroutine

DECLARE
new_sal NUMBER (8,2);
emp_id NUMBER (6): = 126;
PROCEDURE adjust_salary (emp_id NUMBER, sal IN OUT NUMBER) IS
emp_job VARCHAR2 (10);
avg_sal NUMBER (8,2);
BEGIN
SELECT job_id INTO emp_job FROM employees WHERE emp_id = employee_id;
SELECT AVG (salary) INTO avg_sal FROM employees WHERE job_id = emp_job;
DBMS_OUTPUT.PUT_LINE (' The average salary for ' emp_job || || ' employees: ' || TO_CHAR (avg_sal));
sal: = (sal + avg_sal)/2; --adjust sal value which is returned
END;
BEGIN
SELECT AVG (salary) INTO new_sal FROM employees;
DBMS_OUTPUT.PUT_LINE (' The average salary for all employees: ' || TO_CHAR (new_sal));
adjust_salary (emp_id, new_sal); --assigns a new value to new_sal
DBMS_OUTPUT.PUT_LINE (' The adjusted salary for employee ' || TO_CHAR (emp_id) || ' is ' || TO_CHAR (new_sal)); — sal has new value
END;

Constant
constant is a value used in a PL/SQL Block that remains unchanged throughout the program. A constant is a user-defined literal value. You can declare a constant and use it instead of actual value.

The General Syntax to declare a constant is:
constant_name CONSTANT datatype := VALUE;
constant_name is the name of the constant i.e. similar to a variable name.
The word CONSTANT is a reserved word and ensures that the value does not change.
VALUE - It is a value which must be assigned to a constant when it is declared. You cannot assign a value later.


For example, to declare salary_increase, you can write code as follows:

DECLARE
Salary_increase CONSTANT number (5) := 20;
Read more

Block structure and Advantages of PL/SQL



PL/SQL is a language focused on blocks, with procedural and processing characteristics of error handling. These blocks are composed of procedures, functions, and anonymous blocks which are grouped together in logical point of view.

    In block there are three basic parts which are declaration, execution, and exception handling. Only execution part is required and the others are optional.

  • Declaration - all block objects must be declared [optional]

  • Execution – the objects are defined for the data processing

  • Exceptions – here are located the error handling routines [optional]



DECLARE
Variable declaration;
BEGIN
Program Execution;
EXCEPTION
Exception handling;
END;



Declaration Section:
The Declaration section of a PL/SQL Block starts with the reserved keyword DECLARE. This section is optional and is used to declare any placeholders like variables, constants, records and cursors, which are used to manipulate data in the execution section. Placeholders may be any of Variables, Constants and Records, which stores data temporarily. Cursors are also declared in this section.

Execution Section:
The Execution section of a PL/SQL Block starts with the reserved keyword BEGIN and ends with END. This is a mandatory section and is the section where the program logic is written to perform any task. The programmatic constructs like loops, conditional statement and SQL statements form the part of execution section.

Exception Section:
The Exception section of a PL/SQL Block starts with the reserved keyword EXCEPTION. This section is optional. Any errors in the program can be handled in this section, so that the PL/SQL Blocks terminates gracefully. If the PL/SQL Block contains exceptions that cannot be handled, the Block terminates abruptly with errors.

Advantages of PL/SQL

  • Integration with Oracle Server

  • Supports the basic SQL commands

  • Defining and managing blocks of instructions

  • Management of variables, constants and cursors

  • Allow implementation and use of triggers

  • PL SQL consists of blocks of code, which can be nested within each other.

  • PL SQL engine processes multiple SQL statements simultaneously as a single block

  • It also reduces the network traffic.

  • Detection and management execution errors, exceptions

Read more

Introduction to PL/SQL

PL/SQL stands for procedural language extension of Structured Query Language [SQL].
PL/SQL is a programming language that provides accessing data from a relational database-oriented objects and enables grouping of a multitude of commands into a single block of data handling.It enable programmers to codify procedures, functions, and anonymous blocks that combine SQL statements

PL/SQL is a combination of Structured Query Language along with the procedural features of programming languages which was developed by Oracle Corporation in the early 1990’s to enhance the capabilities of SQL.

Oracle introduced PL/SQL to extend some limitations of SQL to provide a more comprehensive solution for building mission-critical applications running on Oracle database.

This was small introduction to PL/SQL which was necesary before the actual tutorial begin.

Read more

Complete Tutorial on Oracle PL/SQL

Read more

Monday, February 20, 2012

Facebook Hacking - Student jailed for eight months


Glenn Mangham, 26, admitted to infiltrating the website between April and May of last year.

26-year-old Glenn Steven Mangham, a student in the UK, has been sentenced to eight months in prison for hacking into Facebook from his bedroom at his parents house. Facebook spent $200,000 (£126,400) dealing with Mangham's crime, which triggered a "concerted, time-consuming and costly investigation.

Apparently no user details were taken, as he went straight for “invaluable” intellectual property instead.
Facebook alerted the authorities last May after they discovered the breach. The FBI took care of the rest, tracing it all back to the UK address. He found his way in by hacking into the account of a Facebook employee.


Facebook operates a bug bounty program in which it pays ethical hackers up to $US 500 for quietly disclosing vulnerabilities. According to reports of Mangham's court appearances, the software development student claimed to have been an ethical hacker who had previously breached Yahoo's system as a service to that company. "It was to identify vulnerabilities in the system so I could compile a report that I could then bundle over to Facebook and show them what was wrong with their system," Mangham told Southwark Crown Court. The judge decided that this was not Mangham's intention at the time.

"You accessed the very heart of the system of an international business of massive size, so this was not just fiddling about in the business records of some tiny business of no great importance," the judge continued, noting that Mangham had at least risked "putting in danger the reputation of an innocent employee of Facebook".

Awesome work by steven, after jail, i am sure many companies will be looking for him. :D
Source: the hackers News
Read more

Sunday, February 12, 2012

Free mobile recharge from facebook - 100% working

signup for embeepay and recharge your mobile phone or get free gift coupons

step 1) login to your facebook account or signup for new account.

Step 2) Click here and signup for embeepay app.

Step 3) Validate the details and earn some embeepay points and recharge your phone for free.

Done.

Since a month I am using it for free mobile recharge and i have recharged my mobile phone many times. more than 1000 INR of recharge has been done to my loop mobile.
just Click here and signup and rest you will understand yourself.
Read more

Friday, February 10, 2012

What to do if you lose your mobile

If you lose your mobile and this is how it works

1) Dial *#06# from your mobile

2) your mobile will show a unique 15 digit number.

3) Note this number anywhere except your mobile as this is the number which will help trace your mobile if in case its lost

4) One stolen you just have to mail the 15 digit number to cop@vsnl.net.

5) No need to go to police also.

6)your mobile will be traced within next 24hrs via a complex system of GPRS and internet.

7) You will find where your handset id operated even if your number is changed.

8) Now compose a mail to cop@vsnl.net. with the following details.

Name :
Address :
Phone Model :
Make :
Last Used Number :
Email for communication :
Missed Date :
IMEI Number [The 15 digit number] :

Keep all the information stored for emergency use.
Read more

Wednesday, February 08, 2012

How to format your computer using Bootable pandrive / USB port / USB Format

Make USB bootable
A complete windows xp or windows 7 formatting process by USB

Requirements
1) Windows CD to make your pandrive bootable
2) Pandrive minimum 2gb for windows XP.
3) A computer/laptop to connect pandrive.
4) Download this software Click here

Note : If you CD ROM is not working then find some other computer where you can put your windows CD to transfer files to Pandrive.

Installing downloaded software and begin formatting your computer
  • Put your windows cd in CD ROM
  • Connect your pandrive [Delete all your data from pandrive]
  • Now open the downloaded software and install it.
  • Open it and you will see three options select windows CD , your pandrive, and select transfer files from windows cd to pandrive.
  • The process of transfering files will begin and all your files will be transferred to pandrive
  • Now your pandrive will act as bootable to install windows

Now steps on formatting using USB

You need to change some settings in BIOS

Step 1) Restart your computer and keep pressing delete key you will enter into BIOS settings

Step 2) Go to boot menu using arrow keys, and select boot priority to floppy disk, default is CD ROM.

Step 3) Now check all menus from top, if you find anything like write protection disabled then make it enabled and if you find usb boot as disabled then make it enabled. [Use your arrow keys to rotate through the menus.]

Step 4) Now click F10 and then Y to save changes . Computer will be restarted and if your pandrive is connected it will boot using USB port and formatting will begin.

Note: when your pandrive is detected after restart, select first option, go for TEXT format option and when after second restart , continue to graphical interface if detected again.
Read more

Tuesday, February 07, 2012

11yr old but completed MCP ,CCNA and OCJP - Srinivasan Radhakrishnan


An 11year old girl but completed MCP ,CCNA and OCJP - Srinivasan Radhakrishnan
Very Inspirational.

GUYS PLS SHARE IT: PAKISTAN WORLD RECORD IS BROKEN BY INDIAN GIRL.
Srinivasan Radhakrishnan is a Tamil girl from Triunelveli VISHALINI and it is also a GUINNESS RECORD MASS.
Her father was a normal person who do electrical works but she is just 11year old and completed MCP (Microsoft Certified Professional)
CCNA (Cisco Certified Network Associate),
CCNA Security(Cisco Certified Network Associate Security),
OCJP (Oracle Certified Java Professional).

she is youngest persoon(11years older and record by a pakistani girl was 12 years) in the WORLD who completed CCNA WITH 90% mass .
she has been also invited for"INTERNATIONAL CONFERENCE", her speech made all the world scholar's stunned BUT a sad thing is no peoples know this real talents must be appreciated.
Read more

‎5 cool websites to download Free ebooks

‎5 cool websites to download Free ebooks....

http://www.freebookspot.ws/

http://www.freetechbooks.com/

http://www.zillr.org/

http://www.asksam.com/ebooks/

http://books.google.com/
Read more

Saturday, February 04, 2012

Prank Your Friends : Trick 1 | Hacking Tips and Trickz

Read more

How to Create a FAKE virus | Hacking Tips and Trickz

Read more

How to Create a FAKE virus | Hacking Tips and Trickz

Read more

Make Money With Google Adsense | Hacking Tips and Trickz

Read more

How to change MAC address of the system | Hacking Tips and Trickz

Read more

Check Your Hard Drives Health and solution to fix it | Hacking Tips and Trickz

Read more