0

I have written a below block of code

#!/bin/bash

TABLE_NAME="${1}"
COL_NAME="${2}"

FIELD_VALUES_1SQ_FUNC()
{
        FIELD_VALUES_1SQS=`sqlplus -s sab/admin@TERM << EOF
        SET FEEDBACK OFF;
        SET HEADING OFF;
        Select TESTING.FIELD_VALUES_TEMP_1SQ.NEXTVAL from dual;
        exit;
EOF`
        FIELD_VALUES_1SQ=`echo ${FIELD_VALUES_1SQS} | tr -d ' '`
}

RT_SEQ_CHECK_FUNC()
{
        RT_SEQ_CHECKS=`sqlplus -s sab/admin@TERM << EOF
        SET FEEDBACK OFF;
        SET HEADING OFF;
        Select * from TESTING.FIELD_VALUES where FIELD_ROW_ID='${1}' and TF_ID='${2}';
        exit;
EOF`
        RT_SEQ_CHECK=`echo ${RT_SEQ_CHECKS} | tr -d ' '`
}

RT_FIELD_IDS_FUNC()
{
        RT_FIELD_IDS=`sqlplus -s sab/admin@TERM << EOF
        SET HEADING OFF;
        SET FEEDBACK OFF;
        select max(TF_ID) from TESTING.TABLE_FIELD where field_id in(select field_id from TESTING.FIELD_DOMAIN where name='${2}') and table_id in (select table_id from TESTING.TABLE where name='${1}');
        EXIT;
EOF`
        RT_FIELD_ID=`echo ${RT_FIELD_IDS} | tr -d ' '`
}

        FIELD_VALUES_1SQ_FUNC

        RT_FIELD_IDS_FUNC ${TABLE_NAME} ${COL_NAME}

        RT_SEQ_CHECK_FUNC ${FIELD_VALUES_1SQ} ${RT_FIELD_ID}
        if [ -z "${RT_SEQ_CHECK}" ]
        then
                echo "Sequence values doesn't exist |--${RT_SEQ_CHECK}--|"
        else
                echo "SEQUNCE VAlue exists |--${RT_SEQ_CHECK}--|"
        fi
echo "TF_ID=${FIELD_VALUES_1SQ}"
echo "FIELD_ROW_ID=${RT_FIELD_ID}"
exit $?

In my script, at first I am calling the function FIELD_VALUES_1SQ_FUNC to generate a sequence number.

Second, I am calling RT_FIELD_IDS_FUNC ${TABLE_NAME} ${COL_NAME} where it will get some value.

Third, the function RT_SEQ_CHECK_FUNC ${FIELD_VALUES_1SQ} ${RT_FIELD_ID} is called, where it checks if the value is there in database. If the value is there, then I should call the FIELD_VALUES_1SQ_FUNC() again to generate a new sequence value and check it with RT_SEQ_CHECK_FUNC ${FIELD_VALUES_1SQ} ${RT_FIELD_ID} function unless the value is not found for that select in FIELD_VALUES_1SQ_FUNC() function.

Any ideas on how this can be achieved!

1

What you're looking for is called a while loop. Consider this simple example:

n=0
while [ $n -lt 5 ]; do
  echo Not done yet
  n=$(($n+1))
done

A while loop does two things, and by implication the programmer must do a third thing.

  1. The while loop tests the condition: is n less than 5?
  2. If the condition is true, then:

    • the body of the while loop is executed once
    • the while loop goes back to step 1 and tests the condition again

If the condition is not true, the loop terminates and script execution continues with the statement that follows the done keyword of the loop.

The third thing, the one that is the programmer's responsibility, is to do something inside the body of the loop that will (or might) change the status of the conditional expression. In the simple example above, that step is the n = $(($n+1)) statement. Without this, the loop will become infinite because the condition is initially true and never changes. Try running the script with that line commented out and see what happens. Then press CtrlC.

To tailor this example to your specific problem, I think you'll want to negate the test [ -z "${RT_SEQ_CHECK}" ] for your while condition. By that, I mean that when [ -z "${RT_SEQ_CHECK}" ] is true, that means ${RT_SEQ_CHECK} is zero-length, and that's when you want to stop looping. Fortunately test has the -n option which is the exact opposite of the -z option.

So in very broad terms, your while loop will look loosely like this:

FIELD_VALUES_1SQ_FUNC
RT_FIELD_IDS_FUNC ${TABLE_NAME} ${COL_NAME}
RT_SEQ_CHECK_FUNC ${FIELD_VALUES_1SQ} ${RT_FIELD_ID}

while [ -n "${RT_SEQ_CHECK}" ]; do

    FIELD_VALUES_1SQ_FUNC
    RT_FIELD_IDS_FUNC ${TABLE_NAME} ${COL_NAME}
    RT_SEQ_CHECK_FUNC ${FIELD_VALUES_1SQ} ${RT_FIELD_ID}

done

Finally, I have which I hope is a constructive comment on the structure of your code. You tend to use global variables to return a value from a function, and then refer to those global variables in the main body of your code. This can make the code difficult to read and follow. Rather than coding in this style:

STEP1() {
  DATE=$(date)
}

STEP2() {
  echo "today is $DATE"
}

STEP1
STEP2

Try this:

STEP1() {
  date
}

STEP2() {
  echo "today is $1"
}

DATE="$(STEP1)"
STEP2 "$DATE"

Again, applying that your code might result in something sort of like this:

FIELD_VALUES_1SQ_FUNC()
{
        sqlplus -s sab/admin@TERM << EOF | tr -d ' '
        SET FEEDBACK OFF;
        SET HEADING OFF;
        Select TESTING.FIELD_VALUES_TEMP_1SQ.NEXTVAL from dual;
        exit;
EOF 
}

RT_SEQ_CHECK_FUNC()
{
        sqlplus -s sab/admin@TERM << EOF | tr -d ' '
        SET FEEDBACK OFF;
        SET HEADING OFF;
        Select * from TESTING.FIELD_VALUES where FIELD_ROW_ID='${1}'
                  and TF_ID='${2}';
        exit;
EOF 
}

RT_FIELD_IDS_FUNC()
{
        sqlplus -s sab/admin@TERM << EOF | tr -d ' '
        SET HEADING OFF;
        SET FEEDBACK OFF;
        select max(TF_ID) from TESTING.TABLE_FIELD 
           where field_id in (select field_id from TESTING.FIELD_DOMAIN where name='${2}') 
             and table_id in (select table_id from TESTING.TABLE where name='${1}');
        EXIT;
EOF 
}

        FIELD_VALUES_1SQ="$(FIELD_VALUES_1SQ_FUNC)"

        RT_FIELD_ID="$(RT_FIELD_IDS_FUNC ${TABLE_NAME} ${COL_NAME})"

        RT_SEQ_CHECK="$(RT_SEQ_CHECK_FUNC ${FIELD_VALUES_1SQ} ${RT_FIELD_ID})"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.