PL/SQL proce thru Unix script

[ajayarora]

Hi,

A stored proc is being called thru a unix and therefore the parameters to the procedure are enclosed in single quotes.But whenever a parameter containing single quote in its value is passed to the proc, the procedure fails with the error message :
'quoted string not properly terminated'

Can anyone suggest anything to overcome this problem.

Regards
Ajay.

 

[sem]

All single quotes should be doubled.

Regards, Dima

 

[sybaseguru]

You can also try to use the backslash \ before single quotes in your script. The same applies to select from tables v$?. For example to run the following in SQL*Plus via UNIX you do:

function get_log_archive_dest {
        WORK_FILE="${LOGDIR}/${ORACLE_SID}_${FILE_NAME}_$0.wrk"
        ${ORACLE_HOME}/bin/sqlplus -S &quot;/ as sysdba&quot; << ! > ${WORK_FILE}
        set heading off feedback off serveroutput on linesize 132
        DECLARE
                 v_log_archive_dest v\$parameter.value%TYPE;
        BEGIN
                SELECT
                        value
                INTO
                        v_log_archive_dest
                FROM
                        v\$parameter
                WHERE
                        name = 'log_archive_dest';
                DBMS_OUTPUT.PUT_LINE(v_log_archive_dest);               -- use this to get rid of heading blanks in count(*)
        EXCEPTION
                WHEN OTHERS
                THEN
                        DBMS_OUTPUT.PUT_LINE('Error code and description: '||sqlcode||' '||sqlerrm);
        END;
/
        exit
!
        LOG_ARCHIVE_DEST=`cat ${WORK_FILE}`
        if [[ -z ${LOG_ARCHIVE_DEST} ]]
        then
                send_alert &quot;Could not get LOG_ARCHIVE_DEST parameter instance ${ORACLE_SID}&quot;
        else
                export LOG_ARCHIVE_DEST
        fi
}

In a similar way you can build quotes within quotes in shell script.

function restore_tempfiles_script {
        echo "`date` $0: Creating script for tempfiles " | tee -a ${LOG_FILE}
        get_db_block_size
        ${ORACLE_HOME}/bin/sqlplus -S &quot;/ as sysdba&quot; << ! > ${RESTORE_TEMPFILES}
        set heading off feedback off linesize 132
        SELECT
                (CASE
                 when autoextensible = 'YES'
                 THEN
                        'alter tablespace '||tablespace_name||' add tempfile '''||file_name||''' size '||bytes
                        ||' reuse autoextend '||decode(autoextensible,'YES','ON','NO','OFF')||' next '
                        || increment_by*${DB_BLOCK_SIZE}||' maxsize '||round(maxbytes/1024/1024)||'M;'
                 ELSE
                        'alter tablespace '||tablespace_name||' add tempfile '''||file_name||''' size '||bytes
                        ||' reuse autoextend '||decode(autoextensible,'YES','ON','NO','OFF')||';'
                 END) &quot;description&quot;
        FROM
                dba_temp_files
        ORDER BY
                tablespace_name;
        exit
!
        if [ $? != 0 ]
        then
                send_alert &quot;Could not create tempfile reuse script&quot;
        else
                cat ${RESTORE_TEMPFILES} |sed -e '/^$/d' > /tmp/temp_{ORACLE_SID}.sql
                mv /tmp/temp_{ORACLE_SID}.sql ${RESTORE_TEMPFILES}
        fi
}

Good luck

 

[ajayarora]

Thanks for your reply.

I'll try to implement this.