Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2005
    Posts
    41

    Post Unanswered: PL/SQL - Dynamic SQL - Help (Scenario)

    Scenario: In a database, there are several tables with appropriate schema names. I am trying to find out for the tables I am interested in (ie: 5), count for all the columns in a table where numerical datatypes = 0, varchar datatypes = '0', and handles the date datatypes with the use of EXCEPTIONS. Then I want the results to be exported to a excel file or a CSV
    where attributes of a table are COLUMNS, and counts is ROW

    PROGRAM:

    -The program (PL/SQL) prompts the user to enter the SCHEMA_NAME and TABLE_NAME
    -As table is rather volumnious, data is fetched into a CURSOR otherwise the queries that will execute one after the other will bog down the db server
    -The program itself DYNAMICALLY (ie: dynamic SQL given) generates SQL queries for all the columns in a specified table (s) [depends upon how many tables are specified]
    -The program finds out count for all number datatypes equal to 0, count for Varchar datatypes equal to '0' and handles date datatypes with the use of EXCEPTIONS;
    -Results need to be exported to a excel file or a CSV where attributes of a table are COLUMNS, and counts is ROW

    Following is an example of Dynamic SQL that will be used.

    set pages 3000
    set trims on
    set lines 400
    set head off verify feed off
    spool query.sql

    select 'select count(1) from '||owner||'.'||table_name||' where '||column_name||' = 0;' from all_Tab_columns
    where owner='TSTG' and table_name='ACCOUNT_DAILY' ;

    set head on verify on feed on
    spool off;
    set trims off

    I want to find out how I can acheive this? Any pseudocode / code / link / help is appreciated.

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    you probably want a procedure with a cursor which loops through the column-names and adds/appends them to your eventual select statement. Once the statement is formulated you will want to run an execute_immediate.

    enjoy!
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Why dont you look at your previous post as there is an answer there.

    Alan

Posting Permissions

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