Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2001
    Posts
    80

    Unanswered: Tool to gen select SQL

    A oracle DB contains 300 customer tables, and each table contains around 20 columns, is there any tool to generate a select statement for each table likes this:

    select <col1>, <col2>, <col3> . . . . from <table1> order by <primary key>
    select <col1>, <col2>, <col3> . . . . from <table2> order by <primary key>
    .
    .
    .
    .

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This is not a tool but sample script; for the exercise, add the ORDER BY clause by yourself
    Code:
    spool sample.sql
    
    DECLARE
       sel_stmt   VARCHAR2 (4000);
    BEGIN
       FOR cur_t IN (SELECT t.table_name
                       FROM user_tables t
                      WHERE t.table_name IN ('EMP', 'DEPT'))
       LOOP
          sel_stmt := 'select ';
    
          FOR cur_c IN (SELECT c.column_name
                          FROM user_tab_columns c
                         WHERE c.table_name = cur_t.table_name)
          LOOP
             sel_stmt := sel_stmt || cur_c.column_name || ', ';
          END LOOP;
    
          sel_stmt :=
                SUBSTR (sel_stmt, 1, LENGTH (sel_stmt) - 2)
             || ' from '
             || cur_t.table_name
             || ';';
          DBMS_OUTPUT.put_line (sel_stmt);
       END LOOP;
    END;
    /
    
    spool off;

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Oh well, here is the ORDER BY too ...
    Code:
    DECLARE
       sel_stmt   VARCHAR2 (4000);
       prim_key   VARCHAR2 (255);
    BEGIN
       FOR cur_t IN (SELECT t.table_name
                       FROM user_tables t
                      WHERE t.table_name IN ('EMP', 'DEPT'))
       LOOP
          sel_stmt := 'select ';
    
          FOR cur_c IN (SELECT c.column_name
                          FROM user_tab_columns c
                         WHERE c.table_name = cur_t.table_name)
          LOOP
             sel_stmt := sel_stmt || cur_c.column_name || ', ';
          END LOOP;
    
          prim_key := ' ';
    
          FOR cur_p IN (SELECT   c.column_name
                            FROM user_cons_columns c, user_constraints t
                           WHERE t.table_name = cur_t.table_name
                             AND c.table_name = t.table_name
                             AND c.constraint_name = t.constraint_name
                             AND t.constraint_type = 'P'
                        ORDER BY c.POSITION)
          LOOP
             prim_key := prim_key || cur_p.column_name || ', ';
          END LOOP;
    
    	  dbms_output.put_line(cur_t.table_name ||' - ' ||LENGTH(prim_key));
    	  
          sel_stmt :=
                SUBSTR (sel_stmt, 1, LENGTH (sel_stmt) - 2)
             || ' from '
             || cur_t.table_name
             || CASE LENGTH(prim_key) WHEN 1 THEN NULL ELSE ' order by ' END
             || SUBSTR (prim_key, 1, LENGTH (prim_key) - 2)
             || ';';
          DBMS_OUTPUT.put_line (sel_stmt);
       END LOOP;
    END;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •