SQL & PL/SQL :: Using A Cursor Record In REGEXP_INSTR
Oct 18, 2012
I've got a task to star out account numbers in a free text field. The account numbers can be in a few different formats and there are a number of tables. The formats could increase or change as could the tables. For that purpose I want to use a couple of tables; one to store the formats and one to store the tablenames. The tablenames work fine, but I'm having problem with the formats.
INSERT INTO account_format
VALUES (1,
'''[0-9]{5}[A-Z]{1}-[0-9]{5}''',
'''[0-9]{5}[A-Z]{1}-[0-9]{5}'',''****-****-****-****''',
'Y');
The idea is to bring back the format and use it in REGEXP_INSTR (or a similar REGEXP) to ascertain if the field contains the account number. The code I have been using looks a bit like this:-
CURSOR m_format_cur IS
SELECT acc.acc_format
FROM account_format acc;
m_format_rec m_format_cur%ROWTYPE;
IF REGEXP_INSTR (m_narrative, m_format_rec.acc_format) > 0 THEN
....
END IF;
I have tried several different ways to get this to work e.g. hardcoding the format and declaring a variable with the format, which both work. Whenever I try to use the value retrieved from the database field it never works. Why should a variable from a cursor not work?
-define a cursor with bind variables -get a cursor record from these cursor -and pass the bind variable in the OPEN clause
Did'nt succeed as shown in the example.
SET SERVEROUTPUT ON SIZE 900000; DECLARE --works fine CURSOR c1 IS SELECT * FROM USER_TABLES WHERE rownum<3; --doesn't work --CURSOR c1 IS SELECT * FROM USER_TABLES WHERE rownum<:1; crec c1%rowtype; BEGIN --works fine OPEN c1; --isn't possible ? --OPEN c1 USING 3;
I'm not able to understand OCCURANCE parameter for REGEXP_INSTR.As per my understanding it should see the occurance of given pattern in source string.Eg.
'[o][[:alpha:]]{3}'Above pattern matches character 'o' followed by any 3 alphabetic characters: case insensitive. SELECT REGEXP_INSTR('500 Oracle Pkwy, pneumococcal Shores, CA herohonda lionab', '[o][[:alpha:]]{3}', 1, 1,0,'i') RESULT FROM DUAL ;
It is giving me 5 as result. Which is correct as search is started from first character of the source string and found first occurance "500 O".
SELECT REGEXP_INSTR('500 Oracle Pkwy, pneumococcal Shores, CA herohonda lionab', '[o][[:alpha:]]{3}', 1, 2,0,'i') RESULT FROM DUAL ;
It is giving me 23 as result. Which is correct as search is started from first character of the source string and found second occurance "500 Oracle Pkwy, pneumo"
SELECT REGEXP_INSTR('500 Oracle Pkwy, pneumococcal Shores, CA herohonda lionab', '[o][[:alpha:]]{3}', 1, 3,0,'i') RESULT FROM DUAL ;
It is giving me 33 as result although I'm expecting 25. Because the next occurance is "'500 Oracle Pkwy, pneumoco", and there are alteast 3 alphabets after it. understand "How OCCURANCE parameter works?".
Whey I try to run this query in TOAD I get an ORA-00920: invalid relational operator error. It's part of a 10g stored procedure. When I highlight it and run it it prompts me for the missing values and then the error pops up. The AND in line 4 is highlighted.
select CRIME_CLASSIFICATION_ID, crime_type, nvl(count(CRIME_CLASSIFICATION_ID),0) as CRIMECNTFrom vaps.vw_offenses where regexp_instr(valoc,to_char(location_id)) AND ( fromdate is null or offense_date between to_date(fromdate, 'mm/dd/yyyy') AND to_date(todate,'mm/dd/yyyy'))group by crime_classification_id, crime_type
I need to run a query that runs a stored procedure. The stored procedure takes a cursor of record ids as a parameter. I've been making the cursor like this, if there was a smarter way. I can't change the stored procedure, so this is the only thing I have control over.
CURSOR id_cur IS SELECT id FROM table t WHERE t.id IN ( id1, id2, ... id500
);It just seems kind of strange to select only ids from records when the list of ids is given.
when I updated a record in my form check my screenshot: and clicked save button the result was:
as you can see in the message at the bottom, it has 18 records. And since my original data(subjects) are just 9. And also the grades that I input didn't display all.
This is my code in when-new-block-instance trigger: DECLARE CURSOR studgrade_cur IS SELECT e.student_id, s.subject_code --, g.grade
SQL> show user USER is "ANDREY" SQL> SQL> SQL> SQL> --create the table:
[code]...
I insert rows into it:
SQL> --fill it with data: SQL> SQL> insert into a(key1 , key2) values (1 , 1); 1 row created. SQL> insert into a(key1 , key2) values (1 , 5);
[code]...
i want to perform a logic by which:for every distinct value of key1 - values of key2 will be checked in all records holding that particular key1 value, and update the key3 field to 'inactive' where the key2 value for that particular key1 is the highest in number.
i've found out that i could do it by an SQL statement:
update a set key3 = 'inactive' where key2 = ( select max(key2) from a a2 where a2.key1=a.key1 );
however I wanted to use the cursor to "load" the max key2 values FOR EACH distinct key1 value exists in the table,and do the same thing as the update statement above WITH A CURSOR,So tried and wrote the following:
SQL> create or replace procedure proc1 2 IS 3 4 5 var1 a.key1%type;
[code]...
unfortunately, it works only for one row, and i don't understand what's wrong, I executed, and checked what has changed:
SQL> exec proc1; PL/SQL procedure successfully completed. SQL> select * from a; KEY1 KEY2 KEY3 ---------- ---------- ---------- 1 1 active 1 5 incative 2 24 active 2 21 active
In my form line level block contains 100 records.i will check the check box for line number 96 and 97. Then i will press save(I have written some logic here) button it will generate one number for selected check boxes. After generating this number cursor(control) should be on same line number 96 or 97.
I have Two cursor record block..which is attached in form..
My TASK IS
In my first Block, When DBCR Column = 'D' Then in backend this column value should be save as a '1' WHEN DBCR Column = 'C' Then Then in backend this column value should be save as a '2'
My Both Field is on Data Block...
In Property palette of this field can we write any decode condition..so it reflects directly on database.
i m using oracle 10g 10.2.0.2 version.i create a form and using check box on this form.when i click this check box then loop is using behind it.and current cursor is going to last record
i want if i click 4 record then cursor is still showing on 4 record mean i click which record after using loopmy current cursor is showing on that particular record
1) i have a hr.departments table that was loaded in hr schema on 1st oct 2012 with 4 columns(department_id, department_name, manager_id, location_id)
2) now I have a new schema by my name 'rahul' and I have loaded departments table but now an additional column has come into picture,ie created_date, this table got loaded on 1st-Nov-2012
3) Now going forward my columns could be dropped from the departments table (it can be a case), for example might be my departments table in my schema 'rahul' one day could comprise of only 3 columns(department_id,department_name,manager_id)
4) Now in the next step, I have managed to extract common column names(in a single line where columns are delimited using a comma) from both the tables(hr.departments and rahul.departments) which are (department_id, department_name, manager_id, location_id) using all_tab_cols table and I have written a function for it which i will be pasting below.
5) now going forward, using the above column names line with column names delimited using comma, I have used a ref cursor and assigned a query to it using the line of columns that I have extracted from the above point
6) Now I want to create a record variable which refers to my ref cursor, something like we do when we create a record variable by reffering to an explicit cursor defination that we give in the declaration block.
PS:
1) I have been out of touch with plsql for a long time so I have lost a lot of mmeory regarding plsql.
2) basically I need to compare data in hr.departments table with rahul.departments table for only columns that are common to both the tables, rest new or discarded columns information will go in one of the log tables that I have created(this is done already)
Code : =================================================================================================== create or replace procedure p_compare_data(fp_old_table_name in varchar2, fp_new_table_name in varchar2) is
how to write below query in pl/sql cursor. The help table has two associated tables, help_txt and help_id, which will have strings of data concatenated into one sales contact record. There are multiple lines of text per comment and multiple lines of resolution text at 40 characters per line. The key to the help_text table (id, date,seqno) is the main key to the help_txt table and help_id t table with a sequence added to each table
The formatted string will contain some text and variables with the comment lines (1-10 or more) concatenated first, followed by the resolution lines (1-10 or more). There will be multiple comment and multiple resolution lines. The Cust_Cmnt_Txt lines and the Resolved_Desc lines should be concatenated and formatted in the following string (% marks the variable string) :
'help taken ' %help.Taken_Dte 'received from the following source: ' %help.id. 'Remark Text: ' %help_text (where help_txt_Seq = 1) %help_text (where help_text_seq = 2-10 or more) 'Resolution: ' %help_id_Res_Txt.Resolved_Desc (where help_ID_Txt_Seq = 1) %help_ID_Res_Txt.Resolved_Desc (where help_id_Txt_Seq = 2-10 or more)
I'm dealing with an ORA-1000 error in a Pro*C application where all the cursors are correctly closed (or so it seems to me).
Here is the code for a simple program which reproduces the problem:
Each cursor is opened in a PL/SQL package:
CREATE OR REPLACE PACKAGE emp_demo_pkg AS TYPE emp_cur_type IS REF CURSOR; PROCEDURE open_cur(curs IN OUT emp_cur_type, dept_num IN NUMBER); END emp_demo_pkg;
[Code]....
While testing the initialization parameter open_cursors is set to 50.
It's my understanding that Oracle doesn't close the cursors until it needs the space for another cursor, which in my test case seems to happen when I enter a value of 50 or bigger for "number of loops". To see how oracle is reusing the cursors, while the test program is running I run SQL*Plus and query v$sesstat for the session that's running the test with the following sentence:
select name, value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and sid = 7 and name like '%cursor%';
Even before I enter a value for number of loops I can see that the session opened 4 cursors and closed 2 of them:
NAME VALUE ---------------------------------------------------------------- ---------- opened cursors cumulative 4 opened cursors current 2
Entering a value of 5 for number of loops yields
NAME VALUE ---------------------------------------------------------------- ---------- opened cursors cumulative 11 <----- 7+ opened cursors current 8 <----- 6+
With a value of 30
NAME VALUE ---------------------------------------------------------------- ---------- opened cursors cumulative 36 <----- 25+ (apparently, Oracle reused at least 5 cursors) opened cursors current 33 <----- 25+
With a value of 47
NAME VALUE ---------------------------------------------------------------- ---------- opened cursors cumulative 53 <----- 17+ opened cursors current 50 <----- 17+
Now I reached the upper limit set by the initialization parameter open_cursors.
Entering a value of 48, I get the ORA-1000 error.
ORA-01000: maximum open cursors exceeded ORA-06512: at "SCOTT.EMP_DEMO
Since I open and close the cursor in the same loop iteration, I expect to find in every iterarion 1 explicit cursor and a number of implicit cursors (the PL/SQL call along with the so-called recursive cursors), but I don't expect the sum of all of them to be greater than 50. If my understanding is correct Oracle should be reusing the 50 cursors previously marked as "closeable", not raising the ORA-1000 error.
I am loading data file using SQL Loader in TOAD 10.5.1.3 in Oracle 10g using the control file below and loading data into 2 tables post1.thead and post1.tdetl. THEAD contains item level transaction and TDETL is detail level when a transaction has a discount or promo attached to for that item. When the
LOAD DATA APPEND INTO TABLE post1.thead WHEN (1:5) = 'THEAD' TRAILING NULLCOLS (file_type POSITION(1:5)CHAR,
[code]....
A particular THEAD value may have 0, 1 or many TDETL corresponding values.below is a sample data file. When the position 21 in the TTAIL has a value of 1 or 2, then we know that there is a promo or discount applicable to the ITEM (THEAD).
What I want to acheive is to accurately reflect a TDETL to its corresponding THEAD, as both THEAD and TDETL are loaded into separate tables. How can we have the 2 records correlated?
I have a table which contains the multiple records for single ID No. Now i have to select single record which contains the latest date. here is the structure Name
Null Type ------ ---- ------------ ID_P NUMBER NAME_P VARCHAR2(12) DATE_P TIMESTAMP(6) Records---------------------1 loosi 22-AUG-13 01.27.48.000000000 PM1 nammi 26-AUG-13 01.28.10.000000000 PM2 kk 22-AUG-13 01.28.26.000000000 PM2 thej 26-AUG-13 01.28.42.000000000 PM
now i have to select below 2 rows how can write select qurie for this?
I have a fairly standard Purchase Order form which contains pre-loaded data (been uploaded from an XML file).When the Purchase Order is processed, the form updates a Price History table only if the Price on the PO_Details changes.The code for updating the price history table is contained in a PRE_UPDATE trigger on the PO_Details Data Block.
No other data changes on the PO_Details table.I now want to change this so that the Price History table is updated even if the price does not change i.e I want to create a history record for each record on the PO_Details irrespective of whether it was updated or not.
Is there an alternative trigger that I can move my code to (ie move it from PRE_UPDATE) to some other trigger that is fired for each PO_Details record even if there is no change.
I need to create a query that returns record by record a field qty_progr with the cumulate qty considering previous records. The result should be the following:
Using 11gR2, windows 7 client machine. I need to update the table missing_volume (below), where I need to calculate the estimated_missing column. The calculation of estimated_missing column for current month needs previous month numbers (as commented inside the code below). I want the output like the first table. Notice the records start from January, hence estimated_missing for January can't be calculated, but for the the rest of the months it can be done by simply changing 'yr' and 'mnth' (commented inside the code towards the end).
yr mnth location volume actual_missing expected_missing estimated_missing --------------------------------------------------------------------------------------------------------------------------------- 2013 January loc1 48037 24 57 2013 February loc1 47960 3660 53 24 2013 March loc1 55007 78 57 28 2013 April loc1 54345 72 58 77The code:
UPDATE missing_volume g
[Code]....
The code does calculate correct number for 'estimated_missing' as I run the code for each month, but the problem is while updating the current month it also erases the record for previous month. E.g. as can be seen below, after I updated April the column only has the record for April, previous month record is gone, similarly updating March removed February, etc. I can't understand why it's happening!! Here is the output I get:
yr mnth location volume actual_missing expected_missing estimated_missing --------------------------------------------------------------------------------------------------------------------------------- 2013 January loc1 48037 24 57 2013 February loc1 47960 3660 53 2013 March loc1 55007 78 57 2013 April loc1 54345 72 58 77
why it's happening (I mean where is the flaw in the code) and how to get the desired output (first table).
CURSOR_ABC IS select...............; BEGIN OPEN CURSOR_ABC; LOOP FETCH CURSOR_ABC INTO V_VARIABLE1; EXIT WHEN CURSOR_ABC%NOTFOUND;
WHAT IF I WANT TO INSERT ANOTHER SAME KINDA CURSOR LOOP HERE LIKE*
CURSOR_XYZ IS select...............; BEGIN OPEN CURSOR_XYZ; LOOP FETCH CURSOR_XYZ INTO V_VARIABLE2; EXIT WHEN CURSOR_XYZ%NOTFOUND; END LOOP; CLOSE CURSOR_XYZ; END LOOP; CLOSE CURSOR_ABC;
We receive hand punches (clock data) every day. Normally a person badges in(hand punch) which creates a row in the clock_tran_processed table. The information from that hand punch is the employee id (emp_id) the date hand punch occurred and a work_summary id (wrks_id). At the end of the day, the employee badges out (hand punch out) and another entry in the clock_tran_processed table is created. The new row will have the emp_id (employee name), date the hand punch occurred and the same work summary id from the morning.
Normally hand punches should occur in pairs. One in, one out... or one in, out for lunch, in for lunch, out for day. I am seeing intervals of three and five. Meaning the employee clocked in twice and out once, or in once and out twice. This shouldn't happen.
I am writing a report that will show number of clocks per for all employees that have three(3) or five(5) clock entries.I wrote a ref cursor that gives all the employees that have a count of 3 or 5 and the employee id (emp_id). I need to pass that employee id to another query that will then get me the dates of the clocks.Here is the ref cursor thus far (I am printing those with a count of 3 and 5).
CODEDECLARE
TYPE ClockTran_Refcur IS REF CURSOR; ClockTran_cur ClockTran_Refcur; NumClock_num number :=0; NumClock_name clock_tran_processed.emp_id%TYPE; [code]...
How do I pass that variable to the get the clktranpro_time's? I am using "Easy Oracle PL/SQL Programming" but I am not seeing this type of example(pgs 140-148).
In our environment we have db link to fetch data from other database. Whenever we try to fetch data using the dblink we receive the wait event "cursor: pin S wait on X" and we do not get any result. The db link works fine. what could be the issue.
