Re: help with this simple proc?

Glen A Stromquist wrote:

> 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
CREATE OR REPLACE PROCEDURE populate_emp (num_recs PLS_INTEGER) IS

BEGIN
   DELETE FROM random_emp;

   INSERT INTO random_emp
   SELECT last_name,first_name
   FROM company_emp
   WHERE empno IS NOT NULL -- undoubedly the primary key so how could it be null?

   AND rownum < num_recs + 1;

   COMMIT;
END;
/

You missed the "IS"
The declare can not be used
And the &num_emp does not work in a procedure: It is SQL*Plus. And I have no idea what you think the dbms_random is doing so I omitted it..

Execute as:

SQL> exec populate_emp(100)

Oracle tables are heap tables. There is no order or ordering. To try to randomize the input is just a waste of perfectly good CPU.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)