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 Go to next message
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);
[Error] Execution (2: 29): ORA-24374: define not done before fetch or execute and fetch

Getting ORA-24374 , when using function in with query [message #685576 is a reply to message #685575] Tue, 15 February 2022 07:03 Go to previous messageGo to next message
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);
[Error] Execution (2: 29): ORA-24374: define not done before fetch or execute and fetch

Re: Getting ORA-24374 , when using function in with query [message #685577 is a reply to message #685575] Tue, 15 February 2022 08:34 Go to previous messageGo to next message
piripicchio
Messages: 20
Registered: April 2018
Location: Rome
Junior Member
Hi, you're using the all_* view in the main query but the user_* view in the function, so probably nothing is returned into l_str variable.
I tried using all_tab_partitions inside the function and works perfectly on an 12.1.
Re: Getting ORA-24374 , when using function in with query [message #685579 is a reply to message #685577] Tue, 15 February 2022 10:08 Go to previous messageGo to next message
gopalMisra
Messages: 7
Registered: July 2021
Junior Member
thank you for your reply piripicchio. I changed code. Now all are fetching from
all_tab_partitions
but still it is throwing same error.
Re: Getting ORA-24374 , when using function in with query [message #685580 is a reply to message #685579] Tue, 15 February 2022 10:16 Go to previous messageGo to next message
gopalMisra
Messages: 7
Registered: July 2021
Junior Member
One more thing i realized- if i comment last line of where condition where i used that function but keep it in selection, it is returning result.
Means it allowing me to put fuction in selection but not in filter condition.

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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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)
/
SY.

[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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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
/
SY.
Re: Getting ORA-24374 , when using function in with query [message #685583 is a reply to message #685582] Tue, 15 February 2022 12:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
And since hint MATERIALIZE is undocumented you could do something like:

You can force to materialize using a dummy clause like "WHERE ROWNUM > 0" or an ORDER BY clause.

[Updated on: Tue, 15 February 2022 12:10]

Report message to a moderator

Re: Getting ORA-24374 , when using function in with query [message #685584 is a reply to message #685580] Tue, 15 February 2022 13:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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>
SY.
Re: Getting ORA-24374 , when using function in with query [message #685588 is a reply to message #685587] Tue, 15 February 2022 15:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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

/forum/fa/2115/0/ 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 Go to previous messageGo to next message
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'))
Re: Getting ORA-24374 , when using function in with query [message #685630 is a reply to message #685595] Mon, 21 February 2022 07:16 Go to previous message
gopalMisra
Messages: 7
Registered: July 2021
Junior Member
Thank you MichelCadot, Piripicchio and SolomonYakobson for your best advices.

I really appreciate 'kind of efforts you are putting in'. Now i understand what was hampering this statement.
Previous Topic: SQL -Report missing string when running a query
Next Topic: Help Needed in Date Variable
Goto Forum:
  


Current Time: Thu Mar 28 12:49:05 CDT 2024