Dynamic PL/SQL

From: Viktor <stant_98_at_yahoo.com>
Date: Thu, 21 Dec 2000 13:39:10 -0800 (PST)
Message-Id: <10717.125184@fatcity.com>

--0-610515434-977434750=:5535
Content-Type: text/plain; charset=us-ascii Content-Disposition: inline

Hello all,

I have a dynamic PL/SQL procedure that selects author_name. For some reason author names that contain

"'" cause the procedure to fail.
Does anyone have experienced this before and has a solution?

Attached is a section from the procedure.

Thanks.

---

Do You Yahoo!?
Yahoo! Shopping - Thousands of Stores. Millions of Products. http://shopping.yahoo.com/
--0-610515434-977434750=:5535

Content-Type: text/plain; name="Proc.txt"
Content-Description: Proc.txt
Content-Disposition: inline; filename="Proc.txt"

   LOOP
    /* BUILD THE SELECT STATEMENT */

	  ls_select_statement := 'SELECT J.JOURNAL_ID, M.SPLIT_CODE, P.PUBKEY_JCODE||P.PUBKEY_YRISSUE||P.PUBKEY_MS_SEQNO||P.PUBKEY_CKCHAR, J.JOURNAL_NAME,
                              P.CAS_RECV_DATE , P.PUBLISH_DATE , M.NO_SUPP_PAGES, P.SUPP_MAT_OTHER, REPLACE(MS.AUTHOR_LASTNAME,'''','' '')
                              FROM  '||R_SCHEMA.J_ID||'.JOURNAL J,'||R_SCHEMA.J_ID||'.PUBSTAT P,
                                     '||R_SCHEMA.J_ID||'.MSCRIPT M,'||R_SCHEMA.J_ID||'.MSAUTHOR MS
							  WHERE P.PUBKEY_JCODE=J.JOURNAL_ID
   							  AND   P.PUBKEY_JCODE = M.JOURNAL_ID
   							  AND   P.PUBKEY_YRISSUE = M.YR_OF_ISSUE
   							  AND   P.PUBKEY_MS_SEQNO = M.MS_SEQUENCE_NO
   							  AND   P.PUBKEY_CKCHAR = M.CHECK_CHAR
   							  AND   MS.AUTHMSNO_JCODE(+) = P.PUBKEY_JCODE
   							  AND   MS.AUTHMSNO_YRISSUE(+) = P.PUBKEY_YRISSUE
   							  AND   MS.AUTHMSNO_MS_SEQNO(+)= P.PUBKEY_MS_SEQNO
   							  AND   MS.AUTHMSNO_CKCHAR(+)= P.PUBKEY_CKCHAR
   							  AND   MS.DISPLAY_SEQUENCE = 1
							  AND P.PUBKEY_JCODE||P.PUBKEY_YRISSUE||P.PUBKEY_MS_SEQNO||P.PUBKEY_CKCHAR = ''JA005514C''
							  AND   (M.NO_SUPP_PAGES > 0 OR P.SUPP_MAT_OTHER > 0)
							  AND   P.cas_recv_date between '||''''||sdate ||''''||' and '||''''|| edate ||'''' ;
           li_cursor_id := DBMS_SQL.OPEN_CURSOR;
            DBMS_SQL.parse(li_cursor_id, ls_select_statement,1);
            DBMS_SQL.DEFINE_COLUMN(li_cursor_id, 1, v_journal_id, 2);
            DBMS_SQL.DEFINE_COLUMN(li_cursor_id, 2, v_split_code, 2);
            DBMS_SQL.DEFINE_COLUMN(li_cursor_id, 3, v_msno,9);
            DBMS_SQL.DEFINE_COLUMN(li_cursor_id, 4, v_journal_name,50);
            DBMS_SQL.DEFINE_COLUMN(li_cursor_id, 5, v_recv_date);
            DBMS_SQL.DEFINE_COLUMN(li_cursor_id, 6, v_pub_date);
            DBMS_SQL.DEFINE_COLUMN(li_cursor_id, 7, v_no_supp_pages);
            DBMS_SQL.DEFINE_COLUMN(li_cursor_id, 8, v_supp_mat_other);
            DBMS_SQL.DEFINE_COLUMN(li_cursor_id, 9, v_author_lastname,30);
          li_rc := DBMS_SQL.EXECUTE(li_cursor_id);
  	  LOOP
	    EXIT WHEN DBMS_SQL.FETCH_ROWS(li_cursor_id) = 0;
	        DBMS_SQL.COLUMN_VALUE(li_cursor_id, 1, v_journal_id);
            DBMS_SQL.COLUMN_VALUE(li_cursor_id, 2, v_split_code);
            DBMS_SQL.COLUMN_VALUE(li_cursor_id, 3, v_msno);
            DBMS_SQL.COLUMN_VALUE(li_cursor_id, 4, v_journal_name);
            DBMS_SQL.COLUMN_VALUE(li_cursor_id, 5, v_recv_date);
            DBMS_SQL.COLUMN_VALUE(li_cursor_id, 6, v_pub_date);
            DBMS_SQL.COLUMN_VALUE(li_cursor_id, 7, v_no_supp_pages);
            DBMS_SQL.COLUMN_VALUE(li_cursor_id, 8, v_supp_mat_other);
            DBMS_SQL.COLUMN_VALUE(li_cursor_id, 9, v_author_lastname);
			  ls_insert_statement := 'INSERT INTO  '||use_name||'.manuscript_pages_received VALUES(';
			  ls_insert_statement := ls_insert_statement ||''''||v_journal_id||''''
                                                         ||','||''''||v_split_code||''''


                                             			 ||','||''''||v_msno||''''

                                             			 ||','||''''||v_journal_name||''''

                                             			 ||','||''''||v_recv_date||''''

                                             			 ||','||''''||v_pub_date||''''

                                             			 ||','||v_no_supp_pages||
                                               			 ','||v_supp_mat_other||
                                               			 ','||''''||v_author_lastname||''''||')';
    	 li_cursor_id1 := DBMS_SQL.OPEN_CURSOR;
          DBMS_SQL.PARSE(li_cursor_id1, ls_insert_statement, 1);
         li_rc := DBMS_SQL.EXECUTE(li_cursor_id1);
		dbms_output.put_line (v_msno||' '||v_author_lastname);
          DBMS_SQL.CLOSE_CURSOR(li_cursor_id1);
    END LOOP;
          DBMS_SQL.CLOSE_CURSOR(li_cursor_id);

Received on Thu Dec 21 2000 - 15:39:10 CST