[1]
Create or Replace Procedure calsalary(PEmp_no in varchar) As
VBasic number(10,2);
VDa number(10,2);
VHra number(10,2);
VTax number(10,2);
Tuesday, December 08, 2020
Tuesday, October 27, 2020
RDBMS Do As Directed
Do as directed (any two)
Train(Train-no,Train-name)
Station(Station-code,Station-name)
Halt(Halt-code,Seq-no,Train-no,Station-no,Time-in,Time-out)
(Halt-code,Seq-no are composite key)
1. Create a procedure which return Time-in and Time-out for given
Train-no and Station-name.
Monday, October 19, 2020
Tuesday, October 13, 2020
RDBMS PLSQL Program 50 program to generate value of primary key column using MAX function automatically
CREATE OR REPLACE TRIGGER PKMAX
BEFORE INSERT ON emp
FOR EACH ROW
DECLARE
CNT NUMBER;
veno emp.eno%TYPE;
BEGIN
SELECT 'E' || LPAD(MAX(TO_NUMBER(SUBSTR(eno,2))),5,'0') INTO veno
FROM emp;
:NEW.eno := veno;
EXCEPTION
WHEN NO_DATA_FOUND THEN
:NEW.eno := 'E00001';
END;
/
RDBMS PLSQL Program 49 program to generate value of primary key column using sequence automatically
create or replace trigger t_old
before insert on emp
for each row
declare
veno NUMBER(4);
begin
SELECT seq1.NEXTVAL INTO veno FROM DUAL;
:new.eno:=’E’|| lpad(veno,5,0);
end;
/
RDBMS PLSQL Program 48 script to create sequence
CREATE SEQUENCE seq1 start with 1 increment by 1;
/
RDBMS PLSQL Program 47 program to generate before insert,update and delete row trigger on emp table which will not allow changes in data on weekends using RAISE_APPLICATION_ERROR() function
CREATE OR REPLACE TRIGGER Weekend
BEFORE INSERT OR UPDATE OR DELETE ON emp
for each row
WHEN (TO_CHAR(SYSDATE,'day') IN('SATURDAY','SUNDAY'))
DECLARE
BEGIN
RAISE_APPLICATION_ERROR(-20001,'Enjoy your Weekend!! Do not do work!');
END;
/
RDBMS PLSQL Program 46 program to generate before insert,update and delete row trigger on emp table which will not allow changes in data before and after office hours using RAISE_APPLICATION_ERROR() function
CREATE OR REPLACE TRIGGER Working_hrs
BEFORE INSERT OR UPDATE OR DELETE ON emp
DECLARE
WORKING_HOURS NUMBER(2);
BEGIN
WORKING_HOURS := TO_CHAR(SYSDATE,’HH24′);
IF WORKING_HOURS < 9 AND WORKING_HOURS > 17 THEN
RAISE_APPLICATION_ERROR(-20001,'Its not official working hours! Can not do operations on emp');
END IF;
END;
/
RDBMS PLSQL Program 45 program to generate before insert row trigger on emp table which will not allow salary less than 1000 using RAISE_APPLICATION_ERROR() function
CREATE OR REPLACE TRIGGER minSalary
BEFORE INSERT ON emp
FOR EACH ROW
BEGIN
IF (:new.sal < 1000) THEN
RAISE_APPLICATION_ERROR (-20004, ‘Minimum Salary should be 1000’);
END IF;
END;
/
RDBMS PLSQL Program 44 program to generate before insert, update and delete row trigger on emp table
SET Serveroutput ON
CREATE OR REPLACE TRIGGER display_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON EMP
FOR EACH ROW
DECLARE
sal_diff number(10,2);
BEGIN
sal_diff := :NEW.esalary - :OLD.esalary;
dbms_output.put_line('Old salary: ' || :OLD.esalary);
dbms_output.put_line('New salary: ' || :NEW.esalary);
dbms_output.put_line('Salary difference: ' || sal_diff);
END;
/
RDBMS PLSQL Program 43 program to generate after update row trigger on emp table
SET Serveroutput ON
CREATE OR REPLACE TRIGGER log_salary
AFTER UPDATE ON emp
FOR EACH ROW
BEGIN
INSERT INTO Emp_log(Emp_id, Log_date, New_salary, Action)
VALUES (:NEW.Eno, SYSDATE, :NEW.Esalary, 'New Salary');
END;6
/
RDBMS PLSQL Program 42 script to create emp_log table
DROP TABLE Emp_log;
CREATE TABLE Emp_log (
Emp_id CHAR(6),
Log_date DATE,
New_salary NUMBER(10,2),
Action VARCHAR2(20));
RDBMS PLSQL Program 41 program to generate before update row trigger on emp table
SET Serveroutput ON
CREATE OR REPLACE TRIGGER display_salary
BEFORE UPDATE ON EMP
FOR EACH ROW
BEGIN
dbms_output.put_line('Old salary: ' || :OLD.esalary);
END;
/
RDBMS PLSQL Program 40 Script to create user in oracle other than sysadmin to generate triggers
connect system/password;
CREATE USER bca
IDENTIFIED BY bca
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 2 Roles for DEVELOPER
GRANT CONNECT TO bca CONTAINER=ALL;
GRANT RESOURCE TO bca CONTAINER=ALL;
ALTER USER bca DEFAULT ROLE NONE;
-- 3 System Privileges for DEVELOPER
GRANT CREATE SESSION TO bca;
GRANT CREATE TABLE TO bca;
GRANT CREATE TRIGGER TO bca;
GRANT CREATE SEQUENCE TO bca;
-- 2 Tablespace Quotas for DEVELOPER
ALTER USER bca QUOTA UNLIMITED ON SYSAUX;
ALTER USER bca QUOTA UNLIMITED ON USERS;
RDBMS PLSQL Program 39 Program to call package to demonstrate procedure overloading
SET Serveroutput ON
DECLARE
veno emp.eno%type;
vename emp.ename%type;
vesalary emp.esalary%type;
vedoj emp.edoj%type;
vedno emp.edno%type;
veno1 emp.eno%type;
vename1 emp.ename%type;
vesalary1 emp.esalary%type;
BEGIN
veno := '&veno';
vename := '&vename';
vesalary := &vesalary;
vedoj := '&vedoj';
vedno := '&vedno';
veno1 := '&veno1';
vename1 := '&vename1';
vesalary1 := &vesalary1;
emp_package.addemp(veno1,vename1,vesalary1);
emp_package.addemp(veno,vename,vesalary,vedoj,vedno);
END;
/
RDBMS PLSQL Program 38 Program to create package body to demonstrate procedure overloading
CREATE OR REPLACE PACKAGE BODY emp_package AS
PROCEDURE addemp(veno emp.eno%type, vename emp.ename%type, vesalary emp.esalary%type)
IS
BEGIN
INSERT INTO emp (eno,ename,esalary)
VALUES(veno, vename, vesalary);
END;
PROCEDURE addemp(veno emp.eno%type, vename emp.ename%type, vesalary emp.esalary%type, vedoj emp.edoj%type, vedno emp.edno%type)
IS
BEGIN
INSERT INTO emp (eno,ename,esalary,edoj,edno)
VALUES(veno, vename, vesalary, vedoj, vedno);
END;
END emp_package;
/
RDBMS PLSQL Program 37 Program to create package specification to demonstrate procedure overloading
CREATE OR REPLACE PACKAGE emp_package AS
PROCEDURE addemp(veno emp.eno%type, vename emp.ename%type, vesalary emp.esalary%type);
PROCEDURE addemp(veno emp.eno%type, vename emp.ename%type, vesalary emp.esalary%type, vedoj emp.edoj%type, vedno emp.edno%type);
END emp_package;
/
RDBMS PLSQL Program 36 Program to call package
SET Serveroutput ON
DECLARE
veno emp.eno%type;
BEGIN
veno := '&veno';
DBMS_OUTPUT.PUT_LINE(emp_name.find_name(veno));
END;
/
RDBMS PLSQL Program 35 Program generate package body with single function in it
CREATE OR REPLACE PACKAGE BODY emp_name AS
FUNCTION find_name(veno emp.eno%TYPE) return varchar IS
vename emp.ename%TYPE;
BEGIN
SELECT ename INTO vename
FROM emp
WHERE eno = veno;
RETURN vename;
END;
END emp_name;
/
RDBMS PLSQL Program 34 Program generate package specification with single function in it
CREATE OR REPLACE PACKAGE emp_name AS
FUNCTION find_name(veno emp.eno%type)return varchar;
END emp_name;
/
Saturday, October 10, 2020
RDBMS PLSQL Program 33 Program to call package
SET Serveroutput ON
DECLARE
veno emp.eno%type;
BEGIN
veno := '&veno';
emp_sal.find_sal(veno);
END;
/
RDBMS PLSQL Program 32 Program generate package body with single procedure in it
CREATE OR REPLACE PACKAGE BODY emp_sal AS
PROCEDURE find_sal(veno emp.eno%TYPE) IS
vesalary emp.esalary%TYPE;
BEGIN
SELECT esalary INTO vesalary
FROM emp
WHERE eno = veno;
dbms_output.put_line('Salary: '|| vesalary);
END;
END emp_sal;
/
RDBMS PLSQL Program 31 Program generate package specification with single procedure in it
CREATE OR REPLACE PACKAGE emp_sal AS
PROCEDURE find_sal(veno emp.eno%type);
END emp_sal;
/
Thursday, October 08, 2020
Saturday, October 03, 2020
Thursday, October 01, 2020
Wednesday, September 30, 2020
RDBMS PLSQL Program 30 Program to handle multiple exceptions
set serveroutput on
DECLARE
veno emp.eno%type;
vename emp.ename%type;
vesalary emp.esalary%type;
BEGIN
veno := '&veno';
SELECT ename, esalary INTO vename, vesalary
FROM emp
WHERE eno = veno;
DBMS_OUTPUT.PUT_LINE ('Name: '|| vename);
DBMS_OUTPUT.PUT_LINE ('Salary: ' || vesalary);
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('No such employee!');
WHEN NOT_LOGGED_ON THEN
dbms_output.put_line('user not logged in ');
WHEN TOO_MANY_ROWS THEN
dbms_output.put_line('multiple records found');
WHEN VALUE_ERROR THEN
dbms_output.put_line('value type of size not appropriate');
WHEN others THEN
dbms_output.put_line('Error!');
END;
/
RDBMS PLSQL Program 29 Program to handle User defined exception
SET serveroutput On
DECLARE
veno emp.eno%type;
vename emp.ename%type;
ex_invalid_no EXCEPTION;
BEGIN
veno := '&veno';
IF substr(veno,1,1) <> 'E' THEN
RAISE ex_invalid_no;
ELSE
SELECT ename INTO vename
FROM emp
WHERE eno = veno;
DBMS_OUTPUT.PUT_LINE ('Name: '|| vename);
END IF;
EXCEPTION
WHEN ex_invalid_no THEN
dbms_output.put_line('ID must start with E');
END;
/
RDBMS PLSQL Program 28 Program to handle User named exception
DECLARE
resource_busy_nowait EXCEPTION;
PRAGMA EXCEPTION_INIT (resource_busy_nowait, -00054);
veno emp.eno%type;
vename emp.ename%type;
BEGIN
veno := '&veno';
vename := '&vename';
SELECT eno, ename into veno, vename
FROM emp
Where eno = veno
FOR UPDATE NOWAIT;
UPDATE emp SET ename = vename WHERE eno = veno;
EXCEPTION
WHEN resource_busy_nowait THEN
Dbms_output.Put_line('emp is locked - please try later');
END;
/
RDBMS PLSQL Program 27 Program to handle oracle named exception
set serveroutput on
DECLARE
veno emp.eno%type;
vename emp.ename%type;
vesalary emp.esalary%type;
BEGIN
veno := '&veno';
SELECT ename, esalary INTO vename, vesalary
FROM emp
WHERE eno = veno;
DBMS_OUTPUT.PUT_LINE ('Name: '|| vename);
DBMS_OUTPUT.PUT_LINE ('Salary: ' || vesalary);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No such employee!');
END;
/
RDBMS PLSQL Program 26 Procedure with in and out parameters and program to call it
Procedure
=======================================================
CREATE or REPLACE PROCEDURE pro1(veno in char,temp out emp%rowtype)
IS
BEGIN
SELECT * INTO temp FROM emp WHERE Eno = veno;
END;
/
Program to call above procedure
=======================================================
set serveroutput on
DECLARE
temp emp%rowtype;
veno emp.eno%type;
BEGIN
veno :='&veno';
pro1(veno,temp);
dbms_output.put_line(temp.eno||' -> '||temp.ename||' -> '||temp.edno||' -> '||temp.esalary);
END;
/
RDBMS PLSQL Program 25 Function with parameter, return value and program to call it
Function
================================================
CREATE or REPLACE FUNCTION fun1(veno in varchar)
RETURN varchar2
IS
name emp.ename%type;
BEGIN
select ename into name from emp where eno = veno;
return name;
END;
/
Program to call above function
=============================================
set serveroutput on
DECLARE
veno emp.eno%type;
name emp.ename%type;
BEGIN
veno := '&veno';
name := fun1(veno);
dbms_output.put_line('Name:'||' '||name);
end;
/
RDBMS PLSQL Program 24 Function to count number of employees in emp table and program to call it
Function
============================================
CREATE OR REPLACE FUNCTION totalemps
RETURN number IS
total number(3) := 0;
BEGIN
SELECT count(*) into total
FROM emp;
RETURN total;
END;
/
Program to call above function
======================================
set serveroutput on
DECLARE
e number(3);
BEGIN
e := totalemps();
dbms_output.put_line('Total no. of Employees: ' || e);
END;
/
RDBMS PLSQL Program 23 Procedure to print hello world message
set serveroutput on
BEGIN
greetings;
END;
/
RDBMS PLSQL Program 22 Procedure to print hello world message and program to call it
Procedure
=====================================
CREATE OR REPLACE PROCEDURE greetings
AS
BEGIN
dbms_output.put_line('Hello World!');
END;
/
Program to call above procedure
========================================
set serveroutput on
BEGIN
greetings;
END;
/
Tuesday, September 22, 2020
RDBMS Theory Assignment 1
Write short answers of following questions:
1. Define self join.
2. What difference is between truncate and drop table ?
3. Give the definition of Active Data Set.
4. Write the difference between unique constraint and primary constraint.
5. What do you mean by save point ? How to createSave point ?
6. Difference between CYCLE and NO CYCLE option in sequence.
7. Define equi join.
8. What is the difference between NO_DATA_FOUND and %NOTFOUND.
9. State the difference between function and procedure in oracle.
10. Explain intersect clause.
11. Write a command to give insert and modify privileges on emp from
user Raj to user Anand.
12. Define view with syntax.
13. What is the functionality of Rollback statement?
14. State the difference between function and procedure in oracle.
15. How to create two procedures having same name ?
16. What is meaning of :New and :Old in trigger ?
Write answers of following questions in detail:
1. List out E.F.T. codd’s rule. Explain any four in detail.
2. What is cursor? Explain types of cursor with example.
3. Write a note on implicit and explicit cursor with example.
4. What is View ? Explain types of View.
5. What is Data Constraints ? List out all types of constraint. Explain foreign key constraint with ON DELETE SET NULL.
6. Discuss types of join.
7. Discuss PL/SQL block structure
8. What is Exception ? Explain types of exception handling
in oracle. Give one example.
9. Write a PL/SQL code for automatic primary key generation using
database trigger.
10. Define overloading. Explain procedure overloading in oracle with proper
example.
Write notes on the following:
1. Any four string functions
2. Any four date functions
3. Index
4. Sequence
5. Range searching and pattern matching operator
6. Aggregate functions
7. Sub-query
8. Trigger
Do as directed:
Consider the following table and do as directed:
Employee (Empno, Emp-name, Date-Of—Birth,Address,City)
Salary (Emp-no, Basic, DA, HRA, Tax, Gross, NET)
1. Create a procedure which calculate gross and Net Salary
after incrementing basic salary by 11% given emp-no.
2. Create a function which return name of employee who
is youngest when empno is passed.
3. Create a trigger which removes records of those
employees who cross their 45 years of age.
Train(Train-no,Train-name)
Station(Station-code,Station-name)
Halt(Halt-code,Seq-no,Train-no,Station-no,Time-in,Time-out)
(Halt-code,Seq-no are composite key)
1. Create a procedure which return Time-in and Time-out for given
Train-no and Station-name.
2. Create a function which return name of train which stops at maximum
stations.
3. Create a trigger which delete all related records from other tables
when any record is deleted from train table.
RDBMS PLSQL Program 21 Explicit Cursor using For Cursor loop
set serveroutput on
declare
cursor cremp is
select Eno, ESalary from Emp;
begin
for rec1 in cremp
loop
update Emp set ESalary = rec1.ESalary + rec1.ESalary * 0.2
where Eno = rec1.Eno;
end loop;
end;
/
commit;
/
RDBMS PLSQL Program 20 Explicit Cursor with If condition
set serveroutput on
declare
VEno Emp.Eno%TYPE;
VESalary Emp.ESalary%TYPE;
cursor cremp is
select Eno,ESalary from Emp;
begin
open cremp;
loop
fetch cremp into VEno,VESalary;
exit when cremp%notfound;
VESalary := VESalary + VESalary * 0.1;
if VESalary < 100000 then
update Emp set
ESalary = VESalary
where Eno = VEno;
end if;
end loop;
close cremp;
end;
/
commit;
/
RDBMS PLSQL Program 19 Explicit Cursor with While loop
set serveroutput on
declare
VEno Emp.Eno%TYPE;
VESalary Emp.ESalary%TYPE;
cursor cremp is
select Eno, ESalary from Emp;
begin
open cremp;
fetch cremp into VEno,VESalary;
while cremp%found
loop
update Emp set ESalary = VESalary + VESalary * 0.1 where Eno = VEno;
fetch cremp into VEno,VESalary;
end loop;
close cremp;
end;
/
commit;
/
RDBMS PLSQL Program 18 Explicit Cursor with Basic loop
set serveroutput on
declare
VEno Emp.Eno%TYPE;
VESalary Emp.ESalary%TYPE;
cursor cremp is
select Eno, ESalary from Emp;
begin
open cremp;
loop
fetch cremp into VEno,VESalary;
exit when cremp%NOTFOUND;
update Emp set ESalary = VESalary - VESalary * 0.05 where Eno = VEno;
end loop;
close cremp;
end;
/
commit;
/
RDBMS PLSQL Program 17 Example of Implicit Cursor
set serveroutput on
begin
update Emp set ESalary = 75000
where Eno = 'E00001';
if SQL%isopen = true then
if sql%found = true then
dbms_output.put_line(sql%rowcount);
else
dbms_output.put_line('No Rows1');
end if;
else
dbms_output.put_line('No Rows2');
end if;
end;
/
commit;
/
Saturday, September 19, 2020
RDBMS PLSQL Program 16 get data from user, select and update table
set serveroutput on
declare
VEno Emp.Eno%TYPE;
VESalary Emp.ESalary%TYPE;
begin
VEno := '&VEno';
select ESalary into VESalary from Emp
where Eno = VEno;
if VESalary > 50000 then
VESalary := VESalary - 3000;
update emp set ESalary = VESalary
where Eno = VEno;
end if;
end;
commit;
/
RDBMS PLSQL Program 15 get data from user, select and update table
set serveroutput on
declare
VEno Emp.Eno%TYPE;
VESalary Emp.ESalary%TYPE;
begin
VEno := '&VEno';
select ESalary into VESalary from Emp
where Eno = VEno;
if VESalary > 10000 then
VESalary := VESalary+2000;
update emp set ESalary = VESalary
where Eno = VEno;
end if;
end;
commit;
/
RDBMS PLSQL Program 14 get data from user, select, update and delete from table
set serveroutput on
declare
VEno Emp.Eno%TYPE;
VESalary Emp.ESalary%TYPE;
begin
VEno := '&VEno';
select ESalary into VESalary from Emp
where Eno = VEno;
VESalary := VESalary + 5000;
update Emp set ESalary = VESalary
where Eno = VEno;
if VESalary < 80000 then
delete from emp
where Eno = VEno;
end if;
end;
commit;
/
RDBMS PLSQL Program 13 get data from user and delete from table
set serveroutput on
declare
VEno char(6);
begin
VEno := '&VEno';
delete from EMP
where Eno = VEno;
end;
commit;
/
RDBMS PLSQL Program 12 get data from user and update table
set serveroutput on
declare
VEno char(6);
VNewSalary number(10,2);
begin
VEno := '&VEno';
VNewSalary := &VNewSalary;
update Emp Set ESalary = VNewSalary
where Eno = VEno;
end;
commit;
/
RDBMS PLSQL Program 11 get data from user and insert into table
set serveroutput on
declare
VEno char(6);
VEname varchar(30);
VESalary number(10,2);
VEDate date;
VEDno char(3);
begin
VEno := '&VEno';
VEname := '&VEname';
VESalary := &VSalary;
VEDate := '&VEDate';
VEDno := '&vEDno';
insert into Emp values(VEno,VEname,VESalary,VEDate,VEDno);
end;
commit;
/
RDBMS SQL Query to Create table and Insert Into the table
Create Table EMP
(
Eno char(6) Primary Key,
Ename varchar(30),
Esalary number(10,2),
EDoj Date,
EDno char(3)
)
/
insert into Emp values
(
'E00001','ABC',10000,'02-Aug-2017','D01'
);
insert into Emp values
(
'E00002','XYZ',50000,'02-Jan-2016','D02'
);
insert into Emp values
(
'E00003','MNO',40000,'01-Sep-2017','D03'
);
insert into Emp values
(
'E00004','PQR',50000,'24-Feb-2020','D03'
);
insert into Emp values
(
'E00005','ABC',10000,'30-Aug-2020','D01'
);
/
Friday, September 18, 2020
RDBMS PLSQL Program 10 Case Statement
SET SERVEROUTPUT ON
DECLARE
v_grade CHAR(1) := UPPER('&p_grade');
v_appraisal VARCHAR2(20);
BEGIN
v_appraisal := CASE v_grade WHEN 'A' THEN 'Excellent' WHEN 'B' THEN 'Very Good' WHEN 'C' THEN 'Good' ELSE 'No such grade'
END;
DBMS_OUTPUT.PUT_LINE ('Grade: '|| v_grade || ' Appraisal ' || v_appraisal);
END;
/
RDBMS PLSQL Program 8 For loop (Forward Direction)
set serveroutput on
declare
i number(10,2);
begin
for i in 1..10
loop
dbms_output.put_line(i);
end loop;
end;
/
RDBMS PLSQL Program 9 For loop (Reverse Direction)
set serveroutput on
declare
i number(10,2);
begin
for i in reverse 1..10
loop
dbms_output.put_line(i);
end loop;
end;
/
RDBMS PLSQL Program 7 While loop (Decrement operation)
set serveroutput on
declare
i number(10,2);
begin
i := &i;
while i>0
loop
dbms_output.put_line(i);
i := i-1;
end loop;
end;
/
RDBMS PLSQL Program 6 While loop (Increment operation)
set serveroutput on
declare
i number(10,2);
begin
i := &i;
while i<11
loop
dbms_output.put_line(i);
i := i+1;
end loop;
end;
/
RDBMS PLSQL Program 5 Simple loop (Decrement operation)
set serveroutput on
declare
i number(10,2);
begin
for i in reverse 1..10
loop
dbms_output.put_line(i);
end loop;
end;
/
RDBMS PLSQL Program 4 Simple loop (Increment operation)
set serveroutput on
declare
i number(10,2);
begin
i := &i;
loop
dbms_output.put_line(i);
i := i+1;
exit when i = 11;
end loop;
end;
/
RDBMS PLSQL Program 3 If statement
set serveroutput on
declare
i number(10,2);
begin
i := &i;
if i=1 then
dbms_output.put_line('black tea');
elsif i=2 then
dbms_output.put_line('green tea');
else
dbms_output.put_line('pink tea');
end if;
end;
/
RDBMS PLSQL Program 1 Hello World
SET serveroutput ON
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World');
END;
/
RDBMS PLSQL Program 2 Basic Input Output
SET SERVEROUTPUT ON
DECLARE
i number(10,2);
str1 varchar(20);
BEGIN
i := &i;
if i = 2 then
DBMS_OUTPUT.PUT_LINE('Input rejected');
else
str1 := '&str1';
DBMS_OUTPUT.PUT_LINE('Value of str =' || str1 );
end if;
END;
/
Tuesday, September 15, 2020
Saturday, September 05, 2020
RDBMS PL/SQL Basic Programs
RDBMS Programs
1) Program
to print Hello World.
SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World');
END;
/
Output
Hello World
PL/SQL procedure successfully completed.
2) Program
to demonstrate the use of variable.
SET serveroutput ON
DECLARE
name
varchar(40);
BEGIN
name :=
'Chirag Mewada';
DBMS_OUTPUT.PUT_LINE('Hello
' || name);
END;
/
Output
Hello Chirag Mewada
PL/SQL procedure successfully completed.
3) Program
to demonstrate the use of %TYPE.
SET
serveroutput ON
DECLARE
name Worker.FIRST_NAME%TYPE;
BEGIN
SELECT FIRST_NAME INTO name
FROM
Worker
WHERE WORKER_ID = 002;
DBMS_OUTPUT.PUT_LINE('Hello ' || name);
END;
/
Output
Hello Chirag Mewada
PL/SQL procedure successfully completed.
Friday, August 28, 2020
RDBMS Using Notepad++ to Execute Oracle SQL
Using Notepad++ to Execute Oracle SQL
Here are the steps:
- Launch Notepad++
- Main menu -> Plugins -> Plugin Manager -> Show Plugin Manager
- Available Tab, Find and check NppExec plugin
- Press Install button to download & install plugin – restarts Notepad++
- Open a SQL script
- Press F6 key (NppExec’s default keyboard mapping for “Execute Statement”)
- Enter the following macro script into the Execute pop-up
set ORA_USER=bert
set ORA_PASS=bert1234
set ORA_SID= ORCL
npp_save
cmd /c copy /y "$(CURRENT_DIRECTORY)\$(FILE_NAME)" "$(SYS.TEMP)\$(FILE_NAME)" >nul 2>&1
cmd /c echo. >> "$(SYS.TEMP)\$(FILE_NAME)"
cmd /c echo exit >> "$(SYS.TEMP)\$(FILE_NAME)"
sqlplus -l $(ORA_USER)/$(ORA_PASS)@$(ORA_SID) @"$(SYS.TEMP)\$(FILE_NAME)"
- Change the first three variables for your database, username and password
- Press the OK button
RDBMS Install Oracle Database Express Edition in Windows 10
Oracle Database Express Edition
After downloading the Oracle Database XE installation executable,
setup.exe, double-click it.In the Oracle Database 11g Express Edition - Install Wizard welcome window, click Next.
In the License Agreement window, select I accept the terms in the license agreement and then click Next.
In the Choose Destination Location window, either accept the default or click Browse to select a different installation directory. (Do not select a directory that has spaces in its name.) Then click Next.
If you are prompted for a port number, then specify one.
The following port numbers are the default values:
1521: Oracle database listener
2030: Oracle Services for Microsoft Transaction Server
8080: HTTP port for the Oracle Database XE graphical user interface
If these port numbers are not currently used, then the installation uses them automatically without prompting you. If they are in use, then you will be prompted to enter an available port number.
In the Specify Database Passwords window, enter and confirm the password to use for the SYS and SYSTEM database accounts. Then click Next.
In the Summary window, review the installation settings, and if you are satisfied, click Install. Otherwise, click Back and modify the settings as necessary.
Enabling the Control Panel Services for .NET Stored Procedures and Oracle Services for Microsoft Transaction Server
Select Start, then Control Panel.
In the Control Panel, double-click Administrative Tools, and then Services.
Right-click each of the following services, and then select Properties from the menu. Then set the start-up type of the service to Automatic.
OracleXEClrAgnt (for .NET stored procedures)
OracleMTSRecoveryService (Oracle Services for Microsoft Transaction Server)
Click OK.
Start each service by right-clicking its name and selecting Start from the menu.
Friday, August 21, 2020
ASP.NET Theory Assignment 1
Click here to download the file
ASP.NET Theory Assignment 1
Write answers of following questions in detail.
All questions are grouped based on the topic.
1. Basics of ASP.NET
================
1) Developer productivity in ASP.NET.
2) Explain major features added in ASP.NET. Also explain how ASP.NET
provides more developer productivity.
3) Write a note on common language runtime.
4) Write note on .NET Framework.
5) Explain page class with its properties, events, and methods.
6) Define Code sharing model in ASP.NET. Explain code directory model in
detail.
7) Explain Page Life Cycle in detail.
8) Demonstrate the use of Application object in ASP.NET.
9) Explain MSIL.
10)What is Response Object? Explain its properties and methods.
11)Discuss significance of Post Back mechanism.
2. Validation Controls
=================
1) What is advantage of validator control? List validator controls and explain
range validator and regular expression validator in detail.
2) Explain Range Validator control.
3) Explain in detail Validation controls.
4) List out various Validation controls and explain any two in detail.
5) Explain Custom Validation control.
3. Navigation Controls
=================
1) Discuss navigation controls.
2) Explain Menu Control.
3) Explain SiteMapPath control.
4. Master Page
============
1) What are advantages of master page over CSS? Explain nested master
page in detail.
2) What is Master Page? Why we need it? Explain briefly about nested
master page.
3) Write note on Master Page.
4) What is Master Page? Explain it in detail along with the Content Page.
5. Theme & CSS
=============
1) Write detailed note on Theme.
2) Explain Theme and CSS.
3) Explain Theme in detail.
6. Login Controls
==============
1) Explain Login Control, LoginName and LoginStatus with its properties.
2) List out all Login Controls. Explain Login Control and Password Recovery
control with its properties.
Thursday, August 13, 2020
Saturday, August 08, 2020
RDBMS Using Notepad++ to Execute Oracle SQL
We can use Notepad++ to execute oracle sql queries and programs without switching from notepad++ to oracle. just follow the steps given in the link
Using Notepad++ to Execute Oracle SQL
Friday, July 31, 2020
Thursday, July 30, 2020
Subscribe to:
Posts (Atom)