<commit_before>declare
   v_input  anydata;
   procedure get_type_info( p_mapper any_type_mapper, p_level integer := 1) is
      begin
         dbms_output.put_line( lpad(' ',2*p_level)||p_mapper.attribute_name||' '||p_mapper.get_typename()||' '||p_mapper.attributes_count );
         if p_mapper.type_code = dbms_types.typecode_object then
            for i in 1 .. p_mapper.attributes_count loop
               get_type_info( p_mapper.get_attribute_type( i ), p_level + 1 );
            end loop;
         elsif p_mapper.type_code in ( dbms_types.typecode_table, dbms_types.typecode_varray, dbms_types.typecode_namedcollection ) then
            get_type_info( p_mapper.get_attribute_type( 1 ), p_level + 1 );
         end if;
      end;

   function get_sql_for_object( p_child_sql varchar2, p_type_name varchar2, p_level integer, p_return_assignment varchar2 ) return varchar2 is
      begin
         return 'declare
         v_out_'||p_level||' any_data := any_data_object( '''||p_type_name||''' );
      begin
         '||p_child_sql||replace( p_return_assignment, '{value}', 'v_out_' || p_level )||'
      end;
         ';
      end;
   function get_sql_for_scalar( p_return_assignment varchar2, p_any_data_type varchar2 ) return varchar2 is
      begin
         return replace( p_return_assignment, '{value}', 'any_data_'||p_any_data_type||'( v_data.a_value.val.a_number )' )||'
         ';
      end;

   function get_sql( p_mapper any_type_mapper, p_level integer := 1 ) return varchar2 is
      v_sql varchar2(32767);
      v_return_assignment varchar2(1000);
      begin
         v_return_assignment :=
         case when p_level = 1
            then ':p_out := {value};'
         when p_mapper.attribute_name is not null
            then 'v_out_'||p_level||'.add_element( any_data_attribute( '''||p_mapper.attribute_name||''', {value} ) );'
         else
            'v_out_'||p_level||'.add_element( {value} );'
         end;
         if p_mapper.type_code = dbms_types.typecode_object then
            for i in 1 .. p_mapper.attributes_count loop
               v_sql := v_sql || get_sql( p_mapper.get_attribute_type( i ), p_level + 1 );
            end loop;
            v_sql := get_sql_for_object( v_sql, p_mapper.get_typename(), p_level, v_return_assignment );
         elsif p_mapper.type_code in ( dbms_types.typecode_table, dbms_types.typecode_varray, dbms_types.typecode_namedcollection ) then
            v_sql := get_sql( p_mapper.get_attribute_type( 1 ), p_level );
         else
            v_sql := get_sql_for_scalar( v_return_assignment, p_mapper.get_typename() );
         end if;
         return v_sql;
      end;
begin
   --   v_input  := anydata.convertObject( test_object_2( test_object( test_number_object(1,2) ) ) );
   --   v_input  := anydata.convertCollection( a_tab( test_object_2( test_object( test_number_object(1,2) ) ) ) );
   v_input  := anydata.convertCollection( a_tab( test_obj_2_bis( test_object( test_number_object(1,2) ) ,123 ) ) );
--   get_type_info( any_type_mapper(v_input) );
   dbms_output.put_line( get_sql(any_type_mapper(v_input) ) );
end;
/

declare
   v_sql varchar2(32767);
   v_input  anydata := anydata.convertObject( test_object_2( test_object( test_number_object(1,2) ) ) );
   v_out    any_data;
begin
   v_sql := q'[
declare
   v_input  anydata := :p_in;
   v_data   test_object_2;
   v_out_1  any_data := any_data_object( 'GENERIC_UTIL.TEST_OBJECT_2' );
begin
   if v_input.getObject( v_data ) = DBMS_TYPES.NO_DATA then
      raise NO_DATA_FOUND;
   end if;
   declare
      v_out_2  any_data := any_data_object( 'GENERIC_UTIL.TEST_OBJECT' );
   begin
      --add object element to v_out_2
      declare
         v_out_3  any_data := any_data_object( 'GENERIC_UTIL.TEST_NUMBER_OBJECT' );
      begin
         --add scalar element
         v_out_3.add_element( any_data_attribute( 'A_NUMBER', any_data_number( v_data.a_value.val.a_number ) ) );
         --add scalar element
         v_out_3.add_element( any_data_attribute( 'B_NUMBER', any_data_number( v_data.a_value.val.b_number ) ) );

         v_out_2.add_element( any_data_attribute( 'VAL', v_out_3 ) );
      end;
      v_out_1.add_element( any_data_attribute( 'A_VALUE', v_out_2 ) );
   end;
   :p_out := v_out_1;
end;
   ]';

   execute immediate v_sql using in v_input, out v_out;
   dbms_output.put_line( v_out.to_string() );
end;
/

declare
   v_input  anydata := anydata.convertObject( test_object_2( test_object( test_number_object(1,2) ) ) );
   v_data   test_object_2;
   v_out_1  any_data := any_data_object( 'GENERIC_UTIL.TEST_OBJECT_2' );
begin
   if v_input.getObject( v_data ) = DBMS_TYPES.NO_DATA then
      raise NO_DATA_FOUND;
   end if;
   declare
      v_out_2  any_data := any_data_object( 'GENERIC_UTIL.TEST_OBJECT' );
   begin
      --add object element to v_out_2
      declare
         v_out_3  any_data := any_data_object( 'GENERIC_UTIL.TEST_NUMBER_OBJECT' );
      begin
         --add scalar element
         v_out_3.add_element( any_data_attribute( 'A_NUMBER', any_data_number( v_data.a_value.val.a_number ) ) );
         --add scalar element
         v_out_3.add_element( any_data_attribute( 'B_NUMBER', any_data_number( v_data.a_value.val.b_number ) ) );

         v_out_2.add_element( any_data_attribute( 'VAL', v_out_3 ) );
      end;
      v_out_1.add_element( any_data_attribute( 'A_VALUE', v_out_2 ) );
   end;
   dbms_output.put_line( v_out_1.to_string() );
end;
/<commit_msg>Testing new way of building any_data dynamic sql.<commit_after>declare
   v_input  anydata;
   v_sql    varchar2(32767);
   v_result any_data;
   procedure get_type_info( p_mapper any_type_mapper, p_level integer := 1) is
      begin
         dbms_output.put_line( lpad(' ',2*p_level)||p_mapper.attribute_name||' '||p_mapper.get_typename()||' '||p_mapper.attributes_count );
         if p_mapper.type_code = dbms_types.typecode_object then
            for i in 1 .. p_mapper.attributes_count loop
               get_type_info( p_mapper.get_attribute_type( i ), p_level + 1 );
            end loop;
         elsif p_mapper.type_code in ( dbms_types.typecode_table, dbms_types.typecode_varray, dbms_types.typecode_namedcollection ) then
            get_type_info( p_mapper.get_attribute_type( 1 ), p_level + 1 );
         end if;
      end;

   function get_sql_for_object( p_child_sql varchar2, p_type_name varchar2, p_level integer, p_return_assignment varchar2, p_attribute_name varchar2 ) return varchar2 is
        v_return_assignment varchar2(1000);
      begin
         if p_attribute_name is not null then
            v_return_assignment := 'any_data_attribute( '''||p_attribute_name||''', v_out_' || p_level ||')';
         else
            v_return_assignment := 'v_out_' || p_level;
         end if;
         return 'declare
         v_out_'||p_level||' any_data := any_data_object( '''||p_type_name||''' );
      begin
         '||p_child_sql||replace( p_return_assignment, '{value}', v_return_assignment )||'
      end;
         ';
      end;
   function get_sql_for_scalar( p_return_assignment varchar2, p_any_data_type varchar2, p_attribute_name varchar2, p_data_breadcrumb varchar2 ) return varchar2 is
      begin
         return replace( p_return_assignment,
                         '{value}',
                         'any_data_attribute( '''||p_attribute_name||''', any_data_'||p_any_data_type||'( '||p_data_breadcrumb||' ) )' )||'
         ';
      end;

   function get_sql( p_mapper any_type_mapper, p_data_breadcrumb varchar2, p_level integer := 0, p_return_assignment varchar2 := ':p_out := {value};' ) return varchar2 is
      v_sql varchar2(32767);
      v_return_assignment varchar2(1000) := p_return_assignment;
      v_data_breadcrumb   varchar2(4000) := p_data_breadcrumb;
      begin
         if p_mapper.attribute_name is not null then
            v_return_assignment := 'v_out_'||p_level||'.add_element( {value} );';
            v_data_breadcrumb    :=  v_data_breadcrumb||'.'||p_mapper.attribute_name;
--          else
--             v_return_assignment := 'v_out_'||p_level||'.add_element( {value} );';
         end if;
         if p_mapper.type_code = dbms_types.typecode_object then
            for i in 1 .. p_mapper.attributes_count loop
               v_sql := v_sql || get_sql( p_mapper.get_attribute_type( i ), v_data_breadcrumb, p_level + 1, v_return_assignment );
            end loop;
            v_sql := get_sql_for_object( v_sql, p_mapper.get_typename(), p_level+1, v_return_assignment, p_mapper.attribute_name );
--            elsif p_mapper.type_code in ( dbms_types.typecode_table, dbms_types.typecode_varray, dbms_types.typecode_namedcollection ) then
--               v_sql := get_sql( p_mapper.get_attribute_type( 1 ), p_level );
         else
            v_sql := get_sql_for_scalar( p_return_assignment, p_mapper.get_typename(), p_mapper.attribute_name, v_data_breadcrumb );
         end if;
         return v_sql;
      end;
begin
      v_input  := anydata.convertObject( test_object_2( test_object( test_number_object(1,2) ) ) );
   --   v_input  := anydata.convertCollection( a_tab( test_object_2( test_object( test_number_object(1,2) ) ) ) );
--   v_input  := anydata.convertCollection( a_tab( test_obj_2_bis( test_object( test_number_object(1,2) ) ,123 ) ) );
--   get_type_info( any_type_mapper(v_input) );
   v_sql := get_sql( any_type_mapper(v_input), 'v_data' );
   v_sql := '
   declare
      v_input anydata := :p_input;
      v_data  test_object_2;
   begin
      if v_input.getObject( v_data ) = DBMS_TYPES.NO_DATA then
         raise NO_DATA_FOUND;
      end if;
      '||v_sql||'
   end;';
   dbms_output.put_line( v_sql );
   execute immediate v_sql using in v_input, out v_result;
   dbms_output.put_line( v_result.to_string() );
end;
/

declare
   v_sql varchar2(32767);
   v_input  anydata := anydata.convertObject( test_object_2( test_object( test_number_object(1,2) ) ) );
   v_out    any_data;
begin
   v_sql := q'[
declare
   v_input  anydata := :p_in;
   v_data   test_object_2;
   v_out_1  any_data := any_data_object( 'GENERIC_UTIL.TEST_OBJECT_2' );
begin
   if v_input.getObject( v_data ) = DBMS_TYPES.NO_DATA then
      raise NO_DATA_FOUND;
   end if;
   declare
      v_out_2  any_data := any_data_object( 'GENERIC_UTIL.TEST_OBJECT' );
   begin
      --add object element to v_out_2
      declare
         v_out_3  any_data := any_data_object( 'GENERIC_UTIL.TEST_NUMBER_OBJECT' );
      begin
         --add scalar element
         v_out_3.add_element( any_data_attribute( 'A_NUMBER', any_data_number( v_data.a_value.val.a_number ) ) );
         --add scalar element
         v_out_3.add_element( any_data_attribute( 'B_NUMBER', any_data_number( v_data.a_value.val.b_number ) ) );

         v_out_2.add_element( any_data_attribute( 'VAL', v_out_3 ) );
      end;
      v_out_1.add_element( any_data_attribute( 'A_VALUE', v_out_2 ) );
   end;
   :p_out := v_out_1;
end;
   ]';

   execute immediate v_sql using in v_input, out v_out;
   dbms_output.put_line( v_out.to_string() );
end;
/

declare
   v_input  anydata := anydata.convertObject( test_object_2( test_object( test_number_object(1,2) ) ) );
   v_data   test_object_2;
   v_out_1  any_data := any_data_object( 'GENERIC_UTIL.TEST_OBJECT_2' );
begin
   if v_input.getObject( v_data ) = DBMS_TYPES.NO_DATA then
      raise NO_DATA_FOUND;
   end if;
   declare
      v_out_2  any_data := any_data_object( 'GENERIC_UTIL.TEST_OBJECT' );
   begin
      --add object element to v_out_2
      declare
         v_out_3  any_data := any_data_object( 'GENERIC_UTIL.TEST_NUMBER_OBJECT' );
      begin
         --add scalar element
         v_out_3.add_element( any_data_attribute( 'A_NUMBER', any_data_number( v_data.a_value.val.a_number ) ) );
         --add scalar element
         v_out_3.add_element( any_data_attribute( 'B_NUMBER', any_data_number( v_data.a_value.val.b_number ) ) );

         v_out_2.add_element( any_data_attribute( 'VAL', v_out_3 ) );
      end;
      v_out_1.add_element( any_data_attribute( 'A_VALUE', v_out_2 ) );
   end;
   dbms_output.put_line( v_out_1.to_string() );
end;
/

declare
   v_input  anydata := anydata.convertObject( test_object_2( test_object( test_number_object(1,2) ) ) );
   v_out_1  any_data := any_data_object( 'GENERIC_UTIL.TEST_OBJECT_2' );
   v_data test_object_2;
begin
   if v_input.getObject( v_data ) = DBMS_TYPES.NO_DATA then
      raise NO_DATA_FOUND;
   end if;
   --going throug internals of object 1
   declare
      v_out_2  any_data := any_data_object( 'GENERIC_UTIL.TEST_OBJECT' );  --
   begin

      --going through internals of object 2
      declare
         v_out_3  any_data := any_data_object( 'GENERIC_UTIL.TEST_NUMBER_OBJECT' );
      begin

         --going through internals of object 3
         begin
            v_out_3.add_element( any_data_attribute( 'A_NUMBER', any_data_number( v_data.a_value.val.a_number ) ) );  --scalar assignend to object 3 as element
         end;
         begin
            v_out_3.add_element( any_data_attribute( 'B_NUMBER', any_data_number( v_data.a_value.val.b_number ) ) );  --scalar assignend to object 3 as element
         end;
         --end going through internals of object 3

         v_out_2.add_element( any_data_attribute( 'VAL', v_out_3 ) );   --object 3 assignend to object 2 as element
      end;
      --end going through internals of object 2

      v_out_1.add_element( any_data_attribute( 'A_VALUE', v_out_2 ) ); --object 2 assignend to object 1 as element
   end;
   --end going through internals of object 1

   dbms_output.put_line( v_out_1.to_string() );
end;
/
{'fraction_non_alphanumeric': 0.10376106194690266, 'fraction_numerical': 0.015412979351032448, 'mean_word_length': 3.0932689405372775, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4014027', 'n_tokens_mistral': 5001, 'n_tokens_neox': 4887, 'n_words': 935}