SQL & PL/SQL :: Fetch All Three Address Line With Max Length Of 40 In Each Columns
Jun 9, 2010
there are 3 columns as Address1(max data length 80) , Address2(max data length 80), Address3 (max data length 80)..I need to fetch all the three address line with max length of 40 in each columns after concatenate address1,address2,address3
E.g Field data_length
Address1 49
Address2 32
Address3 33
now I need to concate all the char and split in 40 char per field i.e. 49+32+33 = 114
Field data_length
Address1 40
Address2 40
Address3 24
View 15 Replies
ADVERTISEMENT
Nov 1, 2012
create or replace PROCEDURE newprocedur(outname OUT VARCHAR2,outroll OUT NUMBER) AS
CURSOR c1 IS
select Name,Rollno,Section from emp;
BEGIN
Open c1;
fetch c1 into outname,outroll;
Here out of 3 columns in cursor is it possible to fetch only two columns using FETCH like i did above?
View 1 Replies
View Related
May 25, 2013
Lets say I have a table in ORACLE database like:
ACC_ID | ACC_AMT
111 | 10000
111 | 12000
111 | 14000
222 | 25000
222 | 30000
333 | 18000
333 | 27000
333 | 13000
333 | 15000
I want to get the output as:
ACC_ID_1 | ACC_AMT_1 | ACC_ID_2 | ACC_AMT_2 | ACC_ID_3 | ACC_AMT_3
111 | 10000 | 222 | 25000 | 333 | 18000
111 | 12000 | 222 | 30000 | 333 | 27000
111 | 14000 | null | null | 333 | 13000
null | null | null | null | 333 | 15000
I need each different ACC_ID with ACC_AMT in different columns. The table may have other different ACC_ID also, but I will fetch only what I need. What is the best way to do this?
So far I have tried this:
SELECT
(CASE WHEN ACC_ID=111 THEN ACC_ID END) AS ACC_ID_1,
(CASE WHEN ACC_ID=111 THEN ACC_AMT END) AS ACC_AMT_1,
(CASE WHEN ACC_ID=222 THEN ACC_ID END) AS ACC_ID_2,
(CASE WHEN ACC_ID=222 THEN ACC_AMT END) AS ACC_AMT_2,
(CASE WHEN ACC_ID=333 THEN ACC_ID END) AS ACC_ID_3,
(CASE WHEN ACC_ID=333 THEN ACC_AMT END) AS ACC_AMT_3
FROM <TABLE_NAME>
But I am not getting the desired result.
View 22 Replies
View Related
Jan 31, 2013
In this query split is a pipe line function to convert row(rows stored with , delimited) as columns like below
for ex for below query
SELECT * from TABLE(SPLIT('bbb003,bb004'));
out put is
bbb003
bb004
now i have to apply same function on column,column is storing data with ',' separated.and i have tried like but it's throwing missing expression. how i can use this function on entire column from this table.
SELECT * from TABLE(SPLIT(select candidates FROM ibis.cw_uploads_inprogress ));
View 7 Replies
View Related
Feb 23, 2013
I have two tables emp , emp address as below
emp table :
emp_id emp_name,
1 rajesh
2 suresh
emp address table :
emp id emp_addresstype emp address
1 O kukatpally
1 H Hitech
2 O Kolkata
2 H hydar nagar
I need the query to display the Office address & House address & emp id in single query
ex: 1,Kukatally,hitech
2,kolkata,hydarnagr
View 3 Replies
View Related
Jul 25, 2013
From two given tables, how do you fetch the values from two columns using values from one column(get values from col.A if col.A is not null and get values from col.B if col.A is null)?
View 2 Replies
View Related
Mar 24, 2011
FRM-40501: ORACLE error: unable to reserve record for update or delete.
ORA-24374: define not done before fetch or execute and fetch
My master-detail form has single canvas. For both blocks, master and detail, two tables joined together in each. One table to be updated, second table has some info for reference (query only).
I am getting these errors when in detail block the item from LOV is selected for existing record. This does not happen for new record inserted in detail block.
View 1 Replies
View Related
Feb 13, 2013
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
We are processing spatial data from another source to display in our GIS environment. The data is a set of multi lines. The gtype is 2006. A typical geometry looks like:
SDO_GTYPE 2006
SDO_SRID 31370
SDO_POINT.X NULL
SDO_POINT.Y NULL
SDO_POINT.Z NULL
SDO_ELEM_INFO (1,2,1, 7,2,1)
SDO_ORDINATES (105094.84, 195084.96,
105094.54, 195080.22,
105094.84, 195084.96,
105094.84, 195084.96,
105094.68, 195082.47 )
Now, this is not an actual multiline... it's just encoded as a multi line, but if you look at the coordinates you'll see that the end point of the first line is the same as the beginning of the second line (105094.84, 195084.96).
A better way to encode this geometry would be:
SDO_GTYPE 2002 <---
SDO_SRID 31370
SDO_POINT.X NULL
SDO_POINT.Y NULL
SDO_POINT.Z NULL
SDO_ELEM_INFO (1,2,1)
SDO_ORDINATES (105094.84, 195084.96,
105094.54, 195080.22,
105094.84, 195084.96, <---
105094.68, 195082.47 )
Is there a standard function in Oracle that does this conversion for me? Or do I have to write my own :)
View 4 Replies
View Related
Jun 16, 2010
I want to create a report by using one field and one text as columns name in layout but display the all the columns. I mention the 5 column names in query.how can I write function in summary column.
View 4 Replies
View Related
Apr 4, 2011
I am running a fairly busy Oracle 10gR2 DB, one of the tables has about 120 columns and this table receives on average 1500 insertions per second. The table is partitioned and the partitioning is based on the most important of the two timestamp columns. There are two timestamps, they hold different times.
Out of these 120 columns, about 15 need to be indexed. Out of the 15 two of them are timestamp, at least one of these two timestamp columns is always in the where clause the queries.
Now the challenge is, the queries we run can have any combination of the 13 other columns + one timestamp. In reality the queries never have more than 7 or 8 columns in the where clause but even if we had only 4 columns in the where clause we would still have the same problem.
So if I create one concatenated index for all these columns it will not be very efficient because after the 4th or 5th column the sorting would no longer be very useful and I believe the optimiser would simply not use the rest of the index. So queries that use the leading columns of the index in sequence work well, but if I need to query the 10th column the I have performance issues.
Now, if I create multiple single column indexes oracle will have to work a lot harder to maintain all these indexes and it will create performance issues (I have tried that). Besides, if I have multiple single column indexes the optimiser will do nested loops twice or three times and will hit only the first few columns of the where clause so I think it will kind of be the same as the long concatenated index.
What I am trying to do is exactly what the Bitmap index would do, it would be very good if I could use the AND condition that a Bitmap index uses. This way I could have N number of single column indexes which the optimiser could pick from and serve the query with exactly the ones it needs. But unfortunately using the Bitmap index here is not an option given the large amount of inserts that I get on this table.
I have been looking for alternatives, I have considered creating multiple shorter concatenated indexes but this still would not address the issue since many queries would still not be served properly and therefore would take a very long time to complete.
What I had in mind would be some sort of multidimensional index, I am not even sure if such thing exists. But essentially it would be some sort of index that could serve a query efficiently regardless of the fact that the where clause has the 1st, 3rd and last columns of the index.
So considering how widely used Oracle is and how many super large databases there are out there, this problem must be common.
View 12 Replies
View Related
Aug 9, 2013
I have a field "Email". The length of it is restricted to 30. But i mayget more than 30 characters. So how to trim the email address so that its max length is 30 characters.
View 5 Replies
View Related
Jan 8, 2013
I want to find the max length for a column in oracle, without querying each of the columns. Are these stats stored somewhere?
I have several fields defined as varchar2(4000). Not all of them use up 4000. Instead of querying each one ..max(column name) i want to explore if there is a way, i can find the max size stored somewhere? dba_tab_cols provides size of the field. is there any table that provides max used so far?
View 1 Replies
View Related
Jan 21, 2011
I have a two question.
Question 1:How to select all columns from table except those columns which i type in query
Question 2:How to select all columns from table where all columns are not null without type each column name which is in empty data
View 5 Replies
View Related
Oct 4, 2012
create table test_schema(
col1 varchar2(50)
)
insert all
into test_schema values ('this_is_a_test')
into test_schema values ('this_is_a_test_test')
into test_schema values ('this_is_a_test_test_xxxx')
into test_schema values ('this_is_a_test_test_aaaaaaaa')
select * from dual;
I want to get the length of the col1 value with maximum length of characters also with that field value.
i.e o/p is
this_is_a_test_test_aaaaaaaa length 28
how can I do it??
View 3 Replies
View Related
Jun 10, 2013
I want to create a table with a length greater than 30.I Thought there was a way to override the max length for for a table name in Oracle 11.2.0.2.I cant find a documentation that states how to get it done.
View 1 Replies
View Related
May 21, 2012
I have a problem to read in data from dmp file to target table.
I've created a dmp file using:
CREATE TABLE table_name
ORGANIZATION EXTERNAL
(TYPE oracle_datapump
DEFAULT DIRECTORY directory_name
LOCATION ('file_name.dmp')
) AS SELECT col1, col2, col3 from source_table;
[Code]...
The file created via db_link from target db to remote db and then the data are insterted into target db table target_table.
desc target_table
col1 varchar2(20)
col2 number
col3 number
There are no values longer than 20 characters in source table, but when I insert into target_table(col1, col2, col3) as select col1, col2 col3 from source_table; I get ORA-12899: value too large for column "target_table"."col1" (actual: 25, maximum: 20).
I gues it has something to do with how the oracle datapump stored the data in dmp file. When I select col1 from source_table where length(col1) > 20; I get two values which clearly are not longer than 20 characters.
Selected values are:
748473358
693197674
where the bug is hidden or any "normal" workaround?
View 17 Replies
View Related
Jan 31, 2013
I think the maximum length of table and column name in oracle 11g is 30 characters.I want to increase the limit as i want to import a mysql database that is having bigger table names.Can i preset the table name and column name length??
View 2 Replies
View Related
Jul 17, 2012
can I control at length of field at tuntime?i want to set the number of the size (length) at runtime.
(no by use "HORIZONTAL ELASTICITY"= variable/expand because I need a specific length)
View 1 Replies
View Related
Dec 27, 2011
I want to know how can I find/take from In parameter who is xmltype an email address ?
My function: (clob is cast to xmltype)
function submit(session_id in number, claim in clob) return xmltype
For example - I have more e-mail addresses in this:
<emails>
wtfn00b1@yahoo.com
wtfn00b2@inbox.com
wtfn00b3@mail.com
...
</emails>
I know that there is `extract` function for xml. How can I know if there is break or space between the emails ?
View 9 Replies
View Related
Dec 31, 2012
How to get ip address in forms 6i ?
View 6 Replies
View Related
Jan 25, 2012
I intend to write procedures/functions in PL/SQL to parse Name field into various fields : Title, FirstName, MiddleName, LastName, Gender.
E.g:
Nguyen Van A
Nguyen Thi B
After parsing, the result will be shown as:
TitleFirstNameMiddleNameLastNameGender
MrNguyenVanAMale
MsNguyenThiBFemale
Parsed.gif ( 4.94K )
Number of downloads: 2
I supposed that Title & Gender are realized through MiddleName field. If MiddleName's values in (Thi, Dieu) then Title is assigned as Ms, and Gender = "F". Otherwise, Title = "Mr", and Gender = "M".
2/ Another procedure/function is [i]ParseAddress with the requirement as:[/i]Address field is divided into Street, Group, Area, Ward, County fields
E.g.:No 6 Sum Street - Group 8 - Area 2 - ABCD Ward - London
The result:
StreetGroupArea Ward County
No 6 Sum StreetGroup 8Area 2ABCD London
I have tried coding by Visual Basic, it is OK. But if I interpret to PL/SQL ->it doesn't work.
View 1 Replies
View Related
Jan 25, 2012
I intend to write procedures/functions in PL/SQL to parse Name
field into various fields : Title, FirstName, MiddleName, LastName, Gender
E.g:
Nguyen Van A
Nguyen Thi B
After parsing, the result will be shown as:
TitleFirstNameMiddleNameLastNameGender
MrNguyenVanAMale
MsNguyenThiBFemale
I supposed that Title & Gender are realized through MiddleName field. If MiddleName's values in (Thi, Dieu) then Title is assigned as Ms, and Gender =
Female. Otherwise, Title = "Mr", and Gender = "Male".
2/ Another procedure/function is ParseAddress with the
requirement as:
Address field is divided into Street, Group, Area, Ward, County fields
E.g.:
No 6 Sum Street - Group 8 - Area 2 - ABCD Ward - London
The result:
StreetGroupAreaWardCounty No 6 Sum StreetGroup 8Area 2ABCDLondon
I have tried coding by Visual Basic, it is OK. But if I interpret to PL/SQL ->
it doesn't work.
View 3 Replies
View Related
Oct 6, 2011
if one of the columns is given as
ABC varchar2(10)
the size of the data in bytes that this column going to hold.
View 5 Replies
View Related
Aug 20, 2008
We are getting an error in our web application that is using Oracle.DataAccess.dll v2.111.6.20. When a couple users are using the site everything is fine, but when the load goes up we start getting the error ORA-24373: invalid length specified for statement. We are unable to duplicate this error in Visual Studio and don't know where to turn. We use stored procedure and the .dll to access the database for everything. Also, when this error occurs, it occurs indefenitely for all OracleCommand objects until the web server is rebooted. Also, when I attempt to remote debug with SQL Developer, the process doesn't even make it to the database!
View 3 Replies
View Related
Dec 8, 2011
I'm trying to select only codes from a column that are above a certain length. how would this be achieved? I've tried char_length(fieldname) > x in the where clause but i'm getting the error ORA-00904: "char_length" invalid identifier.
View 2 Replies
View Related
Apr 8, 2011
What is the Maximum Charter length can be given as a column name in a table?
View 3 Replies
View Related
Feb 19, 2007
how to get the type, length, flags in oracle by query..??
View 1 Replies
View Related
Sep 30, 2011
in the case below, when i created VIEW to add a new Department for some reason, its length is un-identified but i want the length as describe in table, how i can get ?
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning, OLAP and Data Mining options
devtest@ Test.DB> desc dept
Name Null? Type
----------------------------------------------------------------- -------- ------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
devtest@ Test.DB> l
1 select deptno, dname, loc from dept
2 union all
3 select 99 deptno, 'MY DEPTT' dname, 'MY LOCATION' LOC
4* from dual
devtest@ Test.DB> create view my_dept as
2 select deptno, dname, loc from dept
3 union all
4 select 99 deptno, 'MY DEPTT' dname, 'MY LOCATION' LOC
5 from dual;
[code]....
View 4 Replies
View Related
Dec 8, 2010
I am trying to create one Materialized view from a Select Statement and I am getting the following error
SQL Error: ORA-01723: zero-length columns are not allowed
I have checked the table and found nothing like varchar2(0) or char(0) , but the column in my select statment may get a Null value. Will it result in error like above ?
Normal View : I have created this because I think a materialized view would not be created if we have a Sub query.
CREATE OR REPLACE VIEW mx_test
AS
SELECT t."EXTN_SERVICE_REQUEST_NO",
t."EXTN_SERVICE_TYPE",
[Code]....
Materialized View :
CREATE materialized VIEW mx_ashok
AS
SELECT * FROM mx_test
Error :
Error starting at line 1 in command:
CREATE materialized VIEW mx_ashok
AS
SELECT * FROM mx_test
Error at Command Line:3 Column:16
Error report:
SQL Error: ORA-01723: zero-length columns are not allowed
01723. 00000 - "zero-length columns are not allowed"
*Cause:
*Action:
Do I need to use NVLs for all columns which may result in NULL value ?
View 9 Replies
View Related
Jul 11, 2012
I had a varchar2 variable which was storing some data and I could use the LENGTH function to get the length of the data. However, If I change it to CLOB. What is the best way to get the length?
v_clob_size:= (DBMS_LOB.getlength(v_clob)) / 1024 / 1024;
DBMS_OUTPUT.put_line('CLOB Size ' || v_clob_size);
another one is
LENGTHB(TO_CHAR(SUBSTR(<clob-column>,1,4000))) But this seems restricted to first 4000 characters only.
View 3 Replies
View Related