Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Need Shell Script to Pass Variables in Oracle

Posted on 2005-04-04
4
Medium Priority
?
3,195 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
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Suggested Courses
Course of the Month12 days, 13 hours left to enroll

578 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