Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2009
    Posts
    2

    Unanswered: How to insert String into CLOB field

    Hi,

    I have a long .log file sitting in the unix file server.
    I convert the file into the string and insert into the clob filed in oracle database.

    but i am getting the following error.

    ERROR:
    ORA-01756: quoted string not properly terminated


    SP2-0734: unknown command beginning "+ ReturnSt..." - rest of line ignored.
    SP2-0734: unknown command beginning "+ [ 0 -ne ..." - rest of line ignored.
    SP2-0734: unknown command beginning "+ myJobRun..." - rest of line ignored.
    SP2-0734: unknown command beginning "+ br_Updat..." - rest of line ignored.
    SP2-0044: For a list of known commands enter HELP
    and to leave enter EXIT.
    SP2-0552: Bind variable "PLS" not declared.
    SP2-0734: unknown command beginning "PL/SQL pro..." - rest of line ignored.
    SP2-0734: unknown command beginning "+ ReturnSt..." - rest of line ignored.
    SP2-0734: unknown command beginning "+ [ 0 -ne ..." - rest of line ignored.
    SP2-0734: unknown command beginning "+ myJobRun..." - rest of line ignored.
    SP2-0044: For a list of known commands enter HELP
    and to leave enter EXIT.
    SP2-0734: unknown command beginning "+ br_Updat..." - rest of line ignored.
    ERROR:
    ORA-01756: quoted string not properly terminated

    My Code

    br_Update_Proxy_Job_Run_Status()
    {
    typeset -i myArgCount=$#

    if [ $myArgCount -lt 2 ]
    then
    br_PrintMessage W \
    "NOT Enough aruments for the function br_Update_Proxy_Job_Run_Status? $Bell"
    return $BR_WARNING_STATUS
    fi

    typeset myFile=""
    typeset myJobRunStatusMsg="$1"
    typeset -i myRunStatusId="$2"
    typeset -i myRptTotalInsertedETL="$3"
    typeset -i myRptTotalUpdatedETL="$4"
    typeset -i myRptTotalErrorETL="$5"
    typeset myLogFile="$8"

    if [ -z "$8" ]
    then
    myFile=""
    else
    myFile=`cat $myLogFile | sed "s/\'/''/g"`
    fi



    echo "file :: $myFile"


    sqlplus -s $DBCredentials <<-EndOFSQL

    SET DEFINE OFF;
    SET SERVEROUT ON

    DECLARE
    i_run_status_id VARCHAR2(100) := $myRunStatusId;
    i_end_time VARCHAR2(1000);
    i_total_errors NUMBER := $myRptTotalErrorETL;
    i_total_warnings VARCHAR2(1000);
    i_total_inserted NUMBER := $myRptTotalInsertedETL;
    i_total_updated NUMBER := $myRptTotalUpdatedETL;
    i_total_rejected NUMBER := 0 ;
    i_log_file CLOB := TO_CLOB('$myFile');
    i_job_run_message VARCHAR2(512) := '$myJobRunStatusMsg';
    i_modlast_by VARCHAR2(1000);
    o_sqlcode NUMBER := 0 ;
    o_sqlmsg VARCHAR2(1000);
    BEGIN
    $USP_UPD_JOB_RUN_STATUS
    (
    i_run_status_id,
    i_end_time,
    i_total_errors,
    i_total_warnings,
    i_total_inserted,
    i_total_updated,
    i_total_rejected,
    i_log_file,
    i_job_run_message,
    i_modlast_by,
    o_sqlcode,
    o_sqlmsg
    );


    END;
    /
    EndOFSQL

    }

    Please help it very urgent

  2. #2
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    Does the string need to have a null terminator?
    You could add a null to the end of the file using: echo "\000\c" >>file

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    The string does not need to have a null terminator, but all single quotes in it must be escaped.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    myFile=`cat $myLogFile | sed "s/\'/''/g"`
    This line should read:
    myFile=`cat $myLogFile | sed "s/\'/\''/g"`

  5. #5
    Join Date
    Sep 2009
    Posts
    4
    i can't do anything..sorry

Posting Permissions

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