Changing Sql Plus Column Width / Format?
Sep 20, 2004
I am using SQL Plus v9.2.0.1.0 and am having trouble with the column widths. By way of example:
Let's say the column is a varchar(2) and the column name is called V9ABC12345. When I see the result of my query I only get the heading name as V9 ie the maximum width of 2 characters. The table has over 100 columns and I know I can specify the column width using the format command but I am using the select * from table_name command
How do I change my formatting to include the column heading by default?
View 6 Replies
ADVERTISEMENT
Oct 20, 2011
Do we have measure with Oracle sequence width compare with column width.?
today i have ticket to check that.
Quote:Comparing sequence width to column width.We need to be Pro-active and continuous effort (not one time) meaning, any ccput by developer (for new sequence or new column which has corresponding sequence) need to be verified from this perspective
View 3 Replies
View Related
Dec 11, 2006
I'm having a problem with a column that is too short for it's data. It causes multiple lines for the same record. How can I change a column width in Oracle? I'm using a select statement to see the data.
View 1 Replies
View Related
Jul 16, 2013
I have a column named 'Comment'. Its have a huge data due to which the length of that column in interactive report get increased.
I need to fix the length to a desired value.I used following code in Region Footer:
<style> table.apexir_WORKSHEET_DATA td[headers=COMMENTS] { _width: 300px; min-width: 50px; max-width: 300px }</style>
Its solved the problem by fixing the width of the column but the column is not showing the full data now. It is hiding most of its part to fit it in that column.
View 1 Replies
View Related
Jul 10, 2012
How to control the column width on an interactive report. I can force it by making the column header really long but that is a crazy solution. How can I get control over this? I have columns that contain memo entries and if I use the header memo the report has tall skinny columns which makes reading very difficult.
View 1 Replies
View Related
Nov 23, 2009
I have a table in which years are stored in the form '2008/2009'. This is making it very difficult for me to do any calculations on that field and so I was wondering if there was a way to change the years (in a query and not in the actual table) so that if the year was '2008/2009' I would have just '2009'.
View 4 Replies
View Related
Jul 26, 2012
I want to get the last month of my table “a” (Which is in number format) and after that I want to change it to a date format and insert into my table “b”.
I have already done the max month query:
SELECT MAX(MONTH_ID) FROM Table_a;
SELECT DISTINCT MONTH_ID ---This data is the one that I would like to change format "mon-yyyy"
FROM Table_a
WHERE MONTH_ID = (SELECT MAX(MONTH_ID) FROM Table_a);
I am getting trouble with the to_char function, I have been reviewing how it works and how to handle it.
View 3 Replies
View Related
Oct 30, 2011
i have changed date's format and the calendar type in sqlplus through the following command so the date's format and the calender type both changed successfully and work, but in oracle form the they haven't changed.
alter session set nls_calendar='persian';
ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD';
ALTER SYSTEM SET NLS_DATE_FORMAT='YYYY/MM/DD' SCOPE=SPFILE;
Note: i know session would change the format temporally but i want to change it permanently so i tried alter_system but still no result
View 2 Replies
View Related
Sep 30, 2010
How the length of column width effects index performance?
For example if i had IOT table emp_iot with columns:
(id number,
job varchar2(20),
time date,
plan number)
Table key consist of(id, job, time)
Column JOB has fixed list of distinct values ('ANALYST', 'NIGHT_WORKED', etc...).
What performance increase i could expect if in column "job" i would store not names but concrete numbers identifying job names.
For e.g. i would store "1" instead 'ANALYST' and "2" instead 'NIGHT_WORKED'.
View 24 Replies
View Related
Jul 22, 2012
change my row value as column head. Here are my scripts:
Create table temp (code varchar2(3), head varchar2(4), value number(5));
insert into temp values('101','1101',500);
insert into temp values('101','1102',700);
insert into temp values('101','1103',600);
insert into temp values('102','1101',1000);
insert into temp values('102','1102',800);
insert into temp values('102','1103',900);
insert into temp values('103','1101',600);
insert into temp values('103','1102',400);
insert into temp values('103','1103',500);
select * from temp;
CODEHEADVALUE
1011101500
1011102700
1011103600
1021101100
1021102800
1021103900
1031101600
1031102400
1031103500
Now I like to get the above oputput as under:
Code 1101 1102 1103
101 500 700 600
102 100 800 900
103 600 400 500
View 4 Replies
View Related
Aug 9, 2012
I am trying to update all rows of 100 column of a table with '0'. The column name is sequentially increasing one like EMP_1,EMP_2,EMP_3, etc. I tried using the below code but I am getting ora-06550 and ora-00927 error's.
begin
FOR i in 1..100 loop
UPDATE EMP_DETAILS SET EMP_'||i||' =0
END LOOP;
COMMIT;
END;
View 6 Replies
View Related
May 3, 2013
I have a report linked to a form. The link column is currently ROWID and I want to change it to the Primary Key of the DB table, which is called ID.
I have done this before on a previous report/form where that ID was a display column and it works fine. Now I'm trying to copy what I did, which is this:
On the report page, go to Interactive Report attributes, scroll down to Link Column, and for Item 1, change the Name and Value.
The problem is that Name does not list P52_ID. If I type it in, it throws an error when I run the page.
I think I added ID, which is also a Display column, later on, so I wonder whether Apex has a refresh issue and can't "see" P52_ID.
View 1 Replies
View Related
Apr 24, 2013
Using Application Express 4.2.1.00.08
Any problem using 4.2's One Level Tabs - Content Frame page where you have a parent region (of type content frame Body Container ) while number of child regions (of type Hide & Show Region - Borderless ), if you want to re-order them by changing the sequence, it has no effect when you run the page.
The regions still show in the same order top to bottom as it did before the update of sequences. Heading links (to show/hide child regons in content frames) has the same ordering as before.
View 3 Replies
View Related
Jul 26, 2013
I have an amount field and I want the o/p in the format of $9,999,999.99
like for 1500 it should display $1,500.00
for 25000 it should display $25,000.00
for 25000000 it should display $25,000,000.00
Test case
add comma from last 3rd position ,and append a $ in the start with two decimal places
View 15 Replies
View Related
Jun 10, 2011
I have output from select something like :
A_PID B_TEXT id_f
-------------- ----------------------------- -----------
23 B_text sample 9888888
and my question is how can I format the output to have it thinner .
I can do it for text: COLUMN FORMAT B_TEXT A5, but i dont know to apply it for fields with number e.g. A_PID column.
View 4 Replies
View Related
Feb 1, 2011
I have to display a record in column format. I have tried doing it using COUNT & DECODE as in below script.
SELECT COUNT (DECODE (a1.application_status, 21, a1.ppid, NULL)) cnt_app21,
COUNT (DECODE (a1.application_status, 22, a1.ppid, NULL)) cnt_app22
FROM (SELECT a.application_status application_status, a.ppid ppid
FROM vp_recon_upload_dtls a, vp_ppid_upload_info b
[Code]...
This gives output as:-
cnt_app21 cnt_app21
3 1
The second query is little different from above as it has 2 more tables joined and more conditions than first query.
SELECT SUM (DECODE (a2.application_status, 21, a2.rate_amount, NULL)) sum_app21,
SUM (DECODE (a2.application_status, 22, a2.rate_amount, NULL)) sum_app22
FROM (SELECT a.application_status application_status,
d.rate_amount rate_amount
FROM vp_recon_upload_dtls a,
[Code]....
Second query output is :-
SUM_APP21 SUM_APP21
222 111
My requirement is to display all the four values in one row as:-
cnt_app21 cnt_app21 SUM_APP21 SUM_APP21
3 1 222 111
One easy way is to create two views and select values from them.
But for some reason I have to write in a query as there would be more dynamic conditions added to it.
View 2 Replies
View Related
Mar 14, 2007
I'm trying to sort out my coloumn headers and make things a bit neater using the code....
column name format A9 heading 'Emp|Name'
column birthdate format A9 heading 'Birth|Date'
select name, birthdate
from Employee
where ....;
It seems to display all the results in double space if i use more than one 'column' statement though? One statement works fine, what've i gotta change?
View 1 Replies
View Related
Jun 1, 2012
I want to get the following format of data in row format using PLSQL. I want to do that in using a shell script also.
Suppose I have the data like this
123
45
2
789
how to write it in PLSQL as follows:
1427
25 8
3 9
View 3 Replies
View Related
Jun 8, 2011
I have job, working hours, employee id, employee name in test_emp table. The job name and employees are not fixed in this table and it varies from project to project. We don't know how many employees are there and needs to be fetching on runtime.
I have the data like below
JOB---------WRKHR---EMPID-----EMPNM
ANALYST-----10------5478------RAMESH
MANAGER-----10------4258------SACHIN
LEAD---------10------4789------VIVEK
DEVELOPER---20------5632------ROHIT
ANALYST-----20------5843------VIRAT
MANAGER-----20------4789------VIVEK
PROGRAMMER-30------5479------SURESH
LEAD---------30------4258------SACHIN
DEVELOPER---30------5231------PRAVEEN
I need the output like below format.
JOB---------RAMESH--SACHIN--VIVEK--ROHIT--VIRAT--SURESH--PRAVEEN--TOTAL
MANAGER-----0--------10-------20-----0-------0------0---------0---------30
LEAD---------0--------30-------10-----0-------0------0---------0---------40
ANALYST-----10-------0---------0-----0------20------0---------0---------30
DEVELOPER---0--------0---------0-----20------0------0---------30--------50
PROGRAMMER-0--------0---------0------0------0------30--------0---------30
--TOTAL-----10-------40--------30-----20-----20-----30--------30--------180
View 6 Replies
View Related
May 16, 2013
10g- 10.2.0
This is my query
select to_char(exp_Date,'Mon-YYYY') dt, count(*) from exp_main
where exp_type like 'Income%Photo%'
group by to_char(exp_Date,'Mon-YYYY')
order by exp_date
When I run this I get :not a GROUP BY expression
If I remove the order by, it works fine.
Is there a way to order by since the output of the query is character-sorted.
I did search online most have suggested to use order by column_name. But does not work for me.
View 6 Replies
View Related
Jul 9, 2012
CREATE TABLE EMP(NAME VARCHAR2(10 BYTE))
INSERT INTO EMP VALUES ('C');
INSERT INTO EMP VALUES ('A');
INSERT INTO EMP VALUES ('T');
SELECT xmlelement("NAME",NAME) FROM EMP;
I am trying to convert column data to xml format, but I get this error message:
Quote:The query fails because all columns types are currently not supported. I am using:
Quote:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
SQLTools 1.5.0 Beta build 9 as EDITOR
View 7 Replies
View Related
May 8, 2011
I have an Excel spreadsheet that just had a format change. The fourth column is new. Order of columns in Excel is:
oldcol1
oldcol2
oldcol3
oldcol4
newcol
oldcol5
Sqlldr script is in order of the .csv file just listed. Oracle table is in order
oldcol1
oldcol2
oldcol3
oldcol4
oldcol5
newcol
When I run the sqlldr script, the information is loaded:
oldcol1
oldcol2
oldcol3
oldcol4
newcol in oldcol5
so that all the information is loaded incorrectly. Out side of having to change the table, is there anything I can do to make it load correctly?
View 8 Replies
View Related
Dec 12, 2011
I have a report and require the output in xls/xlsx (Excel) format. Currently following properties under System Parameters in Report Builder 6i have been made
Desname = The path for e.g: \C:sz-serverc$ihelp_workingxls
pt_sample_report.xls
Desformat = Delimited
Destype = File
The rest parameters has default settings I have not changed any of the rest User parameter.The report comes up in excel format but totally in distorted manner. i.e; All the column names of report are displayed first in vertical format followed by actual rows for the report.
The requirement is like this
[Select all] [Show/ hide]
Student ID Student Name Fathers Name Guardian Name
1 sample1 sample2 sample3
2 sample4 sample5 sample6
And it is displaying like this
Student ID Student Name Fathers Name Guardian Name 1 sample1 sample2 sample3
Student ID Student Name Fathers Name Guardian Name 2 sample4 sample5 sample6
View 2 Replies
View Related
Feb 10, 2010
i edit forms
web.cfg
width=100%
height=100%
so all applet is loaded full screen in browser.But i want to load particular form with different width and height
View 1 Replies
View Related
Nov 10, 2010
I did a search on this topic and did see the ASK TOM response that storing all varchar2 fields as (2000) or what not is a bad idea based on an array fetch that developers may use etc. However I'm not sure that applies to my specific question, and the other examples he gave certainly didn't apply. So I'll pose the question a different way:
Question #1: Is there, for example, a performance difference between setting a field as varchar(2000) and varchar(25) if I was just running a native SQL query using a front end tool like TOAD?
Question #2: If I also need to index that field, will it take longer to index a varchar(2000) field than a varchar(25) field, assuming the same data is in both fields?
View 2 Replies
View Related
Oct 10, 2013
11gxe , apex 4.0 , firefox 24 ,i want to decrease width of a specific region , how ?
View 3 Replies
View Related
Jun 25, 2013
i have a problem in my ODI 11g with the load into Oralce table of a fixed width file, i configured all the datasource in ODI and when i do view data i see all correct, the end of file is signed like "0D0A" but when i try my load interface i receive the message that my last field is more big than the one declared.
My file have an header of fields and the last field is a data-field of 2000 characters. I controlled and is really fixed the length cause is a COBOL file from a Mainframe. So it looks that ODI don't understand the end of that field and go ahead to the other, i just tryed to enlarge the limit but is always more big like if the file is shifting on the right.
Have i forgot some configuration in some place? The definition of the file present the end of file like Microsoft hexadecimal \u000D\u000A i try all the combination there but no way to avoid this problem.
View 1 Replies
View Related
Nov 13, 2013
Apex 4.2I have a page with a calendar on it. I have several issues with formatting the data within each cell. Currently, I use a substitution string to place the id in the calendar cell.
Clicking the id will link to another page. What I notice is that for each id on a calendar cell (or date), it lists them one under the other. Is there any way to list them side by side? Also, I need to make changes to the calendar cells. I would like to expand the width and the height, but am not sure where to do this.
View 1 Replies
View Related
Apr 14, 2013
I understand when I decrease the width or height of the paper layout, it may cause this error if any of the objects in my report may not fit in the new layout.
But for me this error occurs even when I increase the size of my report paper layout!
View 1 Replies
View Related
Feb 18, 2011
I have been implementing a script to change a lot of data in a database production.Because of this the database will be 100% dedicated to the execution of that script, in the sense that nothing else will be running in this period (the application will be stopped).
what can i do to improve performance of that execution? is there any oracle manual online for this type of problem? I do not know if it's possible, but I'm thinking of things like disabling locking mechanism (if possible I could run instead of a process many processes in parallel), disabling index growing (during the process), disabling constraints.
View 1 Replies
View Related
