|
|
Re: Restrict the user to access the records [message #548825 is a reply to message #548823] |
Mon, 26 March 2012 08:53 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
owais_baba
Messages: 289 Registered: March 2008 Location: MUSCAT
|
Senior Member |
|
|
ok let me give u some exaples note down
ONETIME_WHERE exists in 10g but not in 6i.
So then you have to use the other approach:
In WHEN-NEW-FORM-INSTANCE-trigger do the same as before but set an additional flag fpr AUTOQUERY into a global:
DEFAULT_VALUE(NULL, 'GLOBAL.TXTDEPTNO');
IF :GLOBAL.TXTDEPTNO IS NOT NULL THEN
-- restrict the query of the EMP-block
:GLOBAL.AUTOQUERY:='TRUE';
GO_BLOCK('EMPLOYEES');
-- automatic query
EXECUTE_QUERY;
:GLOBAL.AUTOQUERY:='FALSE';
END IF;
Add a PRE-QUERY-trigger to your block EMPLOYEES with the following code:
IF :GLOBAL.AUTOQUERY='TRUE' THEN
:EMPLOYEES.TXTDEPTNO:=:GLOBAL.TXTDEPTNO;
END IF;
-----------And also try this on sql prompt u will get some idea----
DECLARE
v_empno employees.employee_id%TYPE;
v_name employees.last_name%TYPE;
CURSOR emp_cursor IS
SELECT employee_id,
last_name
FROM employees
WHERE department_id =
&p_deptno;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_empno, v_name;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE
('The number of employee is '
||
TO_CHAR (v_empno)
||
' and his last name is '
|| TO_CHAR(v_name));
END LOOP;
CLOSE emp_cursor;
END;
/
hope u got something from this
[Updated on: Mon, 26 March 2012 08:59] Report message to a moderator
|
|
|
|
|
|
|
|
Re: Restrict the user to access the records [message #548835 is a reply to message #548834] |
Mon, 26 March 2012 10:11 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
owais_baba
Messages: 289 Registered: March 2008 Location: MUSCAT
|
Senior Member |
|
|
use PRE-QUERY
IF :CONTROL.A IS NOT NULL THEN
SET_BLOCK_PROPERTY('EMP',DEFAULT_WHERE,'EMP.JOB LIKE :CONTROL.A');
END IF;
IF :CONTROL.B IS NOT NULL THEN
SET_BLOCK_PROPERTY('EMP',DEFAULT_WHERE,'EMP.SAL LIKE :CONTROL.B');
ELSE
SET_BLOCK_PROPERTY('EMP',DEFAULT_WHERE,'EMP.JOB LIKE :CONTROL.A OR EMP.SAL LIKE :CONTROL.B');
END IF;
AND MAKE ONE CONTROL BLOCK ----->ONE BUTTON ---->AND WRITE THIS
GO_BLOCK('EMP');
EXECUTE_QUERY;
Hope u got it now
[Updated on: Mon, 26 March 2012 10:14] Report message to a moderator
|
|
|
|
|
|