PL/SQL :: Losing Date Formatting Somewhere?

Jun 26, 2013

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. 

View 1 Replies


ADVERTISEMENT

Date Formatting In Oracle

Oct 24, 2006

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.

View 2 Replies View Related

SQL & PL/SQL :: Formatting Date And Time

Jul 21, 2011

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"

View 2 Replies View Related

Application Express :: Date Formatting - Not A Valid Month

Apr 29, 2013

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 Related

SQL & PL/SQL :: Variable Losing Its Value

Jul 21, 2010

I have written an explicit cursor (procedure given below) and the issue I have is, when the cursor runs the sql statement

(CURSOR csr_address is
SELECT rtrn_id,
entp_abn,prog_program_cd,
sched_nbr,schd_version_yr,
litm_line_item_nbr, revise_val_text
FROM RETURN_LINE_ITEMS
WHERE sched_nbr = '000'
AND prog_program_cd = '01' AND litm_line_item_nbr in ('016','023')
AND rtrn_id = v1_rtrn_id;)

against a particular return id, it fetches 2 rows; one for line item 016 and the other one for line item 023 where in the litm_line_item_nbr for 016 is 016 and for litm_line_item_nbr for 023 is 023. Once that's done, (I have used a For loop cursor), it loops through as follows:

FOR country_rec in csr_address LOOP
v_line_item_16 := country_rec.litm_line_item_nbr;
if v_line_item_16 = '016' then
v_line_item_16 :='016'
end if;
[code]....

View 8 Replies View Related

PL/SQL :: Losing Special Characters In CONVERTTOCLOB

Feb 27, 2013

This is in Oracle 10.2.0.4.0. I have some blob records (delimited text uploads) with some diacritic characters in them, and the diacritics are being lost when I run CONVERTTOCLOB.

database character set is AMERICAN_AMERICA.AL32UTF8

View 9 Replies View Related

Application Express :: Losing Chart Settings When Switching To Custom XML

Feb 20, 2013

I have an application which contains a chart of type Dial.In order to set the major interval dynamically I have switched to using custom xml and replaced the #SCALE_DATA# with:

<scale minimum='&P2_CHART_MIN.' maximum='&P2_CHART_MAX.' major_interval='&P2_CHART1_INTERVAL.'/>

However, I would also like to label the value with a Units Label (e.g. '%'). I was previously doing this using the Value: Postfix setting under Axes Settings, but after switching to Custom Settings removes this box and I no longer get the units displayed.

Is there some xml I can add which will set and display the units.I am using Application Express 4.0.2.00.09

View 0 Replies View Related

Application Express :: How To Update Application From One Instance To Another Without Losing SIR

Aug 21, 2013

We have several prod instances and many applications on each of these... and interactive reports saved by many users.Each prod application have its own ID, distinct from the dev application ID.

Until today I used the apex_application_install inside the first wokspace only, and it works fine, but I need to update applications on every instances. It seems the only way to keep a saved report during an update is to use the same application Id (use the app n°200 to overwrite the n°200).So it means the only available method is : release the dev app on the dev workspace ; export 50 apps from it and import those 50 times, maybe more in the future. 

My configuration :Oracle Standard or XEApex 4.1.1OHS

View 3 Replies View Related

PL / SQL Data Formatting?

Sep 10, 2003

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?

View 7 Replies View Related

SQL & PL/SQL :: Formatting In Utl_mail Output

May 26, 2012

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]......

View 7 Replies View Related

SQL & PL/SQL :: Formatting A Variable In Report?

Jun 1, 2011

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?

View 10 Replies View Related

SQL Formatting To Char Function

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

SQL & PL/SQL :: Formatting Output File

Sep 6, 2012

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 ||

View 33 Replies View Related

SQL & PL/SQL :: Formatting Output Into Multiple Columns?

Apr 16, 2013

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;
/

View 5 Replies View Related

SQL & PL/SQL :: Formatting The TEXT In Exported File?

Nov 15, 2011

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.

View 3 Replies View Related

Formatting Number With Comma Separation / TO_CHAR

Mar 1, 2012

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

View 1 Replies View Related

Query On Formatting Data In A Specific Way In Oracle

Apr 25, 2010

I 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.

View 7 Replies View Related

SQL & PL/SQL :: Formatting Output - Only Email Column Should Be Printed

May 4, 2010

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?

View 3 Replies View Related

PL/SQL :: Aligning / Formatting Records In Text File

Feb 26, 2013

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

View 1 Replies View Related

Application Express :: Formatting (dollar Value) Within ITEM?

Mar 21, 2013

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.

View 1 Replies View Related

Application Express :: Formatting A Table Without Headings

Apr 3, 2013

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?

View 4 Replies View Related

SQL & PL/SQL :: Number Datatype Formatting To Show $ Sign And Commas

Mar 8, 2011

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

View 2 Replies View Related

Reports & Discoverer :: Workbook - Export With Formatting To Excel

Aug 5, 2011

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?

View 1 Replies View Related

Reports & Discoverer :: Builder Get Closed - Formatting Page 1000

Dec 23, 2011

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,

View 5 Replies View Related

Reports & Discoverer :: Formatting - Display Value Of Field In Single Line

Oct 20, 2010

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).

View 3 Replies View Related

Application Express :: Interactive Report - Break Formatting Style?

Nov 14, 2013

,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 

View 2 Replies View Related

SQL & PL/SQL :: Display Date Ranges In One Column As Separate Date Periods (start And End Date) In Two?

Jun 1, 2010

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.

View 13 Replies View Related

Forms :: Expiry Date To Automatically Show A Date 15 Years After Initial Date

Apr 12, 2010

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?

View 8 Replies View Related

Reports & Discoverer :: Oracle Reports Text Formatting As Runtime?

Aug 26, 2013

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.

View 2 Replies View Related

Application Express :: Unable To Set Default Date Value For Tabular Form DB Date Field?

Dec 3, 2012

Version : 4.1.1, I have a tabular form on a DB table. One of the columns is a date field. When the user hits the "add Row" button on the tabular form, I want the Date field to be defaulted to sysdate. Here is what I have tried so far,

1. Created a "hidden" item P1_SYSDATE and populated the default value with sysdate. After this, under the DB tabular report date field, I used default type - Item/application on this page and entered P1_sYSDATE

2. Instead of populating the default value of the P1_SYSDATE hidden item, I created a before regions process and added

:P1_SYSDATE := sysdate

and added P1_SYSDATE to default type of the tabular date field with default type as "ITem/application on this page.

I get the error

ORA-01790: expression must have same datatype as corresponding expression

I tried to_Char(sysdate,'dd-mon-yyyy') and then converting it back to to_date. still no luck.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved