1)I want drop 10 procedures at a time?Is it possible by single drop command or any other way is there to do that?
2)Can we use a subquery for 'pivot in clause'?
I googled on this to see that this can be done using 'pivot xml' but the output will be in xml format which doesn't solve my purpose. Is there any other way to do this?
3)can we delete 10 procedures at a time?(An interview question)
1. You mean in a single drop command? No.
create or replace procedure p1 is
begin
null;
end p1;
/
create or replace procedure p2 is
begin
null;
end p2;
/
drop procedure p1, p2;
SQL Error: ORA-00933: SQL command not properly ended
Though perhaps you could argue that if you have 10 procedures in a package and drop that, you're dropping 10 procedures at once...
2. No.
with rws as (
select mod(rownum, 10) x from dual connect by level <= 1000
)
select * from rws
pivot (count(*) for x in (select 1 from dual));
SQL Error: ORA-00936: missing expression
If you want to generate the list from a query you could either:
- Write a query to generate your values. Then use dynamic SQL to pass this list to the pivot
- Use custom types which can "pivot a query".
For more on these, scroll to the "Dynamic Column Lists" section of:
https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot 3. How is this different to q1?