Home » SQL & PL/SQL » SQL & PL/SQL » please find me a soln for this
please find me a soln for this [message #328817] Sun, 22 June 2008 23:33 Go to next message
life.myway
Messages: 2
Registered: June 2008
Junior Member
SQL> SET SERVEROUTPUT ON
SQL> VARIABLE g_name VARCHAR2
SQL> VARIABLE g_mngr NUMBER
SQL> VARIABLE g_depid VARCHAR2
SQL> VARIABLE g_proid number
SQL> VARIABLE g_proname varchar2
SQL> CREATE OR REPLACE PROCEDURE mngr_info
2 ( p_id IN employees.emp_id%type,
3 p_name OUT employees.last_name%type,
4 p_mngr OUT employees.mngr_id%type,
5 p_depid OUT employees.dep_id%type,
6 p_proid OUT projects.pro_id%type,
7 p_proname OUT projects.pro_name%type)
8 IS
9 BEGIN
10 SELECT e.mngr_id,e.last_name,e.dep_id,p.pro_id,p.pro_name
11 INTO p_mngr,p_name,p_depid,p_proid,p_proname
12 FROM employees e, projects p
13 WHERE e.mngr_id=p.mngr_id
14 AND e.emp_id=p_id;
15 EXCEPTION
16 WHEN NO_DATA_FOUND THEN
17 DBMS_OUTPUT.PUT_LINE('emp id does not exist');
18 commit;
19 END mngr_info;
20
21 /

Procedure created.

SQL> EXECUTE mngr_info(3,:g_mngr,:g_name,:g_depid,:g_proid,:g_proname);
BEGIN mngr_info(3,:g_mngr,:g_name,:g_depid,:g_proid,:g_proname); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SCOTT.MNGR_INFO", line 10
ORA-06512: at line 1
Re: please find me a soln for this [message #328818 is a reply to message #328817] Sun, 22 June 2008 23:34 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guidelines as stated in URL above
Re: please find me a soln for this [message #328822 is a reply to message #328817] Sun, 22 June 2008 23:51 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The error is obvious, verify your call and check each parameter.

Next time, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Previous Topic: doubts on update (merged)
Next Topic: index on coulmns
Goto Forum:
  


Current Time: Wed Dec 07 20:14:24 CST 2016

Total time taken to generate the page: 0.09133 seconds