Re: help with this simple proc?

Glen A Stromquist wrote:

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

Congratulations on reinventing the wheel. :-)

The SELECT statement in Oracle contains an optional SAMPLE clause. http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_103a.htm#2065954

and has for many versions.

The following query estimates the number of orders in the oe.orders table:

SELECT COUNT(*) * 100 FROM orders SAMPLE (1);

(1) means 1%.

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