Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to pass string with a ' to PL/SQL

RE: How to pass string with a ' to PL/SQL

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Wed, 11 Jun 2003 05:41:05 -0700
Message-ID: <F001.005AEF79.20030611051422@fatcity.com>

<SPAN

class=184225911-11062003>Lizz,
<SPAN

class=184225911-11062003> 
Remove
the 'execute' from your PL/SQL block.  it is not needed - in fact - it is causing the error.
<SPAN

class=184225911-11062003> 
<SPAN

class=184225911-11062003>"Execute" is only needed within sqlplus when not using a PL/SQL block.  You are using an anonymous PL/SQL block to call your procedure.
<SPAN

class=184225911-11062003> 
Good
Luck!
Tom Mercadante <FONT face=Arial
size=2>Oracle Certified Professional

  <FONT face=Tahoma
  size=2>-----Original Message-----From: laura pena   [mailto:[EMAIL PROTECTED]Sent: Tuesday, June 10, 2003 5:15   PMTo: Multiple recipients of list ORACLE-LSubject: RE:   How to pass string with a ' to PL/SQL   

  Here you go... So far I have not gotten it to work... I have tried all   suggestions so far.
   
  Seems to work if I just do it to a variable but once I call in the stored   proc. It fails to work.
   
  Thanks ....
  -Lizz
   
  "Jamadagni, Rajendra"
  <[EMAIL PROTECTED]> wrote:
  <BLOCKQUOTE
  style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid">     

    <FONT face="Courier New" color=#0000ff     size=2>Show us the package body ... not the spec.     <FONT face="Courier New" color=#0000ff     size=2> 
    <FONT face="Courier New" color=#0000ff     size=2>Raj
    <FONT face="Courier New"

    size=2>-------------------------------------------------------------------------------- 
    Rajendra dot Jamadagni at nospamespn dot     com All Views expressed in this
    email are strictly personal. <FONT face="Courier New"     size=2>QOTD: Any clod can have facts, having an opinion is an art !          
      <FONT face=Tahoma 
      size=2>-----Original Message-----From: laura pena 
      [mailto:[EMAIL PROTECTED]Sent: Tuesday, June 10, 2003 
      2:44 PMTo: Multiple recipients of list 
      ORACLE-LSubject: RE: How to pass string with a ' to 
      PL/SQL
      This does not work when executing my stored procedure:
       
      SQL> var a refcursorSQL> var b varchar2(100);SQL> 
      begin  2     :b :='and sub_account_no=' || 
      '''' || '864240103' || '''';  3     execute 
      :a := pkg_reports_Dynamiccti.fu_sales_analysis('2003-05-21 
      00:00:00','2003-06-02 00:00:00','20','864240103','order by calldate 
      desc',:b);  4  end;  5  /   
      execute :a := 
      pkg_reports_Dynamiccti.fu_sales_analysis('2003-05-2100:00:00','2003-06-02 
      00:00:00','20','864240103','order by calldate 
      desc',:b);           
      *ERROR at line 3:ORA-06550: line 3, column 12:PLS-00103: 
      Encountered the symbol "" when expecting one of the following::= . ( @ 
      % ; immediateThe symbol ";" was substituted for "" to 
      continue.
       
      The package body is defined as:
      CREATE OR REPLACE PACKAGE pkg_reports_dynamiccti AS  -- 
      Function fu_sales_analysis. Return summary information  -- about 
      sales analysis for period of time for NetOne Reports from  -- 
      customerinfo table.  -- Input: begin Date, end date, center_id, 
      account -- Output: reference cursor FUNCTION 
      fu_sales_analysis (in_begdate IN VARCHAR2, in_enddate IN VARCHAR2, 
                                   
      in_center IN VARCHAR2, in_acct IN 
      VARCHAR2,                             
      in_orderbyClause IN VARCHAR2, 
                                   
      in_whereclause IN VARCHAR2)    RETURN 
      pkg_cursors.ref_cursor; FUNCTION fu_sales_analysis_address 
      (in_customerinfoid IN NUMBER, in_addressType IN 
      NUMBER)    RETURN pkg_cursors.ref_cursor;END 
      pkg_reports_dynamiccti;/
      this works:
      SQL> var a refcursorSQL> var b varchar2(100);SQL> 
      begin  2     :b :='and sub_account_no=' || 
      '''' || '864240103' || '''';  3  end;  4  
      /
      PL/SQL procedure successfully completed.
      SQL> print b
      B-------------------------------------------------------------------------
      and sub_account_no='864240103'
       *********************************************************************This 
    e-mail message is confidential, intended only for the named recipient(s)     above and may contain information that is privileged, attorney work product     or exempt from disclosure under applicable law. If you have received this     message in error, or are not the named recipient(s), please immediately     notify corporate MIS at (860) 766-2000 and delete this e-mail message from     your computer, Thank
    you.*********************************************************************1
  
  

  Do you Yahoo!?Free <A
  href="http://us.rd.yahoo.com/mail_us/tag/*http://calendar.yahoo.com">online   calendar with sync to Outlook(TM). Received on Wed Jun 11 2003 - 07:41:05 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US