Home » SQL & PL/SQL » SQL & PL/SQL » Getting ORA-24374 , when using function in with query (merged) (PLSQL 12.1.0.2.0)
| Getting ORA-24374 , when using function in with query (merged) [message #685575] |
Tue, 15 February 2022 07:01  |
 |
gopalMisra
Messages: 7
Registered: July 2021
|
Junior Member |
|
|
Hi team,
I am getting error (ORA-24374: define not done before fetch or execute and fetch) while executing below query. I am using 2 with function in this query. Get_cnt is to fetch partition's total number of rows and another is to get partition date value.
query looks fine to me but still it is still giving error. DB 12c (12.1)
Can you please help.?
WITH
FUNCTION GET_CNT(TNAME IN VARCHAR2,PNAME IN VARCHAR2)
RETURN VARCHAR2 IS VRETVAL NUMBER;
BEGIN
EXECUTE IMMEDIATE 'select count(*) from '||TNAME||
' PARTITION ('||PNAME||')' into vretval;
return vretval;
EXCEPTION
WHEN OTHERS THEN
RETURN SUBSTR(pNAME,1,50);
END GET_CNT;
Function fn_get_part_val (i_tabl_nm IN VARCHAR2,
i_part_nm IN VARCHAR2)
RETURN date IS
l_str varchar2(4000);
l_main_Dt date;
BEGIN
SELECT HIGH_VALUE INTO l_str FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME = i_tabl_nm
AND partition_name = i_part_nm;
EXECUTE IMMEDIATE 'SELECT '||l_str||' FROM DUAL' INTO l_main_dt;
return l_main_dt;
end fn_get_part_val;
SELECT p.PARTITION_NAME,
fn_get_part_val(table_name, partition_name) part_val,
get_cnt(p.table_name, p.partition_name) rec_cnt
from all_tab_partitions p
where p.table_owner=user
and p.table_name = '<partitioned table name>'
and fn_get_part_val(table_name, partition_name) >= trunc(sysdate-7);
|
|
|
|
| Getting ORA-24374 , when using function in with query [message #685576 is a reply to message #685575] |
Tue, 15 February 2022 07:03  |
|
gopalMisra
Messages: 7
Registered: July 2021
|
Junior Member |
|
|
Hi team,
I am getting error (ORA-24374: define not done before fetch or execute and fetch) while executing below query. I am using 2 with function in this query. Get_cnt is to fetch partition's total number of rows and another is to get partition date value.
query looks fine to me but still it is still giving error. DB 12c (12.1)
Can you please help.?
WITH
FUNCTION GET_CNT(TNAME IN VARCHAR2,PNAME IN VARCHAR2)
RETURN VARCHAR2 IS VRETVAL NUMBER;
BEGIN
EXECUTE IMMEDIATE 'select count(*) from '||TNAME||
' PARTITION ('||PNAME||')' into vretval;
return vretval;
EXCEPTION
WHEN OTHERS THEN
RETURN SUBSTR(pNAME,1,50);
END GET_CNT;
Function fn_get_part_val (i_tabl_nm IN VARCHAR2,
i_part_nm IN VARCHAR2)
RETURN date IS
l_str varchar2(4000);
l_main_Dt date;
BEGIN
SELECT HIGH_VALUE INTO l_str FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME = i_tabl_nm
AND partition_name = i_part_nm;
EXECUTE IMMEDIATE 'SELECT '||l_str||' FROM DUAL' INTO l_main_dt;
return l_main_dt;
end fn_get_part_val;
SELECT p.PARTITION_NAME,
fn_get_part_val(table_name, partition_name) part_val,
get_cnt(p.table_name, p.partition_name) rec_cnt
from all_tab_partitions p
where p.table_owner=user
and p.table_name = '<partitioned table name>'
and fn_get_part_val(table_name, partition_name) >= trunc(sysdate-7);
|
|
|
|
|
|
|
|
|
|
| Re: Getting ORA-24374 , when using function in with query [message #685581 is a reply to message #685580] |
Tue, 15 February 2022 11:12 |
Solomon Yakobson
Messages: 3214
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Issue is UDF in WHERE clause with SELECT from same table as in main SELECT. You will have to materialize all results and only then apply that WHERE clause condition:
WITH
FUNCTION GET_CNT(
P_TNAME IN VARCHAR2,
P_PNAME IN VARCHAR2
)
RETURN VARCHAR2
IS
V_RETVAL NUMBER;
BEGIN
EXECUTE IMMEDIATE 'SELECT VOUNT(*) FROM "'|| P_TNAME || '" PARTITION ("' || P_PNAME ||'")'
INTO V_RETVAL;
RETURN V_RETVAL;
EXCEPTION
WHEN OTHERS
THEN
RETURN SUBSTR(P_PNAME,1,50);
END GET_CNT;
FUNCTION FN_GET_PART_VAL(
P_TNAME IN VARCHAR2,
P_PNAME IN VARCHAR2
)
RETURN date
IS
V_HIGH_VALUE VARCHAR2(4000);
V_RETVAL DATE;
BEGIN
SELECT HIGH_VALUE
INTO V_HIGH_VALUE
FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME = P_TNAME
AND PARTITION_NAME = P_PNAME;
EXECUTE IMMEDIATE 'BEGIN :1 := ' || V_HIGH_VALUE || '; END;'
USING OUT V_RETVAL;
RETURN V_RETVAL;
END FN_GET_PART_VAL;
T AS (
SELECT /*+ MATERIALIZE */
PARTITION_NAME,
FN_GET_PART_VAL(TABLE_NAME,PARTITION_NAME) PART_VAL,
GET_CNT(TABLE_NAME,PARTITION_NAME) REC_CNT
FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME = '<your table>'
)
SELECT PARTITION_NAME,
PART_VAL,
REC_CNT
FROM T
WHERE PART_VAL >= TRUNC(SYSDATE - 7)
/
[Updated on: Tue, 15 February 2022 11:15] Report message to a moderator |
|
|
|
| Re: Getting ORA-24374 , when using function in with query [message #685582 is a reply to message #685581] |
Tue, 15 February 2022 11:23 |
Solomon Yakobson
Messages: 3214
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
And since hint MATERIALIZE is undocumented you could do something like:
WITH
FUNCTION GET_CNT(
P_TNAME IN VARCHAR2,
P_PNAME IN VARCHAR2
)
RETURN VARCHAR2
IS
V_RETVAL NUMBER;
BEGIN
EXECUTE IMMEDIATE 'SELECT VOUNT(*) FROM "'|| P_TNAME || '" PARTITION ("' || P_PNAME ||'")'
INTO V_RETVAL;
RETURN V_RETVAL;
EXCEPTION
WHEN OTHERS
THEN
RETURN SUBSTR(P_PNAME,1,50);
END GET_CNT;
FUNCTION FN_GET_PART_VAL(
P_TNAME IN VARCHAR2,
P_PNAME IN VARCHAR2
)
RETURN date
IS
V_HIGH_VALUE VARCHAR2(4000);
V_RETVAL DATE;
BEGIN
SELECT HIGH_VALUE
INTO V_HIGH_VALUE
FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME = P_TNAME
AND PARTITION_NAME = P_PNAME;
EXECUTE IMMEDIATE 'BEGIN :1 := ' || V_HIGH_VALUE || '; END;'
USING OUT V_RETVAL;
RETURN V_RETVAL;
END FN_GET_PART_VAL;
T AS (
SELECT PARTITION_NAME,
FN_GET_PART_VAL(TABLE_NAME,PARTITION_NAME) PART_VAL,
GET_CNT(TABLE_NAME,PARTITION_NAME) REC_CNT,
CASE
WHEN FN_GET_PART_VAL(TABLE_NAME,PARTITION_NAME) >= TRUNC(SYSDATE - 7) THEN ROWNUM
END FLAG
FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME = '<your table>'
)
SELECT PARTITION_NAME,
PART_VAL,
REC_CNT
FROM T
WHERE FLAG IS NOT NULL
/
|
|
|
|
|
|
| Re: Getting ORA-24374 , when using function in with query [message #685584 is a reply to message #685580] |
Tue, 15 February 2022 13:22 |
 |
Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
You can shorten a bit the query like this (which can be improved):
MIKCDB1> col partition_name format a20
MIKCDB1> with
2 function gethigh (p_part in varchar2) return date is
3 v varchar2(32760);
4 r date;
5 begin
6 select high_value into v from user_tab_partitions
7 where table_name = 'INTERVAL_DATE' and partition_name = p_part;
8 execute immediate 'select '||v||' from dual' into r;
9 return r;
10 exception when no_data_found then return null;
11 end gethigh;
12 data as (
13 select partition_name,
14 gethigh(partition_name) part_val,
15 to_number(extractvalue(
16 dbms_xmlgen.getXMLtype(
17 'select count(*) cnt from INTERVAL_DATE partition ('||partition_name||')'),
18 '/ROWSET/ROW/CNT')) rec_cnt
19 from user_tab_partitions
20 where table_name = 'INTERVAL_DATE'
21 )
22 select partition_name, part_val, rec_cnt
23 from data
24 where part_val >= add_months(sysdate,-130)
25 order by partition_name
26 /
PARTITION_NAME PART_VAL REC_CNT
-------------------- ------------------- ----------
P0 01/11/2011 00:00:00 0
SYS_P25 16/12/2011 00:00:00 0
SYS_P30 30/01/2012 00:00:00 0[Updated on: Tue, 15 February 2022 14:52] Report message to a moderator |
|
|
|
| Re: Getting ORA-24374 , when using function in with query [message #685586 is a reply to message #685584] |
Tue, 15 February 2022 14:56 |
|
Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
If you are allowed to create procedural objects, you can do it with a pipelined function:
MIKCDB1> create or replace type part_data is object (
2 partition_name varchar2(30),
3 part_val date,
4 rec_cnt integer
5 )
6 /
Type created.
MIKCDB1> create or replace type part_tab is table of part_data
2 /
Type created.
MIKCDB1> create or replace function part_info
2 return part_tab pipelined
3 authid current_user
4 is
5 part_val date;
6 rec_cnt pls_integer;
7 begin
8 for rec in (
9 select partition_name, high_value
10 from all_tab_partitions where table_name = 'INTERVAL_DATE'
11 order by partition_name
12 ) loop
13 execute immediate 'select '||rec.high_value||' from dual' into part_val;
14 if part_val >= add_months(sysdate,-130) then
15 execute immediate
16 'select count(*) cnt from INTERVAL_DATE partition ('||rec.partition_name||')'
17 into rec_cnt;
18 pipe row (part_data(rec.partition_name, part_val, rec_cnt));
19 end if;
20 end loop;
21 end;
22 /
Function created.
MIKCDB1> select * from table(part_info());
PARTITION_NAME PART_VAL REC_CNT
-------------------- ------------------- ----------
P0 01/11/2011 00:00:00 0
SYS_P25 16/12/2011 00:00:00 0
SYS_P30 30/01/2012 00:00:00 0 |
|
|
|
| Re: Getting ORA-24374 , when using function in with query [message #685587 is a reply to message #685584] |
Tue, 15 February 2022 14:57 |
Solomon Yakobson
Messages: 3214
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Well, in general there is no need for UDF at all:
with t1 as (
select dbms_xmlgen.getxmltype(
q'[
select table_name,
partition_name,
high_value
from user_tab_partitions
where table_name = 'T_INTERVAL'
]'
) as xmldoc
from dual
),
t2 as (
select x.table_name,
x.partition_name,
to_date(
regexp_substr(x.high_value,q'['(.+?)']',1,1,null,1),
regexp_substr(x.high_value,q'['(.+?)']',1,2,null,1),
regexp_substr(x.high_value,q'['(.+?)']',1,3,null,1)
) high_value
from t1,
xmltable(
'/ROWSET/ROW'
passing t1.xmldoc
columns
table_name varchar2(128) path 'TABLE_NAME',
partition_name varchar2(128) path 'PARTITION_NAME',
high_value varchar2(128) path 'HIGH_VALUE'
) x
)
select table_name,
partition_name,
high_value,
xmlcast(
xmlquery(
'/ROWSET/ROW/CNT'
passing dbms_xmlgen.getxmltype(
'select count(*) cnt from ' ||
table_name || ' partition(' ||
partition_name || ')'
)
returning content
)
as number
) row_count
from t2
where high_value >= date '2015-01-01'
/
TABLE_NAME PARTITION_NAME HIGH_VALUE ROW_COUNT
--------------- --------------- -------------------- ----------
T_INTERVAL P4 01/01/2016 00:00:00 2
T_INTERVAL SYS_P3532 01/01/2017 00:00:00 1
T_INTERVAL SYS_P3533 01/01/2018 00:00:00 1
T_INTERVAL SYS_P3534 01/01/2019 00:00:00 2
T_INTERVAL SYS_P3535 01/01/2020 00:00:00 2
T_INTERVAL SYS_P3536 01/01/2021 00:00:00 2
T_INTERVAL SYS_P3537 01/01/2022 00:00:00 1
7 rows selected.
SQL>
|
|
|
|
| Re: Getting ORA-24374 , when using function in with query [message #685588 is a reply to message #685587] |
Tue, 15 February 2022 15:12 |
|
Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote: to_date(
regexp_substr(x.high_value,q'['(.+?)']',1,1,null,1),
regexp_substr(x.high_value,q'['(.+?)']',1,2,null,1),
regexp_substr(x.high_value,q'['(.+?)']',1,3,null,1)
) high_value
 Yes! This is the trick.
|
|
|
|
| Re: Getting ORA-24374 , when using function in with query [message #685595 is a reply to message #685588] |
Wed, 16 February 2022 02:45 |
 |
piripicchio
Messages: 20
Registered: April 2018
Location: Rome
|
Junior Member |
|
|
Great answers!
Just a note as a explanation, I think the topic opener might find it useful: if you look at the query plan below, you'll see why you get the error. The predicate using your function is pushed down into the view's definition (step 11, 30 and 46) and so applied long before it can get valid (or better, expected) inputs.
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 396 | 22 (0)| 00:00:01 |
| 1 | VIEW | ALL_TAB_PARTITIONS | 3 | 396 | 22 (0)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | FILTER | | | | | |
| 4 | NESTED LOOPS | | 1 | 182 | 7 (0)| 00:00:01 |
| 5 | NESTED LOOPS OUTER | | 1 | 165 | 6 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 152 | 5 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 149 | 4 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 121 | 3 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID| USER$ | 1 | 15 | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | I_OBJ2 | 1 | 106 | 2 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | TABPART$ | 1 | 28 | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | I_TABPART_OBJ$ | 1 | | 0 (0)| 00:00:01 |
| 14 | TABLE ACCESS CLUSTER | TS$ | 1 | 3 | 1 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
| 16 | TABLE ACCESS CLUSTER | SEG$ | 1 | 13 | 1 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 0 (0)| 00:00:01 |
|* 18 | TABLE ACCESS CLUSTER | TAB$ | 1 | 17 | 1 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| 00:00:01 |
| 20 | NESTED LOOPS | | 1 | 15 | 2 (0)| 00:00:01 |
|* 21 | FIXED TABLE FULL | X$KZSRO | 2 | 12 | 0 (0)| 00:00:01 |
|* 22 | INDEX RANGE SCAN | I_OBJAUTH2 | 1 | 9 | 1 (0)| 00:00:01 |
|* 23 | FIXED TABLE FULL | X$KZSPR | 2 | 18 | 0 (0)| 00:00:01 |
|* 24 | FILTER | | | | | |
| 25 | NESTED LOOPS | | 1 | 149 | 5 (0)| 00:00:01 |
| 26 | NESTED LOOPS | | 1 | 132 | 4 (0)| 00:00:01 |
| 27 | NESTED LOOPS | | 1 | 121 | 3 (0)| 00:00:01 |
| 28 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 15 | 1 (0)| 00:00:01 |
|* 29 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| 00:00:01 |
|* 30 | INDEX RANGE SCAN | I_OBJ2 | 1 | 106 | 2 (0)| 00:00:01 |
| 31 | TABLE ACCESS BY INDEX ROWID | TABPART$ | 1 | 11 | 1 (0)| 00:00:01 |
|* 32 | INDEX UNIQUE SCAN | I_TABPART_OBJ$ | 1 | | 0 (0)| 00:00:01 |
|* 33 | TABLE ACCESS CLUSTER | TAB$ | 1 | 17 | 1 (0)| 00:00:01 |
|* 34 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| 00:00:01 |
| 35 | NESTED LOOPS | | 1 | 15 | 2 (0)| 00:00:01 |
|* 36 | FIXED TABLE FULL | X$KZSRO | 2 | 12 | 0 (0)| 00:00:01 |
|* 37 | INDEX RANGE SCAN | I_OBJAUTH2 | 1 | 9 | 1 (0)| 00:00:01 |
|* 38 | FIXED TABLE FULL | X$KZSPR | 2 | 18 | 0 (0)| 00:00:01 |
|* 39 | FILTER | | | | | |
| 40 | NESTED LOOPS | | 1 | 160 | 6 (0)| 00:00:01 |
| 41 | NESTED LOOPS | | 1 | 143 | 5 (0)| 00:00:01 |
| 42 | NESTED LOOPS | | 1 | 140 | 4 (0)| 00:00:01 |
| 43 | NESTED LOOPS | | 1 | 121 | 3 (0)| 00:00:01 |
| 44 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 15 | 1 (0)| 00:00:01 |
|* 45 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| 00:00:01 |
|* 46 | INDEX RANGE SCAN | I_OBJ2 | 1 | 106 | 2 (0)| 00:00:01 |
|* 47 | TABLE ACCESS BY INDEX ROWID | TABCOMPART$ | 1 | 19 | 1 (0)| 00:00:01 |
|* 48 | INDEX UNIQUE SCAN | I_TABCOMPART$ | 1 | | 0 (0)| 00:00:01 |
| 49 | TABLE ACCESS CLUSTER | TS$ | 1 | 3 | 1 (0)| 00:00:01 |
|* 50 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
|* 51 | TABLE ACCESS CLUSTER | TAB$ | 1 | 17 | 1 (0)| 00:00:01 |
|* 52 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| 00:00:01 |
| 53 | NESTED LOOPS | | 1 | 15 | 2 (0)| 00:00:01 |
|* 54 | FIXED TABLE FULL | X$KZSRO | 2 | 12 | 0 (0)| 00:00:01 |
|* 55 | INDEX RANGE SCAN | I_OBJAUTH2 | 1 | 9 | 1 (0)| 00:00:01 |
|* 56 | FIXED TABLE FULL | X$KZSPR | 2 | 18 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("O"."OWNER#"=USERENV('SCHEMAID') OR EXISTS (SELECT 0 FROM "SYS"."OBJAUTH$"
"OA",SYS."X$KZSRO" "X$KZSRO" WHERE ("CON_ID"=0 OR "CON_ID"=6) AND "OA"."OBJ#"=:B1 AND
"GRANTEE#"="KZSROROL") OR EXISTS (SELECT 0 FROM SYS."X$KZSPR" "X$KZSPR" WHERE
((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47) OR (-"KZSPRPRV")=(-397) OR (-"KZSPRPRV")=(-48) OR
(-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50)) AND ("CON_ID"=0 OR "CON_ID"=6) AND
"INST_ID"=USERENV('INSTANCE')))
10 - access("U"."NAME"='<schema_name>')
11 - access("U"."USER#"="O"."OWNER#" AND "O"."NAME"='<table_name>' AND
"O"."NAMESPACE"=1 AND "O"."REMOTEOWNER" IS NULL AND "O"."LINKNAME" IS NULL)
filter("FN_GET_PART_VAL"("O"."NAME","O"."SUBNAME")>=TRUNC(SYSDATE@!-7) AND "O"."LINKNAME"
IS NULL)
12 - filter(BITAND("TP"."FLAGS",8388608)=0)
13 - access("O"."OBJ#"="TP"."OBJ#")
15 - access("TS"."TS#"="TP"."TS#")
17 - access("TP"."TS#"="S"."TS#"(+) AND "TP"."FILE#"="S"."FILE#"(+) AND
"TP"."BLOCK#"="S"."BLOCK#"(+))
18 - filter(BITAND("T"."PROPERTY",64)<>64 AND BITAND("T"."TRIGFLAG",1073741824)<>1073741824)
19 - access("TP"."BO#"="T"."OBJ#")
21 - filter("CON_ID"=0 OR "CON_ID"=6)
22 - access("GRANTEE#"="KZSROROL" AND "OA"."OBJ#"=:B1)
23 - filter(((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47) OR (-"KZSPRPRV")=(-397) OR
(-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50)) AND ("CON_ID"=0 OR "CON_ID"=6)
AND "INST_ID"=USERENV('INSTANCE'))
24 - filter("O"."OWNER#"=USERENV('SCHEMAID') OR EXISTS (SELECT 0 FROM "SYS"."OBJAUTH$"
"OA",SYS."X$KZSRO" "X$KZSRO" WHERE ("CON_ID"=0 OR "CON_ID"=6) AND "OA"."OBJ#"=:B1 AND
"GRANTEE#"="KZSROROL") OR EXISTS (SELECT 0 FROM SYS."X$KZSPR" "X$KZSPR" WHERE
((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47) OR (-"KZSPRPRV")=(-397) OR (-"KZSPRPRV")=(-48) OR
(-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50)) AND ("CON_ID"=0 OR "CON_ID"=6) AND
"INST_ID"=USERENV('INSTANCE')))
29 - access("U"."NAME"='<schema_name>')
30 - access("O"."OWNER#"="U"."USER#" AND "O"."NAME"='<table_name>' AND
"O"."NAMESPACE"=1 AND "O"."REMOTEOWNER" IS NULL AND "O"."LINKNAME" IS NULL)
filter("FN_GET_PART_VAL"("O"."NAME","O"."SUBNAME")>=TRUNC(SYSDATE@!-7) AND "O"."LINKNAME"
IS NULL)
32 - access("O"."OBJ#"="TP"."OBJ#")
33 - filter(BITAND("T"."PROPERTY",64)=64 AND BITAND("T"."TRIGFLAG",1073741824)<>1073741824)
34 - access("TP"."BO#"="T"."OBJ#")
36 - filter("CON_ID"=0 OR "CON_ID"=6)
37 - access("GRANTEE#"="KZSROROL" AND "OA"."OBJ#"=:B1)
38 - filter(((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47) OR (-"KZSPRPRV")=(-397) OR
(-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50)) AND ("CON_ID"=0 OR "CON_ID"=6)
AND "INST_ID"=USERENV('INSTANCE'))
39 - filter("O"."OWNER#"=USERENV('SCHEMAID') OR EXISTS (SELECT 0 FROM "SYS"."OBJAUTH$"
"OA",SYS."X$KZSRO" "X$KZSRO" WHERE ("CON_ID"=0 OR "CON_ID"=6) AND "OA"."OBJ#"=:B1 AND
"GRANTEE#"="KZSROROL") OR EXISTS (SELECT 0 FROM SYS."X$KZSPR" "X$KZSPR" WHERE
((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47) OR (-"KZSPRPRV")=(-397) OR (-"KZSPRPRV")=(-48) OR
(-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50)) AND ("CON_ID"=0 OR "CON_ID"=6) AND
"INST_ID"=USERENV('INSTANCE')))
45 - access("U"."NAME"='<schema_name>')
46 - access("U"."USER#"="O"."OWNER#" AND "O"."NAME"='<table_name>' AND
"O"."NAMESPACE"=1 AND "O"."REMOTEOWNER" IS NULL AND "O"."LINKNAME" IS NULL)
filter("FN_GET_PART_VAL"("O"."NAME","O"."SUBNAME")>=TRUNC(SYSDATE@!-7) AND "O"."LINKNAME"
IS NULL)
47 - filter(BITAND("TCP"."FLAGS",8388608)=0)
48 - access("O"."OBJ#"="TCP"."OBJ#")
50 - access("TCP"."DEFTS#"="TS"."TS#")
51 - filter(BITAND("T"."PROPERTY",64)<>64 AND BITAND("T"."TRIGFLAG",1073741824)<>1073741824)
52 - access("TCP"."BO#"="T"."OBJ#")
54 - filter("CON_ID"=0 OR "CON_ID"=6)
55 - access("GRANTEE#"="KZSROROL" AND "OA"."OBJ#"=:B1)
56 - filter(((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47) OR (-"KZSPRPRV")=(-397) OR
(-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50)) AND ("CON_ID"=0 OR "CON_ID"=6)
AND "INST_ID"=USERENV('INSTANCE')) |
|
|
|
|
|
Goto Forum:
Current Time: Thu Mar 30 14:53:37 CDT 2023
|
] 
Search
Help
Members
Register
Login
Home
