Re: help with this simple proc?

From: Glen A Stromquist <glen_stromquist_at_nospam.yahoo.com>
Date: Sat, 03 May 2003 21:04:25 GMT
Message-ID: <t9Wsa.25516$yv1.1969895@news2.telusplanet.net>

Daniel Morgan wrote:

> 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)

Thanks Daniel

Actually empno in this case can be null, in the case of a contract employee there is no empno.

The DBMS_RAND I use for getting x number of random "picks" from the employee table. I'm actully trying to create a little applet for our HR people, who from time to time need to pick X random employees from all employees in some cases, and just certain employees in other cases. For example in this case they want to pick X employees from all that enter for spots in the annual company golf tournament, of which over 200 employees want to participate. In other cases they may want to pick just 2 out of the 400 employees for the 2 company season tickets for the next Oilers game etc.

Using:  

select * from (select first_name,last_name from emp_table where <<whatever>> order by dbms_random.value) where rownum <= &num_to_pick

will always return me a random list of employees. Rather than have them run the SQL from a prompt or batch file I am trying to set up a simple MSaccess form to populate a table with whatever number of random employees they want. I thought the easiest way to do this is to have a procedure in oracle do the work rather than try to run the sql itself from a pass through query in MSaccess, once the random_emp table is populated they can view it through access and create a simple report or whatever they wish.

I thought I could use the & as a variable for testing, I will likely have to figure out how to pass a user entered number from the access form.     Received on Sat May 03 2003 - 16:04:25 CDT