0

I am trying to access index DDL of a table using user_indexes and DBMS_METADATA.GET_DDL

declare

V_DDL clob;

Begin

    for rec in (select ai.owner, ai.index_name, ai.table_name from all_indexes ai, base_table til
    where ai.table_name = upper(til.table_name)) loop
    
    DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);
    DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE);
    DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',FALSE);
    
    V_DDL := DBMS_METADATA.GET_DDL('INDEX',rec.Index_Name, Rec.Owner);
    
    Insert into after_work VALUES(rec.index_name, rec.table_name, V_DDL);
    end loop;
    
end;
/

I need to manually double click on INDEX_SCRIPT column to view the DDL script.

what I am trying understand is how to access the DDL script through the SQL. Kindly advise me on this please. thank you

I got the result as shown in the pic

result

pawan rakesh
  • 45
  • 11
  • What? Do not understand. – OldProgrammer Aug 21 '21 at 18:11
  • Hi @OldProgrammer, I'm trying to access the INDEX DDL script through the SQL , in the pic that I have uploaded, if you observe I need to double click on (HUGECLOB) then a new window pops up with the DDL script. What I am trying to achieve is to view the DDL script without manually clicking on the (HUGECLOB). (Apologies My English is bad ) – pawan rakesh Aug 21 '21 at 18:14
  • What has double-clicking on anything got to do with dbms_metadata or PL/SQL programming? Does your `after_work` table have a column named `index_script`? But to access it in SQL you just need to select it in a query so I don't see what the issue is. – William Robertson Aug 21 '21 at 18:32
  • Hi @WilliamRobertson, Yes, the `after_work` table has a column `index_Script ` After running a select query on the table , I still don't see the DDL script in the result, I have to double click on the `index_Script ` column i.e. (HUGECLOB) to view the DDL script. what I am looking for is to have the entire DDL script present in `index_Script ` column. – pawan rakesh Aug 21 '21 at 18:38
  • 2
    Well, it is present. TOAD just doesn't display CLOBs until you click them. You might try `dbms_lob.substr(index_script,4000)` which makes it a normal varchar2 column instead of clob, but if it's longer than 4000 characters you still have a problem. – William Robertson Aug 21 '21 at 18:57
  • Hi @WilliamRobertson, I haven't worked much with CLOB Data , like you said, toad doesn't display CLOBs. I tried with `'dbms_lob.substr(index_script,4000)` still it is the same. thank you for the help :) – pawan rakesh Aug 21 '21 at 19:15
  • Maybe it needs to be less than 4000 characters. I don't have Toad to test with, I'm afraid. – William Robertson Aug 21 '21 at 19:17
  • 2
    What TOAD or any other IDE displays has nothing to do with `insert into some_table values (some_clob)`. So the actual question is: does the content of the `after_work` table have the data you want or not? – astentx Aug 21 '21 at 19:21
  • Hi @WilliamRobertson, I tried with 2000/1000/500 characters, still it doesn't display, I will continue to research more on this. – pawan rakesh Aug 21 '21 at 19:21
  • Check some settings like 'preview LOB columns', any IDE disables it because LOB data is not fetched by default with simple `select` unless you manually request its content – astentx Aug 21 '21 at 19:23
  • Hi @astentx, Thank you for the response. yes, you got the question, (I think I did not explain my query in a better way, I apologies). All I am trying to achieve is to have the DDL script as the column's content. (Edited) yes, will continue to do more research on this – pawan rakesh Aug 21 '21 at 19:26
  • @pawanrakesh The DDL script is the column's content. Why does it matter how it's displayed in Toad? – William Robertson Aug 22 '21 at 11:22
  • @WilliamRobertson, apologies for very delayed response. The Issue was fixed, the column's content will have the DDL , I was able to run with dynamic DML statement , Thank you all for your time. – pawan rakesh Sep 16 '21 at 18:58

0 Answers0