Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2012
    Posts
    4

    Unanswered: Parameter Passed in Unix to Oracle statement

    Hello,

    I am trying to call a PL/SQL procedure through unix shell script and I am facing following issue.
    ORA-06550: line 1, column 7:
    PLS-00201: identifier 'CFDSYSDB.TABLE_LIST_COLUM' must be declared
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

    My unix script is as below.
    echo "Oracle Spooling for ${2} starts"
    TableName=${1}

    sqlplus -s cfduser/walnut45@acp0cfd <<!
    execute CFDSYSDB.TABLE_LIST_COLUM('CFDSYSDB','TCFDR_AGENT' ,'N');
    !
    User(CFDUSER) has exec privilages on the procedure. When I execute this procedure from SQL developer with same user it is running fine but through unix script it is throwing the error mentioned above.

    Thanks in anticipation.

  2. #2
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    I don't see any direct reference to $Tablename in the sqlplus input, so if you think that sqlplus will automatically see $Tablename as an environment variable, then you should export it.
    Code:
    Tablename=${1}
    export Tablename
    If your original statements runs through an interactive session, then run 'env' to list all the environment variables and compare that list to the list from your script (add an env statement to the script.)

  3. #3
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    We really need to see the procedure spec(i.e. IN and OUT parameters).
    The below code is one example of running a procedure in a ksh shell script:

    Code:
    # Run procedure(cfdsysdb.table_list_colum)
    print `date "+%Y-%m-%d-%H.%M.%S"` "**** Running procedure: cfdsysdb.table_list_colum"
    run_procedure_results=`sqlplus -s cfduser/walnut45@acp0cfd << ENDSQL
    set linesize 500 heading off feedback off
    whenever sqlerror exit 1
    execute cfdsysdb.table_list_colum('CFDSYSDB','TCFDR_AGENT' ,'N');
    exit 0
    ENDSQL`
    if [[ $? -eq 1 ]] then
      ts=`date "+%Y-%m-%d-%H.%M.%S"`
      print "`echo "$run_procedure_results" | sed 's/^[ \t\n]*/'$ts' /'`"
      print `date "+%Y-%m-%d-%H.%M.%S"` "The above error occurred running procedure: cfdsysdb.table_list_colum"
      exit 1
    fi
    print `date "+%Y-%m-%d-%H.%M.%S"` "**** Finished running procedure: cfdsysdb.table_list_colum"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •