Home » RDBMS Server » Server Administration » Redundancy Problem!!!
Redundancy Problem!!! [message #55746] Thu, 13 February 2003 09:22 Go to next message
raman
Messages: 66
Registered: February 2000
Member
Hi

I have 10 SQL scripts(each 100 lines) for Job schedule at a particular time to refresh the materialized views. They are almost identical except a table name.

Is there any possibility that I can make it into one script and send arguments/parameters into it through job scheduling batch files?

Please let me know in detail if there's any!

thanks,
-ramanujam

Report message to a moderator

Re: Redundancy Problem!!! [message #55750 is a reply to message #55746] Thu, 13 February 2003 15:31 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
 
-- i dont get, what you are exactly looking into.
-- see whether this helps you...else let us know..
-- i have just used a sql select statement.
-- similary u can use any stored procedure also!
C:>query
Usage: query Table_Name
C:>query emp

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 25-JAN-03        800                    20
      7499 ALLEN      SALESMAN        7698 04-FEB-03       1600        300         30
      7521 WARD       SALESMAN        7698 04-FEB-03       1250        500         30
      7566 JONES      MANAGER         7839 25-JAN-03       2975                    20
      7654 MARTIN     SALESMAN        7698 04-FEB-03       1250       1400         30
      7698 BLAKE      MANAGER         7839 04-FEB-03       2850                    30
      7782 CLARK      MANAGER         7839 04-FEB-03       2450                    10
      7788 SCOTT      ANALYST         7566 25-JAN-03       3000                    20
      7839 KING       PRESIDENT            04-FEB-03       5000                    10
      7844 TURNER     SALESMAN        7698 04-FEB-03       1500          0         30
      7876 ADAMS      CLERK           7788 25-JAN-03       1100                    20
      7900 JAMES      CLERK           7698 04-FEB-03        950                    30
      7902 FORD       ANALYST         7566 25-JAN-03       3000                    20
      7934 MILLER     CLERK           7782 04-FEB-03       1300                    10

14 rows selected.

C:>query dept

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ....ACCOUNTING NEW YORK
        20 ......RESEARCH DALLAS
        30 .........SALES CHICAGO
        40 ....OPERATIONS BOSTON

----------------------------------------------------------------------

C:>type query.bat
@echo off
if "%1" == "" goto Usage
set pname=%1
echo set echo off;		 > one.sql	
echo select * from %pname%;      >>one.sql
echo exit;                       >>one.sql
sqlplus -s mag/mag @one.sql
goto End
:Usage
  echo Usage: %0 Table_Name
:End

C:>

Report message to a moderator

Re: Redundancy Problem!!! [message #55754 is a reply to message #55750] Thu, 13 February 2003 15:54 Go to previous messageGo to next message
raman
Messages: 66
Registered: February 2000
Member
Hi

I give you an example:
=========================================step1
script.sql
declare
.
.
100 lines
.
.
.End
================================== ================step2
script.bat(P:arcgisarcsderefreshscriptsscript.bat)

sqlplus xxx/xxxx@xxxxx @ P:arcgisarcsderefreshscriptsscript.sql
==================================step3
Job Schedule

c: at 6:00p /every:m,t,w,th,f "P:arcgisarcsderefreshscriptsscript.bat"
===================================

My question is: I have 10 - 30 script.sql in step1....except a table name everthing is same in the scripts,which I want to make it into one generalized script, and use a table name as a parameter in step3 ...

Right now....I have inserted all the table names into a table and trying to loop the table in the script....but not succeeded though!

Please let me know if you didn't get my point:)

thanks,
-Ramanujam

Report message to a moderator

Re: Redundancy Problem!!! [message #55768 is a reply to message #55754] Fri, 14 February 2003 08:26 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
 
-- so you are looking to pass table names as parameters 
-- to the batch file?
-- this sample does that.
-- query.bat is the batch file
-- it takes any parameters (name of the tables)
-- loops through every value, executes the the sql( or a stored procedure)
-- change it as your need.
C:>query emp dept v$database v$version

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 25-JAN-03        800                    20
      7499 ALLEN      SALESMAN        7698 04-FEB-03       1600        300         30
      7521 WARD       SALESMAN        7698 04-FEB-03       1250        500         30
      7566 JONES      MANAGER         7839 25-JAN-03       2975                    20
      7654 MARTIN     SALESMAN        7698 04-FEB-03       1250       1400         30
      7698 BLAKE      MANAGER         7839 04-FEB-03       2850                    30
      7782 CLARK      MANAGER         7839 04-FEB-03       2450                    10
      7788 SCOTT      ANALYST         7566 25-JAN-03       3000                    20
      7839 KING       PRESIDENT            04-FEB-03       5000                    10
      7844 TURNER     SALESMAN        7698 04-FEB-03       1500          0         30
      7876 ADAMS      CLERK           7788 25-JAN-03       1100                    20
      7900 JAMES      CLERK           7698 04-FEB-03        950                    30
      7902 FORD       ANALYST         7566 25-JAN-03       3000                    20
      7934 MILLER     CLERK           7782 04-FEB-03       1300                    10

14 rows selected.

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ....ACCOUNTING NEW YORK
        20 ......RESEARCH DALLAS
        30 .........SALES CHICAGO
        40 ....OPERATIONS BOSTON

      DBID NAME      CREATED   RESETLOGS_CHANGE# RESETLOGS PRIOR_RESETLOGS_CHANGE# PRIOR_RES LOG_MODE     CHECKPOINT_CHANGE#
---------- --------- --------- ----------------- --------- ----------------------- --------- ------------ ------------------
ARCHIVE_CHANGE# CONTROL CONTROLFI CONTROLFILE_SEQUENCE# CONTROLFILE_CHANGE# CONTROLFI OPEN_RESETL VERSION_T OPEN_MODE
--------------- ------- --------- --------------------- ------------------- --------- ----------- --------- ----------
 563053257 LOCAL     26-AUG-02           1377079 22-OCT-02                 1351505 21-OCT-02 NOARCHIVELOG            2330831
        2290529 CURRENT 26-AUG-02                  4233             2330831 14-FEB-03 NOT ALLOWED 22-OCT-02 READ WRITE

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
PL/SQL Release 8.1.6.0.0 - Production
CORE    8.1.6.0.0       Production
TNS for 32-bit Windows: Version 8.1.6.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

----------------------------------------------------------------------

C:>type query.bat
@echo off
:loop
if "%1"=="" GOTO continue
        echo set echo off;               > one.sql
        echo set linesize 130            >> one.sql
        echo select * from %1;           >>one.sql
        echo exit;                       >>one.sql
        sqlplus -s mag/mag @one.sql
        goto End
        :End
SHIFT
GOTO Loop
:continue

C:>

Report message to a moderator

Previous Topic: Urgent Problem in utl_file
Next Topic: Which tool can monitor the sql actions issued from the client?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Sep 20 04:59:59 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.