FROM emp SAMPLE ( MYNUMBER ) 1

[hudo]

Hello,
why does the following code work NOT with the line:
SAMPLE (mynumber )

CREATE OR REPLACE
PROCEDURE mysample
IS

mynumber NUMBER := 20;


BEGIN

DBMS_OUTPUT.PUT_LINE('mynumber:  '||mynumber);

FOR r_samp IN (
	SELECT SAL 
	FROM EMP  
	--SAMPLE ( 20 )
	SAMPLE ( mynumber )
)
LOOP

DBMS_OUTPUT.PUT_LINE('sal:  '||r_samp.SAL);


END LOOP;

DBMS_OUTPUT.PUT_LINE('mynumber:  '||mynumber);

END  mysample;

 

[HimanB]

Hi,
This code will not work if you use a Bind variable instead of a harcoded value.
If you wish to use a Varibale then declare the cursor , open it & then use the values fetched by the cursor.

HTH
Regards
Himanshu

 

[sem]

Do you get syntax error during creation or runtime error? What is Oracle version? How many records does EMP contain?

Regards, Dima

http://seminihin.narod.ru

 

[hudo]

@sem: I get a sysntax error during creation !! It is Oracle version 9.0.2 on Windows XP. EMP has about 14 records.

@Himanshu: Thanks for your hint. I tried it with a cursor. Here is the code, that works fine:

PROCEDURE mysample (mynumber IN NUMBER)
IS
  v_cur	 SYS_REFCURSOR;
  r_rn    NUMBER;  
  r_ename emp.ename%TYPE;
  r_sal   emp.sal%TYPE;
  v_sql	 VARCHAR2(2000);
BEGIN
  v_sql := 'SELECT ROWNUM , ENAME , SAL FROM emp SAMPLE (' || mynumber || ')';
  OPEN v_cur FOR v_sql;
  LOOP
	 FETCH v_cur INTO r_rn, r_ename,r_sal;
	 EXIT WHEN v_cur%NOTFOUND;
	 DBMS_OUTPUT.PUT_LINE ('ename: ' || r_rn ||' , '|| r_ename ||' , '|| r_sal);
  END LOOP;
END mysample;