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


ADVERTISEMENT

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 :: 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 View Related

Client Tools :: Toad Explain Plan On Current Statement

Sep 7, 2011

1. In previous version of Toad (9.7.2) during executing a statement I was able to click on 'Explain plan current statement'. In this version of Toad (10.6.0. 42) is not a possible to do it.In the Toad options I've filled the sign: "use a separate connection when Toad itself is generating transactions"

2. During executing a statement I still see 'clock' cursor.Is it a possible to disable to see it?

View 1 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 :: 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 From Within A CURSOR LOOP

Nov 19, 2010

I am not an Oracle programmer but I have been given a task to produce a number of Text files from an Oracle table based on a selection from the table itself.The table consists of many records with a currency code and I need to extract the data into named files based on the currency code.My first idea was to use Cursors and try a select statement matching on the returned value of the cursor but no Joy I can't make it work - perhaps I need to take a new approach.

Current / latest attempt below

DECLARE
CURSOR tmp_splitter_cur IS
SELECT DISTINCT end_consumer_country
FROM tmp_splitter
WHERE 1 = 1;
currency_rec tmp_splitter_cur%ROWTYPE;
[code]....

View 6 Replies View Related

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

SQL & PL/SQL :: Select Dynamic Column Names In Select Statement In Function?

Jul 4, 2010

i want to select dynamic column names in my select statement in my function.

View 4 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

Client Tools :: Passing Variable And Displaying Select Data In TOAD?

Apr 22, 2010

All I want to do is run a procedure (DO_H_RUN) that returns a number into "v1" and then use that variable to return the result sets in three selects. This is all in TOAD. If I do not have a where clause, the code executes fine (allowing me to see the data in multiple Grid tabs)! But, I want to filter the rows.

I've tried doing a number of different things (finding all sorts of stuff on the Web) like declaring cursor variables and the like but after spinning on this for a couple of hours, I'm stuck.

Here's my attempt (the names have been changed to protect the innocent!):

declare
v1 Numeric;
BEGIN
DO_H_RUN('Me', v1);
--DBMS_OUTPUT.PUT_LINE(v1); --This line works okay!
select * from h_run where h_run_id=v1; --NO GO

[code]....

View 10 Replies View Related

SQL & PL/SQL :: Why Blind Select Is Better Than Conditional Select Statement

Dec 29, 2010

Why Blind select is better than Conditional select Statement?

View 10 Replies View Related

SQL & PL/SQL :: Insert Into Statement Doesn't Insert All Rows Return By Select Statement?

Jan 12, 2011

If i inserted the values in table it gets inserting very few rows only.I dont know y it is?

View 15 Replies View Related

If Statement In Select?

Mar 22, 2013

simply select and works great:

select 'CARAT Issue Open' issue_comment, i.issue_id, i.issue_status, i.issue_title, i.ISSUE_summary ,i.issue_description, i.severity,gcrs.Area_name, gcrs.sector_name,

substr(gcrs.stream_name,1,case when instr(gcrs.stream_name,' (')=0 then 100 else instr(gcrs.stream_name,' (')-1 end) ISSUE_DIVISION,

case when gcrs.STREAM_NAME like 'NON-GT%' THEN 'NON-GT' ELSE gcrs.STREAM_NAME END as ISSUE_DIVISION_2

from table(f_carat_issues_as_of('31/MAR/2013')) i inner join v_gcrs_with_stream gcrs on i.segment_id = gcrs.segment_id

where UPPER(ISSUE_STATUS) like '%OPEN%'

Now I want to callte two columns:ISSUE_DIVISION and ISSUE_DIVISION_2

if they are equal in new columns should be value 1 if are not equal should be 0,how can I do it ?

View 4 Replies View Related

Select Statement Errors

May 5, 2010

I have a question about select statements, as I am new to them and don't know how to work all the commands yet.

I'm making a select statement that is about half right... it is shown below:

select t.warehouse_id,
t.quantity_on_hand,
c.product_name
from pahtest3.inventories t
join pahtest3.product_information c using (product_id)
WHERE warehouse_id in (7);

I need to take this select statement and make it so it shows all the products that don't have any quantities in the warehouse in addition to the ones that are already being shown in that select statement.

View 2 Replies View Related

Format Of Select Statement

Jun 15, 2007

decode (a.cancel_time,'',sum ((to_date('23:59','hh24:mi') - a.alarm_time)*24*60), sum((a.cancel_time - a.alarm_time)*24*60)) Duration,

After executing this statement. I get negative values if cancel time is null

I want to subtract alarm_time by 24 hours, if cancel time is null. If not null then cancel-time - alarm_time

View 1 Replies View Related

How To Set Timing On For Every SELECT Statement

Aug 23, 2008

how to set the timing on for all SELECT statements in a procudeure...and i want that should come as a output for very SELECT statement slong with my original output.

View 6 Replies View Related

SQL & PL/SQL :: Using Alias In Same SELECT Statement

Sep 1, 2011

I have a need to use the Alias name of a column within the same select statement( because I can't have another select statement using the first select as table - BO tool limitation).

Ex:

Select dept_id, agency, sum(quantity) as "sum_qty"
where sum_qty > 500;

Currently oracle won't allow using alias name Sum_qty in the same select statement. Is there a way to use alias within the same select statement?

View 12 Replies View Related

SQL & PL/SQL :: Variable In Select Statement

Jul 5, 2011

I'm trying to write a simple query so I can do some testing on my application. I am trying to do something like this:

SELECT
Location,
LEVEL,

FROM
S_ORG_EXT

where
Location = 'North America' and LEVEL ='Software'
OR location = 'North America'
and Active = 'N'

in the where statement, I have put in the 'Active' that isn't a column. I want to be able to be able to change that in the select part. But I am not able to do so.

this is what I have tried:
SELECT
Location,
LEVEL,
Active = 'N' --I want to change this in the to N or Y so I can get different results.

FROM
S_ORG_EXT

where
Location = 'North America' and LEVEL ='Software'
OR location = 'North America'
and Active = 'N'

View 7 Replies View Related

SQL & PL/SQL :: Select In Case Statement

Feb 11, 2011

I have the following tables:

create table lookups (code varchar2(20), amount number);

insert into lookups values ('Rent' , 500);
insert into lookups values ('Breakpoint' , 10);

create table products (id number, cost number, year varchar2(4));

insert into products values (1, 1000, '2011');
insert into products values (1, 2000, '2011');
insert into products values (2, 100, '2011');
insert into products values (3, 50, '2011');

commit;

I want to write a query which lists the IDs and the sum(cost), and a Y/N indicator which is set to 'Y' IF sum(cost) > ( (lookups.rent value) * (100 - lookups.breakpoint value))/100

I have written this query:

SELECT id,
sum(cost)cost,
year,
CASE
WHEN cost >
((SELECT amount
[code]....... ORDER BY id;

This returns

ID COST YEAR YN
--------- ---------- ---- -
1 1000 2011 Y
1 2000 2011 Y
2 100 2011 N
3 50 2011 N

The YN is correct, but it needs to sum the amounts. So there should only be one row for id1 = 3000.e.g.

ID COST YEAR YN
--------- ---------- ---- -
1 3000 2011 Y
2 100 2011 N
3 50 2011 N

I am not sure how to do this. Or is there a better way of doing this than using CASE.

View 4 Replies View Related

SQL & PL/SQL :: How To Add Two More Columns Into Select Statement

May 6, 2011

Below is the query that I have:

Quote:
select a.contact, b.db_name
from MOM.db_contacts@DB_LINK a, MOM.databases@DB_LINK b,
where a.DB_ID=b.DB_ID
and b.DB_name=(SELECT unique substr(upper(t.target_name), 1, instr(t.target_name,'_',1,1)-1)
FROM mgmt_targets t JOIN mgmt_current_severity s
ON s.target_guid = t.target_guid
WHERE t.target_type = 'oracle_database'
and UPPER(t.target_name) like '%11GDB%');

How can I add two more columns into the select statement and the two columns are in the tables that were in the sub query..I would like to have something like this:

Quote:
select a.contact, b.db_name, COLUM1, COLUMN2
from MOM.db_contacts@DB_LINK a, MOM.databases@DB_LINK b, mgmt_targets t, mgmt_current_severity s
where a.DB_ID=b.DB_ID
and b.DB_name=(SELECT unique substr(upper(t.target_name), 1, instr(t.target_name,'_',1,1)-1)
FROM mgmt_targets t JOIN mgmt_current_severity s
ON s.target_guid = t.target_guid
WHERE t.target_type = 'oracle_database'
and UPPER(t.target_name) like '%11GDB%');

View 5 Replies View Related

SQL & PL/SQL :: Cursor Select Statement?

Apr 28, 2010

I have a cursor in a procedure that selects columns from a table on a remote Oracle database using a database link. It all works fine when the table name and database link name is 'hard coded' into the procedure but what I would like to achieve is have the select statement lookup the table name and database link name from a column in another table. See example below

CURSOR c_total_bookings IS
SELECT personnel_id,
surname,
forenames,
birth_date,

[code]...

What I would like to do is replacecompany_bookings@remote.linkwith a variable that takes its value from another table.

View 1 Replies View Related

SQL & PL/SQL :: How To Use CASE In SELECT STATEMENT

Jun 24, 2011

select GAM.FORACID,
SOL.SOL_DESC,
GAM.ACCT_NAME,
LHT.LIM_SANCT_DATE,
LHT.SANCT_LIM,
to_char( GAM.CLR_BAL_AMT,'9,99,999.99'),

[code]...

i am getting error like this
ERROR at line 9:
ORA-00911: invalid character

View 4 Replies View Related

PL/SQL :: Execute Immediate On SELECT Statement

May 3, 2013

Execute Immediate on SELECT Statement

declare l_stmt VARCHAR2(1000);
begin
l_stmt := 'Select * from mytable' ;
EXECUTE IMMEDIATE l_stmt;
--- dbms_output.put_line( l_stmt);
end;

it is not returning any output. I have hardcoded the table name here where as in real time Mytable name will differ.

View 14 Replies View Related

PL/SQL :: Variables In SELECT Statement

Feb 19, 2013

I was given a SQL query to develop another query that will need to do something similar. The query I was given I believe is a stored procedure. It is PL/SQL and has a SELECT statement that looks like

SELECT :var1 AS var1
FROM t1
WHERE
:var1 = 1

In the past I have seen where variables can be used in the WHERE clause like

SELECT c1
FROM t1
WHERE
c1 = :my_column_valueand this makes sense to me.

Usually :my_column_value is declared in the DECLARE block of the PL/SQL. In the query I was given, :my_column_value is not declared in any DECLARE blocks (maybe I wasn't given the entire code??). But even if it was, that would be even more confusing because in the DECLARE block it could be assigned one value and then set to another value in the WHERE clause???

The first query doesn't make sense to me and when I run it as plain SQL I get an error like "SP2-0552: Bind variable "var1" not declared. I am guessing that such a syntax is not valid for regular SQL?

The only thing I can gather that the first query does is something along the lines of

SELECT 1 as var1

so it will output a 1 for every row. Not always that interesting of a query, but it is needed sometimes, I know. But I still don't understand this type of assignment.

So what's going on here? I'd be happy to look it up and read about it myself, but I don't know what to search for.

View 5 Replies View Related

PL/SQL :: Select Statement For Last Name Added

Nov 12, 2013

i want a select statement to retrieve the last name added into the "ename" column .i mean :if i insert "jack" into "ename" column , i want to write  a select statement to retrieve that last name while i do not know it is jack or any other name ? 

View 2 Replies View Related

SQL & PL/SQL :: Function With DML In Select Statement?

Sep 14, 2013

How to use a function with DML operations in it in a select statement.

View 11 Replies View Related

PL/SQL :: Single Select Statement

Apr 22, 2013

I am trying to find the best solution for the following problem

I have a table called HIERARCHY with 3 columns.

col1 col2 col3
------------------------
1
1 2
1 2 3
4 5
6 7 8

I would like to check if the table contains invalid records. Col1 , Col2 and Col3 are seen as hierarchy levels and col1 is the top level.

For instance record 1,2,3 (level3) is valid because it exists record 1,2 (level 2). Moreover 1,2 record (level 2) is valid because it exists record 1 (top level).
However 4,5 and 6,7,8 are not valid as higher levels are not completed.

How can I check in a single SELECT statement if the table contains invalid records? Maybe analitical functions or regular expressions?!

View 5 Replies View Related

Using Declared Variable Within Select Statement?

Apr 5, 2011

here is what i am trying to do: im as using oracle 8 with sqltool

i have a Very large query. and i notice that many things are repeating. so i want to add them to a variable, instead of re-typing them. for example:

select SomeID from SomeTable;

i want SomeID to be put into a variable. but i still want to be able to get a normal select query at the end so that i can see the returned value:

i tried things like:
declare x number;
begin
set x=45454
select x from SomeTable;
end;

but could not get it to work.

View 2 Replies View Related

SQL & PL/SQL :: Make Cursor To Take Select Statement?

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







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