Shell Programming and Scripting

but why I added that line and it still doesnt work.

That is a good question but, as you can see, I added my query to your PL/SQL block and the results changed from no output to output.

I can think of two possibilities:
1. You mistyped it. The text must be as follows: SET SERVEROUTPUT ON. Note that there are no spaces between SERVER and OUTPUT.

2. Your query results in no rows. Try my code block as a test to prove it to yourself that it works. The only requirement is that you have an EXPLAIN PLAN table defined. If you do not, change PLAN_TABLE to something you do have in your schema.

I had thought probably I mistyped something,however, I checked it again and again, the answer is no.

is it possible to cause this problem because of the system?

one more difference is i got count(*) in the query and u havent.

No, it isn't a system problem. I would guess that your query returns no rows at this point.

Try my example above and remember to change "REM #### LINE COMMENTED OUT #### set serveroutput on" to "set serveroutput on" and change "un/pw" to something meaningful on to your database.

You must have edited your message after I read it. The COUNT(*) isn't going to change anything either but now you have given me a thought of how we can test your query.

Place "SELECT COUNT(*), COUNT(*), repeat for total number of columns you are fetching FROM (" before your your first SELECT keyword and place ")" at your SQL's semi-colon ;.

This will prove how many rows your query is return.

Example with my query:

Code:

    cursor cs is
        select count(*), count(*) from (
         select t.table_name, c.column_name
         from
           (select table_name
                  ,column_name
            from   all_tab_columns) c
          ,(select table_name
            from   all_tables) t
        where t.table_name = c.table_name
        and   t.table_name = 'PLAN_TABLE'
        );

This will always return one row; mine is "112,112". If your query returns no rows, you'll get "0,0" always.

many thanks for your help. i have sorted out. silly mistake.
cheers.

Hi,
When I tried the example above , evrything is working fine.. but one extra blank line is getting printed... how can I avoid that..

Shihab