What are the types of cursors apart from explicit and implicit cursors ? Explain when and where they are used ?

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• Jun 1st, 2008

There are only two types of cursors in oracle Pl/sql.1. Implicit cursors2. Explicit cursors    We can define an explicit cursor as static or dynamic (REF CURSOR).   ...

Manoharan

• Oct 25th, 2007

1. Implicit Cursor2. Explicit Cursor3. Ref CursorImplicit Cursor like SQL%rowcount,SQL%rowtype...Explicit   cursors are user defined cursorRef Cursor is object name of the cursor type. Its mainly used for dynamic purpose

Answer Question  Select Best Answer

Assume that I am using a temporary table in a procedure and I am inserting records and updating another set of records through Merge statement. If I use cursor in that temporary table, How can I extract all of the records from temporary table at the end of the stored procedure. Can you please give coding for this.

johnjerry

 Profile Answers by johnjerry Questions by johnjerry

• Oct 7th, 2009

Make sure you do not COMMIT* in the middle of the procedure. You can do any number of insertion into and deletion from temporary table and use "Select * from temp_table" to fetch a...

priyanka jolly

• Nov 24th, 2007

Create Global temporary tables and say on commit preserve rows. The global temporary tables have data available till the session is active. So you if you insert it at one procedure and want to access it somewhere else you can do that very easily till the session is activeThanks Priyanka

Answer Question  Select Best Answer

While i compiled my code , it got compiled but while running my application i got the Oracle error "Could not find the Program unit ? "Can any one tell exactly why this happens.I had validated all the objects and even released all the Locked objects . But still this error persisted .

binurajnair

 Profile Answers by binurajnair Questions by binurajnair

• Feb 20th, 2008

This error can occur in the following scenario also. The application has a constant connection with the database and a package which the application uses gets recompiled. And the recompiled package co...

raj_imr2000

 Profile Answers by raj_imr2000 Questions by raj_imr2000

• Jan 15th, 2008

Hi,ORA-06508:PL/SQL: could not find program unit being calledCause:An attempt was made to call a stored program that could not be found. The program may have been dropped or incompatibly modified, or ...

Answer Question  Select Best Answer

Hi All,I want create a stored procedure by passing the parameter to top nth recordfor Excreate proc test1(@x integer)asbeginselect top @x empid from emp order by empid descendwhen iam executing iam getting error .Please let me know any other alternative way

shruti.potdar

 Profile Answers by shruti.potdar Questions by shruti.potdar

• Dec 10th, 2008

Solution to your problem:Create Procedure TEST1( p_num number)isv_eid number;begin?? select empid into v_eid from EMPwhere empid=(select A.empid from (SELECT empid, rank() over (order by empid desc) RNK FROM emp) A where A.RNK=p_num);dbms_output.put_line('Top'|| p_num || 'emp is ::'|| v_eid);End;/

xambrose

 Profile Answers by xambrose Questions by xambrose

• Jul 19th, 2008

create proc test1(@x integer)asbeginSELECT empid  FROM   (SELECT empid  FROM emp ORDER BY empid )   WHERE ROWNUM = @x;end

Answer Question  Select Best Answer

Hi Can anyone explain what this mean? I have practise PL/SQL about REF obj_type, my codes are: CREATE or REPLACE TYPE home_type AS OBJECT (street VARCHAR2(50), city VARCHAR2(20), state VARCHAR2(20), zipcode VARCHAR2(6), owner VARCHAR2(10));/CREATE TABLE homes OF home_type;INSERT INTO homes VALUES ('300 Regina St', 'WATERLOO', 'ON', '010010', 'HFWANG');INSERT INTO homes VALUES ('301 Regina St', 'WATERLOO',...

priti

• Nov 11th, 2019

Kindly let me know what do you want in result using ref cursor? here you ref cursor as a table which is not exists in system. that is what it is giving error

krishnaveni_g

 Profile Answers by krishnaveni_g Questions by krishnaveni_g

• Jul 15th, 2008

I checked the code it's working fine.

Answer Question  Select Best Answer

How to create temporary table? Give some example

ravi.8311

 Profile Answers by ravi.8311 Questions by ravi.8311

• Jun 28th, 2008

Create table temp as select*from base table where 1=1;

venkat82.k

 Profile Answers by venkat82.k Questions by venkat82.k

• Mar 20th, 2008

Hi guys........Temporary tables means which tables are hold data temporarlyi mean these holds the data certain time mainly only that session.exp:-create global temporary table employ(emp_id number(10)...

Answer Question  Select Best Answer

How many columns you can create in table from this type "long raw"?

krishnaveni_g

 Profile Answers by krishnaveni_g Questions by krishnaveni_g

• Jul 17th, 2008

We can use only one long column in a table

madhupriya.nukala

 Profile Answers by madhupriya.nukala Questions by madhupriya.nukala

• Jun 25th, 2008

Only single column is allowed per a table of data type LONG or LONG RAW

Answer Question  Select Best Answer

How oracle manages dependency between referenced and dependent objects ?

johnjerry

 Profile Answers by johnjerry Questions by johnjerry

• Oct 7th, 2009

We can set REMOTE_DEPENDENCIES_MODE inside init.ora parameter file to TIMESTAMP or SIGNATURE.ALTER SYSTEM SET REMOTE_DEPENDENCIES_MODE = [TIMESTAMP|SIGNATURE]

YugundharK

 Profile Answers by YugundharK Questions by YugundharK

• Oct 13th, 2008

Oracle manages dependency between referenced and dependent objects by using 2 modes1. Timestamp mode2. Signature mode

Answer Question  Select Best Answer

Hi All,I have requirement of sending the records based on the input received from web.I have a ref_cursor returning 30 rows..Here I am supposed to do paging on the set of records present in the above ref cursor.If web gives 1 , then I should send first 10 records in the ref cursor..If gives 2 , then I should send next 10 records in the ref cursor..similarly�..I will get some sort of indicator from...

muraligrandhi

 Profile Answers by muraligrandhi Questions by muraligrandhi

• Jun 9th, 2010

With the help of Ref cursor you can get particular no of records(means 1=10 records,2=20 records like that), for that you can use rownum in the filter condition.If you add below query in Ref cursor yo...

pvidhya

 Profile Answers by pvidhya Questions by pvidhya

• Sep 3rd, 2008

First you have to filter the no of records you need totally (i.e., if you receive input as 2 and if the no of records you need to return are 20, then you need to filter 20 rows) using where rownum

Answer Question  Select Best Answer

What is the rollforward in pl/sql

Madhu_Das

 Profile Answers by Madhu_Das Questions by Madhu_Das

• May 21st, 2009

Rollforward is to Redo, as Rollback is to undo.

goelumesh

 Profile Answers by goelumesh Questions by goelumesh

• Jul 1st, 2008

Roll forward refers to the process Oracle goes through to apply changes contained in the redo log files (both online and archive). The database clock (as measured by the system change number) is moved...

Answer Question  Select Best Answer

What are Return Statement and OUT Parameter in PLSQL Function?

Database Steve

 Profile Answers by Database Steve Questions by Database Steve

• May 20th, 2009

Functions always return a value.Procedures do not.The use of IN, OUT, and IN_OUT parameters as scalar values or ref cursors is dependent on your need and overall performance not on a pseudo-standard as answered above.

KD09714

 Profile Answers by KD09714 Questions by KD09714

• Jun 28th, 2008

Function must have return statement by which it returns one value.Though we can use out parameter in function(function not getting called from select statement or DML), it is not good programming prac...

Answer Question  Select Best Answer

Write a PL/SQL fuction named say TRUN, which does the same work as the oracle predefined function "TRUNCATE".

ajaz_11i

 Profile Answers by ajaz_11i Questions by ajaz_11i

• Jul 9th, 2008

It will truncate/delete all the rows in the table emp very fast as compared of delete command, but the table must have no Foriegn keyMr. Ajaz Ahmad Kumar

sgbang

 Profile Answers by sgbang Questions by sgbang

• Jul 7th, 2008

Hi,Please check out this function:create or replace function trun(p_table_name varchar2) return number asbeginexecute immediate ' truncate table '|| p_table_name;return 0 ;end; /How to ex...

Answer Question  Select Best Answer

Mean I heard from Oracle 8i onwards the order of where clause condition does not matter which oracle creates the explain plan...and execute..Is it true?

manoj_behera82

 Profile Answers by manoj_behera82 Questions by manoj_behera82

• Aug 7th, 2008

Yes the order is important. But it is important for the performance of the query?and not for the final result. The output of the query will be the same irrespective of the order in which the join con...

gtomar

 Profile Answers by gtomar Questions by gtomar

• Aug 7th, 2008

Hi, Where Clause conditions execution are independent of order in explain plan but it reads top to bottom.Thanks,Gunjan

Answer Question  Select Best Answer

raghuprasad

 Profile Answers by raghuprasad Questions by raghuprasad

• Aug 25th, 2008

Inputting the date in PL/SQL is same as in SQL. Simply pass the date as a string in the format DD-MON-YYYY.Eg: SELECT * FROM TABLE WHERE HIRE_DATE='dd-mon-yyyy'.OrYou can also use Date functions TO_DATE('25-aug-2008','......date formate......');Hope this is the answer, you are looking for.Raghu.

rajivgupta780184

 Profile Answers by rajivgupta780184 Questions by rajivgupta780184

• Aug 22nd, 2008

Create or replace function get_period (pDate varchar2) return varchar2is pday varchar(20);begin SELECT TO_CHAR(to_date('&DATE','MM/DD/YYYY'),'DAY') into pday FROM DUAL;return pday;end get_period;to call the function select get_period(08/03/2008) from dual;

Answer Question  Select Best Answer

Outline the List of PRAGMA'S used in Oracle.

amrapali

• Apr 21st, 2012

PRAGMA The instruction is a statement that provides some instructions to the compiler. Pragmas are defined in the declarative section in PL/SQL. The following pragmas are available: AUTONOMOUS_TRA...

bobbyiiit

 Profile Answers by bobbyiiit Questions by bobbyiiit

• Apr 9th, 2010

PRAGMAPragma is a keyword in Oracle PL/SQL that is used to provide an instruction to the compile.Types Of Pragmas :-1 - AUTONOMOUS_TRANSACTION2 - EXCEPTION_INIT3 - RESTRICT_REFERENCES4 - SERIALLY_REUSABLE

Answer Question  Select Best Answer

What are advantages of Autonomous Transcation?

raghunadhn

 Profile Answers by raghunadhn Questions by raghunadhn

• Mar 3rd, 2010

Autonomous transactions are independent of the main transactions.You can commit the changes in the other tables using these transactions, even though the main transaction is not a success. Depending on one transaction, you can commit/rollback the other transactions.

namita04

 Profile Answers by namita04 Questions by namita04

• Dec 23rd, 2009

A autonomous transaction starts with the first SQL statement of the PL/SQL block and ends with a commit.

Answer Question  Select Best Answer

How can we handle multiple rows without using loops in PL SQL?

sen_sam86

 Profile Answers by sen_sam86 Questions by sen_sam86

• Aug 18th, 2009

This can be done by a function called TABLE FUNCTION by means of this you can return more number of rows and columns this is more effective than using ref cursorregards sen-prakash

rajivgupta780184

 Profile Answers by rajivgupta780184 Questions by rajivgupta780184

• Oct 31st, 2008

If we want to return multiple row from a function then we can use ref cursor function .Example is given below: create or replace function f1 return sys_refcursor as r1 sys_refcursor;begin open r1 fo...

Answer Question  Select Best Answer

How will you stop an infinite loop without closing the program?

muraligrandhi

 Profile Answers by muraligrandhi Questions by muraligrandhi

• Jun 7th, 2010

For terminating the INFINITE LOOP we can use EXIT statement.Example: LOOP     monthly_sales:=daily_sales*31;     EXIT when monthly_sales > 3000; END LOOP;  

joe0082

 Profile Answers by joe0082 Questions by joe0082

• Apr 13th, 2009

Use the?EXIT statementThis will stop the looping and proceed to the next executable statement.or EXIT WHEN condition

Answer Question  Select Best Answer

What is recreating and rebuiding of indexes?

sktest

 Profile Answers by sktest Questions by sktest

• Jul 11th, 2010

ALTER INDEX REBUILD

raghunadhn

 Profile Answers by raghunadhn Questions by raghunadhn

• Mar 3rd, 2010

Rebuilding IndexSyntax: Alter index index_name rebuild.If you rebuild the index, the existing index will be dropped and created immediately, and any overheads existing on the index wil be removed. The...

Answer Question  Select Best Answer

How to optimize SQL Statements?

Sudhir Kumar

• Jan 20th, 2016

Try to make use of mqts or temporary tables if possible ..try to transfer the conditions from non key columns to key columns

ace1234567

 Profile Answers by ace1234567 Questions by ace1234567

• Mar 4th, 2015

update the statistics of all the tables used in the query. Check the explain plan. Check whether query is using the existing indexes. If required add/modify new indexes. Check for the sequence of cond...

Answer Question  Select Best Answer