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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Jun 15, 2010
What are the various collection types available in Oracle 9i.
Mention the differences between them.
View 4 Replies
View Related
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
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
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
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
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
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
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
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
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