This discussion is archived
3 Replies Latest reply: Sep 16, 2013 11:21 AM by user6551037 RSS

Strings in anonymous block, single quote added automatically

hekuran23 Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated

    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points