PL/SQL :: Changing Format From Numeric To Char
Jul 26, 2012
I want to get the last month of my table “a” (Which is in number format) and after that I want to change it to a date format and insert into my table “b”.
I have already done the max month query:
SELECT MAX(MONTH_ID) FROM Table_a;
SELECT DISTINCT MONTH_ID ---This data is the one that I would like to change format "mon-yyyy"
FROM Table_a
WHERE MONTH_ID = (SELECT MAX(MONTH_ID) FROM Table_a);
I am getting trouble with the to_char function, I have been reviewing how it works and how to handle it.
View 3 Replies
ADVERTISEMENT
Jun 16, 2010
I want to add format mask on my field...
my field datatype is char in forms and in database VARCHAR2(30) and I want to add format mask as follows
12345-1234567-1
in format mask of this field property I give it to format mast like as
FMAAAAA"-"AAAAAAA"-"A
when I run the form and when I write and exit from this field then it returns the error.
FRM-40209: Field must be of form FMAAAAA"-"AAAAAAA"-"A
EDIT by VK: Seems your CAPS lock is on or the Shift is stuck.
View 5 Replies
View Related
Apr 4, 2012
select to_date('31-12-2012','DD-MM-RRRR') from dual;
OUT PUT : '12/31/2012'
is't possible to show the date format like
'31-12-2012'
without converting to char
View 8 Replies
View Related
Nov 23, 2009
I have a table in which years are stored in the form '2008/2009'. This is making it very difficult for me to do any calculations on that field and so I was wondering if there was a way to change the years (in a query and not in the actual table) so that if the year was '2008/2009' I would have just '2009'.
View 4 Replies
View Related
Sep 20, 2004
I am using SQL Plus v9.2.0.1.0 and am having trouble with the column widths. By way of example:
Let's say the column is a varchar(2) and the column name is called V9ABC12345. When I see the result of my query I only get the heading name as V9 ie the maximum width of 2 characters. The table has over 100 columns and I know I can specify the column width using the format command but I am using the select * from table_name command
How do I change my formatting to include the column heading by default?
View 6 Replies
View Related
Oct 30, 2011
i have changed date's format and the calendar type in sqlplus through the following command so the date's format and the calender type both changed successfully and work, but in oracle form the they haven't changed.
alter session set nls_calendar='persian';
ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD';
ALTER SYSTEM SET NLS_DATE_FORMAT='YYYY/MM/DD' SCOPE=SPFILE;
Note: i know session would change the format temporally but i want to change it permanently so i tried alter_system but still no result
View 2 Replies
View Related
Oct 27, 2010
I want to pass english character as a parameter and search a string that having swedish character, this needs to be done for all the swedish characters.
test _t table having the below 3 values
päiväp
metervara
flerfärgad
1. If user searches based on english char like below then they should get all the 3 values bcz fist & last have swedish ä and second one having english a.
Query : select name from test_t where name like '%a%'
Regsult:
päiväp
metervara
fräg
2. If user searches based on swedish char like below then they should get only 2 values bcz fist & last have swedish ä and second one having english a.
select name from test_t where name like '%ä%'
päiväp
fräg
Is any in-built function available, for Text alternative string search. If not how to search the string based on the query.
View 6 Replies
View Related
Jul 22, 2009
I have a table with column val1 having data's starting with 0920 and 4 digit char values.
so we have the query to find
select * from table
where val1 between '0920' and 'ZZZZ'
will it work fine?
View 1 Replies
View Related
Oct 10, 2011
I have an sqlldr process running loading data into my database. I have created a trigger to run before inserts on each row to start gathering summary data from the basic underlying data. The trigger compiles ok and the procedures the trigger is calling compile ok, but when the sqlldr process runs I get errors in the log files.
Here is the sqlldr control file:
LOAD data
APPEND INTO TABLE cdr.day_tables
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
RecordCode
,CdrStart DATE 'YYYY DDD SSSSS'
[code].......,
Next is my trigger
create or replace TRIGGER BNUMBER_SUMMARY_INS
BEFORE INSERT ON DAY_TABLES
FOR EACH ROW
DECLARE
[code]......
Next are the procedures that are called by the trigger:
create or replace PROCEDURE BNUMBER_SUMMARY
( BNUMBER IN VARCHAR2
, CALLDATE IN DATE
, CALLDURATION IN NUMBER
) AS
record_found NUMBER;
BEGIN
[code].......
The error messages I am getting are:
Record 1: Rejected - Error on table CDR.DAY_TABLES, column CDREND.
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "CDR.BNUMBER_SUMMARY_INS", line 6
ORA-04088: error during execution of trigger 'CDR.BNUMBER_SUMMARY_INS'
I need to find out what field it is complaining about, especially since I am not even using the cdrend field from the input record?
View 14 Replies
View Related
Apr 22, 2013
select ORDER_NUMBER from OE_ORDER_HEADERS_ALL
WHERE ordered_date=to_char(to_date(substr(ORDERED_DATE,1,10),'YYYY/MM/DD'),'DD-MON-YYYY');
Error:-ORA-01858: a non-numeric character was found where a numeric was expected
View 13 Replies
View Related
Jun 1, 2007
I get the error message mentioned in the subject with this SELECT-statement
....where (t.cfonte=14 and t.data_ultima_modifica between sysdate -4000/(24*60*60) and sysdate ) or (t.data_ultima_modifica > to_date('%TIMESTAMP%','ddmmyyhh24miss'))]]>
If I substitute %TIMESTAMP% with 310507143709 then it works
View 6 Replies
View Related
Apr 24, 2013
Using Application Express 4.2.1.00.08
Any problem using 4.2's One Level Tabs - Content Frame page where you have a parent region (of type content frame Body Container ) while number of child regions (of type Hide & Show Region - Borderless ), if you want to re-order them by changing the sequence, it has no effect when you run the page.
The regions still show in the same order top to bottom as it did before the update of sequences. Heading links (to show/hide child regons in content frames) has the same ordering as before.
View 3 Replies
View Related
Dec 7, 2010
We are getting an error : a non numeric was found where a numeric was expected sometimes when this statement is executed:
INSERT INTO training select * from temp_training where class_id='xyz';
all columns and their datatypes are the same in both the tables
however if i replace the * with the column names as shown below it seems to work fine without giving an error
insert into training (a,b,c) select a,b,c from temp_training where class_id='xyz'
wanted to understand the subtle difference between the 2 statements
View 5 Replies
View Related
Dec 13, 2012
oracle 11 g xe
1) I want to pass date in '12 Dec 2012'(CHARECTER) while calling SP and in side SP i have declared parameter as DATE so I think it should not create any problem in any condition whether it is passed from oracle client or any other front end?
2) I have date in (date type variable) , how to convert it into char but it should be in iso format (i want iso format , so that it should not create any problem in any condition).
3) How to convert (date in char- in iso or any good format) into (date type variable) (so that it should not create problem and this is why i wanted char date in iso format).
4) If there is any good format and function to convert (date to char) and (char to date)?
View 5 Replies
View Related
Feb 11, 2009
Well the company i work for has just recently upgraded from Oracle 9i to 10g. We are having various problems with the migration, and with certain code breaking. My question regards this piece of code;
[code]
to_number(to_char('a_date','YYYY'))
[code]
There are various statements like the one above scattered throughout a query i am trying to fix. When run, the query returns an "invalid number ora-01722" error, which i know is caused by the above code.
if this method of converting from date, to character was discontinued from 9i to 10g?it just seems strange as there are a lot of statements like the one above and they must have worked at some point, but now i cant even get one to work on its own.
View 3 Replies
View Related
Feb 7, 2013
converting a string to date, my string looks like '01022013' '04022013', I tried to_date('01022013', 'dd-mm-yyyy') but couldn't work.
View 6 Replies
View Related
Mar 16, 2011
I've got a table with varchar records and I need to separate numerical value and convert into the number datatype.
create table tmp_mape
(remark varchar2(100) )
insert into tmp_mape values ('Dobitie zdarma 3,32EUR 0910105067 02/02')
insert into tmp_mape values ( 'Dobitie pravidelné 9,00EUR' )
I just need to get values 3.32 and 9 from that example into the new column.
The select below returns ORA-01722: invalid number
how to solve it?
select remark,
to_number( to_number(REGEXP_SUBSTR (remark, '[[:digit:]]+', 1) ) ||','|| to_number(substr(REGEXP_SUBSTR (remark, ',[^EUR]+', 1) ,2) ) )
from tmp_mape
View 7 Replies
View Related
Dec 23, 2012
i have a problem with my SQL programming which i am learning at the moment. Currently here is my Show how many orders were placed in each month of 2012. (group by question, to char function)
select count(orderdate)
from csorder
group by orderdate;
where the to char function would occur?
tables for reference
CSPRODUCT contains (PRODCODE, PRODNAME, CATCODE, PRICE, PARTOF_PRODCODE)
CSCATEGORY contains (CATECODE, CATNAME)
CSORDER contains (ORDERID, ORDERDATE, CUSTID, PAID)
CSORDERLINE contains (ORDERID, PRODCODE, QUANTITY)
View 3 Replies
View Related
Oct 9, 2013
I have been tasked with producing a list of all non standard characters that are not one of 0-9, a-z, A-Z from a parts table. The list should capture any non-English characters i.e. áóú etc as well as !"£$%^&* etc. These are a small example of the data.
Part_Description
Probengefäss kompl.
Stützring zu Probenehmer
Zargendichtung m.2 Ventillöcher
Pneumatikdichtung m.2 Ventillöcher
Result from above example
ä.üö
View 8 Replies
View Related
Aug 18, 2011
I am trying to compare 2 char columns using trim to avoid space padding
CREATE TABLE TRIAL_A
(ABC CHAR(6));
INSERT ALL
[Code]....
View 11 Replies
View Related
Apr 9, 2013
i trying to pass the char varible to the cursor but it is not taking ,,, if i hardcode the values to the cursor it is taking
here is the detailed program ... why this is not taking and tell me how to pass the values through it..
declare
v_name char(6) ;
cursor c1(c_name char) is
select name, parent,child,status from relation
start with name='%'
connect by prior parent=child
union
[Code]...
View 9 Replies
View Related
Apr 1, 2011
I am using forms 6i and oracle 10g. i want to display a char from database. that means a database string 'Bangladesh' is display from by one char to one char.
and display view right to left first display "B" then "BA" then 'BAN' etc.
View 2 Replies
View Related
May 16, 2008
How to find out lowercase char in a String in a SQL query
for example
"ORAclE" here c and l are lowercase so how can i find out this condition... is there any build in function in oracle?
View 13 Replies
View Related
Oct 28, 2011
using listagg function for more than 8000 char.I did the below sample SQL and in "e_orig" and "d_orig" for upto 4000 char it is working fine but I have to use it for more than 8000 char. and it is giving error,I checked the listagg function is having limitation of 4000 char.I tried but I am unable to achieve this.
select d.dname,d.loc,e.hiredate
,listagg(e.ename,',' ) within group (order by e.deptno) over (partition by e.deptno) as e_orig
,listagg(e.ename, ',') within group (order by e.sal) over (partition by e.deptno) as d_orig
from emp e, dept d
where e.deptno=d.deptno;
[code]....
View 17 Replies
View Related
Jul 6, 2011
I am using an portuguese(brazil) Oracle 11g db,which should have ',' as decimal char. it seems to be working fine when i m trying to get numerical data via select query,but when i am trying to retrive the same data using out parameter of an sproc.I am getting data with '.' as decimal char.
View 2 Replies
View Related
Oct 12, 2012
From a list of values from a number column, I need to verify is there any values not satisfying number(3).
Ex- I have a column col1 where we have following values
'qwq'
'12.3'
'1'
'15'
Can I write a query to check the value not satisfying number(3) datatype.Eg the query should return
'qwq'
'12.3'
as the above two will not fit in to number(3) datatype columns
View 13 Replies
View Related
Dec 7, 2010
I tried google and most of bind example using number instead of character, ?
I have the code as below:
variable oldId varchar2(5)
exec :oldId :='&1'
variable newId varchar2(5)
exec :newId :='&2'
[Code]...
Since in TAB1, ID is defined as varchar2(5), and I have defined both oldId and newId as varchar2(5) in my code, I wonder why it doesn't work.
It returned this error:
DECLARE
*
ERROR at line 1: ORA-01722: invalid number ORA-06512: at line xx
After supply the value for oldId(val2) and newId(val1)
SQL > @thisScript val1 val2
I use dbms_output.put_line to print the command executed, it seems it is doing
UPDATE TABLE1 SET ID = val1 WHERE ID = val2
without the single quote ('val1'), ('val2'). But if I put in single quotes, the output statement
UPDATE TABLE1 SET ID = ':newId' WHERE ID = ':oldId'
The same bind logic works in SQL prompt
SQL > variable oldId varchar2(5);
SQL > variable newId varchar2(5);
SQL > exec :oldId :='val2'
SQL > exec :newId :='val1'
SQL > UPDATE TABLE1 SET ID = :newId WHERE ID = :oldId;
1 row updated.
I am wondering what should I do then? With or without single quotes?
View 9 Replies
View Related
Jul 20, 2010
With a very large database (VLDB) for a data warehouse (DW) using primarily a STAR based schema in an environment in which time (both human and CPU) is orders of magnitude more valuable than storage capacity, is there any signficant difference in query performance when tables have all fixed length (CHAR) columns compared to tables with variable length (VARCHAR2) columns?
I realize this is one of those "in general" questions so considering "a given VLDB DW environment" with all other things being equal, what, if any, is the time based performance difference between a database of tables with all fixed sized columns versus one of tables with variable length columns ?
View 2 Replies
View Related
Aug 11, 2011
What is the best way to convert an existing database table set with nls_legnth_semantic=BYTE to CHAR?
View 2 Replies
View Related
Mar 24, 2012
I want to find the hours and minutes between two char field data type.Example I have two char columns one is "start_time" and another one is "end_time".The start time and end time is the machine reading of CNC MACHINE in manufacturing.I want to develope the package to capture the actual machine running time.But I have the start and end time reading in character field.The machine reading format is hour:minutes:seconds only.It will looks like 1234:45:23(the machine ran 1234 hours and 45 minutes and 23 seconds).See the below table to understand my requirements.
start_time end_time result in hours & minutes
---------- -------- -------------------------
345 347 2 hrs
347 350 3 hrs
350 357.20 7 hrs and 20 minutes
If I subtract end_time - start_time I will get the result in char type only not in hours and minutes format.Another example
start_time end_time result in hours & minutes
---------- -------- -------------------------
357.21 360.40 3.19(If subtract end_time - start_time)
View 2 Replies
View Related
