SQL & PL/SQL :: Merging Text For Same ID

Aug 18, 2010

I understand you can do this using cursors, but i need it in plain SQL; so that I can make this a correlated sub-query to another table using the id column.

Here's the original table:

id subid text
-- ----- ----
1 1 red
1 2 blue
1 3 green
2 1 yellow
2 2 black
2 3 orange

result should be:

id text
-- ----
1 red,blue,green
2 yellow,black,orange

SQL to Create and populate the table:

CREATE TABLE testStringJoin(ID number, subid number,text varchar2(50));
INSERT INTO testStringJoin values(1,1,'red');
INSERT INTO testStringJoin values(1,2,'blue');
INSERT INTO testStringJoin values(1,3,'green');
INSERT INTO testStringJoin values(2,1,'yellow');
INSERT INTO testStringJoin values(2,2,'black');
INSERT INTO testStringJoin values(2,3,'orange');

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: Merging Two Schema In One?

Feb 5, 2012

Requirement of merging two schema's into one. I have a client in two diffrent location . intially we setup the application with two diffrent instances of database for them for a smooth opration as they were not having any connectivity between the branches. Now they are moving their both branches together as one organisation. My application database table structure is same in both places.

View 21 Replies View Related

SQL & PL/SQL :: Merging Rows

Oct 17, 2011

there are 2 different types of bar-code which get store in single table

table_structure
item_id
group
bar_code

values are like

item_id group bar_code
1 FG 09878
1 FG 81011

i want output like

item_id group bar_code1 bar_code2
1 FG 09878 81011

What should i need to do...?

View 7 Replies View Related

SQL & PL/SQL :: Merging Into Multiple Tables

Feb 12, 2012

Can I use MERGE statement to insert the same thing into multiple tables?

Like below--

Merge into tbl1
using tbl2
when matched then update...
when not matched then insert all.

View 6 Replies View Related

SQL & PL/SQL :: Merging All Subpartitions In One Partition?

Nov 21, 2012

way to delete my subpartitions by keeping my datas, and keep it into my partitions. In fact, i want to remove my subpartitions, and keep my table partitionning. I already remove my subpartition template, but i don't want to do an insert as select on a new table wich will be partitionned (without subpartitions). ALTER TABLE myTable SET SUBPARTITION TEMPLATE ();

View 6 Replies View Related

PL/SQL :: Merging And Updating Rows

Jun 29, 2013

I have a table structure as follows  

Student(Id,First_Name, Last_Name, email, Contact, Address1,Address2,City,Edit_Date,Create_Date,Archived)  

Now if there is more than one row with same email the one with the latest edit date should be updated with missing fields by using same field value other rows (if the field is present in more than one row, the one with the next latest edit date is to be considered) and the archived status of all rows with same email except this master row must be set to 1.

The Create_Date must be set to the minimum of all the create_date values of rows with same email value The create table would be as follows:

CREATE TABLE student(Id NUMBER PRIMARY KEY,first_name VARCHAR2(30) NOT NULL,last_name VARCHAR2(30) NOT NULL,email VARCHAR2(30) NOT NULL,contact NUMBER,adress1 VARCHAR(30),adress2 VARCHAR(30),city VARCHAR(30),edit_date DATE,create_date DATE,archived CHAR(1))

 Sample insert statements would be: insert into student values

View 3 Replies View Related

PL/SQL :: Merging Two Queries Into A Single

Mar 19, 2013

Below are two queries , in merging two queries into a single so that out put coveres out put from below two queries

create table t_look as
select 1 a, 2 b, 3 c from dual union all
select 1 a, 20 b, 3 c from dual union all
select 1 a, 21 b, 3 c from dual union all

[Code]....

View 12 Replies View Related

Application Express :: Prefix Text Entered Into Text Area With Userid And Timestamp?

Sep 12, 2012

Have got basic form on a table and have a textarea which holds Notes added by user.

So Notes database field is updated on Save / Apply changes button being pressed.But would really like any text added / appended to the Notes field to be prefixed by userid and date / timestamp.

Is it possible via dynamic actions or Javascript to have any new text added / typed to be auto prefixed as per above.

Would only want the first key press in the filed to trigger the auto-prefix and if added text was deleted then the auto prefix to be deleted as well ?? If user doesn't press Save / Apply changes obviously want to leave existing Notes as is.

View 5 Replies View Related

Forms :: Is There Text Changed Trigger With Text Item Function

May 5, 2010

Is there text changed trigger with text item function like when_list_changed trigger of list item?

View 7 Replies View Related

Forms :: MS Word Rich Text Format In Text Item

Apr 17, 2012

Can I use rich text item on oracle form10G with some simple features like BOLD, UNDERLINE, ITALIC and if possible one more feature like spell check.

I Google my requirement, but mostly I found win word attachment. Further more if I can save this type of data in field then how can I print in report.

View 3 Replies View Related

Text :: Index Memory Parameter For Oracle Text Indexes

Sep 17, 2013

I am on Oracle 11.2.0.3 on Linux and have implemented Oracle Text.I created Oracle Text indexes with default setting. However in an oracle white paper I read that the default setting may not be right. Here is the excerpt from the white paper by Roger Ford:URL....(Part of this white paper below....)Index Memory.                                  

As mentioned above, cached $I entries are flushed to disk each time the indexing memory is exhausted. The default index memory at installation is a mere 12MB, which is very low. Users can specify up to 50MB at index creation time, but this is still pretty low. This would be done by a CREATE INDEX statement something like: CREATE INDEX myindex ON mytable(mycol) INDEXTYPE IS ctxsys.context PARAMETERS ('index memory 50M');  Allow index memory settings above 50MB, the CTXSYS user must first increase the value of the MAX_INDEX_MEMORY parameter, like this:  begin ctx_ adm. set_ parameter('max_index_memory', '500M'); end;  The setting for index memory should never be so high as to cause paging, as this will have a serious effect on indexing speed. On smaller dedicated systems, it is sometimes advantageous to temporarily decrease the amount of memory consumed by the Oracle SGA (for example by decreasing DB_CACHE_SIZE and/or SHARED_POOL_SIZE) during the index creation process.

Once the index has been created, the SGA size can be increased again to improve query performance." (End here from the white paper excerpt)My question is:

1) To apply this procedure (ctx_adm.set_parameter) required me to login as CTXSYS user. Is that right? or can it be avoided and be done from the application schema? This user CTXSYS is locked by default and I had to unlock it. Is that ok to do in production?

2) What is the value that I should use for the max_index_memory should it be 500 mb - my SGA is 2 GB in Dev/ QA and 3GB in production. Also in the index creation what is the value I should set for index memory parameter  - I had left that at default but how should I change now? Should it be 50MB as shown in example above?

3) The white paper also refer to rebuilding an index at some interval like once in a month:   ALTER INDEX DR$index_name$X REBUILD ONLINE; We are on Oracle 11g and the white paper was written in 2003.

View 5 Replies View Related

Forms :: Text Box To Accommodate Large External File Text

Dec 30, 2010

I have a requirement in one of my forms screen.I have a text box(large text area) which should display a help text file when i move my cursor on the topics displayed on the screen.know the code and the properties to be changed in the text box to accommodate large external file text.

View 1 Replies View Related

SQL & PL/SQL :: Converting HTML Format Text To Plain Text?

Dec 23, 2009

Is there any way to convert HTML format text to Plain Text ?

View 26 Replies View Related

Text :: Number Search Using Oracle Text

Jun 19, 2012

I'm new to Oracle Text. I want to implement search for the unique ids. Like google search when the user start typing 123 it need to brings anything starting with 123 and has show like entries how google will shows. When I add number 4 to like 1234 then it has bring numbers starting with 1234.

View 2 Replies View Related

Forms :: Export Text File With Same File As Imported Text File?

May 30, 2011

Is it possible to get the same name of file name from imported text file?

What i've is, I import the text file then I exported it.

can I get the same name of the imported text file?

View 2 Replies View Related

Generate A Text File

Apr 28, 2008

i have be requested to create a .txt file. Its for a program that will read the txt file i create to produce a letter i.e I will be extracting, TITLE, FORENAME, SURNAME etc

MR
JOE
BLOGGS

Here is the my code so far,

SELECT
LPA_INPUT.INPUT_TITLE,
LPA_INPUT.INPUT_SURNAME,
LPA_HISTORY.LPA_AMT,
LPA_HISTORY.ELIG_RATE,
LPA_HISTORY.RATE_REBATE,
LPA_HISTORY.RR_AMT,
LPA_HISTORY.LPA_APPLIC,
LPA_HISTORY.LPA_AMT
FROM LPA_HISTORY, LPA_INPUT
WHERE LPA_HISTORY.CLAIM_NO = LPA_INPUT.CLAIM_NO
----------------------------------------------------
Iv been asked to have these eight fields looping over and over for all the records in the database, So im not sure how to do that and how to generate it in a txt file?! I have to produce it in a script and not by a Export/Import wizard in sql server mangement studio!!!

View 11 Replies View Related

How To Find SQL Text History

May 27, 2011

I have SQL ID , sql with bind variables ("SYS_B_005")from AWR report which is taken last week.I need find the full exact sql text with bind variable value (sql ran 1 week before).

View 4 Replies View Related

SQL & PL/SQL :: Update String Anywhere In Text

Mar 3, 2011

We have table with a column REMARKS,where in text somewhere we are storing name also,Now we are planning to update only names with their service number

REMARKS Column Sample row text:

1.It has been created by John
2.John Created this
3.Assistance taken by John for this

Any query which can only replace john in all rows with 1234 .I can update the value if John comes at same position in all rows using SUBSTR() and concat rest of the text.But not getting how to update JOHN in rows at different positions.

View 7 Replies View Related

SQL & PL/SQL :: How To Get Exception Name And Error Text

Aug 21, 2012

I'm inserting to a table through a procedure, I want to log the rejected records to a log table with the execption / reason.How can I get the exception name and error text in PL/SQL ?

View 20 Replies View Related

SQL & PL/SQL :: Convert PLB To Text File?

Feb 11, 2011

is there anyway to convert plb file to sql file or text file

View 20 Replies View Related

Text :: How Does ACCUM Work

Jun 21, 2012

how ACCUM works. We are using a text index over XML documents and want to apply a very simple relevance weighting based on occurrences within a certain tag. For example, if TAG1 has a weight of 10, each occurrence in TAG1 should add 10 to the score. We have 13 Tags that should be weighted and multiple occurrences in different tags should be cumulated. So 3 occurrences in TAG1 (weight 10) and one occurence in TAG2 (weight 7) should result in a score of 37.

Just searching in TAG1 works as expected:
( DEFINESCORE( foo, OCCURRENCE ) WITHIN TAG1 ) * 10Three occurrences score 30.

Just searching in TAG2 works also as expected. One occurrence scores 7. However, when I combine both searches
(( DEFINESCORE( foo, OCCURRENCE ) WITHIN TAG1 ) * 10) ACCUM (( DEFINESCORE( foo, OCCURRENCE ) WITHIN TAG2 ) * 7)the score is 61 instead of 37. More interestingly, including all 13 tags (of which most do not contain the search word) lowers the score again.

I ended up with searches where two documents cont

View 7 Replies View Related

PL/SQL :: Oracle 10g - How To Encrypt The Text

Jan 29, 2013

how to Encrypt the text in oracle 10g? is there any built in function.

View 1 Replies View Related

Excluding Records That Contains Certain Text

Jan 30, 2012

I'm very new to sql. I'm using rapid sql to query an oracle db. I need to exclude records that contain the following text: reissuescholasticmedia tie-inmovie tie-in

Currently, I'm using the following query, which returns records that include the text I'd like to exclude (reissue, scholastic, media tie-in, movie tie-in).

Select
ISBN,
BOOK_TITLE,
PUBLISHER_DESCRIPTION,
AUTHOR_BYLINE,

[Code]....

this query so that it excludes records containing the text that I've indicated?

View 5 Replies View Related

Import From Text File To Table

Dec 14, 2006

I want to load data to a table and from a simple file text, using a Vb.net application which will connect to a oracle 10g , or a SqlServer or a MySql database, depending the params.

When i connect to a SqlServer Database i use the sql command "BULK INSERT CODPOSTAL2 FROM file.txt with( DATAFILETYPE = 'char',FIELDTERMINATOR = ';', ROWTERMINATOR = '
')"m" and it works fine.

With a DB Mysql i use "LOAD DATA INFILE file.txt INTO TABLE CODPOSTAL2 FIELDS TERMINATED BY ';''" and also works.

My problem is with Oracle. I tried the same example as MySql, but it gaves the error "wrong" ou "unknown command". I also tried in Sql*Plus but it seems to not recognised the command "LOAD".

Another thing, i can't use the Oracle Loader, it must be like this.

View 5 Replies View Related

Insert Text In A Specified Format As Number (x / Y)

May 29, 2012

Im trying to insert a number specified by the format for ex. S999.99, but the original text is of the form S99999, is there any way i can insert the data which will store it in the DB with the datatype as Number(5,2), the decimal is not present in the supplied value(through PreparedStatement) but needs to be put while inserting into the DB.

P.S.- 'S' in S999.99 signifies the sign(+/-).

View 1 Replies View Related

SQL & PL/SQL :: How To Search For A Particular Text In String Column

Oct 18, 2012

I have one table for eg. TB_Fruits.

In that i have one column FruitsName(Varchar)

In that column i am storing string in comma separated values.

Select FruitsName from tb_fruits;
Result: orange,banana,apple

Now the issue is suppose if i try to insert any of these fruits name again then it should not allow me to insert.

Suppose now if i try to insert ('grapes,banana')
or
('apple,grapes')

the orange,banana,apple can be in any position.

How to check if any of these names already exist or not in the column fruitsname?

I cannot use like or INstr function here. because the position is not fixed not even string.

View 1 Replies View Related

Forms :: Set Run Time Initial Value Of Text Box

Jun 4, 2010

I want set run time initial value of text box...?

what property need to use for that..?

View 2 Replies View Related

Forms :: How To Add Text Items At Runtime

Dec 24, 2012

I want add a text item at run time depending on the number of values retrieved i want to change the number of text items. Is it possible at run time to add a text item?

View 1 Replies View Related

SQL & PL/SQL :: Convert Excel File Into Text?

Aug 2, 2010

i want to convert an excel file into text and then into oracle table.

View 6 Replies View Related

Forms :: Verification Of Item Text?

Mar 19, 2013

I want to make a verification of an item text ,the user must enter a number of length 4. So I created a text item :number(4). and in when-validate-item trigger of this item ,I put this

if length(:block.empno) != 4 then
message('this item must containonly 4 digit');
end if;

=> the problem, when the user entered "0123" ,it should be acceptable !! but this is not the case ...

View 12 Replies View Related







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