CODEWITHSUNDEEP
oracle-databaseunixsqlplusksh
xGen
xGen

Reputation: 554

return value of select query using oracle sql plus in korn shell

i use below code to return value from oracle. But unable to get the desired result.

EDIT: posting the function with actual values.

function Check_Job_Info 
{
    print "Start week -Check_Job_Info : $start_week " 1>&2;
    print "End Week - Check_Job_Info : $end_week " 1>&2;
    JOBNUM=`sqlplus -s username/pwd@db <<EOF
    SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
    SELECT job_number FROM jobs_list WHERE start_week = 1347 and end_week = 1347 ;
    EXIT;
    EOF`
    print "Job Exists  : $JOBNUM " 1>&2;
}

the value printed are also the same as the hardcoded value

The problem is the value returned doesnt actually exist in the table.So it returns job_number say 1000. and when i do a select using 1000 it returns no rows. the value for start_week and end_week are numeric

JOB_NUMBER                                NOT NULL NUMBER(5)
 TYPE                                      NOT NULL NUMBER(1)
 START_WEEK                                         NUMBER(5)
 END_WEEK                                           NUMBER(5)

What could possibly be wrong in the query? trying to figure this out for the last 2 days.

Basically doing a checking whether a row exists and if not insert new row.

Upvotes: 0

Views: 3014

Answers (1)

Utsav
Utsav

Reputation: 8093

The order of parameter in sqlplus command is wrong. It should be username/password@db . Try connecting to db manually and you will understand.

JOBNUM=`sqlplus -s username/password@db <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
SELECT job_number FROM jobs_List WHERE start_week = $start_week and end_week = $end_week ;
EXIT;
EOF`

Upvotes: 1

Related Questions