SQL & PL/SQL :: Returning Updated Rows With REF CURSOR?
Sep 27, 2013
The following code is indicative of what I'd like to do (as in not correct at all ). Would there be a more immediate way to accomplish this other than executing a SELECT statement after the UPDATE?
-- Incorrect indicative example 1.
DECLARE
v_cur SYS_REFCURSOR;
BEGIN
UPDATE table1(f1, f2)
SET ('v1', 'v2')
WHERE f3 = 'v3'
RETURNING <updated_rows> INTO v_cur
END;
-- Incorrect indicative example 2.
DECLARE
v_cur SYS_REFCURSOR;
BEGIN
OPEN v_cur FOR
UPDATE table1(f1, f2)
SET ('v1', 'v2')
WHERE f3 = 'v3'
END;
View 4 Replies
ADVERTISEMENT
Jul 20, 2011
I am creating a db function to select * from table_name to be used by the application for retrieving lookup data. I found many examples for how to return cursor as a result of executing a dynamic sql but don't know the optimum way.
following is my current trial:
CREATE OR REPLACE PACKAGE types AS
TYPE Cursortype IS REF CURSOR;
END;
/
CREATE OR REPLACE FUNCTION F_GEN_SELECT_CURS
(S_APP_USER IN VARCHAR2, I_MODULE_ID IN NUMBER, S_TABLE IN VARCHAR2)
/*This function maps S_ACC_MAJOR_ACCOUNT_LIST CoolGen service
[code].......
View 5 Replies
View Related
Apr 5, 2011
I've seen several code samples that show how to return a REFCURSOR from a Stored Function, but when I try it with C# it gives me allways WRONG NUMBER OF ARGUMENTS or somehing like that.
I presently don't have my code in here, but it is something like this:
cmd.Parameters.add ("parRefCursor", dbresult.RefCursor, direction.Result);
I'm being able to use it as a OUT parameter, but I would like to have it as a RETURN value from a Function.
View 4 Replies
View Related
Apr 16, 2010
I am having a table with 4 columns as mentioned below
For a particular prod the value greater less than 5 should be rounded to 5 and value greater than 5 should be rounded to 10. And the rounded quantity should be adjusted with in a product starting with order by of rank with in a prod else leave it
Table1
Col1prodvalue1rank
1A21
2A62
3A53
4B61
5B32
6B73
7C41
8C22
9C13
10C74
Output
Col1prodvalue1rank
1A51
2A52
3A33
4B101
5B02
6B63
7C51
8C52
9C03
10C44
I have taken all the records in to a cursor. Once after rounding the request of 1st rank and adjusting the values of next rank is done. Trying to round the value for 2nd rank as done for 1st rank. Its not taking the recently updated value(i,e adjusted value in rounding of 1st rank).
This is because of using a cursor having a value which is of old value. Is there any way to handle such scenario's where cursor records gets dynamically updated when a table record is updated.
View 9 Replies
View Related
Sep 24, 2010
I am select list of ids;
cursor c1 is
select c_id from ctab1 where login = l_id
union
select c_id from ctab2 where login = i_id;
now I need to return err_code and err_message if this combined list is empty ! Can not use exit%notfound.
View 5 Replies
View Related
Jul 17, 2012
write a query find which rows of a table is updated on 2 days before?
(OR)
In table there r so many rows write a query which two rows r updated last two day before?
View 4 Replies
View Related
Jul 13, 2013
There are two tables like I posted below.I want a SQL query which selects all the rows from TABLE A which are not present in TABLE B. Also the select statement should pick all those rows which has updated value of COL_A2 in TABLE A.
TABLE A
COL_A1COL_A2
AAAMOBILE
BBBTABLET
CCCDESKTOP
DDDUNKNOWN
TABLE B
COL_B1COL_B2
AAAMOBILE
BBBUNKNOWN
CCCDESKTOP
The select statement should return following rows from TABLE A
COL_A1COL_A2
BBBTABLET
DDDUNKNOWN
View 2 Replies
View Related
Aug 10, 2010
Can I have all the rows in a table updated at once in the merge statement?
MERGE INTO providermaster a
using
(
SELECT * FROM PROVIDERMASTER@INGEST) b
ON (b.MASTERPROVIDERID=a.MASTERPROVIDERID)
WHEN MATCHED THEN
UPDATE ....... I want to update all the other rows at once..
View 24 Replies
View Related
Mar 1, 2011
I am working on a script in which I want to retrieve multiple rows but I get error ORA-1422.I tried solving it using the following script , but it still gives error.
CREATE OR REPLACE PROCEDURE proc_query
DECLARE
TYPE all_dest IS TABLE OF NUMBER;
destIds all_dest;
BEGIN
SELECT dest_id from sb_packet WHERE src_id = 32;
RETURNING dest_id bulk collect into destIds;
END;
View 3 Replies
View Related
Jun 21, 2011
how to store total no of updated rows (number) in a variable after executing an updation query using script
View 2 Replies
View Related
Oct 13, 2009
I understand what the message "subquery returning multiple rows" means but I have a case where I'm not 100% sure why it's happening to my update query (which in turn probably means I don't fully understand what's going on behind the scenes)
Here is my query:
Update A set (A.id, A.alt_name, A.min_rank)=
(SELECT B.id,
B.fullname,
MIN(B.nm_rankval)
FROM B,
A
WHERE A.id = B.id
AND A.name <> B.fullname
AND B.nametyp = 'ON'
GROUP BY B.id,
B.fullname)
;
The subquery returns 6 rows but they are all unique in terms of the id, name, rankval, etc...I naturally thought that the update statement wouldn't have a problem with this since the subquery is returning rows that are not duplicates and match one for one between table A and B. I used the group by to ensure I return unique values from table B (which does have duplicate id values)
Each of those 6 rows from the subquery of table B can be matched 1-1 with table A...so what am I missing.
View 2 Replies
View Related
Mar 3, 2010
This is a surprisingly common one I've found on the web...even on devshed forum
I am updating one table from another (Updating Table A from Table B):
Table A
ID, Value
-- -----
1 A
1 A
2 B
Table B
ID, Value
-- -----
1 Animal
2 Box
Table A (modified)
ID, Value, Name
1 A Animal
1 A Animal
2 B Box
No I need to update a new column in Table A with the value in Table B.Value where the ID's from both tables match. Problem is: When I do this I get multiple rows and hence Oracle won't let me update this column. Now, I keep reading that for these types of updates, there has to be a one-to-one relationship...
Is this true...is there anyway of telling Oracle to update wherever it finds that ID, regardless of how many duplicate ID's there are?
This is quite a frustrating problem and most of the sites that I've looked for solutions try get the query one-to-one...problem is...with my table sets it's impossible to do that - I need to update wherever the id's match (even if it return multiple rows).
View 10 Replies
View Related
Oct 14, 2010
I'm trying to write a query that counts how many sessions are active during a 1 second time interval, then returns the maximum number of sessions active during any time interval, and all the time intervals that hit that max.
Here's a sample of the inner query results:
"INTERVAL_VALUE""SESSIONS"
"13:14:47" 13
"13:14:52" 13
"13:14:54" 13
"13:19:05" 4
"13:19:28" 4
[code]....
The max(sessions) is 13, so what I want the final output to be is:
"INTERVAL_VALUE""SESSIONS"
"13:14:47" 13
"13:14:52" 13
"13:14:54" 13
Here is the create sql for the test data:
CREATE TABLE "SESSION_TABLE"
(
"SESSIONKEY" NUMBER,
"SESSION_START_TIME" TIMESTAMP,
"SESSION_END_TIME" TIMESTAMP,
CONSTRAINT "PK_SESSIONKEY" PRIMARY KEY ("SESSIONKEY")
);
[code]....
Here is my query that works:
SELECT
maxval.interval_value,
allval.sessions,
licenselimit
FROM
(SELECT
[code]....
View 2 Replies
View Related
Mar 2, 2011
The query below returns 101 rows. If I replace the column list with an asterik the query returns 892 rows. I do not understand why.
--select *
select Ref_Consultant_CD
,Resident_CD
,ID
,Ref_Facility_CD
[code]......
View 6 Replies
View Related
Oct 6, 2010
I have an audit table FEE_RULE_AUD with the following structure.
FEE_RULEVARCHAR2(10 BYTE)
AUDIT_DATETIMESTAMP(6)
AUDIT_ACTIONVARCHAR2(30 BYTE)
AUDIT_USERVARCHAR2(8 BYTE)
NAMEVARCHAR2(30 BYTE)
FEE_BASISCHAR(1 BYTE)
FEES_ONCHAR(1 BYTE)
SECURITYCHAR(10 BYTE)
I have to generate a report for the audit with in the dates specified(range). I got the set of record for the specified date range by using the following query:
select * from fee_rule_aud where TO_NUMBER(TO_CHAR(TRUNC(audit_date),'YYYYMMDD'))>20090629
and TO_NUMBER(TO_CHAR(TRUNC(audit_date),'YYYYMMDD'))<=20100710 order by fee_rule,audit_date
this query returned some five records, now I have to iterate through this and compare 1st and 2nd row in first iteration (1st row will have the new value and 2nd row will have old vale). If there is any difference then insert into audit_log table which has the following structure:
Fee_rule , column_name, old_value,new_value
This process has to repeat for all the 5 rows like comparing 1st,2nd rows and 2nd,3rd rows and 3rd,4th rows and so on if it has more rows.
get the two rows and compare ?
View 16 Replies
View Related
Aug 12, 2010
I have written code as below. I want to insert all rows values fetched by cursor INTO table emp2 (blank as column as employees) . How Can I insert it.
what parameter or code I should used ?
I have marked with color row as below.
DECLARE
CURSOR C1 IS select * from employees;
C2 C1%ROWTYPE;
[Code]....
View 6 Replies
View Related
Jan 22, 2012
I am trying to insert 100K rows, I have written this proc using cursor. But it is saying anonymous block completed, and no rows are inserted. If I just run the select it returns the rows.Could if just insert into select would be fine or should I use cursor.
CREATE OR replace PROCEDURE Insert_data (l_from_date IN VARCHAR2,
l_to_date IN VARCHAR2)
IS
lc_err_msg VARCHAR2 (2000);
ln_count NUMBER := 0;
CURSOR ins_d IS
SELECT a.col1 AS url,
b.col1 AS ref_url,
COUNT (*) AS total_views
[code]....
View 5 Replies
View Related
Jul 29, 2008
How to fetch all the rows in a cursor in local variables(host variables) in PRO C . /*For x in cur is not working....*/
View 2 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
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
Sep 3, 2012
I have a problem where my record is updated yesterday. How to get the record before updated?
View 4 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
May 9, 2011
There is a xxxxx.sql file in the OS level which contains create or replace trigger script. I modified that file with the code below:
if(upper(USER)= 'U_USER') then
execute immediate 'alter session set current_schema=USER';
end if;
after that, I went to sqlplus
and logged in to U_USER like this
SQL>conn U_USER/U_USER;
connected.
SQL>@xxxxx
some numbers are getting generated if i press enter.
Then i press ctrl +c and get to sql prompt.
I issue
SQL>select SYS_CONTEXT('USERENV','CURRENT_SCHEMA') FROM DUAL;
I am getting the output as U_USER but I must get the output as USER and not as U_USER. Basically the trigger is not getting updated.
View 1 Replies
View Related
Apr 11, 2012
I want to know that How to find which table got last updated and how to find last DDL and DML operation obtained in which table? here I know the table name
SQL> SELECT LAST_DDL_TIME FROM DBA_OBJECTS WHERE OBJECT_NAME='PREM';
LAST_DDL_
---------
20-MAR-12
SQL> TRUNCATE TABLE PREM;
Table truncated.
SQL> SELECT LAST_DDL_TIME FROM DBA_OBJECTS WHERE OBJECT_NAME='PREM';
LAST_DDL_
---------
10-APR-12
Note: With out enable the auditing I want to know that .
View 6 Replies
View Related
Apr 2, 2011
I wanted to know if there is a way to keep track of what is being inserted/updated in the database currently.
I tried psoug website and SQL_Trace at orafaq (I cannot post the links) but those are useful when you want to see what happens with your queries in a session... When I tried to modify an outer session by its id I couldn't find any trace file that could've been generated (and its impossible to change "tracefile_identifier" in an outer session)
There is an application that is customized by me and it inserts some additional records into the DB in its backend functionality to which I do not have an access (I mean I don't have any source of the backend code), so if I need to see what are the inserts being made, I have to do that directly from the oracle level. I've heard that it is possible and easy to do in MySQL (but I never used that DBMS).
View 3 Replies
View Related
Dec 8, 2011
I Have a table with 100records.after sometime i updated some records . But after that i want to know how many (count of) records got updated
View 3 Replies
View Related
May 20, 2011
I have a promotion table which is having 3 columns.
PROMOTION_ID
display_name
ASSET_VERSION
This ASSET_VERSION
field keep on getting updated by other system where they may update the display name of the promotion_id.The main concept of this table is there will be a unique combitination of asset_id and promotion id.
Promotion_idDISPLAY_NAME ASSET_VERSION
123 1ST VERSION 1
123 2ND VERSION 2
123 3RD VERSION 3
456 1ST VERSION 1
456 2ND VERSION 2
456 3RD VERSION 3
789 1ST VERSION 1
789 2ND VERSION 2
789 3RD VERSION 3
So,my requirement is that i have the fetch promotion_id ,display_name that is having highset asset_version.
The OUTPUT SHOULD BE:
Promotion_idDISPLAY_NAME
123 3RD VERSION
456 3RD VERSION
789 3RD VERSION
I need a single sql for this .
Driver used: oracle.jdbc.xa.client.OracleXADataSource
Orcale version: 11G
View 24 Replies
View Related
Jul 16, 2013
I want know how the values in view DBA_FEATURE_USAGE_STATISTICS gets updated i have already checked the metadata of this view but unable to find how / when the values are being updated.
View 3 Replies
View Related