1 2 3 Previous Next 32 Replies Latest reply on Jan 23, 2013 5:29 PM by 874273 Go to original post
      • 30. Re: From  and To columns display
        Purvesh K
        One more question, Are you always required to display a same set of columns or you will be asked at Run-Time to display any particular set of columns?

        Reason is, if you do not have any run-time request, then It will still be suggested to use the Static SQL, since using Dynamic SQL just to prevent the Effort of Typing the column names separated by Comma's, isn't a good enough reason to opt for Dynamic SQL.

        Dynamic SQL, is recommended in situations when the Objects Name/Column names/Where filter predicates are not known until run-time. This allows developer to configure the Database statement at a run-time rather than compile-time.

        If you are still obstinate to use Dynamic sql, I have given a solution to be used.
        • 31. Re: From  and To columns display
          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.
          So its not the requirement of the application to get the query dynamically, its just the lazy developer :) Been there felt it. So for that you don't have to develop a pl/sql code to generate the result of the query but you can just generate the actual query itself and execute it as a static query. You can have this as part of your development tool rather than as part of your actual application

          You can write a function that will table input parameters user, table name,column id lower boundary and column id upper boundary. The function will return a SELECT query with the required columns as a CLOB.

          Something like this
          SQL> create or replace function build_query 
            2  ( 
            3    pOwner       in varchar2, 
            4    pTable       in varchar2, 
            5    pColId_lower in integer, 
            6    pColId_upper in integer 
            7  ) 
            8  return clob 
            9  is 
          10     lSql     clob; 
          11     lColName varchar2(50); 
          12  begin 
          13     lSql := 'select '; 
          14     for i in 
          15     ( 
          16        select column_name, 
          17               row_number() over(order by column_id) rno 
          18          from all_tab_columns 
          19         where owner = pOwner 
          20           and table_name = pTable 
          21      and column_id between pColId_lower and pColId_Upper 
          22     ) 
          23     loop 
          24        if i.rno = 1 then 
          25           lColName := i.column_name; 
          26        else 
          27           lColName := ', ' || i.column_name; 
          28        end if; 
          29        dbms_lob.writeappend(lSql, length(lColName), lower(lColName)); 
          30     end loop; 
          31     dbms_lob.writeappend(lSql, length(' from ' || pOwner || '.' || pTable), lower(' from ' || pOwner || '.' || pTable)); 
          32     return lSql; 
          33* end; 
          34  / 
          Function created. 
          SQL> select column_id, column_name       
            2    from user_tab_columns 
            3   where table_name = 'EMP' 
            4   order by 1 
            5  / 
          ---------- ------------------------------ 
                   1 EMPNO 
                   2 ENAME 
                   3 JOB 
                   4 MGR 
                   5 HIREDATE 
                   6 SAL 
                   7 COM 
                   8 DEPTNO 
          8 rows selected. 
          SQL> select build_query('ARBORU', 'EMP', 2, 6) 
            2    from dual 
            3  / 
          select ename, job, mgr, hiredate, sal from arboru.emp 
          • 32. Re: From  and To columns display
            Thanks Purvesh...!!!

            I saw Karthik function also..that is also good. But your's is very simple and only one select query.

            Hey Thanks Karthik...!!! I would have close this issue long back. But I was busy with my work.

            Thanks both of you guys...Both are Rock...!!! :) :)

            1 2 3 Previous Next