PL/SQL :: Way To Make A Cursor Of Record ID
Nov 15, 2012
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.
View 6 Replies
ADVERTISEMENT
Feb 25, 2011
Is it possible to:
-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;
[Code]....
View 3 Replies
View Related
Jul 9, 2012
How to make cursor to take a select statement
then check the rowcount
in certain case: assign another selete statement to the cursor
View 3 Replies
View Related
Jan 25, 2013
I have a button "clear" , I want when the button is clicked the items will be cleared and the cursor is at the first item, here is the
:emp.id:=null;
:emp.ename:=null;
:emp.sal:=null;
go_item('emp.id');
=>but the problem that the item changes color, but the cursor remains at the button "clear"
NOTE:
I used the "when-new-item-instance" trigger to change the color of text items
View 3 Replies
View Related
May 27, 2010
I have one requirement i.e i want to make a particular item as mandatory for the current record in the tabular form.
so i have written code as below:
DECLARE
lv_item item;
BEGIN
lv_item := FIND_ITEM ('XXMZ_DETAIL.QTY_ACT');
SET_ITEM_INSTANCE_PROPERTY (lv_item,
current_record,
required,
property_true);
END;
This code is not effecting qty field. If i write set_item_property built-in that item becomes mandatory.But it's not effecting at current record level.It's effecting block level.
So how can i make a field as mandatory at current record level?
View 7 Replies
View Related
Sep 24, 2010
I have this record set coming from two tables.
cursor c1 is select cust_id from tab1 where login=in_login
union
select cust_id from tab2 where login_id = in_login;
in second step; I need to process and filter these ids based on some conditions.The final filtered record set I need to return as Out cursor;
How can I use the processed data to built the output ref cursor ?
View 4 Replies
View Related
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.
The table definition for the formats is:-
CREATE TABLE account_format (acc_id number(2),acc_format VARCHAR2 (200), acc_replace VARCHAR2 (200),acc_process varchar2(1))
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
and one insert would be
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?
View 8 Replies
View Related
Mar 3, 2012
this is just the continuation of my previous topic,
After I bound data into the textboxes, now I want to delete it when I mistakenly click on the same data twice.
this is my screenshot:
View 4 Replies
View Related
Mar 3, 2012
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
[code]...
View 39 Replies
View Related
Jun 22, 2010
i want to check cursor having record or null/ if having records then count of records?
View 5 Replies
View Related
Feb 17, 2012
I have a table of 3 columns:
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
ORA-01034: ORACLE not available
View 10 Replies
View Related
Jul 18, 2011
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.
View 10 Replies
View Related
Sep 16, 2010
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.
View 2 Replies
View Related
Dec 7, 2010
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
how it is possible
View 1 Replies
View Related
Nov 11, 2012
Just explaining what I am trying to achieve:
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
[Code].....
View 5 Replies
View Related
Sep 23, 2013
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)
View 8 Replies
View Related
Sep 7, 2007
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.
View 1 Replies
View Related
Sep 11, 2011
just looking around to use the new feature available in oracle 11g to convert the dbms_sql numeric cursor to reference cursor, how to do it?
parse and execute the sql string first with dbms_sql and then convert it to ref cursor?
View 1 Replies
View Related
Nov 23, 2011
I want to return ref cursor based on explicit cursors
create table jumbo(id number, name varchar2(20));
insert into jumbo values(1,'jumbo');
create table mumbo(id number, name varchar2(20));
insert into mumbo values(1,'mumbo');
commit;
[Code].....
The above procedure has compilation errors when I am trying to open ref cursor
LINE/COL ERROR
-------- --------------------------------------------------------
20/24 PL/SQL: SQL Statement ignored
20/38 PL/SQL: ORA-00942: table or view does not exist
32/24 PL/SQL: SQL Statement ignored
32/38 PL/SQL: ORA-00942: table or view does not exist
SQL>
View 5 Replies
View Related
Aug 7, 2013
Can i user exist when cursor will using For Cursor .
View 15 Replies
View Related
Jul 26, 2012
My procedure proc_ex is in when_validate_item trigger
I have one Multi Record data block in my form with values in its items
I need to Populate multi record block values to one Record Group using
add_group_row,
add_group_column,
set_group_char_cell to populate values to record group
Let us suppose my multi record data block looks like
item1 item2 item3 item4
10 20 50 70
25 15 30 45
45 90 47 38
75 25 85 90
30 56 78 80
how to populate these multi record datablock values to Record Group ???..Eagerly waiting for your Replies
View 3 Replies
View Related
May 16, 2012
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).
THEAD0000000002201109142011091400000000000002091 1 0 -1
TTAIL0000000003000000
THEAD0000000012201109142011091400000000000002091 1 0 -1 20110914-1
TDETL0000000013CMPSPL1004 0 000000010000P0000000000000019990000000000000000200000
TTAIL0000000014000001
THEAD0000000021201109142011091400000000000002091 1 0 -1
TDETL0000000022EMPDSC1005 0 000
TDETL0000000023SCOUP 1006 0 973452 000
TTAIL0000000024000002
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?
View 11 Replies
View Related
Aug 26, 2013
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?
1 loosi 26-AUG-13 01.27.48.000000000 PM2 thej 26-AUG-13 01.28.42.000000000 PM
View 4 Replies
View Related
Sep 13, 2011
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.
View 4 Replies
View Related
Apr 21, 2010
I have the following case to solve:
Example Table:
Nr_ordPos1Pos2Itemqty
O4018510000107 170,00
O4018520000107 30,00
O40651010000107 500,00
O40651020000107 50,00
O4114510000107 300,00
O31141010000107 50,00
O3114520000107 50,00
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:
Nr_ordPos1Pos2Itemqty qty_progr
O4018510000107 170,00 170,00
O4018520000107 30,00 200,00
O40651010000107 500,00 700,00
O40651020000107 50,00 750,00
O4114510000107 300,00 1050,00
O31141010000107 50,00 1100,00
O3114520000107 50,00 1150,00
View 8 Replies
View Related
Jun 29, 2013
I create a data block on a table when I am inserting new record only one record have been saved. Last record no longer exist.
View 4 Replies
View Related
Sep 10, 2012
I have one multi record block in that i have one LOV..If i choose lov in first record that won't be display in second record lov...
View 4 Replies
View Related
May 16, 2013
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).
View 5 Replies
View Related
Nov 21, 2011
i am writing a script for inserting rows in a table where primary key of that table consists of 4 columns. I want to add a new row where 3 columns of that table will be same but change the value of the last one. this is giving an error saying that i am violating the unique constraint. I tried to disable that constraint/PK by following statement but still this error is coming
alter table sections_budgets modify primary key disable cascade;
OR
alter table sections_budgets modify constraints sec_bdgt_pk disable cascade;
the insert statement which i am using. The first four columns are part of PK
insert into sections_budgets (SELECT sb.bdgt_cd, sb.bdgt_dpf_cd, sb.bdgt_yr, :sec_code, sb.usf_usr_id,
sb.aty, sb.comaty, sb.comytd, sb.expn_ytd, sb.sys_dt, sb.cat,
sb.start_aty, sb.expn_lmt
FROM sections_budgets sb
WHERE sb.bdgt_dpf_cd = :b_d_code AND sb.bdgt_yr = :year);
is there any other way of inserting records in this table without droping the whole table because it contains too many triggers and constraints?
View 24 Replies
View Related
May 2, 2010
How can I make report in below format ?
Month Apr-09 May-09 Jun-09 ...... Mar-10 Total
Basic 1000.00 1100.00 1300.00 ...... 1300.00 13260.00
HRA 500.00 600.00 650.00 ...... 600.00 6350.00
Conveyance 100.00 200.00 300.00 ...... 350.00 1300.00
Gross Amt 12350.00 13500.00 13200.00 ......14500.00 23456.00
View 9 Replies
View Related
