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

If i can use sys.check_constraints to display my constraints from my database using sql server 2005, how can i display then if i am using sql server 2000????

4003

---

What are the different types of database management systems?

995

---

What is sqlcommand?

1019

---

How do you take the union of two tables in sql?

978

---

How many sql are there?

986

---

what is bdb (berkeleydb)? : Sql dba

1024

---

in oracle 10g sw after compiling procedure how to pass parameter values ,if we (v_empid out number)how to give empid after successful compilation program.This site exact suitable for 10g with respect to question & answer same format , im trying sql browser & sql command prompt using exec procedure name & respective parameters.

2069

---

How to fetch values from testtable1 that are not in testtable2 without using not keyword?

1209

---

What is pl sql block structure?

1031

---

Define sql delete statement.

1001

---

What is the difference between stored procedure and view?

1015

---

What is delimiter sql?

978

---

What is difference between group by and partition by?

1050

---

What is the difference between the repeatable read and serializable isolation levels? : Transact sql

1089

---

which command using query analyzer will give you the version of sql server and operating system? : Sql dba

1049

---