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 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 need writing sql which can return the Count of Comma's in a string. Here is my table and data
CREATE TABLE TEST1(SNO NUMBER,STR1 VARCHAR2(30));
INSERT INTO TEST1 VALUES(1234,'ABCD,LL LT,MP');
INSERT INTO TEST1 VALUES(1456,'PP MR');
INSERT INTO TEST1 VALUES(1589,NULL);
INSERT INTO TEST1 VALUES(1897,'PP MR,FTR CLR ON');
Here is the output I am expecting
SNO STR1 STR1_COUNT
1234 ABCD,LL LT,MP 3
1456 PP MR 1
1589 0
1897 PP MR,FTR CLR ON 2
Basically I need to the count of Words separated by comma
I need to use an outer join outside a to_char
to_char(the_date_field,'YYYYWW')(+) = '200815'
This throws up the error ORA-00936 missing expression.
I know it will work if I put the (+) right after the date before the mask but it need to check the week rather than the date.
I have tried using a function based index without success.
I want to convert an amount value in 99,999,999,990.99 format without using TO_CHAR function. I was trying to use regex. Is there any other function through which I would be able to do this?
View 6 Replies View RelatedI have the following test case:
create table t(
col1 varchar2(10),
col2 varchar2(100))
[Code]...
I need to spit col2 like this
P0001 aaa
P0001 ddsd
P0001 sds
.
.
.
.
.
delimeter between individual may be #, ' ',',' or';'
How can I achieve this using PL/SQL
i want create a report like this
location sysdate refdate No_of_days SL1(0-90) SL2(91-180) SL3(>180)
So when no. of days between (0-90) data put in SL1
And when no. of days between (91-180) data put in sl2
I want to achieve this only in single query.
I'm planning to use one Instance of ASM to manage 2 11gR2 RDBMS database in a single server. Each RDBMS will have distinct ORACLE_HOME, OS user and group.
ASM will also have a separate ORACLE_HOME, OS user and group for maintenance purpose.
Based on this configuration, who should own the Raw disks? I am not going to use ASMLib because of the dependancy it creates with the Linux kernel?I just don't want to waste time with test and trial as the server are managed by sys admin.
I have a very simple table with 2 columsn. As_of_date is one of the column. This column is "Date" data type.
When I use distinct clause inside a to_char function it gives the following error:
ORA-00936: missing expression
00936. 00000 - "missing expression"
The Sql is
select to_char(distinct(as_of_date),'mm-dd-yyyy') from sales
I can't see any syntax error in the sql..but forsome reason, it doesn't work.
I have successfully set up a (test) environment for single-instance Grid Infrastructure and Oracle database using job role separation. So I have the recommended grid and oracle users, and the oinstall, dba, oper, asmadmin, asmdba and asmoper groups. I have the following directory structure for my Oracle Bases and Oracle Homes:
/u01/app/11.2.0/grid - GI home
/u01/app/grid - GI base
/u01/app/oracle - DB base
/u01/app/oracle/product/11.2.0/db_1 - DB home
Platform is OEL5, 11.2.0.1 GI and DB.
This all works fine.
What I now want to do (since what I'm trying to do is make this environment as secure as practical) is set up an additional sysoper operating system account, so that that user can connect to carry out sysoper tasks, amongst other things stopping and starting the instance.
So (as the oracle user) I do the following:
$ sqlplus / as sysdba
SQL> create user test identified by passwrod;
SQL> grant sysoper to test;
SQL> exit
As root I do the following:
$ useradd -g oper testoper
$ su - testoper
Now as testoper:
$ sqlplus 'test/password as sysoper'
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
[code].........
As you can see, shutdown works, but startup doesn't. I have registered an SR with Oracle on this, but they're not being useful. The suggestion was that I should give testoper the to install secondary group. But this is not a secure solution as this now gives testoper privileges to do things in OB / OH that it really shouldn't be able to do. No other suggestions have been forthcoming. The reason for the suggestion however is the ownership / permissions on the Grid OH oracle executable:
[root@db03 ~]# ls -la /u01/app/11.2.0/grid/bin/oracle
-rwsr-s--x 1 grid oinstall 184286237 Aug 22 11:15 /u01/app/11.2.0/grid/bin/oracle
As you can see, it has group oinstall, so you can see why giving the user oinstall group would work. But in my view this is not satisfactory.
One thing that occurred to me is that in this environment I should arguably in fact be using srvctl to stop / start instances. But that means setting up a user with asmoper role (presumably) rather than oper. That user would then (again presumable) be able to stop and start other GI resources, which is not what I want.
I have two tables : oa_membership_dtl(in this created_by field is varchar2(200 byte) ,oa_partner_usr_dtl(in this table partner_userid is number(8,0) i need to do join on above fields.
I am using following two queries:
select * from oa_membership_dtl membership
join oa_partner_usr_dtl partner_user
on to_char(partner_user.partner_userid,'9999')=membership.created_by
select * from oa_membership_dtl membership
join oa_partner_usr_dtl partner_user
on rtrim(ltrim(partner_user.partner_userid||' '))=rtrim(ltrim(membership.created_by))
by using first data is not fetched but 2nd is working fine , i am getting the matched records using 2nd query.
whats the diff between to_char and || symbol?
I'm trying to group sets of data based on time separations between records and then count how many records are in each group.
In the example below, I want to return the count for each group of data, so Group 1=5, Group 2=5 and Group 3=5
SELECT AREA_ID AS "AREA ID",
LOC_ID AS "LOCATION ID",
TEST_DATE AS "DATE",
TEST_TIME AS "TIME"
FROM MON_TEST_MASTER
WHERE AREA_ID =89
AND LOC_ID ='3015'
AND TEST_DATE ='10/19/1994';
[code]....
Group 1 = 8:00:22 to 8:41:22
Group 2 = 11:35:47 to 11:35:47
Group 3 = 15:13:46 to 15:13:46
Keep in mind the times will always change, and sometime go over the one hour mark, but no group will have more then a one hour separation between records.
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?
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.
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)
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"
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 ||
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.
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.
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.
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 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 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'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,
