PL/SQL theoretical questions

[kiss07]

Hi,

i want 2 and 3 rd max salary in a table .i want a single query.

please help..

Arun

[debasisdas]

A function called from SQL statements must obey certain rules meant to control side effects.
To check for violations of the rules, you can use the pragma RESTRICT_REFERENCES.
The pragma asserts that a function does not read or write database tables or package variables.

[debasisdas]

Try this sample code---try on scott schema

create or replace procedure insdept(num  number)
is
begin
insert into dept(deptno) values (num);
commit;
exception
when dup_val_on_index then
raise_application_error(-20001,'duplicate entry...!');
end;
 
 
 

Hope that helps you

[debasisdas]

To successfully execute the code and raise the exception in the previous example

1. DEPTNO field in DEPT table should be an UNIQUE/PRIMARY KEY field.
2.User trying to insert deplicate value which already exists in the table.

[jithan]

Hey arun... There r a lot of oracle apps... for example financials, CRM, SCM etc... U need to have some basic knowledge about banking industry if u r planning to study financials... its better u goto oracle's website and see all the apps and their desc... so tht u may get some idea....

http://www.oracle.com/applications/home.html

[debasisdas]

Execute immediate is used to call SQL from within PL/SQL.

Where the main purpose is to dynamically execute some SQL at run-time

But cursor may or may not be opened or closed, must be declared at design time only.

[jithan]

Hey... seeing ur sample code there is error for sure... u cant have a DDL(create,update,truncate etc) statement inside a proc... u need to use dynamic sql(exec immediate)... if there r more errors use SHOW ERR in sqlplus environment to see all of them,....

[debasisdas]

Good suggestion

But dear Arun
its like u r asking which girl to marry without having any knowledge of gals or marriage.

Its always better totakeothers advice.

But Don't follow others blindly.

At times use your own brain also.

[jithan]

hey arun... there r a lot of ways to tune ur SQL query...
for example
1. u can use indexes,
2. check whether all the system parameters r set properly,
3. u can run ur query in parallel,
4. u can cache some tables so that fetching of data from tht table becomes very
fast
5. reduce the number of joins etc etc

the most important feature in oracle is... there is a package called DBMS_SQLTUNE... using this package u can give ur sql query to oracle and it will give u suggestions on how to tune the same...

[kiss07]

Dear friends,

Thanks a lot..

Arun.

[kiss07]

Dear debas,

i wrote a program :This is correct or not . i execute this pgm got some errors .
please rectify...


code:



type c_alltype is refcursor return emp%rowtype;

create or replace procedure arun( c in number,
c1 inout c_alltype)


is

var varchar2(30);


begin

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

execute immediate var;

open c1 for var;

commit;

end;
/

var kumar refcursor;


execute arun(23,:kumar);

print kumar;

o/p:

[kiss07]

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

substitute

var:='select name,number,dept 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]

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]

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]

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]

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