Oracle PL/SQL - parameterizing SAMPLE clause in SELECT statement

Question

I have a Oracle related question. I would like to select a random sample out of a view or table in such a way that the SAMPLE clause is parameterized.

Given the following table.

 CREATE TABLE FOO AS
        (SELECT     LEVEL AS ID
                   FROM DUAL
                    CONNECT BY LEVEL < 101
        );

The following construct works, using a literal parameter in the SAMPLE clause.

SELECT ID FROM FOO SAMPLE (15); -- this will get a 15% sample

However,

DECLARE
    N NUMBER := 50;
BEGIN
    FOR r IN
    ( SELECT ID FROM FOO SAMPLE (N) -- <<< this won't work
    )
    LOOP
        DBMS_OUTPUT.PUT_LINE( r.ID );
    END LOOP;
END;

This block blows up when we put a parameter in the SAMPLE clause. It compiles and works if we put it a literal.

But if it is a variable, I get the following:

ORA-06550: line 5, column 33:
PL/SQL: ORA-00933: SQL command not properly ended

Any ideas? I'm racking by brains where the syntax gets broken.

Matthew McPeak · Accepted Answer · 2018-04-12 19:18:42Z

1

The syntax does not allow a variable there.

One workaround would be to construct the SELECT statement dynamically. For example:

declare
  l_rc sys_refcursor;
  n number := 5;
begin
  -- replace "mtl_system_items" with your table...
  open l_rc FOR 'select count(*) from mtl_system_items sample (' || n || ')';
  -- replace call to RETURN_RESULT with whatever processing you want
  DBMS_SQL.RETURN_RESULT(l_rc);
end;

answered Apr 12, 2018 at 19:18

Matthew McPeak

18.1k3 gold badges32 silver badges64 bronze badges

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

1 Comment

luis.espinal Over a year ago

I was trying to avoid that, but I guess I have no choice. Thanks for answering!

Collectives™ on Stack Overflow

Oracle PL/SQL - parameterizing SAMPLE clause in SELECT statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related