Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 689
  • Last Modified:

Execute immediate bizarre behavior

I have written this procedure which grabs packages, procedure etc from the ALL_SOURCE Table. It concatenates each object togeter and add the create or replace to the synthax and adds schema name for example.
package test becomes create or replace package plinnane_qa_1.test.    I am using it to replicae obbjects from one schema to another.  When I comment the excetue immedaite procedure, the dbms_ouput displays results for 91 objects.
I have added logic to execute objects after max line number for each object. However when I uncomment the excetue immedaite procedure only 4 objects are displayed by dbms_output and hence 4 objects dynamically created.
The function cmn_qa_util_pkg.clob_to_text_fnc  converts clob back to text before execution.
I have no idea why this is happening, any ideas would be appreciated.



declare
   clob_sql         clob;
   str_obj_nm       varchar2 (100);
   n_count          number                   := 0;
   n_count1         number                   := 0;
   str_tab_name     qa_types_pkg.str_tabtype;
   str_tab_type     qa_types_pkg.str_tabtype;
   n_tab_line       qa_types_pkg.ids_tabtype;
   n_tab_max_line   qa_types_pkg.ids_tabtype;
   str_tab_text     qa_types_pkg.str_tabtype;
   str_user_nm      varchar2 (50)            := 'PLINNANE_QA_1';
   str_sql          varchar2 (4000);
begin
   str_sql :=
      'select   type,
                  name,
                  line,
                  text
             from all_source
            where owner = :str_user_nm                                                          
         order by type, name, line';

   execute immediate (str_sql)
   bulk collect into str_tab_type,
                     str_tab_name,
                     n_tab_line,
                     str_tab_text
               using str_user_nm;

   str_sql :=
      'select  max(line) line,type,name
                    from all_source
                   where owner = :str_user_nm
                           group by name,type
                           order by type,name, line';

   execute immediate (str_sql)
   bulk collect into n_tab_max_line
               using str_user_nm;

   for i in str_tab_name.first .. str_tab_name.last
   loop
      str_obj_nm := 'PLINNANE_QA_1_125.' || str_tab_name (i);
      n_count1 := n_count1 + 1;

      if n_tab_line (i) = 1
      then
         n_count := n_count + 1;
         clob_sql :=
                 upper (replace (upper (str_tab_text (i)), str_tab_type (i), 'CREATE OR REPLACE ' || str_tab_type (i)));
         clob_sql :=
                    upper (replace (upper (cmn_qa_util_pkg.clob_to_text_fnc (clob_sql)), str_tab_name (i), str_obj_nm));
      else
         clob_sql := clob_sql || str_tab_text (i);
      end if;

      if n_tab_line (i) = n_tab_max_line (n_count)
      then
         DBMS_OUTPUT.put_line (   ' n_tab_max_line(i) '
                               || n_tab_max_line (n_count)
                               || ' n_tab_line(i) '
                               || n_tab_line (i)
                               || ' i '
                               || i
                               || ' count '
                               || n_count
                               || ' TYPE '
                               || str_tab_type (i)
                               || '.NAME '
                               || str_tab_name (i)
                              );

         execute immediate (cmn_qa_util_pkg.clob_to_text_fnc (clob_sql));

         DBMS_OUTPUT.put_line (' str_sql ' || substr (str_sql, 1, 220));
      end if;
   end loop;
end;
0
dplinnane
Asked:
dplinnane
  • 2
  • 2
1 Solution
 
earth man2Commented:
You should probably put this in a stored procedure and debug it using JDeveloper !
0
 
plamen73Commented:
Isnt't it possible to have objects more than 32000 characters long. In that case I am wondering what will happen with

cmn_qa_util_pkg.clob_to_text_fnc (clob_sql) and the execute immediate statement.
TRy to calculate the length of the object using the
n_tab_max_line (n_count)

and spool them having the execute immediate commented. If this is the case we will think later how to overcome
0
 
dplinnaneAuthor Commented:
I think my problem is that my clob function was returning to a varchar2(4000)
I calculated the lenght of each object as follows.
Would it be best to something like the following

   buffer           varchar2 (4000);
   read_amount number := 10;
   read_offset number := 1;  
 execute immediate (dbms_lob.read(clob_sql, read_amount, read_offset,buffer));
I tried the above but it does not work.
ORA-06550: line 81, column 22:
PLS-00222: no function with name 'READ' exists in this scope
ORA-06550: line 81, column 4:
PL/SQL: Statement ignored

n_clob_len 170 n_count 1
 n_clob_len 455 n_count 2
 n_clob_len 401 n_count 3
 n_clob_len 2770 n_count 4
 n_clob_len 450 n_count 5
 n_clob_len 2163 n_count 6
 n_clob_len 605 n_count 7
 n_clob_len 377 n_count 8
 n_clob_len 414 n_count 9
 n_clob_len 781 n_count 10
 n_clob_len 899 n_count 11
 n_clob_len 1132 n_count 12
 n_clob_len 395 n_count 13
 n_clob_len 999 n_count 14
 n_clob_len 755 n_count 15
 n_clob_len 659 n_count 16
 n_clob_len 192 n_count 17
 n_clob_len 236 n_count 18
 n_clob_len 374 n_count 19
 n_clob_len 579 n_count 20
 n_clob_len 573 n_count 21
 n_clob_len 447 n_count 22
 n_clob_len 356 n_count 23
 n_clob_len 714 n_count 24
 n_clob_len 432 n_count 25
 n_clob_len 397 n_count 26
 n_clob_len 632 n_count 27
 n_clob_len 810 n_count 28
 n_clob_len 186 n_count 29
 n_clob_len 288 n_count 30
 n_clob_len 566 n_count 31
 n_clob_len 582 n_count 32
 n_clob_len 579 n_count 33
 n_clob_len 720 n_count 34
 n_clob_len 542 n_count 35
 n_clob_len 875 n_count 36
 n_clob_len 690 n_count 37
 n_clob_len 150 n_count 38
 n_clob_len 288 n_count 39
 n_clob_len 367 n_count 40
 n_clob_len 2653 n_count 41
 n_clob_len 7453 n_count 42
 n_clob_len 26172 n_count 43
 n_clob_len 1321 n_count 44
 n_clob_len 10752 n_count 45
 n_clob_len 11611 n_count 46
 n_clob_len 4816 n_count 47
 n_clob_len 4478 n_count 48
 n_clob_len 4332 n_count 49
 n_clob_len 2855 n_count 50
 n_clob_len 8871 n_count 51
 n_clob_len 8339 n_count 52
 n_clob_len 6547 n_count 53
 n_clob_len 4718 n_count 54
 n_clob_len 915 n_count 55
 n_clob_len 4804 n_count 56
 n_clob_len 5742 n_count 57
 n_clob_len 3851 n_count 58
 n_clob_len 6531 n_count 59
 n_clob_len 2833 n_count 60
 n_clob_len 2513 n_count 61
 n_clob_len 2727 n_count 62
 n_clob_len 7895 n_count 63
 n_clob_len 8039 n_count 64
 n_clob_len 15396 n_count 65
 n_clob_len 1164 n_count 66
 n_clob_len 8181 n_count 67
 n_clob_len 5635 n_count 68
 n_clob_len 3276 n_count 69
 n_clob_len 17069 n_count 70
 n_clob_len 3456 n_count 71
 n_clob_len 2754 n_count 72
 n_clob_len 12066 n_count 73
 n_clob_len 1417 n_count 74
 n_clob_len 1804 n_count 75
 n_clob_len 7121 n_count 76
 n_clob_len 2693 n_count 77
 n_clob_len 1974 n_count 78
 n_clob_len 1661 n_count 79
 n_clob_len 21746 n_count 80
 n_clob_len 2106 n_count 81
 n_clob_len 601 n_count 82
 n_clob_len 5015 n_count 83
 n_clob_len 4985 n_count 84
 n_clob_len 1494 n_count 85
 n_clob_len 3104 n_count 86
 n_clob_len 914 n_count 87
 n_clob_len 574 n_count 88
 n_clob_len 5266 n_count 89
 n_clob_len 911 n_count 90
 n_clob_len 168 n_count 91
 n_clob_len 940 n_count 92
 n_clob_len 83 n_count 93
 n_clob_len 85 n_count 94
0
 
dplinnaneAuthor Commented:
When I execute this

 n_clob_len := DBMS_LOB.GETLENGTH (clob_sql);
execute immediate (dbms_lob.SUBSTR(clob_sql,n_clob_len,1));

DBMS oUTPUT GIVES only gives the first 3 readings instead of 91 readings

 n_clob_len 170 n_count 1
 n_clob_len 455 n_count 2
 n_clob_len 401 n_count 3
0
 
plamen73Commented:
Look very carefully in this function.
cmn_qa_util_pkg.clob_to_text_fnc (clob_sql);
I suppose there is bug there. Somehow you are limiting its result even below 2770 bytes (see the length of the 4th object source).
One more thing. In PL/SQL you have varchar2 up to 32000 chars. 4000 limitation is for a table field only!
So, make sure you are converting CLOB to up to 32000 chars in clob_to_text_fnc  function!

the big problem will come when your objects become more that 32000 in length but you are lucky and do not have such currently.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now