469,366 Members | 2,386 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,366 developers. It's quick & easy.

PL/SQL theoretical questions

kiss07
99
Hi,

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

please help..

Arun
Apr 23 '07
70 5558
debasisdas
8,127 Expert 4TB
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.
May 10 '07 #51
debasisdas
8,127 Expert 4TB
Try this sample code---try on scott schema
Expand|Select|Wrap|Line Numbers
  1. create or replace procedure insdept(num  number)
  2. is
  3. begin
  4. insert into dept(deptno) values (num);
  5. commit;
  6. exception
  7. when dup_val_on_index then
  8. raise_application_error(-20001,'duplicate entry...!');
  9. end;
  10.  
  11.  
  12.  
Hope that helps you
May 10 '07 #52
debasisdas
8,127 Expert 4TB
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.
May 10 '07 #53
jithan
5
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
May 10 '07 #54
debasisdas
8,127 Expert 4TB
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.
May 10 '07 #55
jithan
5
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,....
May 10 '07 #56
debasisdas
8,127 Expert 4TB
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.
May 10 '07 #57
jithan
5
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...
May 10 '07 #58
kiss07
99
Dear friends,

Thanks a lot..

Arun.
May 10 '07 #59
kiss07
99
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:
May 10 '07 #60
kiss07
99
var:=select name,number,dept from emp where deptno'=c1;

substitute

var:='select name,number,dept from emp where deptno'=c;
May 10 '07 #61
SELECT name,salary
FROM (SELECT name,
salary,
rank() over (order by salary desc) rank
FROM employee)
WHERE rank in (2,3);
May 10 '07 #62
kiss07
99
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..
May 10 '07 #63
debasisdas
8,127 Expert 4TB
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 .
May 10 '07 #64
kiss07
99
Can't understand debas,

pls update my error ( correct coding..)


Arun..
May 10 '07 #65
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
May 10 '07 #66
r035198x
13,262 8TB
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
May 10 '07 #67
kiss07
99
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..
May 14 '07 #68
kiss07
99
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..
May 15 '07 #69
kiss07
99
Dear friends,

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

Arun..
May 15 '07 #70
MMcCarthy
14,534 Expert Mod 8TB
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
May 16 '07 #71

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

16 posts views Thread by Materialised | last post: by
reply views Thread by softwareengineer2006 | last post: by
reply views Thread by connectrajesh | last post: by
8 posts views Thread by Krypto | last post: by
reply views Thread by Joseph Ferris | last post: by
reply views Thread by ramu | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.