Help me with error Compilation unit analysis terminated (2 threads merged by bb) [message #661248] |
Sun, 12 March 2017 20:56 |
|
hosun0919
Messages: 1 Registered: March 2017
|
Junior Member |
|
|
I was trying to execute this statement
CREATE OR REPLACE PROCEDURE get_country_info
(p_id IN w_countries.country_id%TYPE,
p_name OUT w_countries.country_name%TYPE,
p_capital OUT w_countries.capital%TYPE) IS
BEGIN
SELECT country_name, capital INTO p_name, p_capital
FROM w_countries
WHERE country_id = p_id;
END get_country_info;
Error at line 0: PL/SQL: Compilation unit analysis terminated
this error came up, and I never saw these kind of error before
can you guys help me out please?
|
|
|
Re: Help me with error Compilation unit analysis terminated (2 threads merged by bb) [message #661250 is a reply to message #661248] |
Sun, 12 March 2017 22:20 |
|
Barbara Boehmer
Messages: 9090 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Please post the result of:
DESCRIBE w_countries
I am guessing that one or more of your column names does not exist in that table or the table does not exist. For example, if I try to run your code to create the procedure, I get:
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PROCEDURE get_country_info
2 (p_id IN w_countries.country_id%TYPE,
3 p_name OUT w_countries.country_name%TYPE,
4 p_capital OUT w_countries.capital%TYPE)
5 IS
6 BEGIN
7 SELECT country_name, capital
8 INTO p_name, p_capital
9 FROM w_countries
10 WHERE country_id = p_id;
11 END get_country_info;
12 /
Warning: Procedure created with compilation errors.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
Errors for PROCEDURE GET_COUNTRY_INFO:
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
2/18 PLS-00201: identifier 'W_COUNTRIES.COUNTRY_ID' must be declared
because I don't have a w_countries table.
If I create a w_countries table:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE w_countries
2 (country_id NUMBER,
3 country_name VARCHAR2(15),
4 capital VARCHAR2(15))
5 /
Table created.
Then I can create and compile the procedure without error.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PROCEDURE get_country_info
2 (p_id IN w_countries.country_id%TYPE,
3 p_name OUT w_countries.country_name%TYPE,
4 p_capital OUT w_countries.capital%TYPE)
5 IS
6 BEGIN
7 SELECT country_name, capital
8 INTO p_name, p_capital
9 FROM w_countries
10 WHERE country_id = p_id;
11 END get_country_info;
12 /
Procedure created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
[Updated on: Sun, 12 March 2017 22:23] Report message to a moderator
|
|
|
Re: Help me with error Compilation unit analysis terminated (2 threads merged by bb) [message #661251 is a reply to message #661248] |
Sun, 12 March 2017 22:22 |
John Watson
Messages: 8931 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
It works for me:orclz>
orclz> create table w_countries as select * from hr.countries;
Table created.
orclz> alter table w_countries add(capital varchar2(30));
Table altered.
orclz> CREATE OR REPLACE PROCEDURE get_country_info
2 (p_id IN w_countries.country_id%TYPE,
3 p_name OUT w_countries.country_name%TYPE,
4 p_capital OUT w_countries.capital%TYPE) IS
5 BEGIN
6 SELECT country_name, capital INTO p_name, p_capital
7 FROM w_countries
8 WHERE country_id = p_id;
9 END get_country_info;
10 /
Procedure created.
orclz>
|
|
|
|