Results 1 to 4 of 4

Thread: PL/SQL help

  1. #1
    Join Date
    Mar 2005

    Unanswered: PL/SQL help

    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


    -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

  2. #2
    Join Date
    Mar 2002
    Reading, UK
    Write a pipelined function. This allows you to return a result set easily so to execute it you would just do 'select * from colcount('schema','table');'

    1.Write cursor for loop which executes the sql you listed above.

    2. Use execute immediate to execute the select statement created in step 2

    3. Output the result using pipe row.

    To get the output as csv either output the result as one string constituting of column name ||','||column_count or use an ODBC driver in excel to get excel to pull the result directly into a worksheet.

    NOTE using a cursor isnt going to reduce the load on your db server at all. Just do a search on google for pipelined function and execute immediate for more info.

    Hope thats enough pointers for you.


  3. #3
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    >where '||column_name||' = 0;
    Will most likely alway result in zero rows returned, because I doubt any column will be named "0".

    Good luck with your homework assignment. It appears you'll need it.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Mar 2002
    Reading, UK
    He's getting the sql to write other sql statements, there's nothing wrong with it apart from the fact it should really look at the datatype as the implicit conversion to a number will fail with varchar col which doesnt have a number in it


Posting Permissions

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