[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

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

0
gilnari
Asked:
gilnari
  • 4
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
Looks good to me.

What are your specific concerns?
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
gilnariAuthor Commented:
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
slightwv (䄆 Netminder) Commented:
>>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.
0
 
gilnariAuthor Commented:
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
0
 
slightwv (䄆 Netminder) Commented:
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.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now