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;
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.
938946 wrote:Use this
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.
SELECT a.ename,a.sal FROM (SELECT ROWNUM rm, b.* FROM emp b) a WHERE rm >= &c AND rm <= &d order by ename ;
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
I have written a query for display (from row to to row ). Like thisThis 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
Select rownum,ename,sal from emp where rowid in (select rowid from emp where rownum<&upto minus select rowid from emp where rownum<=&start);
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.
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.
You should provide a broad explanation of why you need a requirement like this to get the optimal solution. What is the purpose?
yes, columnids are fixed, it won't change in future. As of now I don't have that kind of problem.
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.