Home » SQL & PL/SQL » SQL & PL/SQL » query (Oracle 10g)
query [message #361240] Tue, 25 November 2008 08:38 Go to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
Hi,

I have a table mine_table which has one column and 10 rows.

I need to pass these 10 rows to another query where clause which returns a distinct value. I use this distinct value to update the other rows in the next step. I wanted do in the plsql.

Furthermore, I want to pass all the values in a single shot.



select disinct a into vari from mine_a
where  serial in (1,5,6,8--here i want add 10 rows)



Please advice.


Regards,
Pointers.

[Updated on: Tue, 25 November 2008 08:39]

Report message to a moderator

Re: query [message #361242 is a reply to message #361240] Tue, 25 November 2008 08:49 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Hi,
IN Condition allows to use subquery, so simply SELECT the column from mine_table instead of using value list.

Documentation link (with examples): http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/conditions013.htm#i1050801
Re: query [message #361244 is a reply to message #361240] Tue, 25 November 2008 09:01 Go to previous messageGo to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
I want to use it in the plsql rather sql.
How can i use a subquery which return morethan one value in the IN clause in plsql.
Furthermore, I cant use cursor loops as the select query should be executed in on shot taking all the IN values.

Re: query [message #361246 is a reply to message #361244] Tue, 25 November 2008 09:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Instead of tell what you want you should post a test case.
If the values comes from a cursor (why?), the only way to do it in one shot is to previously fill an array.

Regards
Michel
Re: query [message #361379 is a reply to message #361244] Wed, 26 November 2008 02:56 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi,
Quote:

I want to use it in the plsql rather sql.



my school taught me plsql should be preferred only when it is not possible by simple sql. What could be the valid preferences to select plsql. I am seriously asking and want to correct myself on the wrong concepts taught to me if so.
yours
dr.s.raghunathan
Re: query [message #361382 is a reply to message #361240] Wed, 26 November 2008 03:03 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
How can you guarantee that this query
select disinct a into vari from mine_a
where  serial in (1,5,6,8--here i want add 10 rows)
wil return a single row if you can pass in an arbitrary IN list.

If you could explain what you're trying to do, it would make things easier - Personally I'd replace everything you're suggesting with a single UPDATE statement.
Previous Topic: cannot perform a DML operation inside a query
Next Topic: ORA-29283: invalid file operation, ORA-06512: at "SYS.UTL_FILE", line 449
Goto Forum:
  


Current Time: Sun Dec 04 08:28:12 CST 2016

Total time taken to generate the page: 0.07283 seconds