5 Replies Latest reply: Mar 12, 2013 4:23 AM by rukbat RSS

    passing parameters in a shell shel script

    Joseph Kay
      Hi everybody,

      I am working on a task which gets report on every remote database.

      I created a small database: ‘REPORTS_DB’ and I created 6 'sql' scripts on that database. While executing the first script, it calls the other 5 different 'sql' scripts. All these 5 sql scripts use a '@db_link' (hard coded) and gets that database's report to the database: REPORT_DB.

      It is working good. But,

      We have 42 databases and I have to get reports of all the 42 databases. Since it is not efficient to create 42 sets of sql scripts, I want to make it automated.

      I know (but I don't know HOW) I can pass a parameter (database link name) while running the first script (which calls the other 5 scripts), so that the parameter replaces the @db_link in all the 5 scripts and gets the report of the related (@db_link) database.

      Could somebody send me a sample shell script to do this task?

      OR, is there any way to do this task without using shell script ?

      (BTW, we are using korn shell on sun Solaris)

      Thanks in advance.
      - JT
        • 1. Re: passing parameters in a shell shel script
          bobthesungeek76036
          Positional parameters are available by number. So if we have the following script (saved as "script1.sh"):

          #!/bin/ksh
          VAR1=$1
          VAR2=$2
          echo "arg 1 is ${VAR1}"
          echo "arg 2 is ${VAR2}"

          And we call it like:

          ./script1.sh abc def

          The output of the script will be:

          arg 1 is abc
          arg 2 is def
          • 3. Re: passing parameters in a shell shel script
            Joseph Kay
            Hi everybody,

            I posted same question earlier for UNIX/shell script and rec'd one response. Thanks to Bob.
            But the requirement has been changed and now my mgr wants the same task using PL/SQL. Could somebody help me ?

            Question again....

            I am working on a task which gets report on every remote database.

            I created a small database: ‘REPORTS_DB’ and I created 6 'sql' scripts on that database. While executing the first script, it calls the other 5 different 'sql' scripts. All these 5 sql scripts use a '@db_link' (hard coded) and gets that database's report to the database: REPORT_DB.

            It is working well. But,

            We have 42 databases and I have to get reports of all the 42 databases. Since it is not efficient to create 42 sets of sql scripts, I want to make it automated.

            I know (but I don't know HOW) I can pass a parameter (database link name) while running the first script (which calls the other 5 scripts), so that the parameter replaces the @db_link in all the 5 scripts and gets the report of the related (@db_link) database.

            Could somebody tell me how to do this task using SQL and PL/SQL ?
            OR
            send me a sample PL/SQL script to do this task?

            Sorry for the inconvenience and thanks in advance.
            - JT
            • 4. Re: passing parameters in a shell shel script
              956800
              Hi JT,

              PL/SQL uses &1, &2... &n to access the parameters.
              Please go through the below link. It should throw some light to your queries.

              http://plsql-tutorial.com/plsql-passing-parameters-procedure-function.htm

              Regards,
              Vinay
              • 5. Re: passing parameters in a shell shel script
                rukbat
                Joseph Kay wrote:
                Hi everybody,

                Could somebody tell me how to do this task using SQL and PL/SQL ?
                OR
                send me a sample PL/SQL script to do this task?
                This thread was posted to a Solaris forum.
                Your new question isn't an OS-related question.
                You should have posted that new question as a separate post to a SQL forum, perhaps even:
                SQL and PL/SQL
                If you thought there was any relevance to this thread, you could have pasted a link to it in your new post.

                That SQL forum exists for only one reason -- for SQL and PL/SQL topics.
                Who know? Your question and its solution may already be in there from past postings.