Home » SQL & PL/SQL » SQL & PL/SQL » How to prepare a script for delivery
How to prepare a script for delivery [message #193952] Wed, 20 September 2006 02:10 Go to next message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

Hi all,

I need to know the best practices for preparing scripts.

Say for example I need to include 4 insert statements in master table and some 20 insert statements in detail table. How should I proceed delivering those scripts.

I usually do it this way

REM ..........
REM ..........
REM ..........

spool filename;

select sysdate from dual;

some the insert or any dml,ddl statemtns
some the insert or any dml,ddl statemtns
some the insert or any dml,ddl statemtns

select sysdate from dual;

spool off;


Any link or material regarding this would be helpful.

Thanks in advance.

regards
Srivaths

Re: How to prepare a script for delivery [message #194106 is a reply to message #193952] Wed, 20 September 2006 16:41 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Should this not be defined by Your organization?

Do You mean a one-off script to be run on a production system by another team?

You could start with
WHENEVER SQLERROR EXIT FAILURE

This way, the script are exited and the transaction are rollback, in case of an error.

Reg. select sysdate from dual:
Use SET TIME ON instead.

Br
Kim
Re: How to prepare a script for delivery [message #194142 is a reply to message #194106] Wed, 20 September 2006 23:35 Go to previous message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

You can use the following practise to prepare your script:
/******************************************************************************
SQL SCRIPT

Filename: learning.sql

Purpose:  Generates Reporting data for learning

Usage:    While in SQLPlus ...

             SQL> @learning.sql &1 

             Where &1 = Extract file name used in SPOOL command                 
******************************************************************************/
WHENEVER OSERROR EXIT SQL.OSCODE ROLLBACK
WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK
SET ECHO OFF
SET ESCAPE ON
SET SERVEROUTPUT ON
SET DOCUMENT OFF
SET VERIFY OFF
/******************************************************************************
Author: Srivaths 
Date:   21-Sep-2006

Edit
History:

  21-Sep-2006 Srivaths 
        o Initial version.
******************************************************************************/
DEFINE csvfile = &1

PROMPT
PROMPT Beginning data extraction.

SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 500
SET TRIMSPOOL ON
SET TERMOUT OFF

SPOOL &csvfile

--
-- Column Headings
--

SELECT 'CUSTOMER,GROUP,PROGRAM,STYLE_NAME,' FROM DUAL;

--
-- Detail Information
--
SELECT i.packed_for||','||i.product_group||','||
       i.program||','||s.style_name||','
  FROM test i
 WHERE i.scen = 0
   AND s.scen =0
   AND (i.item=s.item) 
   AND s.loc='DC'
 ORDER BY i.product_group, i.program, i.style_name;

SPOOL OFF
SET TERMOUT ON

PROMPT Data extraction complete.
PROMPT


Previous Topic: rounding to decimal places
Next Topic: bulk fetch
Goto Forum:
  


Current Time: Wed Dec 07 22:11:09 CST 2016

Total time taken to generate the page: 0.19201 seconds