student solution all subject

Monday, August 1, 2016

oracle ques ans

1.Write a program to print the following format
WELCOME TO PL/SQL PROGRAMMING
BEGIN
DBMS_OUTPUT.PUT_LINE('WELCOME TO PL/SQL PROGRAMMING');
END;
/
2.Write a program to print the numbers from 1 to 100
DECLARE
N NUMBER(3):=1;
V VARCHAR2(1000);
BEGIN
WHILE N <=1000
LOOP
V:=V||''||N;
N:=N+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
END;
/
3.write a program to print the even numbers from 1 to 100
DECLARE
N NUMBER(3):=0;
BEGIN
WHILE N <=100
LOOP
N:=N+2;
DBMS_OUTPUT.PUT_LINE(N);
END LOOP;
END;
/
4.Write a program to print the odd numbers from 1 to 100
DECLARE
N NUMBER(3):=1;
BEGIN
WHILE N <=100
LOOP
N:=N+2;
DBMS_OUTPUT.PUT_LINE(N);
END LOOP;
END;
/
5.write a program for multiplication table
DECLARE
A NUMBER(2):=&A;
B NUMBER(2):=1;
C NUMBER(3);
BEGIN
WHILE B <=10
LOOP
C:=A*B;
DBMS_OUTPUT.PUT_LINE(A||'*'||B||'='||C);
B:=B+1;
END LOOP;
END;
/
6.write a program to find the sum of numbers from 1 to 100
DECLARE
N NUMBER(3):=1;
S NUMBER(4):=0;
BEGIN
WHILE N <=100
LOOP
S:=S+N;
N:=N+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('THE SUM OF 1 TO 100 IS '||S);
END;
/
7.Write a program to find the sum of all odd numbers from 1 to 100
DECLARE
N NUMBER(3):=1;
S NUMBER(4):=0;
BEGIN
WHILE N <=100
LOOP
S:=S+N;
N:=N+2;
END LOOP;
DBMS_OUTPUT.PUT_LINE('THE SUM OF 1 TO 100 ODD NUMBERS IS '||S);
END;
/
8.Write a program to find the sum of all even numbers from 1 to 100
DECLARE
N NUMBER(3):=0;
S NUMBER(4):=0;
BEGIN
WHILE N <=100
LOOP
S:=S+N;
N:=N+2;
END LOOP;
DBMS_OUTPUT.PUT_LINE('THE SUM OF 1 TO 100 EVEN NUMBERS IS '||S);
END;
/
9.Write a program to accept a number and find how many digits it contain
DECLARE
N NUMBER(5):=&N;
CNT NUMBER:=0;
R NUMBER(2):=0;
BEGIN
WHILE N !=0
LOOP
R:=MOD(N,10);
CNT:=CNT+1;
N:=TRUNC(N/10);
END LOOP;
DBMS_OUTPUT.PUT_LINE('NUMBER OF DIGITS OF GIVEN NUMBER IS '||CNT);
END;
/
10.Write a program to accept a number and find the sum of the digits
DECLARE
N NUMBER(5):=&N;
S NUMBER:=0;
R NUMBER(2):=0;
BEGIN
WHILE N !=0
LOOP
R:=MOD(N,10);
S:=S+R;
N:=TRUNC(N/10);
END LOOP;
DBMS_OUTPUT.PUT_LINE('SUM OF DIGITS OF GIVEN NUMBER IS '||S);
END;
/
11.Write a program to accept a number and print it in reverse order
DECLARE
N NUMBER(5):=&N;
REV NUMBER(5):=0;
R NUMBER(5):=0;
BEGIN
WHILE N !=0
LOOP
R:=MOD(N,10);
REV:=REV*10+R;
N:=TRUNC(N/10);
END LOOP;
DBMS_OUTPUT.PUT_LINE('THE REVERSE OF A GIVEN NUMBER IS '||REV);
END;
/
12.Write a program to accept a no and check whether it is Armstrong number or not
13.Write a porgram to generate all the Armstrong numbers from 1 to 1000
14.Write a program to generate all prime numbers between 1 to 100
15.Write a program to aceept a number and check whether it is prime number or not
16.Write a program to display the fibonacci series from 1 to 10
17.Write a program to aceept a number and print it in binary format
18.Write a program to accept a number and find the factorial of the number
19.Find the factorials of numbers from 1 to 10
DECLARE
FACT NUMBER:=1;
V VARCHAR2(100);
BEGIN
FOR I IN 1..10
LOOP
FOR J IN 1..I
LOOP
FACT:=FACT*J;
V:=J||'*'||V;
END LOOP;
DBMS_OUTPUT.PUT_LINE(RTRIM(V,'*')||'='||FACT);
FACT:=1;
V:=NULL;
END LOOP;
END;
/
20.Write a program to aceept a number and display it in the Octal format
DECLARE
N NUMBER(2):=&N;
R NUMBER(2);
V VARCHAR2(1000);
BEGIN
WHILE N>0
LOOP
R:=MOD(N,8);
V:=R||V;
N:=TRUNC(N/8);
END LOOP;
DBMS_OUTPUT.PUT_LINE('OCTAL OF A GIVEN NUMBER IS '||V);
END;
/
21.Write a program to accept a number and print the multiplication tables upto soo
DECLARE
N NUMBER(2):=&N;
M NUMBER;
BEGIN
FOR I IN N..N+5
LOOP
FOR J IN 1..10
LOOP
M:=I*J;
DBMS_OUTPUT.PUT_LINE(I||'*'||J||'='||M);
END LOOP;
DBMS_OUTPUT.PUT_LINE('*********************');
END LOOP;
END;
/
22.Write a program to accept the temp in Centigrade and convert it into Fahrenheit(c=F-32/1.8)
DECLARE
C NUMBER:=&C;
F NUMBER;
BEGIN
F:=C*1.8+32;
DBMS_OUTPUT.PUT_LINE('THE FARENHETT OF GIVEN OC IS '||F);
END;
/
23.Write a program to calculate the area of a triangle by accepting the 3 sides
(s=(a+b+c)/2 area=sqrt(s*(s-a)*(s-b)*(s-c)))
DECLARE
S NUMBER;
A NUMBER:=&A;
B NUMBER:=&B;
C NUMBER:=&C;
AREA NUMBER(7,2);
BEGIN
S:=(A+B+C)/2;
AREA:=SQRT(S*(S-A)*(S-B)*(S-C));
DBMS_OUTPUT.PUT_LINE('THE AREA OF TRIANGLE IS '||AREA);
END;
/
24.Write a program to calculate the area of a circle by accepting the radius and unit of measure Area=PI*r2
DECLARE
R NUMBER:=&R;
AREA NUMBER(7,2);
BEGIN
AREA:=(22/7)*R*R;
DBMS_OUTPUT.PUT_LINE('THE AREA OF CIRCLE IS '||AREA);
END;
/
25.Write a program to calculate the perimeter of a circle(perimeter=2*PI*r)
DECLARE
R NUMBER:=&R;
PERIMETER NUMBER(7,2);
BEGIN
PERIMETER:=2*(22/7)*R;
DBMS_OUTPUT.PUT_LINE('THE PERIMETER OF CIRCLE IS '||PERIMETER);
END;
/
26.Write a program to accept the 3 sides of the triangle and display the type of triangle
DECLARE
A NUMBER(4,2):=&A;
B NUMBER(4,2):=&B;
C NUMBER(4,2):=&C;
PERIMETER NUMBER(7,2);
BEGIN
IF (A=B AND B=C AND C=A) THEN
DBMS_OUTPUT.PUT_LINE('EQUILATERAL TRIANGLE');
ELSIF A=B OR A=C OR C=B THEN
DBMS_OUTPUT.PUT_LINE('ISOSOCELESS TRIANGLE');
ELSE
DBMS_OUTPUT.PUT_LINE('SCALEN TRIANGLE');
END IF;
END;
/
27.Write a program accept the value of A,B&C display which is greater
DECLARE
A NUMBER(4,2):=&A;
B NUMBER(4,2):=&B;
C NUMBER(4,2):=&C;
BEGIN
IF (A>B AND A>C) THEN
DBMS_OUTPUT.PUT_LINE('A IS GREATER '||''||A);
ELSIF B>C THEN
DBMS_OUTPUT.PUT_LINE('B IS GREATE '||''||B);
ELSE
DBMS_OUTPUT.PUT_LINE('C IS GREATER '||''||C);
END IF;
END;
/
28.Write a program accept a string and check whether it is palindrome or not
DECLARE
S VARCHAR2(10):='&S';
L VARCHAR2(20);
TEMP VARCHAR2(10);
BEGIN
FOR I IN REVERSE 1..LENGTH(S)
LOOP
L:=SUBSTR(S,I,1);
TEMP:=TEMP||''||L;
END LOOP;
IF TEMP=S THEN
DBMS_OUTPUT.PUT_LINE(TEMP ||''||' IS PALINDROME');
ELSE
DBMS_OUTPUT.PUT_LINE(TEMP ||''||' IS NOT PALINDROME');
END IF;
END;
/
29.Write a program aceepts the value of A,B and swap the nos and print the values
DECLARE
A NUMBER(2):=&A;
B NUMBER(2):=&B;
FLAG NUMBER(2);
BEGIN
FLAG:=A;
A:=B;
B:=FLAG;
DBMS_OUTPUT.PUT_LINE('A '||'= '||A||' AND '||''||'B '||'= '||B);
END;
/
30.Write a program to accept the values of A , B and swap the numbers and print the values
without using third variable
DECLARE
A NUMBER(2):=&A;
B NUMBER(2):=&B;
FLAG NUMBER(2);
BEGIN
FLAG:=A;
A:=B;
B:=FLAG;
DBMS_OUTPUT.PUT_LINE('A '||'= '||A||' AND '||''||'B '||'= '||B);
END;
/
31.Write a program to accept the side of a square and calculate the area area =a2
DECLARE
A NUMBER:=&A;
AREA NUMBER(5);
BEGIN
AREA:=A*A;
DBMS_OUTPUT.PUT_LINE('AREA OF A SQUARE IS '||''||AREA);
END;
/
32.Write a program to accept principle amount ,rate,time calculate the simple interest si=(p*t*r)/100
DECLARE
P NUMBER(6,2):=&P;
R NUMBER(6,2):=&R;
T NUMBER(6,2):=&T;
SI NUMBER(6,2);
BEGIN
SI:=(P*R*T)/100;
DBMS_OUTPUT.PUT_LINE('SIMPLE INTEREST IS '||''||SI);
END;
/
33.Erite a program to aceept the principle amount,rate,time and find the compound interest
ci=p*(1+r/100)n
DECLARE
P NUMBER(6,2):=&P;
R NUMBER(6,2):=&R;
T NUMBER(6,2):=&T;
CI NUMBER(6,2);
BEGIN
CI:=P*POWER(1+(R/100),T);
DBMS_OUTPUT.PUT_LINE('COMPOUND INTEREST IS '||CI);
END;
/
34.WAP to calculate the sum of 1!+2!+......+n!
DECLARE
N NUMBER:=&N;
S NUMBER:=0;
F NUMBER:=1;
BEGIN
FOR I IN 1..N
LOOP
FOR J IN 1..I
LOOP
F:=F*J;
END LOOP;
S:=S+F;
F:=1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('SUM OF FACT IS '||S);
END;
/
35.WAP to calculate the sum of 1+1/2+1/3+......+1/n
DECLARE
N NUMBER:=&N;
A NUMBER;
S NUMBER(6,2):=0;
BEGIN
FOR I IN 1..N
LOOP
A:=1/I;
S:=S+A;
END LOOP;
DBMS_OUTPUT.PUT_LINE('SUM OF NO ARE '||S);
END;
/
36.WAP to calculate the sum of 1/1!+1/2!+.....+1/n!
DECLARE
N NUMBER:=&N;
S NUMBER(6,2):=0;
F NUMBER:=1;
BEGIN
FOR I IN 1..N
LOOP
FOR J IN 1..I
LOOP
F:=F*J;
END LOOP;
S:=S+(1/F);
END LOOP;
DBMS_OUTPUT.PUT_LINE('SUM IS '||S);
END;
/
37.WAP to calculate the sum of 1/1!+2/2!+......+n/n!
DECLARE
N NUMBER(4):=&N;
S NUMBER(6,2):=0;
F NUMBER(4):=1;
BEGIN
FOR I IN 1..N
LOOP
FOR J IN 1..I
LOOP
F:=F*J;
END LOOP;
S:=S+(I/F);
END LOOP;
DBMS_OUTPUT.PUT_LINE('SUM OF FACT IS '||S);
END;
/
38.Write a program to display the months between two dates of a year
DECLARE
D DATE:='&D';
D1 DATE:='&D1';
BEGIN
WHILE D < D1
LOOP
DBMS_OUTPUT.PUT_LINE(TO_CHAR(D,'MONTH'));
D:=ADD_MONTHS(D,1);
END LOOP;
END;
/
39.Write a program to accept the date and print the weekdays from the given date
DECLARE
D DATE:='&D';
WD DATE;
BEGIN
WD:=D+6;
WHILE D <= WD
LOOP
DBMS_OUTPUT.PUT_LINE(TO_CHAR(D,'DAY'));
D:=D+1;
END LOOP;
END;
/
40.WAP to accept the date and print the weekdays from the given date along with date format
DECLARE
D DATE:='&D';
WD DATE;
BEGIN
WD:=D+6;
WHILE D <= WD
LOOP
DBMS_OUTPUT.PUT_LINE(TO_CHAR(D,'DAY')||D);
D:=D+1;
END LOOP;
END;

Stored Function

Create stored function is called get_cleaners_location. This function takes as input a cleaner’s

number and returns the cleaner’s depot address. Call the function from within an SQL statement

to select the cleaner’s name and location for a particular cleaner.

create or replace function get_cleaners_location (cleaner_num in cleaner.cno%type)

return depot.daddress %type as

dlocation depot.daddress %type;

begin

select daddress

into dlocation

from cleaner c, depot d

where cno= cleaner_num

and d.dno=c.dno;

return (dlocation);

end;

Function created.

select cname, get_cleaners_location (cno) "Address"

from cleaner

where cno='110';

CNAME Address

John Camden Road

exercise for function in oracle

Rewrite the following IF statements so that you do not use an IF statement to set the value of no_revenue . What is the difference between these two statements? How does that difference affect your answer?
```
IF total_sales <= 0
THEN
   no_revenue := TRUE;
ELSE
   no_revenue := FALSE;
END IF;

IF total_sales <= 0
THEN
   no_revenue := TRUE;
ELSIF total_sales > 0
THEN
   no_revenue := FALSE;
END IF;
```
Rewrite the following IF statement to work as efficiently as possible under all conditions, given the following information: the calc_totalsnumeric function takes three minutes to return its value, while the overdue_balance Boolean function returns TRUE/FALSE in less than a second.
```
IF calc_totals (1994, company_id_in => 1005) AND
   NOT overdue_balance (company_id_in => 1005) 
THEN
   display_sales_figures (1005);
ELSE
   contact_vendor;
END IF;
```

Rewrite the following IF statement to get rid of unnecessary nested IFs:

IF salary < 10000 
THEN 
   bonus := 2000;
ELSE
   IF salary < 20000 
   THEN 
      bonus := 1500;
   ELSE
      IF salary < 40000 
      THEN 
         bonus := 1000;
      ELSE
         bonus := 500;
      END IF;
   END IF;
END IF;

Which procedure will never be executed in this IF statement?

IF (order_date > SYSDATE) AND order_total >= min_order_total
THEN
   fill_order (order_id, 'HIGH PRIORITY');
ELSIF (order_date < SYSDATE) OR
      (order_date = SYSDATE)
THEN
   fill_order (order_id, 'LOW PRIORITY');
ELSIF order_date <= SYSDATE AND order_total < min_order_total
THEN
   queue_order_for_addtl_parts (order_id);
ELSIF order_total = 0
THEN
   disp_message (' No items have been placed in this order!');
END IF;

A.1.2 Loops

How many times does the following loop execute?

FOR year_index IN REVERSE 12 .. 1
LOOP
   calc_sales (year_index);
END LOOP:

Select the type of loop (FOR, WHILE, simple) appropriate to meet each of the following requirements:
1. Set the status of each company whose company IDs are stored in a PL/SQL table to closed.
2. For each of twenty years in the loan-processing cycle, calculate the outstanding loan balance for the specified customer. If the customer is a preferred vendor, stop the calculations after twelve years.
3. Display the name and address of each employee returned by the cursor.
4. Scan through the list of employees in the PL/SQL table, keeping count of all salaries greater than $50,000. Don't even start the scan, though, if the table is empty or if today is a Saturday or if the first employee in the PL/SQL table is the president of the company.

Identify the problems with (or areas for improvement in) the following loops. How would you change the loop to improve it?

FOR i IN 1 .. 100
LOOP
   calc_totals (i);
   IF i > 75
   THEN
      EXIT;
   END IF;
END LOOP;

OPEN emp_cur;
FETCH emp_cur INTO emp_rec;
WHILE emp_cur%FOUND
LOOP
   calc_totals (emp_rec.salary);
   FETCH emp_cur INTO emp_rec;
   EXIT WHEN emp_rec.salary > 100000;
END LOOP;
CLOSE emp_cur;

FOR a_counter IN lo_val .. hi_val
LOOP
   IF a_counter > lo_val * 2
   THEN
      hi_val := lo_val;
   END IF;
END LOOP;

DECLARE
   CURSOR emp_cur IS SELECT salary FROM emp;
   emp_rec emp_cur%ROWTYPE
BEGIN
   OPEN emp_cur;
   LOOP
      FETCH emp_cur INTO emp_rec;
      EXIT WHEN emp_cur%NOTFOUND;
      calc_totals (emp_rec.salary);
   END LOOP;
   CLOSE emp_cur;
END;

WHILE no_more_data
LOOP
   read_next_line (text);
   no_more_data := text IS NULL;
   EXIT WHEN no_more_data;
END LOOP;

FOR month_index IN 1 .. 12
LOOP
   UPDATE monthly_sales 
      SET pct_of_sales = 100
    WHERE company_id = 10006
      AND month_number = month_index;
END LOOP;

DECLARE
   CURSOR emp_cur IS SELECT ... ;
BEGIN
   FOR emp_rec IN emp_cur
   LOOP
      calc_totals (emp_rec.salary);
   END LOOP;
   IF emp_rec.salary < 10000
   THEN
      DBMS_OUTPUT.PUT_LINE ('Give ''em a raise!');
   END IF;
   CLOSE emp_cur;
END;

DECLARE
   CURSOR checked_out_cur IS 
      SELECT pet_id, name, checkout_date 
        FROM occupancy
       WHERE checkout_date IS NOT NULL;
BEGIN
   FOR checked_out_rec IN checked_out_cur 
   LOOP
      INSERT INTO occupancy_history (pet_id, name, checkout_date)
         VALUES (checked_out_rec.pet_id, 
                 checked_out_rec.name, 
                 checked_out_rec.checkout_date);
   END LOOP;
END;

How many times does the following WHILE loop execute?

DECLARE
   end_of_analysis BOOLEAN := FALSE;
   CURSOR analysis_cursor IS SELECT ...;
   analysis_rec analysis_cursor%ROWTYPE;
   next_analysis_step NUMBER;
   PROCEDURE get_next_record (step_out OUT NUMBER) IS
   BEGIN
      FETCH analysis_cursor INTO analysis_rec;
      IF analysis_rec.status = 'FINAL'
      THEN
         step_out := 1;
      ELSE
         step_out := 0;
      END IF;
   END;
BEGIN
   OPEN analysis_cursor;
   WHILE NOT end_of_analysis
   LOOP
      get_next_record (next_analysis_step);
      IF analysis_cursor%NOTFOUND AND
         next_analysis_step IS NULL
      THEN
         end_of_analysis := TRUE;
      ELSE
         perform_analysis;
      END IF;
   END LOOP;
END;

Rewrite the following loop so that you do not use a loop at all.

FOR i IN 1 .. 2
LOOP
   IF i = 1
   THEN
      give_bonus (president_id, 2000000);
   ELSIF i = 2
   THEN
      give_bonus (ceo_id, 5000000);
   END IF;
END LOOP;

What statement would you remove from this block? Why?

DECLARE
   CURSOR emp_cur IS 
      SELECT ename, deptno, empno 
        FROM emp
       WHERE sal < 2500;
   emp_rec emp_cur%ROWTYPE;
BEGIN
   FOR emp_rec IN emp_cur
   LOOP
      give_raise (emp_rec.empno, 10000);
   END LOOP;
END;

A.1.3 Exception Handling

In each of the following PL/SQL blocks, a VALUE_ERROR exception is raised (usually by an attempt to place too large a value into a local variable). Identify which exception handler (if any -- the exception could also go unhandled) will handle the exception by writing down the message that will be displayed by the call to PUT_LINE in the exception handler. Explain your choice.

DECLARE
   string_of_5_chars VARCHAR2(5);
BEGIN
   string_of_5_chars := 'Steven';
END;

DECLARE
   string_of_5_chars VARCHAR2(5);
BEGIN
   BEGIN
      string_of_5_chars := 'Steven';
   EXCEPTION
      WHEN VALUE_ERROR
      THEN
         DBMS_OUTPUT.PUT_LINE ('Inner block');
   END;
EXCEPTION
   WHEN VALUE_ERROR
   THEN
      DBMS_OUTPUT.PUT_LINE ('Outer block');
END;

DECLARE
   string_of_5_chars VARCHAR2(5) := 'Eli';
BEGIN
   BEGIN
      string_of_5_chars := 'Steven';
   EXCEPTION
      WHEN VALUE_ERROR
      THEN
         DBMS_OUTPUT.PUT_LINE ('Inner block');
   END;
EXCEPTION
   WHEN VALUE_ERROR
   THEN DBMS_OUTPUT.PUT_LINE ('Outer block');
END;

DECLARE
   string_of_5_chars VARCHAR2(5) := 'Eli';
BEGIN
   DECLARE
      string_of_3_chars VARCHAR2(3) := 'Chris';
   BEGIN
      string_of_5_chars := 'Veva';
   EXCEPTION
      WHEN VALUE_ERROR
      THEN DBMS_OUTPUT.PUT_LINE ('Inner block');
   END;
EXCEPTION
   WHEN VALUE_ERROR
   THEN DBMS_OUTPUT.PUT_LINE ('Outer block');
END;

DECLARE
   string_of_5_chars VARCHAR2(5);
BEGIN
   BEGIN
      string_of_5_chars := 'Steven';
   EXCEPTION
      WHEN VALUE_ERROR
      THEN
         RAISE NO_DATA_FOUND;
      WHEN NO_DATA_FOUND
      THEN
         DBMS_OUTPUT.PUT_LINE ('Inner block');
   END;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.PUT_LINE ('Outer block');
END;

Write a PL/SQL block that allows all of the following SQL DML statements to execute, even if any of the others fail:

UPDATE emp SET empno = 100 WHERE empno > 5000;
DELETE FROM dept WHERE deptno = 10;
DELETE FROM emp WHERE deptno = 10;

Write a PL/SQL block that handles by name the following Oracle error:
```
ORA-1014: ORACLE shutdown in progress.
```
The exception handler should, in turn, raise a VALUE_ERROR exception. Hint: use the EXCEPTION INIT pragma.

When the following block is executed, which of the two messages shown below are displayed? Explain your choice.

Message from Exception Handler	Output from Unhandled Exception
Predefined or programmer-defined?	Error at line 1: ORA-1403: no data found ORA-6512: at line 5

DECLARE
   d VARCHAR2(1);
   /* Create exception with a predefined name. */
   no_data_found EXCEPTION; 
BEGIN
   SELECT dummy INTO d FROM dual WHERE 1=2;
   IF d IS NULL 
   THEN
      /* 
      || Raise my own exception, not the predefined 
      || STANDARD exception of the same name.
      */
      RAISE no_data_found; 
   END IF;
EXCEPTION
   /* This handler only responds to the RAISE statement. */
   WHEN no_data_found
   THEN 
      DBMS_OUTPUT.PUT_LINE ('Predefined or programmer-defined?');
END;

I create the getval package as shown below. I then call DBMS_OUTPUT.PUT_LINE to display the value returned by the getval.getfunction. What is displayed on the screen?

CREATE OR REPLACE PACKAGE getval
IS
   FUNCTION get RETURN VARCHAR2;
END getval;
/
CREATE OR REPLACE PACKAGE BODY getval
IS
   v VARCHAR2(1) := 'abc';
   FUNCTION get RETURN VARCHAR2 IS
   BEGIN
      RETURN v;
   END;
BEGIN
   NULL;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE ('Trapped!');
END getval;
/

A.1.4 Cursors

What cursor-related statements are missing from the following block?

DECLARE
   CURSOR emp_cur IS SELECT * FROM emp;
BEGIN
   OPEN emp_cur;
   FETCH emp_cur INTO emp_rec;
END;

What statement should be removed from the following block?

DECLARE
   CURSOR emp_cur IS SELECT * FROM emp;
   emp_rec emp_cur%ROWTYPE;
BEGIN
   FOR emp_rec IN emp_cur
   LOOP
      give_raise (emp_rec.empno);
   END LOOP;
END;

Name the cursor attribute (along with the cursor name) you would use (if any) for each of the following requirements:
1. If the FETCH did not return any records from the company_cur cursor, exit the loop.
2. If the number of rows deleted exceeded 100, notify the manager.
3. If the emp_cur cursor is already open, fetch the next record. Otherwise, open the cursor.
4. If the FETCH returns a row from the sales_cur cursor, display the total sales information.
5. I use an implicit cursor SELECT statement to obtain the latest date of sales for my store number 45067. If no data is fetched or returned by the SELECT, display a warning.

What message is displayed in the following block if the SELECT statement does not return a row?

PROCEDURE display_dname (emp_in IN INTEGER) IS
   department# dept.deptno%TYPE := NULL;
BEGIN
   SELECT deptno INTO department#
     FROM emp
    WHERE empno = emp_in;
   IF department# IS NULL
   THEN
      DBMS_OUTPUT.PUT_LINE ('Dept is not found!');
   ELSE
      DBMS_OUTPUT.PUT_LINE ('Dept is ' || TO_CHAR (department#));
   END IF;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.PUT_LINE ('No data found');
END;

What message is displayed in the following block if there are no employees in department 15?

PROCEDURE display_dept_count 
IS
   total_count INTEGER := 0;
BEGIN
   SELECT COUNT(*) INTO total_count
     FROM emp
    WHERE deptno = 15;
   IF total_count = 0
   THEN
      DBMS_OUTPUT.PUT_LINE ('No employees in department!');
   ELSE
      DBMS_OUTPUT.PUT_LINE
         ('Count of employees in dept 15 = ' || TO_CHAR (total_count));
   END IF;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.PUT_LINE ('No data found');
END;

If you fetch past the last record in a cursor's result set, what will happen?

How would you change the SELECT statement in the following block's cursor so that the block can display the sum of salaries in each department?

DECLARE
   CURSOR tot_cur IS 
      SELECT deptno, SUM (sal)   
        FROM emp
       GROUP BY deptno;
BEGIN
   FOR tot_rec IN tot_cur
   LOOP
      DBMS_OUTPUT.PUT_LINE 
         ('Total is: ' || tot_rec.total_sales);
   END LOOP;
END;

Rewrite the following block to use a cursor parameter. Then rewrite to use a local module, as well as a cursor parameter.

DECLARE
   CURSOR dept10_cur IS 
      SELECT dname, SUM (sal) total_sales  
        FROM emp
       WHERE deptno = 10;
   dept10_rec dept10_cur%ROWTYPE;
   CURSOR dept20_cur IS 
      SELECT dname, SUM (sal)   
        FROM emp
       WHERE deptno = 20;
   dept20_rec dept20_cur%ROWTYPE;
BEGIN
   OPEN dept10_cur;
   FETCH dept10_cur INTO dept10_rec;
   DBMS_OUTPUT.PUT_LINE 
      ('Total for department 10 is: ' || tot_rec.total_sales);
   CLOSE dept10_cur;
   OPEN dept20_cur;
   FETCH dept20_cur INTO dept20_rec;
   DBMS_OUTPUT.PUT_LINE 
      ('Total for department 20 is: ' || tot_rec.total_sales);
   CLOSE dept20_cur;
END;

Place the following cursor inside a package, declaring the cursor as a public element (in the specification). The SELECT statement contains all of the columns in the emp table, in the same order.
```
CURSOR emp_cur (dept_in IN INTEGER) IS
   SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
     FROM emp
    WHERE deptno = dept_in;
```

Monday, July 25, 2016

Retrieve Data From MySQL Database in Android

<?php

if($_SERVER['REQUEST_METHOD']=='GET'){

$id = $_GET['id'];

require_once('dbConnect.php');

$sql = "SELECT * FROM colleges WHERE id='".$id."'";

$r = mysqli_query($con,$sql);

$res = mysqli_fetch_array($r);

$result = array();

array_push($result,array(
"name"=>$res['name'],
"address"=>$res['address'],
"vc"=>$res['vicechancellor']
)
);

echo json_encode(array("result"=>$result));

mysqli_close($con);

}

dbConnect.php page code

<?php
define('HOST','localhost');
define('USER','root');
define('PASS','');
define('DB','androiddb');

$con = mysqli_connect(HOST,USER,PASS,DB) or die('Unable to Connect');

Creating a new Android Project

Add app

<div id="inner-editor">
<br class="Apple-interchange-newline">compile 'com.mcxiaoke.volley:library-aar:1.0.0'</div>

1	compile 'com.mcxiaoke.volley:library-aar:1.0.0'

Now create a new Java class in your package. I created Config.java. Here we will declare some important constants.

package net.simplifiedcoding.gettingspecificdata;

public class Config {

public static final String DATA_URL = "http://192.168.94.1/Android/College/getData.php?id=";

public static final String KEY_NAME = "name";

public static final String KEY_ADDRESS = "address";

public static final String KEY_VC = "vc";

public static final String JSON_ARRAY = "result";

}

For creating the above layout you can use the following xml

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"

xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent"

android:orientation="vertical"

android:layout_height="match_parent" android:paddingLeft="@dimen/activity_horizontal_margin"

android:paddingRight="@dimen/activity_horizontal_margin"

android:paddingTop="@dimen/activity_vertical_margin"

android:paddingBottom="@dimen/activity_vertical_margin" tools:context=".MainActivity">

<LinearLayout

android:orientation="horizontal"

android:layout_width="match_parent"

android:layout_height="wrap_content">

<EditText

android:id="@+id/editTextId"

android:layout_width="wrap_content"

android:layout_height="wrap_content"

android:layout_weight="1"/>

<Button

android:id="@+id/buttonGet"

android:text="Get"

android:layout_width="wrap_content"

android:layout_height="wrap_content" />

</LinearLayout>

<TextView

android:id="@+id/textViewResult"

android:layout_width="fill_parent"

android:layout_height="fill_parent" />

</LinearLayout>

MAINACtivite.java

package net.simplifiedcoding.gettingspecificdata;

import android.app.ProgressDialog;

import android.os.Bundle;

import android.support.v7.app.AppCompatActivity;

import android.view.View;

import android.widget.Button;

import android.widget.EditText;

import android.widget.TextView;

import android.widget.Toast;

import com.android.volley.RequestQueue;

import com.android.volley.Response;

import com.android.volley.VolleyError;

import com.android.volley.toolbox.StringRequest;

import com.android.volley.toolbox.Volley;

import org.json.JSONArray;

import org.json.JSONException;

import org.json.JSONObject;

public class MainActivity extends AppCompatActivity implements View.OnClickListener {

private EditText editTextId;

private Button buttonGet;

private TextView textViewResult;

private ProgressDialog loading;

@Override

protected void onCreate(Bundle savedInstanceState) {

super.onCreate(savedInstanceState);

setContentView(R.layout.activity_main);

editTextId = (EditText) findViewById(R.id.editTextId);

buttonGet = (Button) findViewById(R.id.buttonGet);

textViewResult = (TextView) findViewById(R.id.textViewResult);

buttonGet.setOnClickListener(this);

}

private void getData() {

String id = editTextId.getText().toString().trim();

if (id.equals("")) {

Toast.makeText(this, "Please enter an id", Toast.LENGTH_LONG).show();

return;

}

loading = ProgressDialog.show(this,"Please wait...","Fetching...",false,false);

String url = Config.DATA_URL+editTextId.getText().toString().trim();

StringRequest stringRequest = new StringRequest(url, new Response.Listener<String>() {

@Override

public void onResponse(String response) {

loading.dismiss();

showJSON(response);

}

new Response.ErrorListener() {

@Override

public void onErrorResponse(VolleyError error) {

Toast.makeText(MainActivity.this,error.getMessage().toString(),Toast.LENGTH_LONG).show();

}

});

RequestQueue requestQueue = Volley.newRequestQueue(this);

requestQueue.add(stringRequest);

}

private void showJSON(String response){

String name="";

String address="";

String vc = "";

try {

JSONObject jsonObject = new JSONObject(response);

JSONArray result = jsonObject.getJSONArray(Config.JSON_ARRAY);

JSONObject collegeData = result.getJSONObject(0);

name = collegeData.getString(Config.KEY_NAME);

address = collegeData.getString(Config.KEY_ADDRESS);

vc = collegeData.getString(Config.KEY_VC);

} catch (JSONException e) {

e.printStackTrace();

}

textViewResult.setText("Name:\t"+name+"\nAddress:\t" +address+ "\nVice Chancellor:\t"+ vc);

}

@Override

public void onClick(View v) {

getData();

}

CREATE OR REPLACE FUNCTION

CREATE OR REPLACE FUNCTION get_dname( p_deptno IN NUMBER )
  RETURN VARCHAR2
IS
  l_dname VARCHAR2(30);
BEGIN
  SELECT dname
    INTO l_dname
    FROM dept
   WHERE deptno = p_deptno;
  RETURN l_dname;
END;

PACKAGE BOdy

CREATE OR REPLACE PACKAGE BODY cv_types AS
  PROCEDURE get_employees(deptid in number,
                          employees in out empinfotyp)
  IS
  BEGIN
    OPEN employees FOR
      SELECT employee_id,
        substr(first_name,1,1) || '. '|| last_name as employee_name,
        hire_date,
        to_char(salary, '999G999D99') as salary,
        NVL(commission_pct,0) as commission_pct,
        to_char(calc_remuneration(salary, commission_pct),
                '9999G999D99') as remuneration
      FROM employees
      WHERE department_id = deptid
      ORDER BY employee_id ASC;
  END get_employees;
END cv_types;

How To Execute a Stored Procedure?

If you want to execute a stored procedure, you can use the EXECUTE statement. The example script below shows how to executes a stored procedure:

SQL> set serveroutput on;

SQL> CREATE PROCEDURE Greeting AS
  2  BEGIN
  3    DBMS_OUTPUT.PUT_LINE('Welcome to FYICenter!');
  4  END;
  5  /
Procedure created.

SQL> EXECUTE Greeting;
Welcome to FYICenter!

Thursday, July 21, 2016

Key Features of the Android Platform

Key Features of the Android Platform
Mobile app developers, mobile device manufacturers, and cell operators consider the Android platform as the most promising platform due to the cost efficiency in its production values. The popularity of the Android platform is mainly due to its numerous intriguing features. Some key features of the Android platform are:
Integrated browser: Android provides an integrated Web browser, which is based on the open-source WebKit engine. SQLite: Android provides a powerful, fast, and lightweight relational database engine called SQLite. Android apps can store application data in a SQLite database. Media support: Android provides support for common audio, video, and still image formats such as MPEG4 SP, MP3, JPEG, PNG, and GIF. Wireless services: Android provides various options to connect to other devices. These

connectivity options include:
Bluetooth: An open wireless technology standard for exchanging data over short distances using short wavelength radio transmissions. Wireless Fidelity (Wi-Fi): A networking technology that does not require wires for devices to communicate with each other provided the devices are in the vicinity of an access point called a hotspot. Hotspots are commonly provided in public places, such as hotels, airports, coffee shops, and train stations to enable people to connect to the Internet through their devices. Dalvik Virtual Machine (DVM): Android apps are mostly written in Java programming language and are compiled into byte codes. Android byte codes are interpreted at runtime by DVM. Application framework: Android’s application framework allows app developers to build rich and innovative apps. These apps can access the same APIs that are used by the core apps provided by the Android platform. In addition, the application framework allows developers to reuse components published by other apps. Rich development environment: The Android ADT bundle provides a rich development environment, which includes: x x x x x The Eclipse + ADT plugin The Android SDK tools The Android platform tools The Android platform The Android system image for the emulator
Android Versions
Android has undergone a number of updates since its original release. The updates to the base operating system fix the bugs in the previous versions and also add new features. Each new version of the Android operating system is developed under a code name, which is the name of a dessert. The various versions of Android launched in an order are shown in the following animation:
The following list describes the various Android versions: q

Android 1.5 (Cupcake): This version was released in April 2009. This was a significant version that showcased the power of the Android platform. It has been said that this version was supposed to be version 1.2 but Google decided to make it a major revision and made it 1.5 instead and gave it the dessert name, Cupcake. Android 1.6 (Donut): This version was released in September 2009. It provides some advanced features such as: x
x x x
Integrated camera, camcorder, and gallery interface Google turn-by-turn navigation feature Updated voice search Updated search experience Android 2.0/2.1 (Eclair): Android 2.0 was released in October 2009. In December 2009, it was released with a bug fix version 2.0.1. Android 2.1 was released in January 2010. Most people consider these versions as a single release. It has features such as Bluetooth 2.1 support, flash and digital zoom for the camera, multitouch support, and live wallpapers. Android 2.2 (Froyo): This version was released in May 2010. This version mainly improved speed by adopting the JavaScript just-in-time compiler engine from the Google browser, Chrome. It improved browser support by adding features such as Flash 10.1 plug-in support and animated GIF support. Android 2.3 (Gingerbread): This version was released in March 2011. The new features in Android 2.3 are: x x x x New UI theme with simpler color scheme Redesigned on-screen keyboard New copy and paste functionality Better power management

Better app management New downloads manager New camera app for accessing multiple cameras Support for extra large screens Android 3.0 (Honeycomb): This version was released as a beta version. It is specifically designed for mobile tablet devices, such as the new generation of Samsung Galaxy tabs and Motorola XOOM. Android 3.1 (Honeycomb): It is an updated version of Android 3.0. It includes new developer features, such as API for USB accessories and new input events ranging from mice, trackballs, and joysticks. Android 3.2 (Honeycomb): It is an updated version of Honeycomb. It includes features, such as media sync from SD card, compatibility zoom for fixed-sized apps, and extended API for managing screen support. Android 4.0 (Ice Cream Sandwich): It is a new version of Android, which provides a brand new look; however, it has some resemblance to Android Honeycomb. It provides various features, such as refined evolved UI, multitasking, resizable widgets, and lock screen actions. Android 4.0.3 (Ice Cream Sandwich): It is an updated version of Android 4.0. It includes enhancement in features, such as social stream API in contacts provider, calendar provider, home screen widgets, and spell checking. Android 4.1 (Jelly Bean): It is another newer version of Android, which provides several features, such as speedier interface, a new Camera app, offline voice typing, and significantly improved notifications. Android 4.2 (Jelly Bean): It is an updated version of Android 4.1, which provides an improved speed and simplicity of Android 4.1. It includes various new features, such as photo sphere, redesigned camera app, and new gesture typing keyboard.

Key Features of the Android Platform

MOBILE APP

Fundamentals of Mobile App Development
Since the beginning of this information age (21st Century), our society has witnessed remarkable growth in technology, which has affected our lifestyle in various ways. For example, technology has remarkably transformed our education system. Computers are being widely used for education delivery. Online education is also gaining rapid popularity. Technology has been growing at a rapid pace to accommodate the needs and desires of people for obtaining a simpler and an immaculate lifestyle. Mobile devices are one of the greatest technological advancements that have hit the 21st Century. Today, there is a tremendous usage of gadgets ranging from Personal Computers (PCs) to Personal Digital Assistants (PDAs) to featured cellular phones in our
everyday life. As per the demand for usability and needs of a consumer, all device manufacturers are continuously innovating and rolling out new and innovative products. Mobile devices, such as PDAs, Tabs, and smartphones, allow people to access the Internet for different purposes, such as sending e-mails, instant messaging, text messaging, and Web browsing. In addition, these mobile devices allow users to even perform professional tasks, such as managing documents and presentations. To perform each of these tasks through a mobile device, you need to install an appropriate mobile app on your mobile device. A mobile app is software that runs on a mobile device. Mobile apps can entertain, educate, and assist users on a daily basis. These mobile apps provide the user with several services, such as communication and messaging, contact management, audio/video, gaming, and network connectivity.

Types of Mobile Apps

Mobile apps can be divided into various categories according to their usage. Some key categories of mobile apps are:

Multimedia apps: These include video players, audio players, live TV players, and graphics/ image viewers. Travel apps: These include currency convertors, language translators, and weather forecasters. Utilities: These include contact manager, task manager, and call manager. Web-based apps: These include search tools, instant messaging tools, and Web browsers. Communication apps: These include e-mail (Gmail/corporate/others), WhatsApp, Viber, and Skype, and so on for voice-to-voice/video calling and instant messaging. Enterprise apps: These include office productivity tools, such as Microsoft Office Mobile and ThinkFree. Productivity apps: These include calendars, calculators, and memo pad. Social networking apps: These include social networking sites, such as Facebook and Twitter. Location-based apps: These include map-based apps, such as Google maps and Bing maps. Gaming apps: These include various types of games, such as puzzles, cards, and sports

tree in c

#include<stdlib.h>
#include<stdio.h>

struct bin_tree {
int data;
struct bin_tree * right, * left;
};
typedef struct bin_tree node;

void insert(node ** tree, int val)
{
node *temp = NULL;
if(!(*tree))
{
temp = (node *)malloc(sizeof(node));
temp->left = temp->right = NULL;
temp->data = val;
*tree = temp;
return;
}

if(val < (*tree)->data)
{
insert(&(*tree)->left, val);
}
else if(val > (*tree)->data)
{
insert(&(*tree)->right, val);
}

}

void print_preorder(node * tree)
{
if (tree)
{
printf("%d\n",tree->data);
print_preorder(tree->left);
print_preorder(tree->right);
}

}

void print_inorder(node * tree)
{
if (tree)
{
print_inorder(tree->left);
printf("%d\n",tree->data);
print_inorder(tree->right);
}
}

void print_postorder(node * tree)
{
if (tree)
{
print_postorder(tree->left);
print_postorder(tree->right);
printf("%d\n",tree->data);
}
}

void deltree(node * tree)
{
if (tree)
{
deltree(tree->left);
deltree(tree->right);
free(tree);
}
}

node* search(node ** tree, int val)
{
if(!(*tree))
{
return NULL;
}

if(val < (*tree)->data)
{
search(&((*tree)->left), val);
}
else if(val > (*tree)->data)
{
search(&((*tree)->right), val);
}
else if(val == (*tree)->data)
{
return *tree;
}
}

void main()
{
node *root;
node *tmp;
//int i;

root = NULL;
/* Inserting nodes into tree */
insert(&root, 9);
insert(&root, 4);
insert(&root, 15);
insert(&root, 6);
insert(&root, 12);
insert(&root, 17);
insert(&root, 2);

/* Printing nodes of tree */
printf("Pre Order Display\n");
print_preorder(root);

printf("In Order Display\n");
print_inorder(root);

printf("Post Order Display\n");
print_postorder(root);

/* Search node into tree */
tmp = search(&root, 4);
if (tmp)
{
printf("Searched node=%d\n", tmp->data);
}
else
{
printf("Data Not found in tree.\n");
}

/* Deleting all nodes of tree */
deltree(root);
}

student solution all subject

Labels

Monday, August 1, 2016

oracle ques ans

Stored Function

exercise for function in oracle

A.1.2 Loops

A.1.3 Exception Handling

A.1.4 Cursors

Monday, July 25, 2016

Retrieve Data From MySQL Database in Android

Creating a new Android Project

CREATE OR REPLACE FUNCTION

PACKAGE BOdy

How To Execute a Stored Procedure?

Thursday, July 21, 2016

Key Features of the Android Platform

Key Features of the Android Platform

MOBILE APP

tree in c

Blog Archive

Autotechboys