help with this very simple proc?

[Glenstr]

I am trying to write a simple proc to call from an access form to populate a table with random entries. It is as follows:
=========================
declare

num1 number;

begin
insert into table2
select * from (select col1,col2 from
table1 where col3 is not null
order by dbms_random.value) where rownum <= %num1;

end;

===========================

It runs fine from sqlplus with an "SQL:>@procedure", but wont run if I enter "SQL:>exec procedure" nor will it compile.
As you can see I havent spent a lot of time in PL/SQL - can anyone help here?

TIA

[pando]

that is not a procedure ok!

that is called anonymous pl/sql block

go tahiti.oracle.com and read some manuals oke!

[Glenstr]

Sorry - I should have incuded the full text, it is indeed a procedure, or at least my attempt at one. I was creating it in DBA studio and I just pasted the body of it in to a .sql file and ran it from there to test it, where it ran fine.

The DDL for it does have the "create or replace procedure <procedure>", I just forgot to include it in the original post.

GS

[spbraja]

DBMS_RANDOM.VALUE??????????

It's very new to me.

What version of Oracle u have?


Raja.M

spbraja@rediffmail.com

[Glenstr]

8.1.7.4

DBMS_RANDOM has been available since 8.x I believe - I use it in this particular case to populate a table with x number of random picked records from a larger table. It works quite well.