Installation Steps of Oracle 11g R2

Installation Steps of Oracle 11g R2 

Read More

Cursor Attributes ISOPEN

Example:-
DECLARE
  TYPE EMPRECTYPE IS RECORD (LAST_NAME EMPLOYEES.LAST_NAME%TYPE,
                             SALARY EMPLOYEES.SALARY%TYPE);
  EMPREC EMPRECTYPE;
  CURSOR EMPCUR IS SELECT LAST_NAME,SALARY FROM EMPLOYEES;
BEGIN
  IF NOT EMPCUR%ISOPEN THEN
     OPEN EMPCUR;
  END IF;
  LOOP
     FETCH EMPCUR INTO EMPREC;
     DBMS_OUTPUT.PUT_LINE(EMPREC.LAST_NAME||' GETS '||EMPREC.SALARY);
     EXIT WHEN EMPCUR%NOTFOUND;
  END LOOP;
  CLOSE EMPCUR;
END;

Read More

Cursor with parameters in Plsql / PL/SQL Parameterized Cursor

Cursor with parameters
 we can declare cursors with parameters.
Parameterized cursors are also saying static cursors that can passed parameter value when cursor are opened.

Syntax
The syntax for a cursor with parameters in Oracle/PLSQL is:

CURSOR cursor_name (parameter_list)
IS
  SELECT_statement;


Example
For example, you could define a cursor called emp_cursor  as below.


Cursor display employee information from emp_information table whose department_id=80 and Job_id='SA_REP'

DECLARE
CURSOR emp_cursor (p_deptno NUMBER, p_job VARCHAR2)
 IS
SELECT *
FROM employees
WHERE department_id = p_deptno
AND job_id = p_job;

emprec employees%rowtype;
BEGIN

OPEN emp_cursor (80, 'SA_REP');
loop
fetch emp_cursor into emprec;
dbms_output.put_line(emprec.employee_id || ' is a '||emprec.job_id ||
' is in Department '||emprec.department_id );

exit when emp_cursor%notfound;
end loop;

CLOSE emp_cursor;
dbms_output.put_line('----------------------------------------------------------------');
OPEN emp_cursor (60, 'IT_PROG');
loop
fetch emp_cursor into emprec;
dbms_output.put_line(emprec.employee_id || ' is a '||emprec.job_id ||
' is in Department '||emprec.department_id );

exit when emp_cursor%notfound;
end loop;

CLOSE emp_cursor;


END;

Read More

PLSQL RECORD IN ORACLE

PLSQL RECORD  IN ORACLE

PLSQL RECORD 

A record is a composite datatype, which means that it can hold more than one piece of information, as compared to a scalar datatype, such as a number or string. It’s rare, in fact, that the data with which you are working is just a single value, so records and other composite datatypes are likely to figure prominently in your PL/SQL programs.

Records are another type of datatypes which oracle allows to be defined as a placeholder. Records are composite datatypes, which means it is a combination of different scalar datatypes like char, varchar, number etc.  Each scalar data types in the record holds a value. A record can be visualized as a row of data. It can contain all the contents of a row. 

What are records?

Records are another type of datatypes which oracle allows to be defined as a placeholder. Records are composite datatypes, which means it is a combination of different scalar datatypes like char, varchar, number etc.  Each scalar data types in the record holds a value. A record can be visualized as a row of data. It can contain all the contents of a row. 

Declaring a record:

To declare a record, you must first define a composite datatype; then declare a record for that 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.

SET SERVEROUT ON

DECLARE

  -- CREATE RECORD DATA TYPE

  TYPE EMPRECTYPE IS RECORD(LAST_NAME EMPLOYEES.LAST_NAME%TYPE,

                            SALARY    EMPLOYEES.SALARY%TYPE);

  -- DECLARE VARIABLE OF THAT RECORD DATA TYPE

  EMPREC EMPRECTYPE;

BEGIN

  SELECT LAST_NAME,SALARY

  INTO   EMPREC

  FROM   EMPLOYEES

  WHERE  EMPLOYEE_ID=&EMPNO;

  DBMS_OUTPUT.PUT_LINE(EMPREC.LAST_NAME||' GETS '||EMPREC.SALARY);

END;

Read More

Oracle conditional join conditions

--Notice that the SELECT INTO statement uses an equijoin.
 The join condition is listed in the JOIN clause, indicating columns that are part of the primary key and foreign key constraints.

EXAMPLE 5:  To determine employee bonus depending on on salary  criteria.In this program,
the IF condition is evaluated first and if true, the bonus of the employee is calculated.

DECLARE
dno number:=&dno;
lname varchar2(50);
v_bonus number;
  CURSOR EMPCUR IS SELECT last_name,department_name,salary from   EMPLOYEES e
join departments d on (e.department_id=d.department_id )and e.department_id=dno;
BEGIN
  FOR i IN EMPCUR LOOP
 IF i.salary   >= 20000 THEN
     V_BONUS :=i.salary*.2;
  ELSIF i.salary>=15000 THEN
     V_BONUS := i.salary*.3;
  ELSIF i.salary>= 10000 THEN
     V_BONUS := i.salary *.4;
  ELSE
     V_BONUS := i.salary*.5;
  END IF;
dbms_output.put_line('Name:  '||i.last_name||'Salary is drawing   :'||i.salary||' bonus=   '||v_bonus);
END LOOP;
END;
/

Read More

forgot sys password in oracle 11g.

Hi,

Log on to your Windows server as a member of the Administrators group or a member of the ORA_DBA group.

Try the below steps
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\NIIT>set oracle_sid=orcl

C:\Users\NIIT>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 30 16:27:11 2017

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> connect /as sysdba
Connected.
SQL> alter user sys identified by admin;

User altered.

SQL> conn
Enter user-name: sys as sysdba
Enter password:
Connected.
SQL> show user

USER is "SYS"

Read More