Answer Posted / elumalai d

CREATE TABLE question38 (empid NUMBER(10),ENAME VARCHAR2(200));

INSERT INTO question38 VALUES(100,'AAAAA');
INSERT INTO question38 VALUES(200,'BBBBB');
INSERT INTO question38 VALUES(300,'CCCCC');
INSERT INTO question38 VALUES(400,'DDDDD');
INSERT INTO question38 VALUES(500,'EEEEE');
INSERT INTO question38 VALUES(600,'AAAAABBBBBCCCCC');
COMMIT;

CREATE OR REPLACE PROCEDURE p1 (
p_empid IN NUMBER,
p_ename OUT VARCHAR2
)
AS
v_ename VARCHAR2(10);
BEGIN
SELECT ename INTO v_ename FROM question38 WHERE empid = p_empid;
p_ename := v_ename;
END;
/

CREATE OR REPLACE PROCEDURE p2 (p_empid IN NUMBER)
AS
v_ename VARCHAR2(100);
BEGIN
p1(p_empid,v_ename);
dbms_output.put_line(v_ename);
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line('using dbms_utility.format_error_backtrace.....');
dbms_output.put_line(dbms_utility.format_error_backtrace||'-'||sqlerrm);
END;
/

EXECUTE p2 (600);

DROP PROCEDURE P1;
DROP PROCEDURE P2;
DROP TABLE question38;

EXECUTE p2 (600);

DROP PROCEDURE P1;
DROP PROCEDURE P2;
DROP TABLE question38;

Final Result:-
==========
PROCEDURE P1 compiled
PROCEDURE P2 compiled
anonymous block completed
using dbms_utility.format_error_backtrace.....
ORA-06512: at "PRDMMIS.P1", line 8
ORA-06512: at "PRDMMIS.P2", line 5
-ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Is oracel sql developer written in java?

1058

---

How do I make sql search faster?

936

---

What are the advantages of pl sql?

1070

---

Can we insert data into view?

848

---

What is difference between nchar and nvarchar?

956

---

what is rollback? : Sql dba

1022

---

How do I find sql profiler?

884

---

How many tables can you join in sql?

898

---

How can check sql version from command line?

1009

---

Can we change the table name in sql?

888

---

what are the differences between procedure-oriented languages and object-oriented languages? : Sql dba

970

---

What is sql basics?

991

---

how tsql statements can be written and submitted to the database engine? : Transact sql

921

---

What are sql functions? Describe the different types of sql functions?

1057

---

Can we write dml inside a function in sql server?

898

---