Home » SQL & PL/SQL » SQL & PL/SQL » Help me with error Compilation unit analysis terminated (2 threads merged by bb) (window7)
Help me with error Compilation unit analysis terminated (2 threads merged by bb) [message #661248] Sun, 12 March 2017 20:56 Go to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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>
Re: Help me with error Compilation unit analysis terminated (2 threads merged by bb) [message #661252 is a reply to message #661248] Mon, 13 March 2017 01:09 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

Use SQL*Plus and copy and paste your session, the WHOLE session.
With any SQL or PL/SQL question, please, Post a working Test case: create statements for all objects so that we will be able work to reproduce what you have.

Previous Topic: Query Help
Next Topic: Uploading csv files with different names on periodical basis.
Goto Forum:
  


Current Time: Thu Mar 28 15:24:19 CDT 2024