SQL> set serveroutput on SQL> --Procedures SQL> create or replace procedure printhello 2 is 3 begin 4 dbms_output.put_line('Hello there!'); 5 end; 6 / Procedure created. SQL> begin 2 printhello; 3 end; 4 / Hello there! PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 begin 2 printhello; 3* end; SQL> create or replace procedure print_name(p_name IN varchar2) 2 is 3 begin 4 dbms_output.put_line('Hello' || p_name); 5 end; 6 / Procedure created. SQL> declare 2 x varchar2 :='Biray'; 3 begin 4 print_name(x); 5 end; 6 / x varchar2 :='Biray'; * ERROR at line 2: ORA-06550: line 2, column 3: PLS-00215: String length constraints must be in range (1 .. 32767) SQL> ed Wrote file afiedt.buf 1 declare 2 x varchar(10) :='Biray'; 3 begin 4 print_name(x); 5* end; SQL> / HelloBiray PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 x varchar(10) :='Biray'; 3 begin 4 print_name('Omid'); 5* end; SQL> / HelloOmid PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 x varchar(10) :='&enter_name'; 3 begin 4 print_name('&Your_name'); 5 print_name(x); 6* end; SQL> / Enter value for enter_name: olayinka old 2: x varchar(10) :='&enter_name'; new 2: x varchar(10) :='olayinka'; Enter value for your_name: afolabi old 4: print_name('&Your_name'); new 4: print_name('afolabi'); Helloafolabi Helloolayinka PL/SQL procedure successfully completed. SQL> create and replace procedure greetings(v_gender IN char) 2 is 3 begin 4 if v_gender in ('F', 'f') then 5 dbms_output.put_line('Hello madam'); create and replace procedure greetings(v_gender IN char) * ERROR at line 1: ORA-00905: missing keyword SQL> ed Wrote file afiedt.buf 1 create or replace procedure greetings(v_gender IN char) 2 is 3 begin 4 if v_gender in ('F', 'f') then 5 dbms_output.put_line('Hello Madam'); 6 elsif v_gender in ('M', 'm') then 7 dbms_output.put_line('Hello Sir'); 8 else 9 dbms_output.put_line('Hello Mate'); 10* end; SQL> / Warning: Procedure created with compilation errors. SQL> show err Errors for PROCEDURE GREETINGS: LINE/COL ERROR -------- ----------------------------------------------------------------- 10/4 PLS-00103: Encountered the symbol ";" when expecting one of the following: if SQL> ed Wrote file afiedt.buf 1 create or replace procedure greetings(v_gender IN char) 2 is 3 begin 4 if v_gender in ('F', 'f') then 5 dbms_output.put_line('Hello Madam'); 6 elsif v_gender in ('M', 'm') then 7 dbms_output.put_line('Hello Sir'); 8 else 9 dbms_output.put_line('Hello Mate'); 10 endif; 11* end; SQL> / Warning: Procedure created with compilation errors. SQL> show err Errors for PROCEDURE GREETINGS: LINE/COL ERROR -------- ----------------------------------------------------------------- 11/4 PLS-00103: Encountered the symbol ";" when expecting one of the following: if SQL> ed Wrote file afiedt.buf 1 create or replace procedure greetings(v_gender IN char) 2 is 3 begin 4 if v_gender in ('F', 'f') then 5 dbms_output.put_line('Hello Madam'); 6 elsif v_gender in ('M', 'm') then 7 dbms_output.put_line('Hello Sir'); 8 else 9 dbms_output.put_line('Hello Mate'); 10 endif; 11* end; SQL> / Warning: Procedure created with compilation errors. SQL> ed Wrote file afiedt.buf 1 create or replace procedure greetings(v_gender IN char) 2 is 3 begin 4 if v_gender in ('F', 'f') then 5 dbms_output.put_line('Hello Madam'); 6 elsif v_gender in ('M', 'm') then 7 dbms_output.put_line('Hello Sir'); 8 else 9 dbms_output.put_line('Hello Mate'); 10 end if; 11* end; SQL> / Procedure created. SQL> execute greetings('F'); Hello Madam PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 begin 2 dbms_output.put_line('First Call to Procedure '); 3 greetings('m'); 4* end; 5 / First Call to Procedure Hello Sir PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v_gend1 char(1); 3 v_gend2 char(1):='f'; 4 begin 5 dbms_output.put_line('First Call to Procedure '); 6 greetings('m'); 7 dbms_output.put_line('Second Call to Procedure '); 8 greetings(v_gend2); 9 dbms_output.put_line('Third Call to Procedure '); 10 greetings(v_gend1); 11 dbms_output.put_line('Second Call to Procedure '); 12 greetings('&gender'); 13* end; SQL> / Enter value for gender: m old 12: greetings('&gender'); new 12: greetings('m'); First Call to Procedure Hello Sir Second Call to Procedure Hello Madam Third Call to Procedure Hello Mate Second Call to Procedure Hello Sir PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v_gend1 char(1); 3 v_gend2 char(1):='f'; 4 begin 5 dbms_output.put_line('First Call to Procedure '); 6 greetings('m'); 7 dbms_output.put_line('Second Call to Procedure '); 8 greetings(v_gend2); 9 dbms_output.put_line('Third Call to Procedure '); 10 greetings(v_gend1); 11 dbms_output.put_line('fourth Call to Procedure '); 12 greetings('&gender'); 13 dbms_output.put_line('fifth Call to Procedure '); 14 greetings(); 15* end; SQL> / Enter value for gender: s old 12: greetings('&gender'); new 12: greetings('s'); greetings(); * ERROR at line 14: ORA-06550: line 14, column 1: PLS-00306: wrong number or types of arguments in call to 'GREETINGS' ORA-06550: line 14, column 1: PL/SQL: Statement ignored SQL> ed Wrote file afiedt.buf 1 declare 2 v_gend1 char(1); 3 v_gend2 char(1):='f'; 4 begin 5 dbms_output.put_line('First Call to Procedure '); 6 greetings('m'); 7 dbms_output.put_line('Second Call to Procedure '); 8 greetings(v_gend2); 9 dbms_output.put_line('Third Call to Procedure '); 10 greetings(v_gend1); 11 dbms_output.put_line('fourth Call to Procedure '); 12 greetings('&gender'); 13 dbms_output.put_line('fifth Call to Procedure '); 14 greetings(null); 15* end; SQL> / Enter value for gender: s old 12: greetings('&gender'); new 12: greetings('s'); First Call to Procedure Hello Sir Second Call to Procedure Hello Madam Third Call to Procedure Hello Mate fourth Call to Procedure Hello Mate fifth Call to Procedure Hello Mate PL/SQL procedure successfully completed. SQL> create or replace procedure get_dname( p_did IN departments.deaprtment_id%type, p_dname OUT departments.department_name%type) 2 is 3 begin 4 select department_name into p_dname 5 from employees 6 where department_id=p_did; 7 end; 8 / Warning: Procedure created with compilation errors. SQL> ed Wrote file afiedt.buf 1 create or replace procedure get_dname( p_did IN departments.department_id%type, p_dname OUT departments.department_name%type) 2 is 3 begin 4 select department_name into p_dname 5 from employees 6 where department_id=p_did; 7* end; SQL> / Warning: Procedure created with compilation errors. SQL> show err Errors for PROCEDURE GET_DNAME: LINE/COL ERROR -------- ----------------------------------------------------------------- 4/1 PL/SQL: SQL Statement ignored 4/8 PL/SQL: ORA-00904: "DEPARTMENT_NAME": invalid identifier SQL> ed Wrote file afiedt.buf 1 create or replace procedure get_dname( p_did IN departments.department_id%type, p_dname OUT departments.department_name%type) 2 is 3 begin 4 select department_name into p_dname 5 from departments 6 where department_id=p_did; 7* end; SQL> / Procedure created. SQL> ed Wrote file afiedt.buf 1 create or replace procedure get_dname( p_did IN departments.department_id%type, p_dname OUT departments.department_name%type) 2 is 3 begin 4 select department_name into p_dname 5 from departments 6 where department_id=p_did; 7* end; SQL> declare 2 did departments.department_id%type :=10; 3 dname departments.department_name%type; 4 begin 5 get_dname(did,dname); 6 dbms_output.put_line('for id '|| did || ' the name is '||dname); 7 end; 8 / for id 10 the name is Administration PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 did departments.department_id%type :=10; 3 dname departments.department_name%type; 4 begin 5 get_dname(50,dname); 6 dbms_output.put_line('for id '|| did || ' the name is '||dname); 7* end; SQL> / for id 10 the name is Shipping PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 did departments.department_id%type :=10; 3 dname departments.department_name%type; 4 begin 5 get_dname(50,dname); 6 dbms_output.put_line('for id '|| did || ' the name is '||dname); 7* end; SQL> ed Wrote file afiedt.buf 1 declare 2 did departments.department_id%type :=10; 3 dname departments.department_name%type; 4 begin 5 get_dname(50,dname); 6 dbms_output.put_line('for id '|| '50' || ' the name is '||dname); 7* end; SQL> / for id 50 the name is Shipping PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 did departments.department_id%type :=10; 3 dname departments.department_name%type; 4 begin 5 get_dname('&enterid',dname); 6 dbms_output.put_line('for id '|| '50' || ' the name is '||dname); 7* end; SQL> / Enter value for enterid: 20 old 5: get_dname('&enterid',dname); new 5: get_dname('20',dname); for id 50 the name is Marketing PL/SQL procedure successfully completed. SQL> desc employees; Name Null? Type ----------------------------------------- -------- ---------------------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4) SQL> create or replace procedure get_emps_no(v_did departments.department_id%type, v_count OUT number) 2 is 3 begin 4 select count(*) into v_count 5 from employees 6 where department_id = v_did; 7 end; 8 / Procedure created. SQL> ed Wrote file afiedt.buf 1 create or replace procedure get_emps_no(v_did departments.department_id%type, v_count OUT number) 2 is 3 begin 4 select count(*) into v_count 5 from employees 6 where department_id = v_did; 7 exception 8 when others then 9 v_count := -1; 10* end; SQL> / Procedure created. SQL> declare 2 v_count number; 3 did departments.department_id%type; 4 begin 5 did := &Enter_id; 6 get_emps_no(did,v_count); 7 dbms_output.put_line(did || 'has' || v_count || 'employees'); 8 end; 9 / Enter value for enter_id: 10 old 5: did := &Enter_id; new 5: did := 10; 10has1employees PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v_count number; 3 did departments.department_id%type; 4 begin 5 did := &Enter_id; 6 get_emps_no(did,v_count); 7 dbms_output.put_line(did || ' has ' || v_count || ' employees'); 8* end; SQL> / Enter value for enter_id: 50 old 5: did := &Enter_id; new 5: did := 50; 50 has 45 employees PL/SQL procedure successfully completed. SQL> / Enter value for enter_id: 5 old 5: did := &Enter_id; new 5: did := 5; 5 has 0 employees PL/SQL procedure successfully completed. SQL> / Enter value for enter_id: 32 old 5: did := &Enter_id; new 5: did := 32; 32 has 0 employees PL/SQL procedure successfully completed. SQL> / Enter value for enter_id: 101 old 5: did := &Enter_id; new 5: did := 101; 101 has 0 employees PL/SQL procedure successfully completed. SQL> create or replace procedure increment(v IN number) 2 is 3 begin 4 v:=v+2; 5 end; 6 / create or replace procedure increment(v IN number) * ERROR at line 1: ORA-04050: invalid or missing procedure, function, or package name SQL> ed Wrote file afiedt.buf 1 create or replace procedure incr(v IN number) 2 is 3 begin 4 v:=v+2; 5* end; SQL> / Warning: Procedure created with compilation errors. SQL> show err Errors for PROCEDURE INCR: LINE/COL ERROR -------- ----------------------------------------------------------------- 4/1 PL/SQL: Statement ignored 4/1 PLS-00363: expression 'V' cannot be used as an assignment target SQL> ed Wrote file afiedt.buf 1 create or replace procedure incr(v IN OUT number) 2 is 3 begin 4 v:=v+2; 5* end; SQL> / Procedure created. SQL> ed Wrote file afiedt.buf 1 create or replace procedure incr(v IN OUT number) 2 is 3 begin 4 v:=v+2; 5 dbms_output.put_line(v); 6* end; SQL> / Procedure created. SQL> execute incr(10); BEGIN incr(10); END; * ERROR at line 1: ORA-06550: line 1, column 12: PLS-00363: expression '10' cannot be used as an assignment target ORA-06550: line 1, column 7: PL/SQL: Statement ignored SQL> execute printhello; Hello there! PL/SQL procedure successfully completed. SQL> declare 2 v number :=10; 3 begin 4 dbms_output.put_line('out value before call is ' || v); 5 incr(v); 6 dbms_output.put_line('our value after call is ' || v); 7 end; 8 / out value before call is 10 12 our value after call is 12 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v number :=10; 3 begin 4 dbms_output.put_line('out value before call is ' || v); 5 incr(v); 6 dbms_output.put_line('our value after call is ' || v); 7* end; SQL> / out value before call is 10 12 our value after call is 12 PL/SQL procedure successfully completed. SQL> spool off