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


ADVERTISEMENT

PL/SQL :: Aligning / Formatting Records In Text File

Feb 26, 2013

I have a query then I write it to a text file using utl_file. However, I'm having some trouble formatting the data itself on the text file. =(
How would I align these records against its header considering the length of the Name/subscriber no. column which is not constant. Would LPAD work?

ID       Name                       Subscriber No

1     James Smith               12454654     
123     Scott Kevin Joseph           4564564
21     Dana Marice Curtis          455
2     Stephanie Hans Walter      23134

View 1 Replies View Related

PL/SQL :: Read Only Unique Records From Text File

May 10, 2013

i have 20000 records from text file.here i want to read only unique records from text file using collections.

View 3 Replies View Related

Forms :: Records Not Displaying In Specific Text Item

Feb 14, 2012

I have for example two text items(number of records to display is set to 9). What I want is when i go to studentprofile block, it should automatically shows his subjects that are already taken up. the first text item is for 1st semester subjects and the other for 2nd semester.

here is the structure of my subjsec table:

ID number(1)
subj_code varchar2(10)
day varchar2(10)

and for subjects table:

subj_code varchar2(10)
description varchar2(50)
units number(1)
semester varchar2(10)

I restricted subjects that are in 1st sem. So i did something like this in my

DECLARE

CURSOR studgrade_cur IS
SELECT g.stud_id, g.grade, g.subj_code, s.description, s.units, s.semester
FROM studgrades g , subjects s
WHERE g.subj_code = s.subj_code
AND g.stud_id = :Studentprofile.student_id;

[Code]....

View 32 Replies View Related

Oracle Forms PL/SQL - Create Text File For Multiple Records?

May 15, 2003

i'm working on a project right now using Oracle Forms 6.0 and Oracle 9i. after i create a record and save the data in the table, how can i generate/create a text file of that particular record? i need this text file in order to run it in another computer and somehow upload the data in the text file to another database.

i will also need to create the text file for multiple records.

View 7 Replies View Related

SQL & PL/SQL :: Export Large Number Of Records From Select Into Text File

Feb 9, 2011

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?

DECLARE
fileid UTL_FILE.file_type;
BEGIN
fileid := UTL_FILE.fopen ('VRS_CEM', 'cust.txt', 'W');
UTL_FILE.put ( fileid, 'IMSI|MSISDN|CONTRACT');
UTL_FILE.NEW_LINE(fileid);
[code]....

View 8 Replies View Related

Forms :: How To Show Total Number Of Records In Text Item

May 3, 2013

I have a multi record field of five rows. And 3 values.

the values are

IT
CSE
ECE

And i have one text item name is COUNT.

how to show the total number of records in Text item .

View 10 Replies View Related

PL/SQL :: Enable Dbms_job To Run On Every Day At 4am Excluding Fridays?

Jun 7, 2013

how to enable dbms_job to run on every day at 4am excluding Fridays.

View 7 Replies View Related

SQL & PL/SQL :: How To Calculate Between 2 Dates Excluding Saturday And Sunday

May 3, 2012

select to_date('25-04-2012', 'DD-MM-YYYY') + rownum -1 dt
from dual
connect by level <= to_date('05-05-2012', 'DD-MM-YYYY') - to_date('25-04-2012', 'DD-MM-YYYY') + 1;

The above query returning the following output,

DT
04/25/2012
04/26/2012
04/27/2012
04/28/2012
04/29/2012
04/30/2012
05/01/2012
05/02/2012
05/03/2012
05/04/2012
05/05/2012

here I need to exclude the Dates which comes on 'saturday' and 'sunday' and also the common holiday..Here it is '01-May-2012' and I need the output like the following,

DT
04/25/2012
04/26/2012
04/27/2012
04/30/2012
05/02/2012
05/03/2012
05/04/2012

I need the common query to calculate between any two dates.

View 2 Replies View Related

Server Utilities :: Excluding Data Of Some Tables In EXPDP

Oct 5, 2013

i want to exclude only data of some particular tables not complete table object when exporting using expdp.

View 13 Replies View Related

SQL & PL/SQL :: Remove Spaces By Excluding Double Quotes From A String

Oct 29, 2012

I want to remove more than one space from a string by excluding double quotes.

For example:

I/P: Item .getChildByType(" Agreement").getParent( ) .hasChildByType("Agreement ")

O/P : Item.getChildByType(" Agreement").getParent().hasChildByType("Agreement ")

View 17 Replies View Related

Export/Import/SQL Loader :: EXPDP Excluding Tablespaces

Sep 9, 2013

I am using EXPDP to export a schema (Oracle 11g R2), and I need to exclude all the tablespaces that the schema is using. I have seen exluding Oracle objects like functions, tables, packages, indexes...etc. But I have not seen excluding tablespaces.Iis it possible to exclude tablespaces while creating the export dump?

View 5 Replies View Related

SQL & PL/SQL :: Find Missing Date For A Year Excluding Saturday And Sunday

Feb 3, 2011

I given the table name,column name,datatype and sample record in the table. I have given the sample record for 01-jan-2008 to 8-Jan-2008, but in the real thing it will be for 30 years.

My Requirement:

For each class_no (202,203..), I need the missing date excluding weekends (sat, sun), I have provided the sample output below.

Table Name : ABC

Column Name : Class_no Data Type : Number
Column Name : Class_DateData Type : Date

Sample Record in the Table :

Class_noClass_Date
202 1-Jan-08
202 2-Jan-08
202 7-Jan-08
202 8-Jan-08
203 1-Jan-08
203 2-Jan-08
203 3-Jan-08
203 7-Jan-08
203 8-Jan-08

OUTPUT:

Class_noClass_Date
202 3-Jan-08
202 4-Jan-08
203 4-Jan-08

View 5 Replies View Related

Server Utilities :: Faster Impdp Excluding Indexes And Constraints

May 24, 2012

We need to setup a test system using data from one of the offsite customer location.The option we would like to use is impdp with network link. Below given step will make import faster if we exclude indexes and constraints

Steps
Import schema excluding constraints,rf_constraints,indexes with metadata only
Import schema with data only
spool ddl using dbms_metadata.get_ddl from Source for constraints,rf_constraints and indexes
execute on Destination the Index creation ddl
execute on Destination constraints and rf_constraints ddl

View 1 Replies View Related

PL/SQL :: What Kind Of Exception Can Raise A Select Statement Excluding NO_DATA_FOUND

Oct 23, 2013

what kind of exception can raise a select statement excluding NO_DATA_FOUND; For example i try to run the following: select * from departments where departments_id=11; In a situation like that what kind of error oracle can raise?I'm asking this because i have some procedure that just do a select statment and i want to know if there is a valid reason to put the exception others at the end of the procedure.

View 25 Replies View Related

Backup & Recovery :: Restore And Recover Database Excluding Tablespace

Jul 27, 2011

I am doing some test, seeking your expert opinion.I have a database and have the following backup strategy.

1) Database running in archive log mode.
2) First I backup all the table space excluding one tablespace 'DP_TS_LOB'.

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE 'DP_TS_LOBS';
RMAN> BACKUP DATABASE;

3) I take a separate Backup of DP_TS_LOB tablespace.

RMAN> BACKUP TABLESPACE DP_TS_LOB;

4) I backup all the archived redo logs.

RMAN> SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
RMAN> backup archivelog all delete input;

Now I wanted to restore the database from the backup excluding the DP_TS_LOB and bring it up and running. DP_TS_LOB is huge and used only on certain work flows. I don't want the database to be down until we restore DP_TS_LOB. I wanted to restore all the other tablespace and bring the database up operational and restore and recover the DP_TS_LOB tablespace datafile's in background taking the datafiles of DP_TS_LOB tablespace offline.I tried the following but unsuccessful.

RMAN> restore controlfile to 'g:ctl_bckctl_01' from autobackup;
RMAN> restore spfile to 'g:ctl_bckspfile' from autobackup;
RMAN> sql "create PFILE = ''G:ctl_bckPFILE'' from SPFILE = ''G:ctl_bckSPFILE''";

Update the new parameter with new control file name. Copied the control file and parameter file to corresponding location.

RMAN> startup nomount pfile=D:appdivaproduct11.1.0db_1databasepfile;
RMAN> restore database skip tablespace 'dp_ts_lob';
RMAN> alter database mount;
RMAN> sql 'alter database datafile 8 offline'; ==> datafile of DP_TS_LOB tablespace.

First I assumed "Recover Database" will only recover online database files. But got the following error.

RMAN> recover database;

Starting recover at 27-JUL-11
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/27/2011 18:23:03
RMAN-06094: datafile 8 must be restored

Then I tried recovering data file separately, but same error.

RMAN> recover datafile 1;

Starting recover at 27-JUL-11
using channel ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/27/2011 18:24:30
RMAN-06067: RECOVER DATABASE required with a backup or created control file

View 4 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

Reports & Discoverer :: Excluding SQL Query Data When Linked With XML Query?

Apr 26, 2013

I have data in a table and another in XML file,I used SQL query to retrive the data placed on the table, and link this query with XML query that retrieves the data stored in the xml file. The data stored in the table and xml file sharing a key field, but the xml contents are less than what in the table.I want to show only the data shared between the two queries, how can I do that?

e.g.:

Table emp:

e_id | e_name | e_sal
023 | John | 6000
143 | Tom | 9000
876 | Chi | 4000
987 | Alen | 7800

XML File

<e_id>
143
876

So, I want the output to be:

e_id | e_name | e_sal | e_fee
143 | Tom | 9000 | 300
876 | Chi | 4000 | 100

View 2 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

SQL & PL/SQL :: Oracle 10g - Update Records In Target Table Based On Records Coming In From Source

Jun 1, 2010

I am trying to update records in the target table based on the records coming in from source. For instance, if the incoming record is present in the target table I would update them in the target else I would simply insert. I have over one million records in my source while my target has 46 million records. The target table is partitioned based on calendar key. I implement this whole logic using Informatica. Looking at the informatica session log I find that the informatica code is perfectly fine but its in the update part it takes long time (more than 5 days to update one million records). find the TARGET TABLE query and the UPDATE query as below.

TARGET TABLE:
CREATE TABLE OPERATIONS.DENIAL_REGRET_FACT
(
CALENDAR_KEY INTEGER NOT NULL,
DAY_TIME_KEY INTEGER NOT NULL,
SITE_KEY NUMBER NOT NULL,
RESERVATION_AGENT_KEY INTEGER NOT NULL,
LOSS_CODE VARCHAR2(30) NOT NULL,
PROP_ID VARCHAR2(5) NOT NULL,
[code].....

View 9 Replies View Related

SQL & PL/SQL :: Procedure To Delete Records / Count Number Of Records Has Been Deleted

Feb 21, 2011

I have written the following PL/SQL procedure to delete the records and count the number of records has been deleted.

CREATE OR REPLACE PROCEDURE Del_emp IS
del_records NUMBER:=0;
BEGIN
DELETE
FROM candidate c
WHERE empid in
(select c.empid
from employee e,
candidate c
where e.empid = c.empid
and e.emp_stat = 'TERMINATED'
);
[code]....

View 6 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

SQL & PL/SQL :: Returning List Of Records That Do Not Have Associated Records From A Sub-query

Sep 6, 2010

I am running a query in our Clarity PPM database to return a list of all Support projects. This returns a simple list of project code and project name:

The query has the project resource tables associated with it, so I am able to list all resources allocated to the project. But for now i am only selecting a DISTINCT list of projects.

Query for anyone interested:

Select distinct
Project_code,
project_name
from
niku.nbi_project_current_facts nbip,
niku.odf_ca_project cst,
niku.prtask t,
[code]........

I have a separate query which returns a list of support resources.

select res.full_name, res.unique_name , dep.description
from niku.srm_resources res,
niku.pac_mnt_resources pac,
niku.departments dep
where res.unique_name = pac.resource_code
and pac.departcode = dep.departcode
and res.is_active = 1
and description like 'IMS%'
and UPPER(dep.description) like '%SUP%'

What I need to be able to do in the first query, is return only projects that do NOT have a resource that appears in the resource list in the second query.

(the res.unique_name field in the second query can be linked to the same in the first query)

Logically, the process would be:
1. Identify Support Project
2. Identify Resources allocated to the project team
3. Compare with List of Support Resources
4. If any Resources in that list do NOT appear on the project, then return project.

View 18 Replies View Related

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 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







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