SQL & PL/SQL :: How To Extract Full Text From Clob Where Having Distinct Select
May 23, 2013
I am only able to extract only 4000 characters from the clob column "DESCRIPTION".how to get more characters or max for that column with the same query concept?
I have one query regarding how to retrieve the CLOB data.
The requirement is something that in the select statement there are around 20+ columns which i need to retrieve from around 5 tables after joining.
Since the result set after joining also will get duplicate values i need to use distinct keyword to filter the resultset. But in the 20+ columns there are 2 CLOB data columns which i need to retrieve.
Whenever i use DISTINCT i'm getting ORA-00932: inconsistent datatypes: expected - got CLOB error. I know that DISTINCT keyword cannot be used for CLOB datatypes.
Have 2 small tasks for regexp..where we need to extract numbers from the text & multiply & get the result.
Input field varchar2 : 5x a day x 10 days Output : 5 * 10 = 50 Select regexp_replace('5x a day x 10 days', '[^[:digit:]]' from dual;
The code extracts numbers ..but doesn't multiply & give the result.Have one more scenario as well.
Input field varchar2 : take 2 tablets (800 mg) by oral route every 4 hours while awake for 10 days Output : 2 * 800 * 4 * 6 * 10 = 384000 Select regexp_replace('take 2 tablets (800 mg) by oral route every 4 hours while awake for 10 days', '[^[:digit:]]' from dual;
For the above code, if it's hours ..we need to convert into day by multiplying with the required factor to make it a day.
I need write a query based on a bunch of user supplied IDs. The IDs will be pasted as plain text, one per row, by end user in a memo field in the reporting environment, and I need to do something like this:
SELECT PHONE_NUMBER FROM TELEPHONE WHERE ID IN('MEMO_ID1', 'MEMO_ID2',.....)
Reporting environment does not provide any tools to automatically convert plain text into IDs.
I've read so many different pages on this topic but I can't seem to get my query the way it needs to be. Here's the query:
select admitnbr, lastname||', '||firstname||' '||finitial, hphone, mobile, wphone, med_rec, dob from patients join schedule using (key_patien) join adtmirro using (key_patien) where appt_state = 'ON HOLD'
Because patients in my database can have multiple appointments "on hold" there are duplicates in the results. I only need 1 record per patient in order to forward this information into an automated dialer to contact that patient. I do NOT want to call the patient over and over again. Once will suffice. I'm trying to make a distinction on the column 'med_rec'. One row per 'med_rec' will be awesome but I can't find a way to create a distinct on that column.
SELECT DISTINCT a.emp_id, a.cal_id, TO_CHAR(a.ts_date, 'DD/MM/YYYY') tsdate, a.ts_date, 1 as days FROM tmsh_timesheet a INNER JOIN project b ON TO_CHAR(b.proj_id) = a.proj_id INNER JOIN tmsh_ts_calendar c ON c.cal_id = a.cal_id INNER JOIN (SELECT a.cal_id, a.emp_id, MAX(a.status) as status, a.create_dt, a.create_by FROM tmsh_stat_hist a
I have a select statement that selects all columns from the join of 3 oracle views. I would like to change it to select only the distinct rows, not sure how to code this. Here is my sql statement:
select * from myschema.view_1 acct Left JOIN myschema.view_2 freq
Having production system: 11.2.0.1 on Windows Server x64 Test system: 9.2.0.1 on Windows XP
Problem preface: to get all unique CASEID which should be checked up by biometric system.What i should check - all CASEs for different PERSONs having same PHONEs at least among one phone type (1..4).Real table contains little bit more than 10 million records.I made test scripts.
Below the DDL for test table creation: ------------------------------------------ -- Create CASEINFO test table ------------------------------------------ DROP TABLE CASEINFO; CREATE TABLE CASEINFO
[code]...
Below i've put SQL/DLL to make test data.number of records inserted 2 millions. PERSON_COUNT := #/8; ------------------------------------------ -- fill CASEINFO with sample data ------------------------------------------ DECLARE I INTEGER;
[code]...
Below SQL select to check the data in created table. ------------------------------------------ -- Check test data counters ------------------------------------------ SELECT 'TOTAL',count(*) from CASEINFO UNION ALL SELECT 'LEGAL',count(*) from CASEINFO where
[code]...
The PROBLEM is that i am experiencing HUGE perfomance problems on both test and production systems with that query:
select distinct b.caseid from CASEINFO a, CASEINFO b where (a.person<>b.person) and (a.sex=b.sex) and ( (a.phone1=b.phone1) or (a.phone1=b.phone2) or (a.phone1=b.phone3) or
[code]...
This query takes almost 90 minutes to execute.And i do not know how to avoid this.Full SQL file to make test attached.
I need to write a query in plsql to select records for first 3 distinct values of a single column (below example, ID )and all the rows for next 3 distinct values of the column and so on till the end of count of distinct values of a column.
eg: ID name age 1 abc 10 1 def 20 2 ghi 10 2 jkl 20 2 mno 60 3 pqr 10 4 rst 10 4 tuv 10 5 vwx 10 6 xyz 10 6 hij 10 7 lmn 10 . . . so on... (till some count) Result should be Query 1 should result ---> ID name age 1 abc 10 1 def 20 2 ghi 10 2 jkl 20 2 mno 60 3 pqr 10
query 2 should result --> 4 rst 10 4 tuv 10 5 vwx 10 6 xyz 10 6 hij 10
query 3 should result --> 7 lmn 10 . . 9 .. .. so on..
i want to insert the text lenght containing more than 4000 characters, that column datatype is in CLOB Even though in CLOB we can able to store upto 4GB. Its not allowing me to insert more than 4000 characters at a time , but we can able to insert by splitting the data by 4000 and can append remaining characters But i am receving the text contains more than 4000, that how can i split the data upto 4000
I have extracted data from table and write into one text via sqlplus utility in shell scripts. i got correct output. i am having two issues on the output file
1) Outfile file size is huge high compare then table segment data.
2) last column having extra space.
The output column is clob datatype. so i have added set long 50000 and set longchunksize 50000 parameter. after adding these only i got above issues. without two options, i am not getting this isssue but lines are wrapped.
#Set the scripts Path SCRIPTS_PATH="/usr/local/ccms/gpa/svr/scripts" echo $SCRIPTS_PATH
We have a large table of equally sized data blobs in our Oracle system and we'd like to select the whole table once into the memory. The corresponding tablespace is stored in SSD fast disks and is managed by ASM. However the achievable select speed (reading data into memory) of Oracle is not satisfactory. When we store the data in SSD disk using custom methods (e.g. in SQLite DB files) and load then into memory by multithreading (8 thread) techniques, the speed is more than 15 times that of Oracle.
Is there any way to optimize the oracle and ASM for increased full table select in our case. We tried FULL TABLE SCAN and PARALLEL hints and DB_FILE_MULTIBLOCK_READ_COUNT too, but with no success.
FYI:Our data blob is about 4.2kB and each DB_BLOCK_SIZE is 8kB. The ASM segment is configured for AUTO-ALLOCATE. The table is partitioned by HASH. Our oracle system is not RAC.
I'm updating a large piece of legacy code that does the following type of insert:
INSERT INTO foo_temp (id, varchar2_column) SELECT id, varchar2_column FROM foo;
We're changing varchar2_column to clob_column to accommodate text entries > 4000 characters. So I want to change the insert statement to something like:
INSERT INTO foo_temp (id, clob_column) SELECT id, clob_column FROM foo;
This doesn't work, since clob_column stores the location of each text entry, rather than the actual content. But is there some way that I can achieve the insert with one call to a select statement, or do I need to select each individual record in foo, open the clob_column value, read it into a local variable and then write the content to the matching record in foo_temp?
I have a "simple" query that needs to extract all of the XML messages stored in a CLOB column in a table for a data deidentification project. This query
select c.cc_data_area.getClobVal() as cc_xml from ci_cc c;
This query returns an ORA-22806: not an object or REF
cc_data_area is the clob column, and contains messages longer than 4000 characters.
I have a table that contains a CLOB column with pseudo-XML in it. I want to keep this data in an XMLType column so that I can leverage some of Oracle's built-in XML features to parse it more easily.
The source table is defined as: CREATE TABLE "TSS_SRM_CBEBRE_LOGS_V" ( "INCIDENT_ID" NUMBER, "EVENT_TYPE" VARCHAR2(100 BYTE) NOT NULL ENABLE, "EVENT_KEY" VARCHAR2(100 BYTE), "CREATION_DATE" TIMESTAMP (6) NOT NULL ENABLE, "CREATED_BY" VARCHAR2(100 BYTE) NOT NULL ENABLE, "LOG_MSG" CLOB);
The target (for testing this problem) table is defined as: CREATE TABLE "TESTME" ( "LOG_MSG" "XMLTYPE") My query is: insert /*+ APPEND */ into testme ("LOG_MSG")select XMLTYPE.createXML("LOG_MSG") as LOG_MSG from "TSS_SRM_CBEBRE_LOGS_V" b; In SQL*Developer, my error is: Error report:SQL Error: No more data to read from socket In SQL*PLUS and Toad, my error is: ORA-03113: end-of-file on communication channelProcess ID: 13903Session ID: 414 Serial number: 32739
I need to export large number of records from select into the text file. It's about 2milion records.I can do it by PLSQL (see below) where executing of process takes time too much. How to export to text file faster?
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.
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.
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.