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

SQL & PL/SQL :: Returning Cursor For Dynamic Database

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

Windows :: Returning REF Cursor From PL/SQL Function?

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

SQL & PL/SQL :: Dynamic Update Of Cursor Records When Table Gets Updated Immediately

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

SQL & PL/SQL :: Checking If Cursor Is Returning Empty List?

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

SQL & PL/SQL :: Rows Of A Table Updated

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

SQL & PL/SQL :: Select Updated Rows From One Table

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

SQL & PL/SQL :: Can Have All Rows In A Table Updated At Once In Merge Statement

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

Returning Multiple Rows

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

How To Store Total No Of Updated Rows (number) In A Variable

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

Subquery Returning Multiple Rows

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

Subquery Returning Multiple Rows And Update

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

Performance Tuning :: Returning All Rows That Equal Max (value)?

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

SQL & PL/SQL :: Select Column_list Vs Returning Different Number Of Rows

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

SQL & PL/SQL :: Compare Two Rows Of Cursor?

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

SQL & PL/SQL :: Insert All Rows Values Fetched By Cursor?

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

SQL & PL/SQL :: Insert Into Select Or Cursor For 100k Rows

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

Precompilers, OCI & OCCI :: How To Fetch All Rows In A Cursor In Local Variables

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

Precompilers, OCI & OCCI :: Pro*C - Cursor Leak With Cursor Array

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

SQL & PL/SQL :: Cursor With Bind Variable And Cursor Record

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

PL/SQL :: How To Get The Record Before Updated

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

SQL & PL/SQL :: Converting Numeric Cursor To Ref Cursor?

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

SQL & PL/SQL :: Open Ref Cursor From Explicit Cursor

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

PL/SQL :: Can User Exist When Cursor Will Using For Cursor

Aug 7, 2013

Can i user exist when cursor will using For Cursor .

View 15 Replies View Related

Logon Trigger Not Getting Updated

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

SQL & PL/SQL :: How To Find Which Table Is Last Updated

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

SQL & PL/SQL :: What Is Being Inserted / Updated In Database Currently

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

SQL & PL/SQL :: How Many (count Of) Records Got Updated

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

SQL & PL/SQL :: Field Keep On Getting Updated By Other System

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

How View Data Gets Updated

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







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