PL/SQL :: CTXRULE Matches Non-existing Query String?

May 25, 2013

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
==============================================
SQL> select * from books_cat;
     ID CAT               QRY
---------- -------------------- --------------------------------------------------
141 Oracle          {Oracle} OR {RDBMS}
142 Russia          {Russia} OR {Russian}
SQL> select id, file_desc from books;

[code]....

I am learning Oracle Text and created the above routine to check whether Russia/Russian appears in the OracleTextDeveloper.txt file (text version of the standard Oracle documentation - B28303-03).

Some more information: -

DBMS_METADATA.GET_DDL('INDEX','IDCTXR_BOOKS_CAT')
--------------------------------------------------------------------------------
CREATE INDEX "READER"."IDCTXR_BOOKS_CAT" ON "READER"."BOO
KS_CAT" ("QRY")
INDEXTYPE IS "CTXSYS"."CTXRULE"
SQL> select token_text, token_type from dr$idctxr_books_cat$i;

[code]....

View 0 Replies


ADVERTISEMENT

Select Query Matches Part Of The String

Mar 11, 2010

Select * from XXX where xxx.a in �123,456�-----> this values comes dynamic to my query.

Here xxx.a will contain values like 123 or 456 like that .

View 6 Replies View Related

SQL & PL/SQL :: Insert Using String Matches / Matching And Searching Match

Jul 3, 2012

i have three tables ot_cut_head,ot_cut_det and om_mc_master based on which fourth table ot_cut_opr and fifth table ot_cut_mc must get populated , Conditions are as follows

first one is based on job_no in ot_cut_head the selection criteria will be filtered,if the job number is like '%M' then type MISC will be chosen ,if job number is '%G' then GRAT TYPE will be picked from om_mc_master (Machine Master) and operations and machines based on this will be filtered.

Second all the cd_ps_desc will be taken from ot_cut_det and will be compared with om_mc_master to get their corresponding operation codes and machine codes , there can be 2 operations or 1 operation.

Finally if the match is found record will be inserted into ot_cut_opr and ot_cut_mc ,based on the criterias and what i want is the search criteria to be more flexible and if there are 2 operations 2 rows will be inserted and if one opeation is defined in om_mc_master ,then only one record will be inserted.

We have to make sure that if based on operation number stage will be populated ,if its first operation then stage will be 1 and if its second operation the stage will be 2.like previous operation also depends on them , the second operation will have the previous operation as first operation and so on.

CREATE TABLE om_mc_master ( mc_type VARCHAR2(12),mc_prof VARCHAR2(30),mc_prep_cd1 VARCHAR2(30),mc_mach_cd1 VARCHAR2
(30),mc_prep_cd2 VARCHAR2(30),mc_mach_cd2 VARCHAR2(30));
INSERT INTO OM_MC_MASTER VALUES ('MISC','TEE SCH','IR','HO','RE','HO');
insert into om_mc_master values('MISC','Vertical Brace','R','HM','I','HO');
insert into om_mc_master values('MISC','Pipe','IR','HO',NULL,NULL);
INSERT INTO OM_MC_MASTER VALUES ('GRAT','PL','RE','HO',NULL,NULL);
SQL> SELECT * FROM OM_MC_MASTER;
[code]....

View 6 Replies View Related

SQL & PL/SQL :: Oracle Query Joining A Table With Existing Query?

Jun 19, 2012

I have the following four tables with the following structures Table A

ColA1 ColA2 ColA3 ColA4 ColA5 AA 100 CC DD EE

Table B

ColB1 ColB2 ColB3 ColB4 ColB5 AA 100 40452 A9 CDE

when these two tables were joined like the following:

Select colA1,ColA2, ColA3, ColA4, ColB3,ColB4, ColB5 from table A Left outer join (select ColB3, ColB4, ColB5 from table B where colB3 = (select max(colB3) from table B ) on (colA1 = colB1 and ColA2 = col B2)

Now i have to join the next table C with table B

Table C structure is

ColD1 ColD2 ColD3 Desc1 A9 Executive Desc1 A7 Engineer

I have the common column such as ColD2 and colB4 to get the Col D3

how do i join the existing query + join between table b and table c?

View 4 Replies View Related

How To Use String Buffer Instead Of String Query

May 9, 2008

show an ex to use string buffer for select statemnt

View 1 Replies View Related

SQL & PL/SQL :: How To Add Query To Existing Procedure

Nov 10, 2011

how to add a auery to my procedure.

View 12 Replies View Related

SQL & PL/SQL :: How To Find Unique ID When Several Matches

Feb 1, 2012

I have a simple question, but i can't resolve it.

I have a table like this :

ID_A | VALUE
-------------
A 1
A 2
B 1
B 2
B 3
C 1
C 3
D 1

I need to fid the unique ID that match perfectly VALUE = 1 or 2.

I tried SOME/ANY/ALL/IN

View 14 Replies View Related

SQL & PL/SQL :: Linking 2 Tables Without Exact Matches

Mar 4, 2011

I have two tables as per attachement - TABLE_A has Vehicle details while TABLE_B has Address details:

I am trying to build a query in order to link the Vehicle Details with their Address Details.

I would require an SQL that links records in TABLE_A with records in TABLE_B when group_number are the same; however links with group_number '999' (for the same customer_number) if there are no exact matches.

SELECT A.*,B.*
FROM TABKE_A A, TABLE_B B
WHERE ((A.CUSTOMER_NUMBER = B.CUSTOMER_NUMBER AND A.GROUP_NUMBER = B.GROUP_NUMBER)
OR (A.CUSTOMER_NUMBER = B.CUSTOMER_NUMBER AND A.GROUP_NUMBER = '999'))

The only problem with such query is that record in TABLE_A with group_number '456' will return 2 times. One with address having group_number '456' and one with group_number '999'.

View 8 Replies View Related

SQL & PL/SQL :: Existing Role Claimed To Be Not Existing?

Dec 19, 2011

I'm am getting an error that says that a role doesn't exist when I can cleary see it in DBA_ROLES. I can successfully grant other roles in this session. I am using Oracle 11.1 on RAC.

GRANT "CONNECT" TO BAKERD
Error at line 1
ORA-01919: role 'CONNECT' does not exist
select * from dba_roles where role = 'CONNECT'

[code]....

View 5 Replies View Related

RAC/ASM Clusterware Installation :: Query On Adding New Node To Existing 2 Node Cluster

Jun 15, 2012

current environment

Oracle VM 2 Node RAC cluster
Oracle 11G 11.2.0.3.0
Oracle 11g rel 2 GI

on current 2 node cluster we have GI and RAC db configured Nodes: vmorarac1,vmorarac2

we shutdown vmorarac1 to clone it to vmorarac5

on new node I have changed the hostname to vmorarac5 In /etc/sysconfig/network-scripts/ifcfg-eth0 change ip to new ip and same for /etc/sysconfig/network-scripts/ifcfg-eth1 152.144.199.210,152.144.199.211

mad echanges to /etc/hosts on vmorarac1/2 for 2 new IP address assigned to vmorarac5

152.144.199.171 vmorarac1.pbi.global.pvt vmorarac1
192.168.2.30 vmorarac1-priv.pbi.global.pvt vmorarac1-priv
152.144.199.184 vmorarac1-vip.pbi.global.pvt vmorarac1-vip

[Code]....

Query is is that all ok to reboot the new node to reflect changes as below and change the hostname to vmorarac2

152.144.199.210 vmorarac2.pbi.global.pvt vmorarac2
192.168.2.32 vmorarac2-priv.pbi.global.pvt vmorarac2-priv
152.144.199.211 vmorarac2-vip.pbi.global.pvt vmorarac2-vip

hostname change in /etc/sysconfig/network

On new node vmorarac5, there is alreay software for RAC DB and GUI cloned from vmorarac1

addnode.sh is used to add existing node, but as part of pre-requisite configuratoin is there any config step missing

View 3 Replies View Related

SQL & PL/SQL :: Dynamic Sql Query String - Getting Error?

Aug 22, 2010

FUNCTION get_attributed_sedol( p_ref_number IN VARCHAR2,
p_field IN VARCHAR2 )
RETURN VARCHAR2
IS
l_query VARCHAR2(1000);

[code]...

It gives me an error on the line where the execute immediate statement is.
Quote:
ORA-00905
missing keyword

Cause: A required keyword is missing.

Action: Correct the syntax.

But when I check my stack trace table to see what the actual query string looks like, I see this

Quote:
SELECT sedol INTO l_attributed_sedol_code FROM integration.tmp_attributed_sedol WHERE CLIENT_LEDGER_REF='LEAE057090' AND ROWNUM=1

There's nothing wrong with that, is there? It executes fine if I try it manually.

View 3 Replies View Related

How Many Quotes To Use In Dynamic Query Using IN Clause With String

Nov 23, 2011

I have a dynamic query which has this clause in it: WHERE [COLUMN NAME] IN (' || theString || ')

My problem is that theString is being passed in through a C# call and the variable is a bunch of strings concatenated together and separated by a comma. Ex: theString = "'val1','val2'"

How many quotes are supposed to go around val1 and val2?

I've tried the following and none work:
'val1','val2'
''val1','val2''
''val1'',''val2''
'''val1'',''val2'''
''''val1'',''val2''''

When I run the procedure in Oracle it works with '''val1'',''val2'''

View 1 Replies View Related

Sql Query - Insert Concatenation String To Table?

Aug 6, 2012

i try to insert Concatenation string to my table,i need that all traps that has 12 length will be insert the new trapnum like this:

for example: 26001005CC45 = 260001005CC0045 ....... 08060027RF05 = 080600027RF0005 ......... and so....

update trap set TrapNum = (
select trim(both from to_char(substr(TrapNum,1,4),'0000'))||
trim(both from to_char(substr(TrapNum,5,1),'00'))||
trim(both from to_char(substr(TrapNum,6,3),'000'))||
substr(TrapNum,9,2)||
trim(both from to_char(substr(TrapNum,11,2),'0000')) from Trap)
where length(Trapnum)=12

but i got error ORA-01427

View 1 Replies View Related

SQL & PL/SQL :: Query To Split Character String Using XQUERY

Aug 5, 2010

I am using this query to split numeric values i.e ('1,2,3,4,5,6')but when i am trying with char value i.e ('a,b,c,d'), its not working.

select id
FROM employee e
WHERE e.id IN
( SELECT TO_NUMBER(xt.column_value)
FROM XMLTABLE('1,2,3,4,5,6') xt );

View 6 Replies View Related

SQL & PL/SQL :: Convert Column Value To Delimited String Using Query?

Jan 10, 2012

I want to convert a column value to a delimited string using a query.

Example

TableA
Col1 Col2 Col3
1 x200 MIS-X
2 x200 BTS-X
3 x200 TYR-X
4 x100 YRY-X

Select Col3 From TableA where Col2 = 'x200'

Expected Output:

'MIS-X','BTS-X','TYR-X'

View 4 Replies View Related

SQL & PL/SQL :: Space In Every Character Of A String With Single Query

Mar 19, 2012

How can we get 'space in every character of a string with Single select query'

for example:-
string 'INDIA'
result should be 'I N D I A'

View 1 Replies View Related

SQL & PL/SQL :: Query To Return Value / String If No Records Found

Jun 17, 2012

Having following table:
UserID REC_TYP REC_CD
12345 'OFFR' 12
23456 'MSG' 13

I'd like to construct the query which in this particular case would return the REC_CD as 'Record_ID' for REC_TYP='OFFR' where USERID=? (always fetched by the application) and if such USER_ID doesn't exists (for the particular REC_TYP of course) to return string or any other value. e.g. The result for this query in case of user_id 23456 = would be "doesn't exist" or sth for instance 'FALSE' and for 123456 it would be '12'

View 2 Replies View Related

SQL & PL/SQL :: Query To Get Whether A String Contains 2 Upper Characters And Numbers Using Regular_exp

Jun 12, 2013

A sample query to check whether a string contains 2 upper characters and numbers are present in that character using regular_exp .

View 12 Replies View Related

SQL & PL/SQL :: Query Of Functions - Display Half Part Of String

Jun 8, 2011

I want to write a plsql program to display half part of a string .Example there is a string like gillmadden@myharbour.org.uk

I want to display only the later part of the string

myharbour.org.uk as output.

Can we write using string functions in plslql?

View 19 Replies View Related

PL/SQL :: String Search Query In Any Columns / Table In Database?

Sep 4, 2012

I am trying to search a word which starts with 'FRA' in any columns and any tables.

I am unable to find what is generating a join datasets in the webservice from teh database as it is not apparent within the 100 tables.

I ahve looked into

Re: How to search in all rows and all columns?
Re: SQL Search Query?

but none of these queries is working out for me as I am a user with no tables on its own but rather a user quering other tables.I think its a tweak on which dat a dictionary I can view

select distinct substr (table_name, 1, 14) "Table",
substr (t.column_value.getstringval (), 1, 50) "Column/Value"
from all_cons_columns,
table
(xmlsequence
(dbms_xmlgen.getxmltype ('select ' || column_name
|| ' from ' || table_name
|| ' where upper('
|| column_name
|| ') like upper(''%' || 'fra'
|| '%'')'
).extract ('ROWSET/ROW/*')
)
) t
order by "Table";

running teh above query got me thsi error:

ORA-19202: Error occurred in XML processing
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_XMLGEN", line 288
ORA-06512: at line 1
19202. 00000 - "Error occurred in XML processing%s"
*Cause:    An error occurred when processing the XML function
*Action:   Check the given error message and fix the appropriate problem

View 4 Replies View Related

ORA-02085 - Database Link String Connects To String

Jun 19, 2012

I have this error (and solution):

ORA-02085: database link string connects to string

Cause: a database link connected to a database with a different name. The connection is rejected.

Action: create a database link with the same name as the database it connects to, or set global_names=false.
Where should I set global_names=false ?

View 7 Replies View Related

SQL & PL/SQL :: How To Find Whether Exact String Is Present / Not In Given String

Mar 14, 2013

I'm facing some problem even after using INSTR function in Oracle.The problem is I have written the logic in the PL/SQL block which appends all the values fetched in a loop on the basis of whether the string is present or not.

For ex:

The first value fetched from the select query first is ABCDEFG which gets appended to a variable
The next value fetched is AB even this has to be appended to the variable since this exactly doesn't match with ABCDEFG.
The next value fetched is BCDEF even this has to be appended to the variable since this exactly doesn't match with ABCDEFG.
The third Value fetched is ABCDEFG this will not get appended presently according to the logic which is correct.

writing that piece of code to append the value fetched which doesn't exactly match with the existing string

View 3 Replies View Related

SQL & PL/SQL :: Recreating Existing Trigger

Nov 28, 2011

I am trying to update an existing trigger in my Prod DB, but it may be possible that while recreating the trigger,existing trigger might be under any transaction.

So what could be the possible scenarios? Will I be throwing any error like ORA-0054: resource busy or any other error or what? If it does what could be possible escape ways apart from downtime.

View 17 Replies View Related

SQL & PL/SQL :: How To Add Row Dependencies In Existing Table

Jul 20, 2012

How to add ROWDEPENDENCIES in an existing table. I mean How to Alter any table to add ROWDEPENDENCIES..

View 2 Replies View Related

SQL & PL/SQL :: Give New ID To Existing Employee?

Mar 13, 2012

want to create a PL/SQL procedure, update_id(id_emp in number), that gives a new id_emp (id_emp=y) to an existing employee (id_emp=x).So before updating the EMP table, we have to :

1- create a new row on EMP(with id_emp=y) that has the same informations of the employee (id_emp=x),
2- update all tables that contains the id_emp column (update <TAB> set id_emp=y where id_emp=x),
3- delete employee (id_emp=x).

The problem is in step 2 : it creates a lot of locks and makes the DB unusable.To deal with this problem, I thought for many solutions, but the problem is how to implement them correctly and efficiently.

Before executing step 2, we have to ensure (through a marker I guess) that all the tables that have the id_emp column, are managed by our session, and any other acces (through SELECT, UPDATE, DELETE, INSERT statment) from another session will be rejected since we have a marker on that table.

When step 2 ends, we release all the tables from the markers.

My simple question : how to achieve this ?

View 12 Replies View Related

SQL & PL/SQL :: Partition Of Existing Table

Nov 30, 2011

We have a transaction table and has 30 million rows. The table is not partitioned till date. We need to create partition on this table. We had an idea of moving this data to a temporary table and create partition[range]on the original table and move the data back.

View 5 Replies View Related

SQL & PL/SQL :: Update Existing Table

Apr 24, 2012

I want to either UPDATE or add a new column with ROWNUMBER() OVER an column on a table

the output will shows like below

empID DeptNo New_column Start_Date
11 7778 1 01-02-2010
11 3400 202-06-2010
25 4444 103-02-2010
25 7775 204-07-2011
25 7777 305-02-2012
30 2223 109-02-2012

the forth one column is in date format and I want to update table with respect to order by the column start_date

View 1 Replies View Related

SQL & PL/SQL :: ORA-00955 / Name Already Used By Existing Object

Mar 15, 2012

During import , tables were imported but index was not imported due to ORACLE home was full.after import i was trying to create the the same index as it was not imported i am getting below error.

The "error ORA-00955: name is already used by an existing object"

My index name is GL01PK . I am trying to verify if the index with above name is present or not , using following query.

select OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID ,OBJECT_TYPE from dba_objects where object_name like '%GL01%'

below is the output which shows that GL01PK index is not present.

GLOT1 GL01 150943 TABLE
FNSONLC3 GL01 147240 TABLE
FNSONLC3 GL01_13042011 148184 TABLE
FNSONLC3 GL01_07052011 148195 TABLE

how can i create the index here with the same name GL01PK

View 3 Replies View Related

How To Modify Existing Spfile

Jul 15, 2013

I need to know how to modify the spfile - I know how to create it from scratch (from pfile to spfile), but need to modify an existing spfile.

View 3 Replies View Related

PL/SQL :: To Set A Bonus Value From The Existing Column

Jun 2, 2013

we are providing a salary column. from that salary column we need to calculate bonus with the condition as bonus=(salary+10%). how to execute this.

View 5 Replies View Related







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