1 2 3 Previous Next 32 Replies Latest reply: Jan 23, 2013 11:29 AM by 874273 Go to original post RSS
      • 15. Re: From  and To columns display
        874273
        not working ...!!! it is displaying all columns...!!

        Regards
        SA
        • 16. Re: From  and To columns display
          874273
          thanks for your advise...!!! :)
          • 17. Re: From  and To columns display
            941949
            is your problem solved?
            • 18. Re: From  and To columns display
              874273
              No boss....!!! I have seen your answer ... first day oracle learner also can do that....!! but i am not expecting that kind of answer. Hope you can't understand my problem. Check once again my question.


              Regards
              SA
              • 19. Re: From  and To columns display
                Purvesh K
                The use of ROWNUM, will never ever suppress the Projection of Query.

                Explicit specification of Column Names will do it. All, you have tried is fetching rows between a Lower and Upper bound.

                IMO, You should follow advice of Jeneesh, to put a Static SQL with the number of columns you need to fetch. Since, you did not mention, if you would be getting a Dynamic/Run Time "Request" to project different columns, I am recommending you to follow some solution similar to one I gave, which involves use of Dynamic SQL. If you have a fixed request of column positions, then it willl always be better to use the Static SQL.
                • 20. Re: From  and To columns display
                  Nitesh.
                  Oh Sorry Jeneesh :) ... I am on the job now so unable to notice 'e' instead written 'i' as while expressing your name in thoughts itself Haaa .. Chill and still OP didnt get solution ah .. Did you tried my query just try that once and add order by clause as per your requirement in my query ..
                  • 21. Re: From  and To columns display
                    874273
                    Hi Purvesh,

                    I am getting error while compiling your script...!!!
                    create or replace procedure print_dynamic_cols
                    (
                      p_start_col_no        number,
                      p_stop_col_no         number,
                      p_ref_cur         out sys_refcursor
                    )
                    is
                      v_sql     varchar2(4000);
                      ref_cur   sys_refcursor;
                    begin
                      select 'select ' || rtrim(trailing ', ' from listagg(column_name, ', ') within group (order by column_id)) || ' from ODM_ITEM_PARTS'
                        into v_sql
                        from user_tab_columns
                       where table_name = 'ODM_ITEM_PARTS'      --- give ur tablenamme
                         and column_id between p_start_col_no and p_stop_col_no
                      connect by level <= (p_stop_col_no - p_start_col_no) + 1;
                      
                      dbms_output.put_line('v_sql' ||v_sql);
                      
                      open ref_cur for v_sql;
                    end;
                    Error
                    ******
                    remote_object_name=>'ODM_ITEM_PARTS');
                    END;
                    Error at line 1
                    ORA-44004: invalid qualified SQL name
                    ORA-06512: at "SYS.DBMS_ASSERT", line 188
                    ORA-06512: at "SYS.DBMS_COMPARISON", line 372
                    ORA-06512: at line 4
                    Script Terminated on line 1.
                    Please advise....!!!

                    Regards
                    SA
                    • 22. Re: From  and To columns display
                      Nitesh.
                      Try this and kindly let me know as how its not meeting your requirements dude

                      SELECT a.ename,a.sal
                      FROM (SELECT ROWNUM rm, b.*
                      FROM emp b) a
                      WHERE rm >= :c AND rm <= :d
                      order by ename ;


                      Just give bind variables input as a as 10 and for d as 25

                      Edited by: Niteshkhush on Jan 23, 2013 12:32 PM

                      Edited by: Niteshkhush on Jan 23, 2013 12:32 PM
                      • 23. Re: From  and To columns display
                        941949
                        SCOTT>SELECT a.ename,a.sal
                        2 FROM (SELECT ROWNUM rm, b.*
                        3 FROM emp b) a
                        4 WHERE rm >= :c AND rm <= :d
                        5 order by ename ;
                        SP2-0552: Bind variable "D" not declared.
                        • 24. Re: From  and To columns display
                          Nitesh.
                          Which application you are using there .. In SQL Developer and TOAD and sql*plus its supported, as just give CTRL+ENTER on the query so it will prompt you to enter the values for BIND variables and then you can give INPUT values to the Bind variables... Got it ..
                          • 25. Re: From  and To columns display
                            jeneesh
                            938946 wrote:
                            SCOTT>SELECT a.ename,a.sal
                            2 FROM (SELECT ROWNUM rm, b.*
                            3 FROM emp b) a
                            4 WHERE rm >= :c AND rm <= :d
                            5 order by ename ;
                            SP2-0552: Bind variable "D" not declared.
                            Use this
                            SELECT a.ename,a.sal
                            FROM (SELECT ROWNUM rm, b.*
                            FROM emp b) a
                            WHERE rm >= &c AND rm <= &d
                            order by ename ; 
                            • 26. Re: From  and To columns display
                              Nitesh.
                              Yes Jeneesh that also same as may be '&' may support in his application ...
                              • 27. Re: From  and To columns display
                                Purvesh K
                                I do not have Oracle 11g at hand to test the solution I earlier provided.

                                Here is a 10g compliant version of it: Just replace the Select statement with the below and fetch it into the V_SQL variable.
                                create table test_table
                                (
                                  col1        number,
                                  col2        number,
                                  col3        number,
                                  col4        number,
                                  col5        number,
                                  col6        number,
                                  col7        number,
                                  col8        number
                                );
                                
                                select 'select ' || 
                                       ltrim(max(sys_connect_by_path(column_name, ', ')) keep( dense_rank last order by rn - 1), ', ') || 
                                       ' from test_table' sel
                                  from (
                                        select table_name, column_name,
                                               row_number() over (order by column_id) rn
                                            from user_tab_columns
                                           where table_name = 'TEST_TABLE'      --- give ur tablenamme
                                             and column_id between 2 and 6
                                       )
                                 group by table_name
                                connect by rn - 1 = prior rn
                                       and table_name = prior table_name
                                   start with rn = 1;
                                
                                SEL
                                --------------------------------------------------------------------------
                                select COL2, COL3, COL4, COL5, COL6 from test_table
                                • 28. Re: From  and To columns display
                                  Karthick_Arp
                                  I have written a query for display (from row to to row ). Like this
                                   
                                  
                                  Select rownum,ename,sal from emp where rowid in 
                                  (select rowid from emp where rownum<&upto 
                                  minus 
                                  select rowid from emp where rownum<=&start); 
                                  This is termed as Pagination and this has been in use for a very long time. And your implementation is not the best one available. Please read [url http://www.oracle.com/technetwork/issue-archive/2007/07-jan/o17asktom-093877.html] On Top-N and Pagination Queries By Tom Kyte 
                                  But now I got a new requirement. I have a table with 50 columns. I wanted to display 10 column to 25 column data. I don't want to display 1 to 9 and 26 to 50 column data. Is there any solution like above query.
                                  Yes its doable. But the question is how you want to do it. Basically this is a presentation requirement and generally better handled in the presentation layer of the application.

                                  You should provide a broad explanation of why you need a requirement like this to get the optimal solution. What is the purpose?

                                  Next, when you want to display 10 th to 25 th column of a table how you determine which are the columns that fall in this condition. Because in SQL the order of appearance of column in SELECT list is of user choice. You need to specify it manually.

                                  So when you have answers for the above questions you can build a Dynamic SQL as already demonstrated.
                                  • 29. Re: From  and To columns display
                                    874273
                                    Hi Karthik,

                                    Nice questions...!!! Please find below my answers..!!
                                    You should provide a broad explanation of why you need a requirement like this to get the optimal solution. What is the purpose? 
                                    I am working on Oracle Apps, Hope you know apps tables are having huge columns. If i want to check some column values every time I have to write all column names. And you know writing a column lists with comma(',') irritation. and every time I have to see table desc. then remember column names and write the query.
                                    Next, when you want to display 10 th to 25 th column of a table how you determine which are the columns that fall in this condition. Because in SQL the order of appearance of column in SELECT list is of user choice. You need to specify it manually. 
                                    yes, columnids are fixed, it won't change in future. As of now I don't have that kind of problem.

                                    I have seen your words that "yes, it is doable" ... Appreciate your confidence. keep it up.. Thanks in adv.

                                    Regards
                                    SA