?
Solved

Need Shell Script to Pass Variables in Oracle

Posted on 2005-04-04
4
Medium Priority
?
3,168 Views
Last Modified: 2010-05-19
Hi Experts ,
  I am looking for a script which will run a sqlplus command i,e
 
  select owner,object_name,object_type from dba_objects where trunc(LAST_DDL_TIME) >= trunc(sysdate)-1;
 
 which will spool into a spool file of format ddmonyyyy.txt   . Every day this is spooled and a new file would be generated which would be identified by date. Can any one tell me how to do this .

Regards  ,



 
0
Comment
Question by:ratnaprasad123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 1

Expert Comment

by:DonFreeman
ID: 13701410
Here's a code snippet I use to spool a file name like nedarx.ddl.20050405.2310.sql.  You have to escape the dots.

col db noprint new_value db
SELECT value db
FROM v$parameter
WHERE name LIKE 'db_name';

col sysd noprint new_value sysd
SELECT TO_CHAR(SYSDATE, 'yyyymmdd') sysd FROM DUAL;

col syst noprint new_value syst
SELECT TO_CHAR(SYSDATE, 'hh24miss') syst FROM DUAL;

SPOOL F:\Oracle\DDL\&db..ddl.&sysd..&syst..sql
0
 

Author Comment

by:ratnaprasad123
ID: 13701545
Don
You are great .....
Its working . Could you tell me more abt the following line .

col sysd noprint new_value sysd
SELECT TO_CHAR(SYSDATE, 'yyyymmdd') sysd FROM DUAL;

what exactly we are doing here .

0
 
LVL 1

Accepted Solution

by:
DonFreeman earned 2000 total points
ID: 13701693
This particular one or all of the ones I used in general?  This is a sqlplus feature that has been adapted.  I didn't invent the technique and don't really know the history.  But, we are using the new_value function of the sqlplus column command on a dummy column sysd.  We are passing a value from the select statement into a variable sysd.  You can use it just about anywhere.   You can read more about it in the sqlplus manual under column and ttitle.  
0
 

Author Comment

by:ratnaprasad123
ID: 13701858
Thanks a Lot
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question