3 Replies Latest reply: Sep 16, 2013 1:21 PM by user6551037 RSS

    Strings in anonymous block, single quote added automatically

    hekuran23
      Hi All

      Version of Oracle:
      Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
      PL/SQL Release 10.2.0.5.0


      I have a below anonymous block put in PL/SQL code of Job Scheduler.

      -----
      DECLARE
      --n number(5);
      v_name           VARCHAR2(50);
      v_diskusage     NUMBER;

      CURSOR c_count IS

      SELECT 'Database '||b.name ||', disk '||a.name||', used % '||round(round((total_mb-free_mb)/1024, 2)/round(total_mb/1024,2)*100,2), round(round((total_mb-free_mb)/1024, 2)/round(total_mb/1024,2)*100,2)
      FROM v$asm_diskgroup a, v$database b
      WHERE round(round((total_mb-free_mb)/1024, 2)/round(total_mb/1024,2)*100,2)>60; -- Alert Limit

      BEGIN
      OPEN c_count;

      LOOP
      FETCH c_count INTO v_name, v_diskusage;
      EXIT WHEN c_count%notfound;
      .....
      ...


      However once I saved it adds a *'* single quote in every string I have. Did anybody encountered something similar. Any suggestion

      -----
      DECLARE
      v_name VARCHAR2(50);
      v_diskusage NUMBER;

      CURSOR c_count IS

      SELECT ''Database ''||b.name ||'', disk ''||a.name||'', used % ''||round(round((total_mb-free_mb)/1024, 2)/round(total_mb/1024,2)*100,2), round(round((total_mb-free_mb)/1024, 2)/round(total_mb/1024,2)*100,2)
      FROM v$asm_diskgroup a, v$database b
      WHERE round(round((total_mb-free_mb)/1024, 2)/round(total_mb/1024,2)*100,2)>60; -- Alert Limit

      BEGIN
      OPEN c_count;

      LOOP
      FETCH c_count INTO v_name, v_diskusage;
      EXIT WHEN c_count%notfound;
      .....
      ...


      Thank you very much in advance.
      Regards

      Edited by: hekuran23 on May 2, 2013 10:18 AM
        • 1. Re: Strings in anonymous block, single quote added automatically
          spajdy
          String in PL/SQL is enclosed in '. So when you want put ' in string you must double it.
          '' - empty string
          '''' - string with one '

          If you use dbms_scheduler package to define job it up to you put connrect string as parameter.
          If you use some tool with GUI it probably do it for you automaticly.
          • 2. Re: Strings in anonymous block, single quote added automatically
            hekuran23
            String in PL/SQL is enclosed in '. So when you want put ' in string you must double it.
            '' - empty string
            '''' - string with one '

            If you use dbms_scheduler package to define job it up to you put connrect string as parameter.
            If you use some tool with GUI it probably do it for you automaticly.

            Thank you for your reply, but I don't know if you got the question. The thing is that when we run that anonymous block in any Client like TOAD, or SQL Developer, it works fine. Once we paste the same code to PL/SQL Block of Job Scheduler in Enterprise Manager, after saving it adds the single quote to all the strings.


            Originally it's like this
            SELECT 'Database '||b.name ||', disk '||a.name||', used % '....


            After saving the job in Enterprise Manager it changes to this:

            SELECT ''Database ''||b.name ||'', disk ''||a.name||'', used % '' ...

            Thank you.
            • 3. Re: Strings in anonymous block, single quote added automatically
              user6551037

              Yes, we have exactly the same problem.  As a work around, we created a procedure and then scheduled the job to kick off the procedure instead of using an anonymous PL/SQL block.  We've discovered this issue on all of our environments, which is forcing us to use this workaround for many of our new and existing jobs that need to be modified.