PL/SQL :: Add Another FETCH INTO Statement In While Loop Block

Jul 12, 2012

I execute the below code in TOAD.

DECLARE
CURSOR c1
IS
SELECT *
FROM tab
WHERE ROWNUM < 5;
[code]....

If I add another FETCH INTO statement in the while loop block ,I will get the output.why I am getting this error exactly and how another FETCH INTO is preventing it.

View 10 Replies


ADVERTISEMENT

SQL & PL/SQL :: Place Insert Statement In Loop Inside Anonymous Block?

Nov 25, 2012

can we place insert statement in loop inside anonymous block?

CREATE TABLE DEP(DEPTID NUMBER(5) NOT NULL PRIMARY KEY,DNAME VARCHAR2(10),LOCID VARCHAR2(10));
DECLARE
I NUMBER(5);
BEGIN
I := 0;
LOOP
INSERT INTO DEP VALUES(&DEPTID,'&DNAME',&LOCID);
I := I+1;
EXIT WHEN I = 5;
END LOOP;
END;

View 6 Replies View Related

Precompilers, OCI & OCCI :: Pro* C Code To Fetch Cursor And Loop

Nov 18, 2009

i HAVE THE FOLLOWING CODE WRITTEN IN A *.pc FILE. I am trying to loop to fetch data from cursor. But the code exist after it fetches the first record. Let me know what is it the right way to fetch data from cursor?

EXEC SQL BEGIN DECLARE SECTION;

char str[64];

EXEC SQL END DECLARE SECTION;

/*cursor declarations*/
EXEC SQL DECLARE Get_SQLText_Cursor CURSOR FOR

[Code]....

View 6 Replies View Related

SQL & PL/SQL :: Loop Through Records Using Block?

Nov 8, 2011

How do I loop through a Input parameter (varchar_table) and pass the input value to a select query.

Procedure Test
(
param1 IN dbms_sql.varchar2_table
)

[Code]....

1. How do I define temp_tbl and is it a best practice to use temporary table - if not what is the best method to do the same.

View 5 Replies View Related

SQL & PL/SQL :: Oracle 9i - Block Logic To Fetch Records From Table And Compare

Dec 26, 2011

the following proble.The emp table is having 14 records.

SELECT * FROM emp ORDER BY empno;

EMPNOENAMESALDEPTNO
7369SMITH80020
7499ALLEN160030
7521WARD125030

[code]...

The emp table is having 10 records.

SELECT * FROM emp_10 ORDER BY empno;

EMPNOENAMESALDEPTNO
7369SMITH80020
7499ALLEN160030
7521WARD125030
7566JONES200020

[code]...

I have written the following PL/SQL block logic tofetch the records from the emp table and compare the records with emp_10 table to perform insert if the records are newelse to perform update the existed records in the emp_10 table.

DECLARE
CURSOR tranche_balance_cur
IS
SELECT empno,
ename,
sal,

[code]...

Execution scenario 1:

I have commented insert and update statements in that case I got the following out put.

Inserted Records4
Updated Records10

As per the logic it's giving the correct output because the cursor is fetching 14 records in that already 10 records are existed in emp_10 tableand 4 are new records.so that it's showing the count for inserted records as 10 and updated records as 4.

Execution scenario 2:
I have uncommented insert and update statements in that case I got the following out put.

Inserted Records13
Updated Records1

As per the logic it's not giving the correct output.

I tried with using TRIM function in the comparision logic to avoid spaces.

TRIM(emp_10.empno) = TRIM(tranche_balance_rec.empno)
AND TRIM(emp_10.ename) = TRIM(tranche_balance_rec.ename)
AND TRIM(emp_10.sal) = TRIM(tranche_balance_rec.sal)
AND TRIM(emp_10.deptno) = TRIM(tranche_balance_rec.deptno)

View 10 Replies View Related

SQL & PL/SQL :: No Data Found Stops The Loop Action Inside Block?

Jul 1, 2013

I am trying to create an anonymous PL/SQL block to output privilege information for each of the users listed in DBA_USERS In a loop. This is my block so far (not finished):

declare
v_usr varchar2(30);
v_out_header varchar2(100);

[Code]....

The output is as follows:

***User-Role Privilege report***
-----------------------------------
username: ANDREY , profile: DEFAULT
SYSTEM privileges granted directly to the user(not through ROLE) :
no_data_found

A problem I am encountering is that for some users I have no direct privileges that are not granted through roles, And when I have the expression v_qry (which is basically "'select grantee ||'',''|| privilege from DBA_SYS_PRIVS where grantee not in (select role from dba_roles) and grantee ='||'''' ||v_usr||''''") not initialized with values because the select statement retrieved 0 results, I have the process interfered by the no_data_found error/exception.

Questions: how I can preferrably simply, avoid/overcome my problem? Some way to make the loop go on in spite of no data found? maybe something similar to NVL?

View 14 Replies View Related

TOAD - LOOP With A Select Statement

Oct 19, 2011

I can work with 'straight' query's etc., but now I want to make a query with a loop. I have made a simple one to demonstrate what I want but the real one is working is working by it self but I want to get it work with a loop.

This is the simpel version:

DECLARE

P_UID NUMBER;
Max_UID NUMBER := 42220;

BEGIN

P_UID := 42210;
LOOP
select *
from contract lc
where lc.UIDCONTRACT = P_UID;

P_UID := P_UID + 1;
EXIT WHEN P_UID > Max_UID;
END LOOP;
END;

The error I get is:

Line 10, column 9:

PLS-00428: an INTO clause is expected in this SELECT Statement.

So I know you can declare a variable and then CONTRACTID INTO v_contractID.

But if I have to put every field in a variable what is then the advantage of a loop. So I expect that I'm on the wrong road and not understand how this works.

View 4 Replies View Related

Client Tools :: Loop Select Statement

Mar 10, 2010

I have this following select statement

Select name
from names_list
where name like ('A%')

The output returns a list of names beginning with 'A'...What I want is for the output to repeat that list of names multiple times e.g. if the only two names returned are 'Andrews' and 'Apple', I want to have the output show

Andrews
Andrews
Andrews
Apple
Apple
Apple

View 8 Replies View Related

PL/SQL :: Commit After 2000 Records In Update Statement But Not Using Loop

Mar 12, 2013

My oracle version is oracle 9i

I need to commit after every 2000 records.Currently am using the below statement without using the loop.how to do this?

do i need to use rownum?

BEGIN

UPDATE
(SELECT A.SKU,M.TO_SKU,A.TO_STORE FROM
RT_TEMP_IN_CARTON A,
CD_SKU_CONV M
WHERE

[Code].....

View 8 Replies View Related

SQL & PL/SQL :: Confirmation Of Statement Execution In Block

Jul 12, 2010

I have a set of sql statements which i have to execute inside a pl/sql block. But i need to know the response of each statement and confirmation whether it is executed successfully.

Practically i need to get info as such in sqlplus status msg for each sql statement

E.g. :
SQL> insert into test values(1);

1 row created.

or similar to this.

View 7 Replies View Related

SQL & PL/SQL :: Tracking Exception In One Single Block For Three Different Select Statement?

Mar 8, 2010

how can i track the exception for three select statement in one pl-sql block. here is synario.......

begin
select * from emp where empno=1234; --statement 1
select * from cusotmers where cust_id=125; --statement 2
select * from products where product_id='a-3'; --statement 3
end;

i want to track exception any one for ex no_data_found for all these three different statement.

I know if i put this three statement in three different pl-sql sublock then i can trap it....

how can i trap it in one single block?

View 4 Replies View Related

Forms :: ORA-24374 / Define Not Done Before Fetch Or Execute And Fetch

Mar 24, 2011

FRM-40501: ORACLE error: unable to reserve record for update or delete.

ORA-24374: define not done before fetch or execute and fetch

My master-detail form has single canvas. For both blocks, master and detail, two tables joined together in each. One table to be updated, second table has some info for reference (query only).

I am getting these errors when in detail block the item from LOV is selected for existing record. This does not happen for new record inserted in detail block.

View 1 Replies View Related

OO4O And Wizards :: How To Fetch Columns In Cursor Using FETCH

Nov 1, 2012

create or replace PROCEDURE newprocedur(outname OUT VARCHAR2,outroll OUT NUMBER) AS

CURSOR c1 IS
select Name,Rollno,Section from emp;
BEGIN
Open c1;
fetch c1 into outname,outroll;

Here out of 3 columns in cursor is it possible to fetch only two columns using FETCH like i did above?

View 1 Replies View Related

PL/SQL :: Possible To Use Forall Instead Of For Loop - End Loop

Nov 19, 2012

CREATE OR REPLACE PROCEDURE IND_MONITOR(P_tab VARCHAR2)
is
type ind_table is table of varchar2(20);
p_ind ind_table;
v_sql varchar2(2000);
begin
select index_name bulk collect into P_Ind from user_indexes where table_name=upper(P_tab);
for i in 1..p_ind.count loop
v_sql :='alter index '||p_ind(i)|| ' monitoring usage'
execute immediate v_sql using p_ind(i);
end loop;
end;

can i use forall instead of 'for loop ..end loop'

View 10 Replies View Related

Forms :: Multi Data Block And Checkbox Field Based On Control Block

Nov 19, 2010

i have multi data block filed. and checkbox field which based on control block...My task is when i check checkbox only one field should enabled and my mouse goes to that field

e.g

item11 item21 item31 chkbox1
item12 item22 item32 chkbox2

Scenario like this :

My item field based on data block and checkbox based on control block,while i checked chkbox1 , only item31 on that current record should be enabled and i changed value only on that field

when i checked chkbox1 , my cursor goes to item31...not item32

View 12 Replies View Related

Forms :: Distribute Master Block Total Amount In Detail Block Line Wise

May 5, 2012

i have a master detail form, In Master block we have one field cheque amount and in Detail block we have field receiveable amount invoice wise. if company paid us a cheque amount we will enter this amount in Master block field Cheque amount and in detail block there will be invoice wise receivable amounts. i want to distribute the cheque amount in detail block invoice wise for example

Cheque amount in master block = 291

Invoice wise receiveable amount is as follows

Invoice No , receivable amount , Received amount

10, 196 , 0
20 , 95 , 0
30 , 54 , 0

Result should be as follows:

Invoice No , receivable amount , Received amount

10, 196, 196
20 , 95 , 95
30 , 54, 0

Received amount field should be distributed according to the receivable amount when recevied amount = cheque amount then remaining will be 0.

View 2 Replies View Related

Backup & Recovery :: Error / Corrupt Block Relative Dba / 0x01c12a58 (file 7 / Block 76376)

Jan 25, 2011

i have an error with block corruption

Error: Corrupt block relative dba: 0x01c12a58 (file 7, block 76376)

What all the ,methods I can go for if we are working on a production environment with out any down time.

I can go for dbms repair package and restore and recover

View 3 Replies View Related

Forms :: How To Read Values In A Multi Record Control Block Using Pre-insert Trigger (block Level)

Jul 24, 2010

I have a multi record control block (basically a text item displaying 6 records) where user enters values and I want to process the values using pre-insert trigger.

I want to read value in each record and then do some tasks using a pre-insert trigger before I commit the values. To navigate between the records I was using first_record, next_record, clear_record built-ins but it gives errors like "40737-illegalrestricted procedure next_record in pre-insert trigger".

View 3 Replies View Related

Variable Usage In Type Of Table Declaration Statement And Execute Immediate Statement

Aug 10, 2011

HOW to use variable P_TMPLID in following statement

TYPE typ_unrecon IS TABLE OF REC_' || P_TMPLID ||'_UNRECON%ROWTYPE index by binary_integer;

because its throwing error while compiling

and also in statement
FORALL i IN unrecondata.FIRST .. unrecondata.LAST SAVE
EXCEPTIONS
--STRSQL := '';
--STRSQL := ' INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES ' || unrecondata(i);
-- EXECUTE IMMEDIATE STRSQL;
INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES unrecondata(i);---throwing error on this statement
commit;
--dbms_output.put_line(unrecondata(2).TRANSID);
EXCEPTION

View 2 Replies View Related

SQL & PL/SQL :: Select Statement From Schemas In MERGE Statement In USING Clause

Sep 13, 2013

In the following merge statement in the USINg clause...I am using a select stament of one schema WEDB.But that same select statement should take data from 30 schemeas and then check the condition below condition

ON(source.DNO = target.DNO
AND source.BNO=target.BNO);

I thought that using UNIONALL for select statement of the schemas as below.

SELECT
DNO,
BNO,
c2,
c3,
c4,
c5,
c6,
c7
[code]....

View 5 Replies View Related

SQL & PL/SQL :: Select Statement Is Blocking A Delete Statement

Jan 11, 2012

I am using JDBC to run a few queries from my Java program (multi-threaded one).I am facing an issue where a select statement is blocking a delete statement. From the java code point of view, there are 2 different threads accessing the same tables (whith different DB connection objects).

When the block occurs (which i was able to find out from the java thread dump that there is a lock on oracle), the below is the output:

SQL> SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')
2 || ' User '||s1.username || '@' || s1.machine
3 || ' ( SID= ' || s1.sid || ' ) with the statement: ' || sqlt2.sql_text
||' is blocking the SQL statement on '|| s2.username || '@'
4 5 || s2.machine || ' ( SID=' || s2.sid || ' ) blocked SQL -> '
6 ||sqlt1.sql_text AS blocking_status FROM v$lock l1, v$session s1, v$lock l2 ,
7 v$session s2,v$sql sqlt1, v$sql sqlt2
8 WHERE s1.sid =l1.sid
9 AND s2.sid =l2.sid AND sqlt1.sql_id= s2.sql_id
AND sqlt2.sql_id= s1.prev_sql_id AND l1.BLOCK =1
10 AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2;
[code]...

From the above it can be seen that a select statement is blocking a delete. Unless the select is select for Update, it should not block other statements is not it ?

View 10 Replies View Related

How To Fetch Data

Sep 19, 2011

Table structure:

table x
x1 x2 (Columns)
1 x
2 y
1 z

now i need to o/p like this

1 x,z
2 y

how to fetch data in above format.

View 2 Replies View Related

SQL & PL/SQL :: Best Way To Loop Against CSV

May 25, 2010

I have a list of strings ( like a,b ,c) that I want to loop againts. I will be creating string to execute them as dynamic sql.I could have put the string in a table and loop againt the table but this is a deployment script so I do not want to create any table.I can also do

DECLARE
cursor c is SELECT 'a' FROM dual UNION SELECT 'b' FROM dual ;
BEGIN
for i in c
loop
.......
end loop.
END;

But I have many strings to loop over. What is the best way to achieve it. Can i use collection to achieve this?

View 4 Replies View Related

SQL & PL/SQL :: Fetch Data From Table

May 11, 2011

I want to write a SELECT query on the data which are collected in a PLSQL table which is having 4 columns.

Looping through all the records in the PLSQL table will not get my requirement. Because I need to group the data based on two columns and need to fetch the count of groups.

Is there any way to query that PLSQL table?

View 1 Replies View Related

SQL & PL/SQL :: Fetch Into Object Type?

Oct 2, 2011

what ios wrong in the following code

create or replace type testobj as object(col1 number);
create or replace type tabtest as table of testobj;
create or replace procedure proc(a out tabtest) is
cursor c is

[code]..

View 5 Replies View Related

SQL & PL/SQL :: Fetch Information For A Foreign Key

Jul 13, 2010

the view from where we can fetch information for a foreign key viz. reference table, referenced columns etc.

View 3 Replies View Related

SQL & PL/SQL :: How To Fetch Name Of Column And Table Name

Nov 30, 2011

how to get name of coloumn and table name which is being modified in trigger

View 8 Replies View Related

SQL & PL/SQL :: Fetch Age From Birth-dates?

Jul 28, 2013

Im supposed to fetch first names, last names and age of some people based on their birthdate. In my code so far i manage to get the names and the birthdates but what i actually need is a name and lastname and age for example 65 years.

here is the code :

SET SERVEROUTPUT ON
DECLARE
CURSOR C_kundålder IS
SELECT FNAMN,ENAMN,PNR
FROM BILÄGARE;
v_fnamn bilägare.fnamn%TYPE;

[code].....

View 24 Replies View Related

SQL & PL/SQL :: Using A Cursor To Fetch Data?

Sep 25, 2013

when i run this code i get a invalid number error

create or replace
PROCEDURE BULK_REJECT(System_name VARCHAR2)
as
MDM_run_id VARCHAR2(14);
V_sql clob;
cursor Job_metric is
select rowid_job,system_name, table_display_name, run_status

[code]....

View 17 Replies View Related

PL/SQL :: How To Fetch A Row With Maximum Rownum

Jan 15, 2013

In my sql query, how can i fetch the row with max row count? the query has around 10 columns.

View 2 Replies View Related







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