escape sequence in pl/sql [message #435843] |
Fri, 18 December 2009 06:46  |
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   |
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   |
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 #435877 is a reply to message #435857] |
Fri, 18 December 2009 08:52   |
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 #435889 is a reply to message #435887] |
Fri, 18 December 2009 10:07   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
mnitu wrote on Fri, 18 December 2009 16:52Starting 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   |
mnitu
Messages: 159 Registered: February 2008 Location: Reims
|
Senior Member |
|
|
ThomasG wrote on Fri, 18 December 2009 17:07mnitu wrote on Fri, 18 December 2009 16:52Starting 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   |
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   |
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   |
wakula
Messages: 150 Registered: February 2008 Location: Poland
|
Senior Member |
|
|
JRowbottom wrote on Mon, 21 December 2009 11:44Really - 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 #437812 is a reply to message #437316] |
Wed, 06 January 2010 02:26   |
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   |
wakula
Messages: 150 Registered: February 2008 Location: Poland
|
Senior Member |
|
|
JRowbottom wrote on Wed, 06 January 2010 09:26Really, 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   |
cookiemonster
Messages: 13963 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   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
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 #437968 is a reply to message #437960] |
Wed, 06 January 2010 11:21   |
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   |
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   |
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  |
wakula
Messages: 150 Registered: February 2008 Location: Poland
|
Senior Member |
|
|
JRowbottom wrote on Thu, 07 January 2010 10:24Let me rephrase - have you ever actually recomended using this approach in a production application, on an Oracle database? Not yet.
|
|
|