asked on

Prompt for information in Oracle and output to log

I need a review of some code I was given for usein Oracle to delete reocrds in a table and capture a before and after output to a log file. It has been years since I worte this kind of stuff and what I was given was not all too familar.

If someone could review this short set and let me know if this will run in Oracle and/or hekp revise will be greatly appciated.

column date_Time new_value today_var
column database new_value d_var

select name DATABASE, user,TO_CHAR(sysdate,'_MMDDYYYY_HH24_MI_SS') DATE_TIME from dual, v$database;

SET ECHO ON

spool C:\PROT_CELL_SMP_TMP_&d_var&today_var..txt

-- Delete all records from PROT_CELL_SMP_TMP
-- ===========================================================================
-- Name: ROT_CELL_SMP_TMP.sql

-- Description:   This script performs the following activities:
-- Delete all records from table PROT_CELL_SMP_TMP
--
--
-- Modification History: 
--  Created : 27-OCT-2011 Peggy Weber
--  Modified : <<DD-MON-YYYY>> <<AUTHOR>> - <<description>>

-- Execution: This SQL, PL/SQL script is to be run FROM SQL*Plus by the LIMS Owner. 
--
-- Prerequisites:  No Pre Req. required
--
--
-- Structural Changes 
--  Updated: 
--    Tables:
--    Fields:   
--   
--  Created:
--    Tables:
--    Fields:   
--
--  Deleted:All Records
--    Tables:PROT_CELL_SMP_TMP
--    Fields:
--   
--  Views:
--  
--  Synonyms:
--
-- Environments: <<Where it is to be run.  DB, Release, etc...>>
-- 
-- ==============================================================

PROMPT -- Database, Executor, Date Time
Prompt -- =============================

select name DATABASE, user,TO_CHAR(sysdate,'_MMDDYYYY_HH24_MI_SS') DATE_TIME from dual, v$database;


PROMPT -- ==============<<Code Group - Purpose>>=============
PROMPT --
PROMPT --
PROMPT -- ==============PRE CHECKS===========================

PROMPT -- =======<<Check to be Performed.  Acceptance Criteria if Known.>>======

-- <<CODE>>
SELECT * FROM PROT_CELL_SMP_TMP;

PROMPT -- ==============EXECUTION===========================

PROMPT -- =======<<SQL to be Performed>>======

-- <<CODE>>
DELETE FROM PROT_CELL_SMP_TMP;

PROMPT -- ==============POST CHECKS==========================

PROMPT -- =======<<Check to be Performed.  Acceptance Criteria if Known.>>======

-- <<CODE>>
SELECT * FROM PROT_CELL_SMP_TMP;


-- ****************************************************************************************************************

-- <<Additional Code Groups if needed.>>

spool off

SET ECHO OFF

Open in new window

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

There is nothing wrong here but there is no need to join to dual here:

select name DATABASE, user,TO_CHAR(sysdate,'_MMDDYYYY_HH24_MI_SS') DATE_TIME from dual, v$database;

This produces the same result.:
select name DATABASE, user,TO_CHAR(sysdate,'_MMDDYYYY_HH24_MI_SS') DATE_TIME from v$database;

Again, not a problem more of an FYI.

gilnari

ASKER

Not actual having access to Oracle makes testing a bit hard so some feed back from you guys gives a girl a piece of mind and will allow me to sleep tongiht.

Thank again for the quick response

slightwv (䄆 Netminder)

>>Not actual having access to Oracle makes testing a bit hard

I'm not saying it will do what you want it to do. you will need to eventually test it.

I'm just saying that nothing jumps out at me that would cause you any major issues.

You might have issues with line wrapping/etc...

But you won't know that until you actually run it and confirm the output is in an acceptable format.

Also, as-written the last select should never return rows since it selects from a table it just deleted everything from.

gilnari

ASKER

My hope is I don't get anything in the last table but you gave me an idea instaed of select * think I willd do a count instead

ya and it is a bummer not to have direct access..I need to get my hands on single lic of oracle for a developer

slightwv (䄆 Netminder)

For education and development most Oracle products are free.

Oracle XE is even free for production use.

Go to www.oracle.com and click on downloads.