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


ADVERTISEMENT

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

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

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

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

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

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

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

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

SQL & PL/SQL :: Handling Circular Data In Oracle / Get Disjoint Sets Of Data Connected By 2 Values

Sep 29, 2011

I have this table :

column1 column2
--------- ---------
value1 value2
value1 value3
value2 value4
value3 value7
value7 value1
value8 value9

What I was trying to retrieve is something like that:

Quote:
output_column
---------------
value1, value2, value3, value4, value7
value8, value9

I don´t care about the order of the values in the row. In other words, I want to get disjoint sets of data connected by any of both values.Every pair in the input table is unique.

I have seen in the web that it is possible to do using connect by and hierarchical retrieving but I've been trying to make a lot of combinationts and I can reproduce the output.

View 2 Replies View Related

Any Data Compression Wizards That Automatically Suggest Level At Which Data Should Be Compressed

Jun 29, 2011

whether Oracle has any capability of automatically checking which lossless compression algorithm it should apply by analyzing a data stream on data load? Does Oracle have any compression advisors/wizards that would make recommendations as to type and level of compression?

View 3 Replies View Related

Store Data In CLOD Data Type - How To Create A Unique Index

May 20, 2013

We have been recommended to store data in CLOD data type.

Sample data: 1:2:2000000:20000:4455:000099:444:099999:....etc it will grow to a large number.

We want to create a Unique index, for functional reason. Is it advised to create a unique index on a CLOB datatype?

View 2 Replies View Related

Data Guard :: Unable To Get Data Of Primary In Standby Database (dataguard)

Jan 16, 2013

i have configured physical standby in my local system, to check logshipping i created a table at primary db, wen i tried to check in standby, it says table does not exist..below are primary & standby alert entries..

Primary alert log

Fatal NI connect error 12514, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.0.98)(PORT=1522))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STAND)(SERVER=dedicat ed)(CID=(PROGRAM=d:oracle11gappadministratorproduct11.1.0db_1inORACLE.EXE)(HOST=A960M)(USER=SYSTEM))(SERVER=dedicated)))
VERSION INFORMATION:
TNS for 64-bit Windows: Version 11.1.0.6.0 - Production

[code]....

View 1 Replies View Related

SQL & PL/SQL :: Remove Data Recently Loaded But Not Disturbing Existing Original Data?

Jun 24, 2010

I have few tables in Oracle 9i/10g , and they already have data in them. I am trying to migrate the data coming from various source systems into these Oracle tables. There is a chance that after loading I might get some unwanted data into these tables.

How do I remove just that data which I have loaded recently, and do not disturb the original data it already has.

Need to backup those tables and reload the data back if there is any problem, but I am looking at a different approach. I just don't want to change the existing system, as lot of users use the system.

View 8 Replies View Related

Server Utilities :: Transferring Changed Data From Database A To B By Data Pump?

Apr 1, 2011

I have database A (Working in Live environment) and Database B copy of Database (Not live) I have Restored whole database (A) RMAN backup file on Database (B) Previous week now i don't want to change anything in any schema and want to import only updated and new records in the table in Database B

There are around 20 schema If for example i have everything in new database B all required database objects like Procedure,functions, packages with indexes in all tables and data in tables, i just want to add new data and updated data.

IF i do following in source database

expdp directory=dpump_dir dumpfile=table_data.dmp content=data_only schemas=ACCMAIN,HRMAIN,..... include=TABLE

AND Import in destination database B, will it add new data and update existing one in table and not touch the table structure and indexes.

View 5 Replies View Related

Data Guard :: Changing Dbname / Sid / Data / Control File Locations?

Nov 5, 2010

I want to change dbname,sid,data/control file locations in operational dataguard setup i plan to follow as below

1)shutdown primary and standby (stop managed recovery)

2)change db name in init.ora of primary and standby change database name control file location

3)create control file for primary from trace(script) make changes for db name and file locations

4)mount and open primary database

5)create standby control file

6) transfer standby control file to standby

7) mount stand by database and start manage recovery

If this steps will error free do i need to follow any thing additional to this or what is other best way for this or its not possible at all

View 1 Replies View Related







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