4 Replies Latest reply: Feb 18, 2013 10:13 PM by jeneesh RSS

    How to Use SPOOL Command with Bind Variables

    user10903866
      For the Following SPOOL I want the GEN_DATE to be entered by the User at the time of execution of the SQL Script. Is it possible in SPOOL Command.


      set colsep , -- separate columns with a comma
      set pagesize 1000 -- get rid of disturbing ---- between pages
      set heading on -- Print column heading
      set trimspool on -- remove trailing blanks. eliminating the spaces up to eol
      set linesize 700 -- line size should be the sum of the column widths
      spool spool_results.csv
      SELECT *
      FROM GUI_SITE_JOURNAL
      WHERE GENDATE_ BETWEEN '2012-11-01 00:00:00:00000' AND '2012-11-02 00:00:00:00000'* ORDER BY GEN_DATE;
      spool off;

      The reason is to give the ability to user so that he can enter any range without modifying the code of the script.

      Can Any one help me please.

      Edited by: user10903866 on Feb 18, 2013 7:44 PM
        • 2. Re: How to Use SPOOL Command with Bind Variables
          Frank Kulash
          Hi,
          user10903866 wrote:
          For the Following SPOOL I want the GEN_DATE to be entered by the User at the time of execution of the SQL Script. Is it possible in SPOOL Command.
          Do you want the user input in the SPOOL command, or do you want it in the query?
          set colsep , -- separate columns with a comma
          set pagesize 1000 -- get rid of disturbing ---- between pages
          set heading on -- Print column heading
          set trimspool on -- remove trailing blanks. eliminating the spaces up to eol
          set linesize 700 -- line size should be the sum of the column widths
          spool spool_results.csv
          SELECT *
          FROM GUI_SITE_JOURNAL
          WHERE GENDATE_ BETWEEN '2012-11-01 00:00:00:00000' AND '2012-11-02 00:00:00:00000'* ORDER BY GEN_DATE;
          What is the data type oif gen_date?
          If it's a string, that's a big mistake. Information about dates belongs in DATE columns.
          If it's a DATE, then don't try to compare it to strings, such as '2012-11-01 00:00:00:00000' .
          spool off;

          The reason is to give the ability to user so that he can enter any range without modifying the code of the script.

          Can Any one help me please.
          One way to do that is with substitution variables:
          ...
          SET     VERIFY  OFF
          
          ACCEPT  start_gen_date     PROMPT "Starting date (e.g., 2013-02-18 23:00:00.00000): "
          ACCEPT  end_gen_date     PROMPT "Ending date   (e.g., 2013-02-18 23:59:59.99999): "
          
          
          SPOOL  spool_results.csv
          
          SELECT    *
          FROM        gui_site_journal 
          WHERE        gen_date  BETWEEN '&start_gen_date'
                          AND     '&end_gen_date'
          ORDER BY  gen_date;
          
          SPOOL  OFF
          There are security considerations. Substitution variables give devious users the power to issue any SQL command, such as DROP TABLE. Users that have SQL*Plus access already have that power, anyway.
          • 3. Re: How to Use SPOOL Command with Bind Variables
            vlethakula
            Use

            GENDATE_ BETWEEN &a and &b
            • 4. Re: How to Use SPOOL Command with Bind Variables
              user10903866
              Thanks It has solved my Issue. Thanks Heaps again.