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)
>
>
well, now I know 2 ways of selecting random records/fields from a table,
but before now I had not heard of the SAMPLE clause. I did a search on
"select random records" and DBMS_RANDOM came up - so thats that I worked
with.
I did log in and try the sample method on my table, it works well for
returning a sample % of rows, but to get a set number, say 50 for
example, the dbms_random method seems to work better.
GS
Received on Sat May 03 2003 - 23:40:38 CDT
