My Oracle Support Banner

Plsql Block With Dynamic Sql Failing With PLS-00801: Internal Error [ph2csql_strdef_to_diana:bind] In 12.2 (Doc ID 2456859.1)

Last updated on OCTOBER 15, 2018

Applies to:

PL/SQL - Version 12.1.0.2 and later
Information in this document applies to any platform.

Symptoms

After upgrade from 12.1.0.1 to 12.2.0.1 we have been noticing [ph2csql_strdef_to_diana:Bind] error while running below PLSQL block.

> sqlplus das_desc/das_desc

SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 19 12:22:41 2018

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>
CREATE TABLE "DAS_DESC"."AUTHORITY1"
( "NAID" NUMBER(20,0) NOT NULL ENABLE,
  "AUTH_TYPE" VARCHAR2(40 BYTE),
  "XML_DATA" "SYS"."XMLTYPE" ,
  "TERM_NAME" VARCHAR2(4000 BYTE),
  "GROUP_ID" VARCHAR2(1000 BYTE),
  "GROUP_CD" VARCHAR2(1000 BYTE),
  "CREATED_DATE" DATE,
  "CHANGED_DATE" DATE,
  "FEET_COUNT" NUMBER,
  "APPROVED_DATE" DATE
);

Table created.

SQL>
DECLARE
  v_query VARCHAR2(4000);
  v_where_clause VARCHAR2(1000);
  v_where_clause_del VARCHAR2(1000);
  v_where_clause_edt VARCHAR2(1000);
  v_where_clause_crt VARCHAR2(1000);
  v_where_clause1 VARCHAR2(1000);
  v_date_clause VARCHAR2(200);
  v_date VARCHAR2(20);
  v_num_bio NUMBER;
  v_num_adm NUMBER;
  v_num_edt NUMBER;
  v_num_del NUMBER;
  v_num_crt NUMBER;
  v_num_nac NUMBER;
  v_num_sac NUMBER;
  v_auth_type VARCHAR2(200);
  v_date_clause_sb VARCHAR(200);
  i INTEGER := 0;

  reportData sys_refcursor;

BEGIN

  v_where_clause1 := 'AND changed_date >= TO_DATE(''01-OCT-15'',''DD-MON-YY'')
        AND changed_date
        <= TO_DATE(''03-JAN-16'',''DD-MON-YY'') and

       (to_date(replace(ax.dateTime, ''T'' , '' ''), ''YYYY-MM-DD HH24:MI:SS'') >
       to_date(replace(XMLCast(XMLQuery(''declare default element namespace
       "http://authority.das.nara.gov/";
  for $i
    in/*/recordHistory/broughtUnderEdit/modification[1]/dateTime/text() return $i ''
     PASSING XML_DATA RETURNING CONTENT )
      AS varchar2(20) ) , ''T'' , '' ''), ''YYYY-MM-DD
      HH24:MI:SS'')) AND auth_type = ''OtherPreservationCharacteristics'' and
      approved_date >= TO_DATE(''01-OCT-15'',''DD-MON-YYYY HH24:MI:SS'')';

  v_query := 'BEGIN
                WITH variable_holder AS (SELECT :v_num_edt FROM DUAL)
                SELECT COUNT(1)
                  INTO :v_num_edt
                FROM authority1,
                     XMLTABLE(''declare default element namespace "http://authority.das.nara.gov/";
                       for $i in /*/recordHistory/approved/modification ' ||
                      v_date_clause || '
                return
                   $i/dateTime''
                   PASSING xml_data
                   COLUMNS
                   dateTime VARCHAR2(20) PATH ''.'') ax
                   WHERE 1=1' ||
                   v_where_clause1 || '
                   ;
             

              END;';

  begin
     EXECUTE IMMEDIATE v_query USING IN OUT v_num_edt;
  end;
END;
/
DECLARE
*
ERROR at line 1:
ORA-06550: line 0, column 0:
PLS-00801: internal error [ph2csql_strdef_to_diana:Bind]
ORA-06550: line 2, column 32:
PL/SQL: ORA-06544: PL/SQL: internal error, arguments:
[ph2csql_strdef_to_diana:Bind], [], [], [], [], [], [], []
ORA-06550: line 2, column 1:
PL/SQL: SQL Statement ignored
ORA-06512: at line 58

 

NOTE: Sample code is provided just for demonstration purpose only.

 

Changes

 None.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.