Re: help with this simple proc?

From: Glen A Stromquist <glen_stromquist_at_nospam.yahoo.com>
Date: Sat, 03 May 2003 19:23:39 GMT
Message-ID: <%GUsa.25079$yv1.1936545@news2.telusplanet.net>

Sybrand Bakker wrote:

> On Sat, 03 May 2003 01:45:14 GMT, Glen A Stromquist
> <glen_stromquist_at_nospam.yahoo.com> wrote:
>

>>Sorry - I didn't include the entire text in the post - I was creating the
>>procedure in DBA studio and couldnt get it to compile, there is a "create
>>or replace procedure" at the start.
>>
>>When I copied the body of it and saved it as a .sql file and ran it from
>>sqlplus I got a "plsql procedure succesfully completed"
>>
>>GS

>
> So basically you have wasted the groups time by committing 2 sins
> - not posting the complete source
> - not posting the error message ('doesn't run' doesn't tell anything
> does it?)
>
> Even in your followup you don't post the error message.
> Questions:
> -How do you expect any useful answers?
> -Do you want to be ignored in the future?
>
>
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address

OK - here is the DDL pasted verbatim from DBA studio, after clicking compile it still shows INVALID, and SHOW ERRORS shows nothing.

CREATE OR REPLACE PROCEDURE "GLEN"."POPULATE_EMP"     declare
num_emp number;
begin
delete from random_emp;
commit;
insert into random_emp
select * from
(select last_name,first_name from company_emp where empno is not null
order by DBMS_RANDOM.VALUE) where rownum < &num_emp; commit;
end;
/

So I copy the sql:

declare
num_emp number;
begin
delete from random_emp;
commit;
insert into random_emp
select * from
(select last_name,first_name from company_emp where empno is not null
order by DBMS_RANDOM.VALUE) where rownum < &num_emp; commit;
end;

to a sql file and run it from SQLPLUS and return a "PL/SQL procedure succesfully completed" after entering a number for the variable.

The sql runs fine, but I must have omitted something else that prevents me from creating a procedure out of it.

If I run the actual procedure DDL from SQL I get a "procedure created with compilation errors", and when I type show errors I get a "no errors" message.

When I run "exec populate_emp" from SQL I get the following: ERROR at line 1:

ORA-06550: line 1, column 7:
PLS-00201: identifier 'POPULATE_EMP' must be declared
ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

I am obviously missing something very simple here, but I cant find it in any doc's I have at hand, and thought someone on the group could quickly point out my mistake and save me a lot of slogging through more docs.

TIA Received on Sat May 03 2003 - 14:23:39 CDT