CODEWITHSUNDEEP
oracle-databaseplsql
luis.espinal
luis.espinal

Reputation: 10529

Oracle PL/SQL - parameterizing SAMPLE clause in SELECT statement

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.

Upvotes: 1

Views: 457

Answers (1)

Matthew McPeak
Matthew McPeak

Reputation: 17924

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;

Upvotes: 1

Related Questions