PL/SQL theoretical questions

[kiss07]

Execute immediate stmt

Dear friend,

How to use cursor With execute immediate statement?pls tell me.. sample prgm..



Arun..

[pradeep kaltari]

Dear friend,

How to use cursor With execute immediate statement?pls tell me.. sample prgm..



Arun..

Hi Arun,
Could you be more clear as in what exactly you are looking for.

[frozenmist]

Hi Arun,
On a column you have a unique index i.e; only unique values will exist in that column, no duplicates should be there.
This Error occurs if you try to insert or update so that a duplicate value is created in column with unique index.
Hope you understood,
Cheers

[debasisdas]

In the example below, procedure ADD_ENTRY refers to varray LEXICON both as a parameter and as a global variable. When ADD_ENTRY is called, the identifiers WORD_LIST and LEXICON point to the same varray.

DECLARE
TYPE Definition IS RECORD
(
word VARCHAR2(20),
meaning VARCHAR2(200)
);

type

TYPE Dictionary IS VARRAY(2000) OF Definition;
lexicon Dictionary := Dictionary();

procedure

PROCEDURE add_entry
(
word_list IN OUT NOCOPY Dictionary
) IS
BEGIN
word_list(1).word := 'sort';
lexicon(1).word := 'arrange';
END;
BEGIN
lexicon.EXTEND;
add_entry(lexicon);
dbms_output.put_line(lexicon(1).word);
END;

[frozenmist]

Hi Arun,
Just read through this page.You should be able to understand it. There is an example also at the end.

http://download-east.oracle.com/docs/cd/B14117_01/appdev.101/b10807/13_elems039.htm

Cheers

[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_REFERE NCES.
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,upda te,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,dep t 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: