SQL & PL/SQL :: Searching Collection For A Specific Value?

Oct 18, 2011

I need to check if a small collection contains a specific value. I know I can use the .exist method to let me know if the nth element exists. But is there a similar syntax for checking if an actual value exists?

So the below collection has 6 values (300,301,310,320,400,410) after the cursor values are fetched. I want to use something similar to the below exists syntax in order to search the collection for a value (rather than position) within an IF statement.

The below code shows the kind of thing I want to do. Currently, unless my test value (310) is in the range 1 to 6 the test will always return FALSE. But is there a similar syntax for testing against the value?

The example I have given is a simplification of what I need. In reality there will be more than one test value... returned by another cursor.

DECLARE
CURSOR c_type_id_usg
IS

[Code]....

-- get the list of sms type id usg values
OPEN c_type_id_usg;
FETCH c_type_id_usg bulk collect into l_type_ids;
CLOSE c_type_id_usg;

-- the above returns value 300,301,310,320,400,410

IF l_type_ids.exists(310)
then
dbms_output.put_line('I have found an entry ');
else
dbms_output.put_line('I have NOT found an entry ' );
end if;
END;

View 2 Replies


ADVERTISEMENT

Security :: Audit Specific Statement On Specific Table By Specific User?

May 29, 2012

I'd like to know if it is possible to track DML actions issued on a specific table by a specific user, for example , i tried :

AUDIT SELECT on SCOTT.DEPT by HR by ACCESS;

I get an error, where is my syntax error ?

i want to know if it's possible to do it without trigger ?

View 7 Replies View Related

Displaying Records Between Specific Times On Specific Dates?

Apr 11, 2011

I have a database which consists of various orders and various field.

I have a variable called createddatetime . I want that whenever i should run the database it should display records from

Yesterday 06:00:00 am to Current Date 05:59:59 am

Now to implement this i tried to put this syntax

and to_char(Createddatetime,'dd/mm/yyyy HH24:mi:ss') between 'sysdate-1 06:00:00' and 'sysdate 05:59:59'

But nothing comes up

where as definitely there are records between times because when i do and Createddatetime between sysdate-1 and sysdate I see valid records coming up.

View 3 Replies View Related

PL/SQL :: Related Collection Within A Collection

Sep 20, 2012

There is a nested table with in a nested table type and i want to print the value and again assign a new value to the next subscript and i have tried a lot but couldn't find any solution.

declare
type type_name is table of varchar2(10);
type type_name1 is table of type_name;
names type_name1:=type_name1(type_name('hello'));
begin
-----HOW TO PRINT A VALUE--------
-----HOW TO ASSIGN A NEW VALUE TO NEW SUBSCRIPT
null;
end;

1) need to print the values of names(1)
2)Assign a value to names(2)

View 3 Replies View Related

SQL & PL/SQL :: Searching For A String In Whole Database

Aug 16, 2012

I've tried to write the procedure for search the string from whole database. If user give the string as an input the output will be tablename and column in which the string contained in the table,But it's showing an error .

CREATE OR REPLACE procedure sample.pr_search_table (p_search varchar )
is
type tb_type is table of varchar(30);
tb_table tb_type:= tb_type();
tb_column tb_type := tb_type();
v_temp varchar(30);
[code]...

View 6 Replies View Related

SQL & PL/SQL :: Searching City Name From Address

Apr 16, 2010

I want to write a SQL statement to search valid city name from address field. Valid city names are in one table and address column is in another table.

View 5 Replies View Related

PL/SQL :: Searching String In A Table

Sep 9, 2013

I am using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production. 

I have a situation where I need to find the number of occurrences of a string that is present in a table having comma separated values. To explain with an example:  

create table test_data ( textfield varchar2(100)); 
insert into test_data values('DM,HM'); 
insert into test_data values('EM'); 
insert into test_data values('AM,CA,CD,FM,ST'); 
insert into test_data values('LS');
  insert into test_data values('TQ,SP,AM,FM,ST,CA,CD'); 
insert into test_data values('TQ,SP,AM,FM,ST,CA,CD,LS'); 
insert into test_data values('DM,HM,LS');

 The data in the table test_data looks like  DM,HMEMAM,CA,CD,FM,STLSTQ,SP,AM,FM,ST,CA,CDTQ,SP,AM,FM,ST,CA,CD,LSDM,HM,LS Now I need to search"LS" in the table test_data. Basically, I need to find if "LS" is present at least once in any of the rows or not. I want to avoid looping here. 

View 4 Replies View Related

PL/SQL :: How To Create One-column Collection From Another Multi-column Collection

Mar 22, 2013

I am describing a SQL statement to get it's column list:DECLARE

cur     NUMBER;
col_cnt INTEGER;
rec_tab DBMS_SQL.DESC_TAB;

[Code]....

Now I need to get out the columns list from rec_tab.col_name and put it to my_colls collection. Have Oracle any build-in to do that?

View 4 Replies View Related

SQL & PL/SQL :: Searching Numbers In Description Column?

Jun 15, 2010

I have two tables emp and dept

sql>select emp_id from emp;
emp_id
------
123
345
6782
32

[code].....

I would like to search all the employee id's which are present in description column of dept.

View 32 Replies View Related

SQL & PL/SQL :: Searching Data In Backup Tables

Apr 30, 2013

TEMP1--ORINIGAL TABLE NAME

DESC IS:

SNO NUMBER;
SNAME VARCHAR2(100);

BACK UP TABLES ARE

TEMP1_1;
TEMP1_2;
TEMP1_3;
.
.
.
.
.
TEMP1_10

IN THE ABOVE 11 TABLES SNAME COLUMN HAVING THE DATA AS 'NARESH'
IN SOME OF THE TABLES AND NOT IN OTHERS.

REQUIRMENT:

I WANT THE TABLES ONLY HAVING THE

SNAME COLUMN 'NARESH'.

View 7 Replies View Related

SQL & PL/SQL :: Use Of Index For Searching Data In Table

Mar 5, 2010

i want to know the use of index for searching data in table...tell answer to my query with table example....

View 2 Replies View Related

PL/SQL :: Searching Text Clobs From User_views?

Jul 22, 2013

I needed to search for some specific text in the DDL of each of the views in a particular schema. The text column of the user_views is LONG, and I looked at some old Tom's threads for converting long2clob, but found these processes to be really cumbersome, so I just opted to use DBMS_METADATA.GET_DDL instead even if it is a little slow.

One area I do not have a lot of experience with is searching clob fields. I tried this but not sure what would be the appropriate function to use for something like this:

SELECT *
FROM
(

[Code]....

WHERE contains(object_text, 'WHERE t.policy NOT LIKE') > 0; -- Show the names of all views that contain the matching text

View 4 Replies View Related

Tuning Searching Into Clob In Batch Operation?

Jul 3, 2012

My application runs a batch procedure weekly once for searching 'A_Text' from a column in Table1 in a clob column in Table2 and inserts accordingly into another Table3.

code snippet is like this -

---
CURSOR cr_sn
IS
SELECT serial_number

[Code]....

TABLE1 will have at least 1.1 Million rows but not significantly more than this.

This procedure takes 24+ hours to complete. I tried -

1. putting parallel hint ( INSERT /*+ PARALLEL*/INTO Table3)

2. partitioning TABLE2 based on last_update_date and putting a where clause in the above query last_update_date ( last_update_date between date1 and date2)

View 3 Replies View Related

Forms :: Creating Searching Tool Using Oracle 6i?

Nov 26, 2012

I have created searching tool using oracle forms 6i. while searching the cursor move from text field (A) to other block query the data according to the field (A) and come back again to field (A). Everything is working fine but the problem is, when the cursor move back to the field (A) the existing text is highlighted and when user right something in it. It's overwriting the existing text.

I want when the cursor moving bank to the field (A) the text should not be highlighted and when the user writes something that will be added to the existing text.

View 1 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 :: Searching PRODUCT Column Based Upon User Input?

Apr 30, 2010

I have table in Oracle with one column PRODUCT. Column PRODUCT have following values -

Account Management
Active Directory
Adobe Acrobat Reader
NT Account
Application Security

[code]....

I am designing application where I need to search for PRODUCT based upon user's input. Lets say user wants search on 'Laptop Account Broken'. I want to search for all products which contains any of words in user's input. So based upon user's input I want output like below.

Expected Output:

Account Management
NT Account
WebSite Account
HP Laptop

View 2 Replies View Related

Stored Procedure For Searching Data From A Table By Passing Tablename As A Parameter

Feb 6, 2012

This procedure is not working properly.

create or replace procedure bank_search_sp
(
p_tablename in varchar2,
p_searchname in varchar2,
p_bankcode out varchar2,
p_bankname out varchar2,
p_dist_code out number
)
as
v_tem varchar2(5000);
begin
v_tem :='select bankcode,bankname,dist_code from ' || UPPER (p_tablename) || '
where bankname like '''|| p_searchname||'';
execute immediate v_tem into p_bankcode,p_bankname,p_dist_code using p_searchname ;
commit;
end bank_search_sp;

the Procedure is getting created but i dont know what actually happens when it was executed ,This is the error shown..ORA-01756: quoted string not properly terminated

ORA-06512: at "PENSIONS.BANK_SEARCH_SP", line 14
ORA-06512: at line 1

View 1 Replies View Related

SQL & PL/SQL :: Collection Over Cursor?

Jun 30, 2010

What is the advantage of collections over CURSOR? What is the use for collections and when collections is used?

View 1 Replies View Related

SQL & PL/SQL :: Collection Of Records

Sep 29, 2010

I have been doing some code in collection for testing. I have been doing the below but getting the error.

SQL> desc t_mine;
Name Null? Type
----------------------------------------------------- --------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)

SQL> ed
Wrote file afiedt.buf

1 declare
2 v_start_time PLS_INTEGER := DBMS_UTILITY.GET_TIME;
3 v_elapsed PLS_INTEGER;
4 type allobjects_record is record
5 (owner varchar2(1000)
[code].......

ERROR at line 15:

ORA-06550: line 15, column 15:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 15, column 3:
PL/SQL: SQL Statement ignored

I followed the below link

[URL].......

View 5 Replies View Related

SQL & PL/SQL :: Table Variable Or Collection

Jul 11, 2012

Is there any table except (global temp table and permanent table) which can be used to store data and be used in inner, left and right join for a session.

View 7 Replies View Related

SQL & PL/SQL :: Various Collection Types Available In Oracle 9i?

Jun 15, 2010

What are the various collection types available in Oracle 9i.

Mention the differences between them.

View 4 Replies View Related

SQL & PL/SQL :: How To Remove Duplicates From Collection

Mar 24, 2011

remove duplicates from my collection(table type).Or an alternate solution for my issue is get clean data into my collection which i am not able to get to either.

Object creation

create table testingtype_table(ordernumber number,org_id number , company_name varchar2(10))
insert into testingtype_table values (1124,2424,'cbaaa');
insert into testingtype_table values (1124,2424,'cbaaa');

create or replace type testingtype_obj as object (ordernumber number,org_id number , company_name varchar2(10));
create or replace type testingtype_tab as table of testingtype_obj;

Code Block
declare
l_testingtype_tab testingtype_tab := testingtype_tab();
begin
select distinct testingtype_obj(ordernumber
,org_id
,company_name)
bulk collect into l_testingtype_tab
from testingtype_table;
end;

If only i can get a way to bulk collect only distinct values into the table type that will just do great but when i try the above (with distinct highlighted in red) it throws an error

ORA-22950: cannot ORDER objects without MAP or ORDER method

View 4 Replies View Related

SQL & PL/SQL :: Using Collection Variable As OUT Parameter

Aug 8, 2012

The following procedure stores the retrieved data in o_ref_primary_dept collection variable, suppose I want to display the data from that variable while executing what code I have to write.

CREATE OR REPLACE PROCEDURE sp_ost(
o_ref_primary_dept OUT PRIMARY_DEPT)
IS
l_primary_dept LONG;
[code].....

how to use collection variables as OUT parameters in procedure.

View 1 Replies View Related

SQL & PL/SQL :: Error In Oracle Collection

Aug 8, 2011

I got error in oracle collection

CREATE OR REPLACE PACKAGE BODY pkg_das_stag_to_master
AS
PROCEDURE sp_load_dasstage_security
AS
cursor cur_stag is
select Asset_id,
ID_ISIN,
ID_SEDOL1,
ID_CUSIP,
[code].........

error:

66/35 PLS-00302: component 'ASSET_ID' must be declared
66/35 PLS-00302: component 'ASSET_ID' must be declared
66/18 PL/SQL: ORA-00904: "TYP_SECURITY_VAR"."ASSET_ID": invalid identifier
63/1 PL/SQL: SQL Statement ignored

View 2 Replies View Related

SQL & PL/SQL :: Populating Ref Cursor From Collection?

Aug 17, 2011

I have an existing Procedure that uses a ref cursor as a parameter. The procedure currently simply selects data into the ref cursor using an OPEN FOR and then exits.

I have a need to extend the processing of the procedure. This will mean retrieving extra columns in the select and also removing some of the retrieved records.

I have the constraint that the definition of the REF CURSOR (which is based on a record type as follows) cannot be modified.

TYPE t_charge
IS
RECORD (
id number,
date_from invoice_detail.from_date%TYPE,
date_to invoice_detail.to_date%TYPE,
description invoice_detail.desc_text%TYPE,
amount invoice_detail.amount%TYPE
);
TYPE t_charge_cursor IS REF CURSOR RETURN t_charge;

The procedure does this

PROCEDURE get_bill_lines (
p_bill_id IN NUMBER,
bill_lines_list OUT t_charge_cursor )
IS
OPEN bill_lines_list FOR
SELECT id, from_date, to_date, desc_text, amount
from invoice_table
where id = p_bill_id;

I am thinking instead of selecting directly into the bill_lines_list parameter I will use a local REF CURSOR to select into a COLLECTION. I can then do some work on the collection, similar to below:

PROCEDURE get_bill_lines (
p_bill_id IN NUMBER,
bill_lines_list OUT t_charge_cursor )
TYPE t_local_charge
IS
RECORD (

[code].....

This all works fine, but the question is, how do I then put the collection into the bill_lines_list REF CURSOR where the '*** HERE ***' comment is so I can send the existing REF CURSOR structure back? (Remember I cannot change any definition that is referenced externally)

View 4 Replies View Related

SQL & PL/SQL :: Append Collection Variable

Oct 11, 2012

Declare
Cursor c1...;
Cursor c2...;
begin
open c1 ;
fecth c1 bulk collect into v1;
close c1;

[Code]...

Is there any way by which if condition gets true then v1 gets appended rather than being overwritten?

declare
type lst_deptno is table of dept.deptno%type index by binary_integer;
type lst_deptno_emp is table of emp.deptno%type index by binary_integer;
v_deptno lst_deptno;
v_deptno_emp lst_deptno_emp;
cursor c1 is select deptno from dept;

[Code]...

View 8 Replies View Related

PL/SQL :: How To Handle Breaks On Collection

Oct 22, 2012

I'm working on a plsql program and i'm using collections. I loop the collection and delete rows of it depending on the edits of my program. Here is the question.

if my collection holds
rows
[1]value
[2]value
[3]value

i can simply do something like FOR indx in invoice.first..invoice.lasthowever if i delete row 2 of my collection i get an error. no data found.
ive been researching this site

[URL].......

rows
[1]value
[3]value
[4]value

is there a way to tell plsql i just want it to loop the collection from top to bottom regardless of the index values?

View 2 Replies View Related

PL/SQL :: Using Collection Variable With OUT Parameter?

Aug 8, 2012

The following procedure stores the retrieved data in o_ref_primary_dept collection variable,suppose I want to display the data from that variable while executing what code I have to write.

CREATE OR REPLACE PROCEDURE sp_ost(
o_ref_primary_dept OUT PRIMARY_DEPT)
IS
l_primary_dept LONG;
BEGIN
l_primary_dept :=
'SELECT emp_obj(empno,ename)'
|| ' FROM emp';
EXECUTE IMMEDIATE l_primary_dept BULK COLLECT INTO o_ref_primary_dept;
END;

how to use collection variables as OUT parameters in procedure.

View 1 Replies View Related

PL/SQL :: Using Collection As Input For Cursor?

Oct 29, 2013

In PL/Sql, I create a new collection using TYPE. Then populate it with BULK COLLECT.The next step I need is to load data from the collection to pass as WHERE condition to a ref cursor.How I can do this?Every test I did fais. It seems to me that I cannot use some kind of SELECT to retrieve all data from the collection, but I might use a cursor to read row by row. (I'm on Oracle 11gR2 DB )

View 3 Replies View Related

Stats Collection In Oracle 11g

Nov 9, 2013

while doing stats collection weather system takes the backup of current statistics. i think we can specify stattab. but weather it takes stats backup before over writing? I got this requirement as a part of upgrade, i have already gone through export_schema_stats and import_schema stats already.  Just trying all other possible options only.

View 4 Replies View Related







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