PL/SQL theoretical questions

[kiss07]

var:=select name,number,dep t from emp where deptno'=c1;

substitute

var:='select name,number,dep t from emp where deptno'=c;

[soumya1011]

SELECT name,salary
FROM (SELECT name,
salary,
rank() over (order by salary desc) rank
FROM employee)
WHERE rank in (2,3);

[kiss07]

Problem in ref cursor

Dear debas,

I have an doubt .Is ths possible ref cursor inside execute immediate.
i occured an error:

CREATE OR REPLACE PROCEDURE GetEmployeeDtl (p_deptno IN
emp.deptno%TYPE ,
p_emp OUT SYS_REFCURSOR) AS
v_var varchar2(4000);
BEGIN
v_var:='OPEN p_emp FOR';
v_var:= ' SELECT ename,';
v_var:= v_var || ' empno,';
v_var:= v_var || ' deptno';
v_var:= v_var || ' FROM emp';
v_var:= v_var || ' WHERE deptno = ' ||p_deptno;
v_var:= v_var || ' ORDER BY ename';
execute immediate v_var ;
END GetEmployeeDtl;
/

Procedure created.

It geting compiled sucessfully.

When I am executing it gets executed too, but when printing cursor value its giving error as:

SQL> var x refcursor;
SQL> exec GetEmployeeDtl (20,:x);

PL/SQL procedure successfully completed.

SQL> print x;
ERROR:
ORA-24338: statement handle not executed


SP2-0625: Error printing variable "x"


pls rectify . what's error ..


Arun..

[debasisdas]

Since your procedure contains an OUT parameter ,it can't be executed at SQL> prompt.

Call the procedure from within an Anonymous block.

It will work .

[kiss07]

Can't understand debas,

pls update my error ( correct coding..)


Arun..

[venkatanathen]

Hi,
First and foremost is statistics for tables and indexes should be gathered using dbms_stats. So, please try to understand what is this pkg. how best we can manipulate its parameters and its outcome. once you masted this pks, you are half way thru.

Thanks
VK

[r035198x]

All the theoretical questions posted by kiss07 have now been merged into this thread.

For more information on the topics discussed here, you can check the
User guide and reference

[kiss07]

BulkInsert

Dear Debas,

Almost my posting all are improving...

oracle version:9i

what is difference between for loop and forall loop in plsql.
any relationship from Bulk insert and forall .please explain clear..

Expecting Replies..

Arunkumar..

[kiss07]

Joins

Dear Friends,

Now i atttend Oracle interviews.I want to learn queries in left outer join
and right outer joins ,full outer joins in oracle 9i.Any body Help to me..any notes or send any links ..please help..

Expecting Your Replies..

Arun..

[kiss07]

Bind Variables?

Dear friends,

In oracle version 9i.What is the use of Bind variables and Host variables?
expecting ypur replies..

Arun..

[MMcCarthy]

I have again merged your basic questions into this thread. As you were warned the experts are here to help but not to supply definitions and examples for every term you come accross in the textbook.

As you have ignored repeated warnings about cluttering up the forum with these kind of posts your account will now be banned for a period of 1 week.

After this period if this behavior continues your account will be permanently banned.

ADMIN