| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: help with this simple proc?
Glen A Stromquist wrote:
> Daniel Morgan wrote:
> > 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)
> >
> >
>
>
Just generate the percentage by creating a fraction solving for that number of records out of total records in the table. A little trunc or round and you are in business.
-- 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)Received on Sun May 04 2003 - 09:18:52 CDT
 |
 |