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
ADVERTISEMENT
Apr 19, 2013
I have a table that has hierarchical data within it. I need to select this data in a specific way, showing the hierarchy.
E.g. Data in table (Key is unique)
Lvl KeyParKey HasChild
1k101
1k200
1k301
1k401
2k34k10
2k22k11
2k24k10
2k13k30
2k52k30
2k35k30
2k13k30
2k11k40
3k56k220
3k109k221
3k67k220
4k61 k1090
Etc etc....
That�s generally the format the values would appear in the table if I just did a standard select. I want it displayed in a more hierarchical Parent � child way.
The format I need to get out is as follows:
Lvl KeyParKey hasCh
1k101
2k34k10
2k22k11
3k56k220
3k109k221
4k61 k1090
3k67k220
2k24k10
1k200
1k301
2k13k30
2k52k30
2k35k30
2k13k30
1k401
2k11k40
View 1 Replies
View Related
Jul 22, 2013
I have an application connected to Oracle 11g that sends its own querys to the db based on what the user is clickng on. The applicaiton is connected via one user id and I was wondering, is there a way that I can capture the tiem each query starts, the sql itself, and the amount of time it took to fetch the data?
View 7 Replies
View Related
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
May 29, 2012
I'd like to know if it is possible to track DML actions issued on a specific table by a specific user, for example , i tried :
AUDIT SELECT on SCOTT.DEPT by HR by ACCESS;
I get an error, where is my syntax error ?
i want to know if it's possible to do it without trigger ?
View 7 Replies
View Related
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
May 6, 2010
query only specific date only. example: '06-MAY-2010'.
1.from the statement below, it will display out more than 06-mAY-2010.
2.if i want more the date from 03-MAY-2010, 04-MAY-2010 and 05-MAY-2010.
select * from PNG_ORA_SERVER_PERF where SERVER_NAME = 'MLYDESPINTF1' and DATE_TIME >= TO_DATE('06-MAY-2010','DD-MM-YYYY');
View 3 Replies
View Related
Apr 14, 2011
I want to convert a number to this format using below query but i'm getting error. how to correct the below query.
SELECT TO_CHAR(12345678, '99G9999D99') FROM dual;
error:-###########
View 2 Replies
View Related
Apr 11, 2011
I have a database which consists of various orders and various field.
I have a variable called createddatetime . I want that whenever i should run the database it should display records from
Yesterday 06:00:00 am to Current Date 05:59:59 am
Now to implement this i tried to put this syntax
and to_char(Createddatetime,'dd/mm/yyyy HH24:mi:ss') between 'sysdate-1 06:00:00' and 'sysdate 05:59:59'
But nothing comes up
where as definitely there are records between times because when i do and Createddatetime between sysdate-1 and sysdate I see valid records coming up.
View 3 Replies
View Related
Apr 5, 2008
i have columns called data_received(data received from server) , URL (sites visited)
now i want to display top 50 sites based on highest data_received.URL column will have some sites repeated.
View 1 Replies
View Related
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
Jul 4, 2012
In details table i am having loc is column in which data is like this(Jones,180),(US,host name),(job, company),(id,0122)
output should be it should take only first value from ().
View 2 Replies
View Related
Jan 24, 2013
I have the following requirement and we are on R12.1.3 with Oracle 11gR2 database on a Linux R5 machine.
1) Sensitive data such as SSN and credit card needs to masked in the oracle seeded table like per_all_people_f etc.
2) This original data should not be visible either from the database (apps user) or via the application. They should both provide the decrypted data to the users.
3) There are a set of users that need point # 2 overridden. That is, they should be able to see the decrypted data using sql on the tables or via the application (front end).
View 4 Replies
View Related
Nov 9, 2012
I'd like to stop Recovery in specific time. For instance ; My db works Day-1 and Every day Recovery starts 20:00 and finish 05:00. If it is possible that when DB time reach to sync 03:00, I'd like to stop recovery.
DB version Oracle 10.2.0.5 .....
View 5 Replies
View Related
May 29, 2013
Can we make Data Guard to take clone Primary Database at Specific Time?
e.g. Clone Primary Database at 10 AM and at 6 PM?
View 10 Replies
View Related
Aug 5, 2010
I have requirement of loading a part of the flat file that contains many headers and lines info. The program has to load the lines whose header recrd_type is 05 using SQL*LOADER.
eg of flat file.
Header
trans_code comp date rec_type
------------------------------------------------------------
8 12800002 0729201005
transcode_line acct date refrence
------------------------------------------------------------
4424604001002738307272010 24427330207710017569675
4424604001002738307272010 24427330207710017569675
View 7 Replies
View Related
Nov 25, 2012
the more customers we have for our software solution the more individual oracle objects (Tables, Packages, Functions, etc.) we have in our scheme. Right now we separate these by giving them identifying names like "X_CUSTOMER1_TABLENAME" e.g. (I know ..... )
This is not very practical when keeping our reference clean and when deploying/syncing our reference with a customer db: One customer would receive the objects of other customers on a deploy.
Is there a common solution to this problem? We were thinking about having a separate scheme for each customer. That way we would have our standard, untouched scheme with the basic functionality and the customer schemes with the individual content.
To make it a bit more concrete: We have around 100 basic tables that make the most of the content/functionality of the software. Each customer might have between 1 -5 additional tables with "custom" data that is used in conjunction with the standard objects in individual packages, functions etc. The installations will be made on our customers systems. So I have in mind to have schemes 0001, 0002, etc for each customer IN OUR REFERENCE. But we would then deploy only the scheme for that certain user when installing on their system. So for example for customer 0001 I would deploy the STANDARD and 0001 scheme
View 6 Replies
View Related
Nov 12, 2013
How to select a substring in oracle up to a more than one specific characterfor ex : 121.051^NP: FAMILY PRACTICE ( trim the values before ^ ) 121.051^*NP: FAMILY PRACTICE (trim the value before *). with below function I can only get rid of ^ , I want both the specific characters ^ and ^* to be removed at the same time.
SUBSTR(p.phys_sub_grp_2_desc,INSTR(p.phys_sub_grp_2_desc, '^') +1)
View 7 Replies
View Related
Aug 1, 2012
I'm having some difficulty securing my Oracle APEX + Oracle HTTP Server environment.
There are currently around 20 Applications running on the APEX Server, and I'd like to publish 2 of them Externally for users.
I have added a certificate to Oracle Wallet Manager to secure the OHS, but I can access all the Applications on the APEX Server by just changing the Application ID suffix.
How can allow only two applications to be accessible via the internet?
View 11 Replies
View Related
Jan 18, 2013
I was asked if it was possible to restrict which users / or client IP's connect to my Oracle 11.2 database. I guess I could just shutdown the listener and have me and one other DBA connect to it via SSH / LOCALHOST but I was wondering if there was a more DBA specific way to restrict client connections to just two specific IP's over the Oracle listening port?
View 5 Replies
View Related
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
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
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
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
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
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
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
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
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
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
