I have the age of a property stored in my DB. (Lets say 3 yrs old). I would like to extract from that information, the year the property was built. I know I can minus the sysdate by days (-1) and months (add_months), but how can I do it using the YEAR information I have stored?
So if I was doing it against sysdate, I would want the result to be 10/24/2003.
I have the following piece of
SELECT decode(substr('18900607|000000|20110718112109|23972|002',
17,
14),
'00000000000000',
NULL,
(to_date(substr('18900607|000000|20110718112109|23972|002',
17,
14),
'YYYY/MM/DD HH24:MI:SS')))
FROM dual;
When I run the above code I get an incorrect display ie. "18-JUL-11". when I remove the decode I get the correct display ie."2011/07/18 11:21:09 AM"
So the data is coming from a collection where the first column is like '1-May'
declare
no_year EXCEPTION;
holddate VARCHAR2(20);
totaldate VARCHAR2(20);
cursor emp_cursor is
[code]...
I can't see what is in the temp table because even when I don't truncate it global temp table and can't get to the information in it.
I am using the cloud version of APEX for a college course. The database script I am trying to load has dates formatted in "DD-MON-RR" and I receive 'not a valid month' and 'a non-numeric character was found where a numeric was expected'. Is there a workaround in APEX for this? I'm trying to avoid changing thousands of lines of data.
View 4 Replies View RelatedI am currently doing a project where i need to write a stored procedure which will be doing the following-
i)it will retrieve multiple columns from multiple tables in a single database(through join) based on certain conditions
II)then it will store the entire data in a certain field(File_data) of staging table
inside file_data a header and a trailer will be present with the records.also the field values will be pipe separated and a new record will start in a new line.
So,the data inside the file_data of staging table will look like this-
H|v1000
transdate|ordnmb|deposite_amt|order_status....
12-nov-09|123456|23.8|C...
4-dec-07|234567|67.7|R...
..........
7-jan-04|567890|54.7|x.....
T|234(record count)
i did this formatting using java, but my project leader wants me to do the formatting using SP,and wants me to use staging table.
I need the below output in oracle reports 6i.
this is a testing string retrieved from database.
now what i need is that to make bold the specific words in a string came from database.
I am trying to develop a PL/SQL function to perform some data cleansing. One of the data cleansing requirements is If there are instances of more than 1 space together, the extra spaces should be removed.Say for example, if my data comes in as "123 456 789", then my function should clean it as "123 456 789" replacing multiple spaces with just 1 space.
What PL/SQL functions could I use to accomplish this?
Formatting the mail message sent using utl_mail, i have created one table and it has around 17 rows inside as sample and it may increase as well, my present email format is like very distorted and i have to have it as readable in single line .Please find below the code i have written along with test case, you may substitute your emailid and test, especially my main focus is on the lable aff.mark number where all the mark numbers are distorted and want to be arranged in line.I have attached the email format that i am getting at present also.
CREATE TABLE FAB_LOG ( MRK VARCHAR2(30))
INSERT ALL
INTO FAB_LOG VALUES ('1018017M-6001')
INTO FAB_LOG VALUES ('1018017M-6002')
INTO FAB_LOG VALUES ('1018017M-6003')
INTO FAB_LOG VALUES ('1018017M-6004')
[code]......
I'm creating a report and wrapping the output line.
These are the variables I'm using
lv_actn_line_1 VARCHAR2(60);
lv_actn_line_2 VARCHAR2(60);
lv_actn_line_3 VARCHAR2(60);
lv_actn_line VARCHAR2 (192);
lv_rpt_ln_19 VARCHAR2(102);
I assign variable
lv_actn_line:=lv_actn_line_1||' '||lv_actn_line_2||' '||lv_actn_line_3;
And want to use it in output as below.
lv_rpt_ln_19 := edge_blank ||lv_actn_line;
osfile.put_line(v_fileid,lv_rpt_ln_19);
line_cnt := line_cnt+1;
How can I format the output so that lv_actn_line will wrap at character 102?
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)
CREATE OR REPLACE PROCEDURE A516907.proc_data_T_DTIA_TS_ADDR
is cursor CSR1 IS SELECT * FROM T_DTIA_TS_ADDR ;
v_file UTL_FILE.FILE_TYPE ;
BEGIN
v_file := UTL_FILE.FOPEN( 'DATA_PUMP_DIR' , 'T_DTIA_TS_ADDR.txt','w',32767);
FOR CS1 IN CSR1
[Code]....
The procedure above is dynamically generated. It's simple enough in that I am calling "the parent procedure" by passing the table name in. After this the procedure for creating the output data file for that table is generated and that's what we have above. I am doing this to load data into DB2.
What I need is to be able to generate the output such that if the column is of type varchar, char (anything non numeric) then the data should have " around it. If the data is of type number it should not have ".
I suppose I could use something as follows in lieu of the procedure above
Declare
cursor c1 is select column_name, data_type from user_tab_Cols where table_name = 'Table Name here';
-- I would like to know how to use the utl_file.put_line
-- where if the column data type is varchar 2
-- I could get ' ||"|| CS1.TS_ADDR_TY_CD||" || '
-- and if it's a number -- || CS1.CB_TS_DTIS_ID ||
as part of an exercise we are to demonstrate a FOR LOOP from 1 - 100.
We must show the output as evidence of doing the work.
Is it possible to display the results in two columns, instead of all in one row? Because 100 rows will not fit it one screenshot!
BEGIN
FOR i in 1..100
LOOP
DBMS_OUTPUT.put_line(i);
END LOOP;
END;
/
How to make the multiple lines of character in single line. Below is the example where i am getting the value when we export the data from the database table.
----------------------
Welcome Aboard customer contact based on Account activation.
The current selection criteria for Welcome Aboard is based at the service level. Therefore, each time a customer connects a service, Welcome Aboard material is mailed to the customer. This is causing the following problems;
Customer complaints. If a service or a number of services are connected on a particular day, it will result in (ie except for some minor exclusions) a single Welcome Aboard pack being sent to each individual customer. As you can expect for corporate accounts, they receive a high volume of this material. Other problems occur when, service/s are deactivated and reactivated, each time a Welcome aboard pack will be distributed.
High costs to Telstra. Each MNET service connected may produce a Welcome aboard pack. This volume of material currently costs Telstra over $500,000 per quarter for Welcome Aboard alone.
------------------------------
here is what i want to the above data to export:
----------------------
Welcome Aboard customer contact based on Account activation.The current selection criteria for Welcome Aboard is based at the service level. Therefore, each time a customer connects a service, Welcome Aboard material is mailed to the customer. This is causing the following problems;Customer complaints. If a service or a number of services are connected on a particular day, it will result in (ie except for some minor exclusions) a single Welcome Aboard pack being sent to each individual customer. As you can expect for corporate accounts, they receive a high volume of this material. Other problems occur when, service/s are deactivated and reactivated, each time a Welcome aboard pack will be distributed. High costs to Telstra. Each MNET service connected may produce a Welcome aboard pack. This volume of material currently costs Telstra over $500,000 per quarter for Welcome Aboard alone.
how a number can be displayed using comma separation without using TO_CHAR function.
NUMBER 3455678.05 should be displayed as 3,455,678.05 NUMBER 3455678 should be displayed as 3,455,678
i have column names as empno,ename,email...
i want output as
asb@asd.com,sad@sad.com,..... only email column should be printed
how can we do that?
I have a query then I write it to a text file using utl_file. However, I'm having some trouble formatting the data itself on the text file. =(
How would I align these records against its header considering the length of the Name/subscriber no. column which is not constant. Would LPAD work?
ID Name Subscriber No
1 James Smith 12454654
123 Scott Kevin Joseph 4564564
21 Dana Marice Curtis 455
2 Stephanie Hans Walter 23134
So I'm trying to display a monetary value in an item.
SELECT to_char(SUM(TXN_AMT),'$9,999') from CMS_COST_TRACKING where UPPER(TXN_NODE)='VMAX'this results in ######### in the item
If I remove the to_char formatting then the SUM displays correctly.Is this a restriction of some kind. The item is a display only.
APEX Application Express 4.1.1.00.23
ORacle 11G.
I want to make a report which is formated (rebuilding an older tool in Access).
So on the one hand I need some formattings which I implemented with the search bar in the interavtive report. (content of the first column is the heading for a table with the rest of the content of all rows which have the sam content in the first column) On the other hand I don't want to show the headings of the columns, what I solved with the classic report(report attributes/ column attributes: none).
Is the a solution to match these two requirements in one report?
I have a Number(18,0) datatype.It could have millions/billions stored in that field.How can I show that amount in the form of $7,123,787 ($ sign and commas)?
By using To_char function like below, I am not getting the desired format.
Select to_char(123000000.25, '$9,9999999999.00') FROM dual;
Result is (no commas):
$123000000.25
I have a workbook in Discoverer which is not formatted correctly when I export it to excel. I have to re-size the columns and also adjust the height of the rows to see the data clearly.
Is there a way I can export the report without having to make these changes all the time?
I've created report to generate letters,my query contains records more than 1000 while generating report it shows report progress
"Formatting Page 1000" after that report builder gets closed without any error message,I've tried for 10 records it is working fine,
I have developed one report but need one formatting suggestion. There is one field called "DESCRIPTION", I want the value of this field to be displayed in a single line. Now the big values are wrapped into multiple line.
Now,
DISPLAY
----------------
This is a Oracle
Report.
I want,
DISPLAY
------------------------
This is a Oracle Report.
Maximum size of "DISPLAY" field is VARCHAR2(240).
,Is there a way to make Interactive reports break format work/look like classic reports break format? i.e.1st,2nd,3rd column option. The way IR breaks is that is reserves a whole row for the column used in the break, which is not what I want, I want the report to look like the following:
city emp sal
Chicago John Miller 1500
Mark Horton 2000
Denver Rob Martino 1200
I tried to use rollup queries which work fine except that it does not do exactly what I want when I sort columns through the front end , I am basically displaying a check box for grouped records ( and I want it to display at the first record of each group), so the order is important. for example:
checkbox City emp sal[ ]
Chicago John Miller 1500
Mark Horton 2000[ ]
Denver Rob Martino 1200
I'm trying to work out how to take a table like this:
IDDate
12502-Feb-07
12516-Mar-07
12523-May-07
12524-May-07
12525-May-07
33302-Jan-09
33303-Jan-09
33304-Jan-09
33317-Mar-09
And display the data like this:
IDPeriodPeriod StartPeriod End
125102-Feb-0702-Feb-07
125216-Mar-0716-Mar-07
125323-May-0725-May-07
333102-Jan-0904-Jan-09
333217-Mar-0917-Mar-09
As you can see, it's split the entries into date ranges. If there is a 'lone' date, the 'period start' and the 'period end' are the same date.
I have a two date fields in my form; valid from date and expiry date.
Currently my valid from date has an inital value property of $$date$$ which automaitcally brings up todays date.
I need my expiry date to automatically show a date 15 years after this date?
In my application I show the Date in Hijri using the NLS setting. but the issue that "i face a deference between the real hijri date with oracle hijri date"
Assume that the real hijri date on 18-SEP-2012 was 02/11/1433 in hijri but in oracle was 03/11/1433
when I execute the following query it shows me "1433/11/03", but the actual Hijri date is "1433/11/02"
select to_char(to_date('20120918','YYYYMMDD'),'YYYY/MM/DD', 'NLS_calendar='''||'ARABIC hijrah') FROM DUAL;
why doesn't the following SQL doesn't return any rows. (column1 is a 'date' datatype)
select * from table1
where to_char(column1, 'dd-mon-yyyy') = to_char(sysdate + 3 , 'dd-mon-yyyy')
I tried to insert date in date column from another table date column. Below is SP ans execute procedure.
CREATE OR REPLACE PROCEDURE test(
start_time Varchar
)
AS
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE(start_time);
Insert into test_ora (report_date) (select start_time from
[code]....
execute test ('27-10-2011 12:01:01');
It gives error as Invalid number.
I have a table with date column (16/06/1996 15:03:59) as value in the displayed format. As I have tried with the below query format I could not able to retrieve the
records. select * from <table> where DATE_INSERT=SYSDATE;
Retrieve the rows for the tables ?
I need to convert below functionality in Oracle. At present it is working with SQL server
current_end_time TIMESTAMP,
current_start_time TIMESTAMP
current_duration int
current_end_time := DATEADD(second, current_duration/10, current_start_time)
