How to execute block in oracle?

Question

I am looking for the solution to search string/number in oracle all tables and columns.

I checked online and could find many solutions.

But when I am executing below solution in SQL Oracle developer, it creates anonymous block.

Can anyone help me how to use below solution in Oracle to get the results.

DECLARE
  match_count integer;
  v_search_string varchar2(4000) := <<string you want to search for>>;
BEGIN  
  FOR t IN (SELECT owner, table_name, column_name FROM all_tab_columns) LOOP   
    EXECUTE IMMEDIATE    
      'SELECT COUNT(*) FROM '||t.owner || '.' || t.table_name||
      ' WHERE '||t.column_name||' = :1'   
       INTO match_count  
      USING v_search_string; 
    IF match_count > 0 THEN 
      dbms_output.put_line( t.owner || '.' || t.table_name ||' '||t.column_name||' '||match_count );
    END IF; 
  END LOOP;
END;
/

Please help as I am new in Oracle.

Thanks in advabce

I'm not sure what you mean by "it creates anonymous block". An anonymous block is not stored anywhere so it doesn't make sense to talk about "creating" it like it would if you created a stored object like a procedure or a function. Are you executing the anonymous block? Are you seeing any results? Have you enabled `dbms_output` for your session? Have you put the string you're actually searching for in the "<>"? Are you getting an error? — Justin Cave, Aug 11 '15 at 02:18
@JustinCave: Thanks for the response.. Yes I have entered the string that I want to search in database. I am simply executing this in SQL window in SQL Oracle developer and it shows me that anonymous block is created. So here is my query how should I execute in SQL Oracle developer? — cool_taps, Aug 11 '15 at 02:37
What exactly is the message? Are you seeing "PL/SQL procedure successfully completed."? That indicates that the anonymous block was successfully executed. As I said, anonymous blocks do not get created in the sense that persistent objects like procedures and functions do. Is the problem that you are executing the anonymous block but not seeing any output from `dbms_output`? If so, I'll close this as a duplicate of the question I linked to. — Justin Cave, Aug 11 '15 at 02:41

score 0 · Answer 1 · answered Aug 11 '15 at 04:42

0

First execute

Set serveroutput on;

And then execute your anonymous block.

answered Aug 11 '15 at 04:42

Krishnat Molawade

216
3
17

score 0 · Answer 2 · answered Aug 11 '15 at 06:15

0

Click view and DBMS output in SQL Developer. In this moment you can see result PL/SQL blocks.

answered Aug 11 '15 at 06:15

starko

1,150
11
26

How to execute block in oracle?

2 Answers2