?
Solved

random sample - ORA-00933 error

Posted on 2005-04-12
13
Medium Priority
?
10,359 Views
Last Modified: 2011-10-03
OK, I'm running on Oracle 8.1.7. I have 2 tables...both with EMPLID: active_emp and sample_emp

I am doing this:

insert into emp_sample
select * from active_emp
sample(10);

And I get back "ORA-00933: SQL command not properly ended"

Any ideas?
0
Comment
Question by:jrb1
  • 3
  • 2
  • 2
  • +5
13 Comments
 
LVL 3

Expert Comment

by:ToddBarry
ID: 13767982
Perhaps that insert into/select with sample clause combination is not supported in 8i.  It works fine in 9.2.0.6 at least.
0
 
LVL 25

Author Comment

by:jrb1
ID: 13768023
The Oracle8i SQL Reference  Manual Release 2 (8.1.6) gives these examples. Mine looks very similar.

SELECT COUNT(*) * 100 FROM emp SAMPLE BLOCK (1);
CREATE TABLE sample_emp AS SELECT empno, deptno FROM emp SAMPLE(10);
0
 
LVL 11

Expert Comment

by:sujit_kumar
ID: 13769376
I have tried this in my Oracle 9.0.1.1 DB. It's working fine.

insert into emp
select * from scott.emp
sample(10);

  See if Oracle 8i supports sample function or not.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 25

Author Comment

by:jrb1
ID: 13769553
even in the 8.1.5 documentation, it is supported:

http://www.csee.umbc.edu/help/oracle8/server.815/a67779/ch4l.htm
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 13769581
This is from MetaLink Note:95455.1:
...
This is an Oracle8i feature.
...
1. You can specify SAMPLE only in a query that selects from a single table. Oracle does not support sample table scans when the query involves a join or a remote table.
...

I think that your example is different from manual's example - 1 step in yours and 2 steps in the manual.
0
 
LVL 7

Expert Comment

by:Mehul Shah
ID: 13769976
I tried it in ORacle 9.2.0.1

  1  insert into tab_
  2  select * from tab
  3* sample(10)
SQL> /

150 rows created.

SQL> select count(*) from tab;

  COUNT(*)
----------
       150

But the record count remained the same so what is the use of the sample clause?
0
 
LVL 8

Expert Comment

by:Pierrick LOUBIER
ID: 13769990
Confirmation under 8.1.7 : CREATE AS SELECT SAMPLE works, INSERT SELECT SAMPLE doesn't.

SQL> insert into samp_emp select * from emp sample(20);
insert into samp_emp select * from emp sample(20)
                                             *
ERROR at line 1:
ORA-00933: SQL command not properly ended

SQL> create table samp_emp as select * from emp sample(20);

Table created.

SQL>
0
 
LVL 4

Expert Comment

by:plamen73
ID: 13770029
Consider this example. Have in mind I am in scheme plamen and have a DB Link to the same DB, schemme scott:

----------------------------------------------------------------------------------------------
SQL> create table emp_sample as select * from scott.emp sample(10);

Table created.
SQL> insert into emp_sample select * from scott.emp sample(40);

7 rows created.

SQL> insert into emp_sample select * from emp@scott sample(40);
insert into emp_sample select * from emp@scott sample(40)
                                                     *
ERROR at line 1:
ORA-00933: SQL command not properly ended
-------------------------------------------------------------------------------------------

So, be sure ACTIVE_EMP is not actually a view over a DB link
0
 
LVL 4

Expert Comment

by:plamen73
ID: 13770039
I am on 9.2.
According to ploubier it is seems like a bug in 8.1.7 in which way it should be somewhere in the metalink or it is a good chance to post it there.
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 13770050
I have found this:

In 8.1.5+ the SAMPLE-Clause will not be any longer supported for views.
         If you try to create a view with SAMPLE or SAMPLE BLOCK option you get ORA-00933 Error. The following is an example.

         SQL> CREATE VIEW sample_view AS SELECT * FROM emp SAMPLE (30);

    CREATE VIEW sample_view AS SELECT * FROM emp SAMPLE (30)
    *    ERROR at line 1:    ORA-00933: SQL command not properly ended
0
 
LVL 8

Accepted Solution

by:
Pierrick LOUBIER earned 1000 total points
ID: 13770146
From Metalink :

Q:
INSERT SELECT * FROM EMP SAMPLE ( ... )

Creating table using SELECT ... FROM table SAMPLE (...) works fine.
But inserting does not !!

Seems to be a bug as documentation does not show such restriction for SAMPLE.

A:
Yes this is expected. The SQL Reference and Performance Tuning guides indicate this will work with CREATE TABLE ... AS SELECT ..., but never mention anything about INSERT ... SELECT.
0
 
LVL 23

Assisted Solution

by:paquicuba
paquicuba earned 1000 total points
ID: 13771223
Follow this example:

SQL> select count(item) from i;

COUNT(ITEM)
-----------
    5505277


  1  declare
  2  cursor sample_cur is select item from i sample(0.001);
  3  begin
  4  for sample_rec in sample_cur loop
  5  insert into sampletable values( sample_rec.item);
  6  end loop;
  7* end;
SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.07
SQL> select * from sampletable;

COL1
--------------------------------------------------
109897152
109897410
150829081
196783580
197104051
223377464
237197376
242198655
295383476
331395621
332058160
351890350
355715065
360161270
364415166
375512340
379267770
379460546
396708605
423835230
439112494
439861262
453692024
488147225
507923172
508265796
526280613
528872525
541197624
544863336
583388621
584803715
585356855
591871464
591900831
635460980
697488853
722101671
734992462
754597642
760112824
765968346
784455416
821860572
822281714
822419511
826265716
841680512
958857712
960151382
996674744

51 rows selected.

Elapsed: 00:00:00.00
0
 
LVL 25

Author Comment

by:jrb1
ID: 13773150
Thanks.  I knew I had to be missing something.  That worked great...created a sample of 149 out of 1352 recs.
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month14 days, 17 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question