Home » SQL & PL/SQL » SQL & PL/SQL » escape sequence in pl/sql (oracle)
escape sequence in pl/sql [message #435843] Fri, 18 December 2009 06:46 Go to next message
neha7.m
Messages: 6
Registered: December 2009
Junior Member

hi, I need to execute a query :

SQL> begin
2 execute immediate 'Insert into job_run_statistics select * from job_run_statistics_archieve where to_char(DM_LSTUPDDT,'YYYYMMDD') > (select to_char(add_months(sysdate, -6),'YYYYMMDD') from dual)';
3 end;
4 /

It throws this error:

execute immediate 'Insert into job_run_statistics select * from job_run_statistics_archieve where to_char(DM_LSTUPDDT,'YYYYMMDD') > (select to_char(add_months(sysdate, -6),'YYYYMMDD') from dual)'; * ERROR at line 2: ORA-06550: line 2, column 120: PLS-00103: Encountered the symbol "YYYYMMDD" when expecting one of the following: . ( * @ % & = - + ; < / > at in is mod not rem return returning <> or != or ~= >= <= <> and or like between into using || bulk \PLEASE HELP!!
Re: escape sequence in pl/sql [message #435846 is a reply to message #435843] Fri, 18 December 2009 06:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, you don't need to use execute immediate for this query - you only need that if the query has some sort of dynamic component that can't be handled by bind variables (such as a variable list of columns).

It's also what's giving you the error - because your query is contained in a string, the single quotes need to be double quotes in order to avoid being interpreted as 'end of string' markers.

Also, you don't need to use SELECT to evaluate a TO_CHAR or ADD_MONTHS function - I'm guessing you did T-SQL before you came to Pl/Sql

Also also, you don't need to convert dates into strings to compare them.

So, the best solution would be:
begin 
  Insert into job_run_statistics 
  select * 
  from   job_run_statistics_archieve 
  where  trunc(DM_LSTUPDDT) > add_months(sysdate, -6); 
end; 


Re: escape sequence in pl/sql [message #435857 is a reply to message #435846] Fri, 18 December 2009 07:47 Go to previous messageGo to next message
neha7.m
Messages: 6
Registered: December 2009
Junior Member
But waht if I NEED to write the query that way. Like;

Begin
execute immediate 'Create index IDX_BATCH_RUN_STATISTICS_2 on BATCH_RUN_STATISTICS (to_char(DM_LSTUPDDT,'YYYYMMDD'))
';
END;
/
Re: escape sequence in pl/sql [message #435860 is a reply to message #435857] Fri, 18 December 2009 08:00 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As JRowbottom said:
Quote:
It's also what's giving you the error - because your query is contained in a string, the single quotes need to be double quotes in order to avoid being interpreted as 'end of string' markers.

You have to double the single quotes inside the string.

Regards
Michel
Re: escape sequence in pl/sql [message #435877 is a reply to message #435857] Fri, 18 December 2009 08:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You REALLY, REALLY don't want to create Tables, indexes, etc from pl/sql. Just do it from SQL instead.

If you're thinking 'But I need to create and index temporary tables at runtime' then the answer is 'No you don't - this is Oracle. You almost never need temporary tables to handle intermediate stages in queries, and in the rare case when you do, you create a proper, permenant temporary table (it's a special type of table that writes no Redo to the logs files, and only holds data for transient times) and that way all of your users can use it.
Re: escape sequence in pl/sql [message #435887 is a reply to message #435843] Fri, 18 December 2009 09:52 Go to previous messageGo to next message
mnitu
Messages: 159
Registered: February 2008
Location: Reims
Senior Member
Starting with Oracle 10 en alternative quoting mechanism is available

begin 
 execute immediate q'[Insert into job_run_statistics 
select * from job_run_statistics_archieve 
where to_char(DM_LSTUPDDT,'YYYYMMDD') > 
(select to_char(add_months(sysdate, -6),'YYYYMMDD') from dual)]'; 
end; 
/

[Updated on: Wed, 06 January 2010 10:46] by Moderator

Report message to a moderator

Re: escape sequence in pl/sql [message #435889 is a reply to message #435887] Fri, 18 December 2009 10:07 Go to previous messageGo to next message
ThomasG
Messages: 3184
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
mnitu wrote on Fri, 18 December 2009 16:52
Starting with Oracle 10 en alternative quoting mechanism is available


However, shooting yourself in the foot by doing it in a completely wrong way doesn't get better just because there is an easier way available to hold the gun.
Re: escape sequence in pl/sql [message #435905 is a reply to message #435889] Fri, 18 December 2009 14:36 Go to previous messageGo to next message
mnitu
Messages: 159
Registered: February 2008
Location: Reims
Senior Member
ThomasG wrote on Fri, 18 December 2009 17:07
mnitu wrote on Fri, 18 December 2009 16:52
Starting with Oracle 10 en alternative quoting mechanism is available


However, shooting yourself in the foot by doing it in a completely wrong way doesn't get better just because there is an easier way available to hold the gun.


@Thomas
It seems to me that others persons have already pointed out what's wrong with the original query. So what's your "completely wrong way" about ?
Re: escape sequence in pl/sql [message #435906 is a reply to message #435843] Fri, 18 December 2009 15:52 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
DM_LSTUPDDT - what is this? Column in some table ?!?

BEGIN EXECUTE IMMEDIATE '<Static SQL query>';END; => SQL compilation, extra work overhead for Oracle and some other problems.
BEGIN <Static SQL query>;END; => Better but you don't need PL/SQL here unless I am missing something
<Static SQL query> => seems most reasonable.

TO_CHAR(date,format)>'abcdefghijklmn' => yes, you compare strings... why the hell anyone would do such thing?

date<TO_DATE('2009-12-31','YYYY-MM-DD') => date comparison here

date>(SELECT 1 FROM DUAL) => Wouldn't it be better to use just date>1 ?!?

date>addmonths(sysdate,-6) => I guess that there is an index on that table. And probably you are going to use it. Note that Oracle would read index from sysdate-6months to infinity so it might become a problem if you change this to a finished range (like BETWEEN).

Are you sure that you want to compare the date with SYSDATE-6months? Do you know that DATE contains hour,minute and second part? If the column holds year/month only then why not storing it as a number?

And finally - why would you need to clone the data? I hope that you are not going to execute something like "insert into other_table select from source_table;delete from source_table;".

Good luck
Re: escape sequence in pl/sql [message #436060 is a reply to message #435906] Mon, 21 December 2009 04:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Are you sure that you want to compare the date with SYSDATE-6months? 
Do you know that DATE contains hour,minute and second part? 
If the column holds year/month only then why not storing it as a number?


You store it in a DATE column because it is date information, and DATE columns are where you store dates.

If you store it in a number / varchar2 column then you can, by dint of some work get the same level of validation of data that you do with a date column, but you then have to remember to change it to a date every time you want to compare a date to it.

Really - just store dates in DATE columns - it's easier and safer.

[Updated on: Wed, 06 January 2010 10:47] by Moderator

Report message to a moderator

Re: escape sequence in pl/sql [message #437316 is a reply to message #436060] Sun, 03 January 2010 17:36 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
JRowbottom wrote on Mon, 21 December 2009 11:44
Really - just store dates in DATE columns - it's easier and safer.

And keep in mind that this advice it is not always the best option.
Re: escape sequence in pl/sql [message #437319 is a reply to message #437316] Sun, 03 January 2010 19:14 Go to previous messageGo to next message
BlackSwan
Messages: 24903
Registered: January 2009
Senior Member
wakula wrote on Sun, 03 January 2010 15:36

And keep in mind that this advice it is not always the best option.


Keep in mind that with free advice, sometime you get what you paid for it.
Re: escape sequence in pl/sql [message #437812 is a reply to message #437316] Wed, 06 January 2010 02:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
Quote:
Really - just store dates in DATE columns - it's easier and safer.


And keep in mind that this advice it is not always the best option.


Really, I'm going to have to call you on this.
I'd like you to support this claim by detailing a situation in which storing dates in DATE type columns is not the best solution.
Re: escape sequence in pl/sql [message #437956 is a reply to message #437812] Wed, 06 January 2010 10:33 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
JRowbottom wrote on Wed, 06 January 2010 09:26
Really, I'm going to have to call you on this.
I'd like you to support this claim by detailing a situation in which storing dates in DATE type columns is not the best solution.

create table a_1(d date,v number);
create table a_2(d number,v number);
insert into a_1 values (to_date('2009-01-01','yyyy-mm-dd'),1);
insert into a_2 values (to_number(to_char(to_date('2009-01-01','yyyy-mm-dd'),'J')),1);

select d,v,(vsize(d)+vsize(v)) as "total size", vsize(d) as "date size" from a_1;
D                       V total size  date size
------------------- ----- ---------- ----------
2009-01-01 00:00:00     1          9          7

select d,v,(vsize(d)+vsize(v)) as "total size", vsize(d) as "date size" from a_2;
                  D     V total size  date size
------------------- ----- ---------- ----------
            2454833     1          7          5

DATE is using 7 bytes. Number is using variable number of bytes (here it is 5).
By truncating the time part we have saved >20% of the disk space.
With some additional operation (year manipulation or using some different function to convert date into a number) we can save additional disk space (to get 1-2 bytes).
Cost: worse readability, possibly higher CPU load, no time.

Sometime it is good that we don't have the time.
Sometime we need only a part of the date (ex. day of week).
AFAIK - Oracle does not offer any type that holds only YYYY-MM-DD.
Re: escape sequence in pl/sql [message #437960 is a reply to message #435843] Wed, 06 January 2010 10:50 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Of course in any real database the chances are that the amount of space occupied by date columns is going to be massively outweighed by varchar and other types, so you really aren't ever going to notice that 2 byte saving. And if you are going to notice it then you probably need to buy bigger disks.

If you want no time what is the problem (other than the 2 bytes) with setting it to midnight, which is the standard way?

And if you literally just want a day of the week - well that's not really a date is it? That I would store in a varchar or number.
Re: escape sequence in pl/sql [message #437962 is a reply to message #437956] Wed, 06 January 2010 10:52 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Cost: worse readability, possibly higher CPU load, no time.

And fool Oracle optimizer on the real functional datatype.
And how do you add/subtract 6 months with numbers or make any date arithmetic?

I don't see any case it is better to use number instead of date.
And you DO NOT save 20% of space, how many dates column have you in the table? how many tables containing dates? If you save 1% you are lucky.

Regards
Michel

[Updated on: Wed, 06 January 2010 10:53]

Report message to a moderator

Re: escape sequence in pl/sql [message #437963 is a reply to message #437956] Wed, 06 January 2010 10:57 Go to previous messageGo to next message
BlackSwan
Messages: 24903
Registered: January 2009
Senior Member
>Cost: worse readability, possibly higher CPU load, no time.
include dealing with "date" like 20101332; which would be allowed in NUMBER field.
Re: escape sequence in pl/sql [message #437968 is a reply to message #437960] Wed, 06 January 2010 11:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In a situation where:
1) you had a very high percentage of your data as dates,
2) the volume of the data on disk was the single biggest priority affecting application design
3) you couldn't use anything sensible like Oracle table compression
4) You'd already done everything else you can do to save space

then I might consider an approach like that, but other than that, I honestly think it would be a very bad idea.
It's never going to be the best solution - the most it can be is making the best of a bad situation.

The 'we can save two bytes per field' mindset is the one that gave us the Y2K problem, if you remember that far back.

[Updated on: Wed, 06 January 2010 11:22]

Report message to a moderator

Re: escape sequence in pl/sql [message #438021 is a reply to message #437968] Wed, 06 January 2010 15:12 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
I consider that I have supported "this claim by detailing a situation in which storing dates in DATE type columns is not the best solution".

I agree that in most cases the DATE type would be the best.

I do not agree that dealing with date like 20101231 is the case in my example as I have specified Julian date.
It would be the case if I have used nasty date like TO_DATE('2099-12-31','YYYY-MM-DD') => 20991231=0x1404cff => 4+1 bytes(?).

Since this is a Julian date - I can easily use it for operations like adding 6 months: ADD_MONTHS(TO_DATE(d,'J')).

Conversion between Julian date and DATE might be however an overhead for the CPU computation - which has been marked.

Amount of data saved in the presented case is 7-5=2 bytes. Is we can restrict the dates in let say 2000-01-01/2099-12-31 then 100*365.25=36525=0x8EAD => 2+1 bytes so we would save 5 bytes per single date (unless I did something wrong in the calculations which is likely). For some DBs this might be significant, although it usually wouldn't matter. Oracle DB is not the smallest one - if you are looking for small DB then ex. Google's sparse DB can hold a single NULL in a single bit.

I agree that this is usually the best option to use DATE. However usually!=always.
Re: escape sequence in pl/sql [message #438113 is a reply to message #438021] Thu, 07 January 2010 03:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm afraid we will have to disagree on this - what you have provided is information about a minor beneficial side effect of using a poor practice. This is not the same as providing a situation in which this practice would be better than using DATE datatypes.

Let me rephrase - have you ever actually recomended using this approach in a production application, on an Oracle database?
Re: escape sequence in pl/sql [message #438182 is a reply to message #438113] Thu, 07 January 2010 06:35 Go to previous message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
JRowbottom wrote on Thu, 07 January 2010 10:24
Let me rephrase - have you ever actually recomended using this approach in a production application, on an Oracle database?
Not yet.
Previous Topic: oracle 10g
Next Topic: Help in PROCEDURE
Goto Forum:
  


Current Time: Sun Sep 25 04:25:05 CDT 2016

Total time taken to generate the page: 0.14768 seconds