PL/SQL :: SYSDATE In DDMMYYYY Inside Single Quotes
Dec 4, 2012
I have a requirement like:
Create Table A
( a number,
CreationDate DATE)
PARTITION BY RANGE (CreationDate)
(
Partition p_03122012 VALUES LESS THAN (TIMESTAMP' 2012-12-04 00:00:00'),
[Code]...
NOTE: Partition are named as p_ddmmyyyy where ddmmyyyy is date.
select * from dba_tab_partitions where table_name = 'A'
Now my requirement is :
select * from dba_tab_partitions where table_name = 'A' and partition_name > 'p_ddmmyyyy' (Here I want ddmmyyyy to be from sysdate i.e. date we get from - Select to_char(sysdate,'ddmmyyyy') from dual
i.e. for today it becomes
select * from dba_tab_partitions where table_name = 'A' and partition_name > 'P_04122012'
I'm trying to use SYSTDATE in a WHERE clause of nested SELECTS..I want to select a range of info from two days back from today until today (or time it is being run). But when I run this, it says I have a missing expression...
SELECT XXXX FROM XXXX WHERE DATE BETWEEN TO_DATE(SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') -2) AND TO_DATE(SELECT TO_CHAR(SYSDATE, 'YYYYMMDD HH24:MI:SS'))
I am writing a procedure that will be called from a java wrapper.
The procedure do a lot of data manipulations and in between i am creating global temp table and saving the data into it for each request thats given as a parameter to the procedure. After all the processing i have to write the data from this global temp table into a physical table and atlast drop the temp table.
Create or replace proc_name ()
update table........
delete from ..........
CREATE GLOBAL TEMPORARY TABLE TSAAG ( supplier_id numeric(10) not null, supplier_name varchar2(50) not null, contact_name varchar2(50) )
insert into............
drop table TSAAG;
End;
creating a global temp table inside a procedure is expensive...
Do we have anything like creating table before and calling the instanse of it in procedure.
Here is a sample function which takes in a string of CSV fields and prints the third field :
create or replace function restr(istr varchar2) return INTEGER is var_1 varchar2(30); begin dbms_output.put_line('input:'||istr); select regexp_substr(',' || istr,1,3,null,1) into var_1 from dual;
[Code]..
When I pass an input string as :
JOHN,MARY,O'DONNEL,O'CONNELLY,MARK
with quotes/apostrophe in it to the function then it prints the input string in the first dbms_output but errors out at the select with ORA-01760.
how we can use the quote/apostrophe character here ?
In the code segment below (hope it appears right) I can understand the use of single quotes in the first two examples but in the third example below I had to use double quotes around the word - Today's - and I not sure I understand why?! I'm aware of the rules ...If you want a single quote to appear in the middle of a string add another single quote to it.If you want a single quote to appear at the beginning or end of a string add 2 single quotes to it.If you want a single quote to appear on its own add 3 single quotes to it.
SQL> select 'This isn''t' from dual; 'THISISN'' ---------- This isn't SQL> select to_number('34@456#789', '999G999D999', 'nls_numeric_characters=''#@'' ') from dual; TO_NUMBER('34@456#789','999G999D999','NLS_NUMERIC_CHARACTERS=''#@''') --------------------------------------------------------------------- 34456.789 SQL> select to_char(sysdate, 'fm"Today''s" ddth Month YYYY') from dual; TO_CHAR(SYSDATE,'FM"TODAY''S"DDTHMONTHYYYY') ------------------------------------------------------ Today's 16th August 2013
I have a dynamic query which has this clause in it: WHERE [COLUMN NAME] IN (' || theString || ')
My problem is that theString is being passed in through a C# call and the variable is a bunch of strings concatenated together and separated by a comma. Ex: theString = "'val1','val2'"
How many quotes are supposed to go around val1 and val2?
I've tried the following and none work: 'val1','val2' ''val1','val2'' ''val1'',''val2'' '''val1'',''val2''' ''''val1'',''val2''''
When I run the procedure in Oracle it works with '''val1'',''val2'''
I searched the forum but could not find an answer. I am creating an extract via SPOOL that will send the output to a CSV file. I am comfortable concatenating commas in between the fields, the problem lies in text fields such as last_name which may contain a comma. The requirements of my client state to put double quotes around text fields ONLY if they contain a comma (so as not to throw off the CSV file obviously).
The only thing I could think of was a nested DECODE checked every character of every text field for a , and if it finds one to put " and if not to put nothing. This would get very tedious to program it as there are many text fields and they can be very long. I may even run out of nested DECODE statements (I forget what the limit is now).
I am loading .csv file into Oracle using sql loader file has strings, and numberics, Strings are surrounded by double quotes(") and field terminated by comma(,)
load data BADFILE '/var/opt/app/bad/filename' DISCARDFILE '/var/opt/app/discard/filename' append into table source_file FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
Some time String fields may have double quotes in it, at that time it is rejecting the records. how to handle those records to load into table.
Since there is an extra double quote (denoting inch) in the third column, im getting an error. Is there any way to avoid this error without modifying the csv file.
Any way to replace the Double Quotes used to enclose column names with an alternative character. This is the SQL I have now that Works!
select (case when CUST is null then "/BIC/Z_SUPPLNT" else NM1 end) CMPNTSUPSRCE from TBL1, TBL2 where "/BIC/Z_MAJVEND"=CUST(+) and Material = '1ABCD456' order by Material
But would like to do something along these lines below but keep getting error "ORA-00936: missing expression".
select (case when CUST is null then chr(34)||/BIC/Z_SUPPLNT||chr(34) else NM1 end) CMPNTSUPSRCE from TBL1, TBL2 where chr(34)||/BIC/Z_MAJVEND||chr(34)=CUST(+) and Material = '1ABCD456' order by Material
I am having a similar problem like above ONLY in UNIX box where my datafile is delimited by "|". The last field is ITM_CMNT declared as VARCHAR2(60) in Oracle. When I have exactly 60bytes in the last field it rejects the record saying actual 61 and max allowed is 60. If i reduce it to < 60bytes then it is stored as a value enclosed with double quotes. The enclosing double quote is on the next line.
"PROC,RAM,FLPY,HD,ACT MTX CLR DSP,D/PCMCIA,TRKBAL,LIT ION BA"
Expected: the one below is exactly 60bytes.
PROC,RAM,FLPY,HD,ACT MTX CLR DSP,D/PCMCIA,TRKBAL,LIT ION BAT LOAD DATA INFILE * INTO TABLE TMPTLI_LAWSON_ITM_MST TRUNCATE FIELDS TERMINATED BY "|" (ITM_NO, HAZ_MAT_CD, ITM_SHRT_DS, ITM_SON "TRIM(:ITM_SON)", ADDED_DT DATE "YYYY-MM-DD", AVL_CD , ITM_CST_AMT, ITM_SLL_AMT, EXCHG_PRC_AMT , ITM_UOM "TRIM(:ITM_UOM)", PCK_QTY INTEGER EXTERNAL, SPC_HNDL_CD "TRIM(:SPC_HNDL_CD)", EFF_DT DATE "YYYY-MM-DD", ITM_CMNT "TRIM(:ITM_CMNT)")
I am not strong in SQL but can write easy SQL for data extraction but seem to not understand how to correctly use sysdate in a where clause.
Case: I have to create an alert that will email my IT dept once a person is terminated in Oracle HR. The alert will only run once a day with all terminations specified in the alert. Setting up the alert is not the issue, but rather the SQL code I want to use.
The alert will run everyday at CoB 17:30. Now, in my query, how do i specify that the results should be only for the current day? The problem however, is that I also retrieve Person Type which should show as Ex-Employee, but this is only shown the day after the actual termination has been done, because the employee is still active on the date of termination.
I have an employee table. I Have to get the data of all employees in such a way that. If today I run the Query,then i have to get the data of all employees working between december 1st of previous year(current year-1 i.e., december 1st 2010.) till today(april 21st). If the query run date is in the month of december(example december 15th) then the query should get the data from december 1st of current year(december 1st 2011) to December 15th. I wrote the if statement some how its not working. I want to make use of this If or Case Statement as the start date of the employee_timestamp. Is this possible here or not.
select * from employee where employee.employee_timestamp > (select to_date(to_char(concat('12-01-', extract(YEAR FROM sysdate)-1)),'MM/DD/YYYY') as Startdate From DUAL) and employee.employee_timestamp < (SELECT SYSDATE FROM DUAL).
What is the difference between the values of sysdate and current_date ? On querying the database I got the results as sysdate = the date and time for the database server location and current_date = my local system date
If however, i change my local system date - it still shows the correct date.Myunderstanding was that current_date uses the dbtimezone.But..
I have a table called transaction_dw and I need to select all records that have an account balance that has been below 0 in the past 6 months. initial query I tried was:
select account_balance, timestamp from transaction_dw where account_balance < 0 and timestamp between sysdate and sysdate - 6;
but this is only taking 6 days off the sysdate rather than months, how I can get it to take off 6 months?
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Aug 30 11:45:59 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Enter user-name: sys as sysdba Enter password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select value from v$nls_parameters where parameter= 'NLS_DATE_FORMAT';
VALUE ---------------------------------------------------------------- DD-MON-RR
But while inserting the output of the above query in my table it throws error like this... ORA-01830: date format picture ends before converting entire input string Is there any possibility to achieve this.
I'm working on a project and I can't figure out the procedure I will need to use. I've got a sysdate field in my "Calls" table which generates a call date + time, however I need to insert a severity level of the call after a certain amount of time,
e.g. after 1 hour level turns from level 4 to level 3 and so forth until reaching level 1 after x amount of time. I know I'd need to put in an else if statement within this procedure I just can't work out how to do it with the sysdate field that has also been created.
A bit of info about the table, table name = calls, column 1 = date_time and column 2 is called severity_level
