Oracle PL/SQL: syntax error when using a variable within SAMPLE clause

Question

The following PL/SQL block works:

DECLARE
    r TABLE1%ROWTYPE;
BEGIN
    SELECT * INTO r FROM TABLE1 SAMPLE(1) WHERE ROWNUM = 1;
END;

However, when I try to replace the literal with a variable within the SAMPLE clause, Oracle returns a syntax error:

DECLARE
    s NUMBER;
    r TABLE1%ROWTYPE;
BEGIN
    s := 1;
    SELECT * INTO r FROM TABLE1 SAMPLE(s) WHERE ROWNUM = 1;
END;

ORA-06550: line 6, column 39:
PL/SQL: ORA-00933: SQL command not properly ended

What am I doing wrong?

I'm using Oracle 10 and SQL Developer.

(These are simplified examples. What I'm actually trying to do in practice is to optimize the selection of random row, where SAMPLE percentage would be calculated dynamically, based on the current number of rows in the table. So I can't use literal, I need a variable to assign the result of the calculation.)

Vincent Malgrat · Accepted Answer · 2011-10-03 12:33:22Z

5

The SAMPLE synthax requires a numeral. You could use dynamic SQL to build a dynamic query, for example with a ref cursor:

SQL> CREATE TABLE table1 AS
  2  SELECT ROWNUM ID, rpad(ROWNUM, 10, 'x') DATA 
  3    FROM dual CONNECT BY LEVEL <= 1000;

Table created

SQL> DECLARE
  2     l_cur SYS_REFCURSOR;
  3     l_row table1%ROWTYPE;
  4     l_pct NUMBER := 50;
  5  BEGIN
  6     OPEN l_cur
  7        FOR 'SELECT * FROM table1 SAMPLE('||l_pct||') WHERE rownum = 1';
  8     LOOP
  9        FETCH l_cur INTO l_row;
 10        EXIT WHEN l_cur%NOTFOUND;
 11        dbms_output.put_line(l_row.id);
 12     END LOOP;
 13  END;
 14  /

3

PL/SQL procedure successfully completed

answered Oct 3, 2011 at 12:33

Vincent Malgrat

67.9k9 gold badges122 silver badges176 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle PL/SQL: syntax error when using a variable within SAMPLE clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related