SQL & PL/SQL :: Difference Between Implicit And Explicit Cursor?

Feb 21, 2013

What is the difference between implicit cursor and explicit cursor in PL/SqL?

And what is ref cursor ?

View 2 Replies


ADVERTISEMENT

PL/SQL :: Difference Between Explicit And Implicit Cursor?

Nov 5, 2013

difference between between these two constructs. Finally  when i read the asktom.oracle.com , I was totally confused. The reason is thatTom says...we can retrieve more than one row in implicit cursor. If that would be case, what's the difference between these two cursors?? when to use?? My understanding was implicit cursors" ---> single-row queryExplicit cursors ---> multi-row query Experts

View 10 Replies View Related

SQL & PL/SQL :: Differences Between Explicit And Implicit Cursor?

Nov 16, 2011

what are the main points that these examples are considered cursors? and why are they called explicit and implicit cursor.

explicit
for x in (select * from emp) loop
dbms_output.put_line('emp no: '||x.empno);
end loop;

implicit
select empno
into vEmpno
from empno
where empname = 'SCOTT';

for all we know that these are not clearly defined on the declaration area as cursor.

View 16 Replies View Related

SQL & PL/SQL :: Open Ref Cursor From Explicit Cursor

Nov 23, 2011

I want to return ref cursor based on explicit cursors

create table jumbo(id number, name varchar2(20));
insert into jumbo values(1,'jumbo');
create table mumbo(id number, name varchar2(20));
insert into mumbo values(1,'mumbo');
commit;

[Code].....

The above procedure has compilation errors when I am trying to open ref cursor

LINE/COL ERROR
-------- --------------------------------------------------------
20/24 PL/SQL: SQL Statement ignored
20/38 PL/SQL: ORA-00942: table or view does not exist
32/24 PL/SQL: SQL Statement ignored
32/38 PL/SQL: ORA-00942: table or view does not exist
SQL>

View 5 Replies View Related

SQL & PL/SQL :: How To Fetch Two Times Same Explicit Cursor

Nov 25, 2012

I need to open an explicit cursor for making a total: after I have to use the same information of that explicit cursor for dividing a column of the cursor by that total. It is not enough to open close, reopen and reclose because I just obtain one register at the same time and it is the same register two times consecutively.

I don't want to use auxiliary structures cause there are 18000 columns for 10200 rows.

FOR i IN 300..300 --18000
LOOP
y:=ymax-ysize*(i+0.5);

[Code]......

View 6 Replies View Related

PL/SQL :: Creating A Procedure Using Explicit Cursor?

Nov 19, 2013

I am writing this procedure with a explicit cursors defined in it.  However when i compile the procedure i get this error: Error(39,1): PL/SQL: SQL Statement ignored Error(39,1):PLS-00394: wrong number of values in the INTO list of a FETCH statement .

create or replace PROCEDURE PRO_ICMISd_customer_no BB_PM.customer_no%type;d_pr_code_bbl BB_PM.pr_code_bbl%type;d_pr_code_pmm BB_PM.pr_code_pmm%type;d_subdept_desc PM.subdept_desc%type;d_class_desc PM.class_desc%type;d_cat_desc PM.cat_desc%type;d_subcat_desc PM.subcat_desc%type;d_brand_name PM.brand_name%type;d_product_desc PM.product_desc%type;d_unit_price_bbl PM.unit_price%type; e_customer_no BB_PM.customer_no%type;e_pr_code_bbl BB_PM.pr_code_bbl%type;e_pr_code_pmm BB_PM.pr_code_pmm%type;e_subdept_desc PM.subdept_desc%type;e_class_desc PM.class_desc%type;e_cat_desc PM.cat_desc%type;e_subcat_desc PM.subcat_desc%type;e_brand_name PM.brand_name%type;e_product_desc

[code]....

View 17 Replies View Related

SQL & PL/SQL :: Use Implicit Cursor In Program

Aug 18, 2010

I've tried to use implicit cursor in my pl/sql program and i get an error as following:

DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 17

Then,I tried to include an exception for it and the error is 'solved'.

DECLARE
d_call_id course.call_id%TYPE;
d_course_name course.course_name%TYPE;
d_term_desc term.term_desc%TYPE ;
d_f_first faculty.f_first%TYPE;
d_f_last faculty.f_last%TYPE;
d_day course_section.c_sec_day%TYPE;
d_time course_section.c_sec_time%TYPE;
d_bldg_code location.bldg_code%TYPE;
d_room location.room%TYPE;
[code]....

By actually the program are suppose to return the records instead or returning the error. I've included the table that i'm trying to retrieve the records from as well.

View 4 Replies View Related

SQL & PL/SQL :: Choose Explicit Cursor To Loop Through At Runtime?

Nov 1, 2011

Oracle Version: 11.2.0.2.0. I have two explicit cursors and I would like to choose at run time which one to run. Here is a simplified code snippet of what I am doing today:

DECLARE
CURSOR Cursor_A IS
SELECT * FROM EMP_A;
CURSOR Cursor_B IS
SELECT * FROM EMP_B;
RUNA CHAR(1) := 'Y';

[code]....

I want to avoid maintaining the same long list of transformations. I also want to avoid, if possible, an explicit FETCH INTO, because there are hundreds of fields in both tables. I'm looking for something like this (and I know this doesnt work):

DECLARE
CURSOR Cursor_A IS
SELECT * FROM EMP_A;
CURSOR Cursor_B IS
SELECT * FROM EMP_B;
RUNA CHAR(1) := 'Y';
CursorToRun IS REF CURSOR;

[code]....

View 4 Replies View Related

SQL & PL/SQL :: Implicit Cursor In Single Statement?

Feb 7, 2011

1) SQL Statements are not using IMPLICIT CURSORS.

2) Only the SQL statements of the PLSQL program create implicit cursors.

View 1 Replies View Related

SQL & PL/SQL :: Does Commit Close Implicit Cursor

Feb 3, 2011

We all know that commit will do the following.

1. Save the Txn Permanently to the database.

2. Release the table locks and

3. Erase the save points.

TABLE NAME:
==========
create table TEST_PREC (NO NUMBER(4,2));
DECLARE
BEGIN
INSERT INTO TEST_PREC VALUES (12.34);
DBMS_OUTPUT.PUT_LINE('the no of records before commit '||SQL%ROWCOUNT);
commit; /* What's happening inside commit */

[code]....

why the SQL%ROWCOUNT is set to zero after commit.

Does the commit, close the implicit cursor?

View 3 Replies View Related

PL/SQL :: Difference Between Ref Cursor And Sys_refcursor?

Dec 6, 2012

i found this difference between ref cursor and sys_refcursor.

If you specify return_type, then the REF CURSOR type and cursor variables of that type are strong; if not, they are weak. SYS_REFCURSOR and cursor variables of that type are weak.

my situation is i have to write many stored procedure for reports and also for entery pages. few enter pages use typed datasets and few does not.

which one is better in terms of performance and maintainability in above three conditions?

View 2 Replies View Related

SQL & PL/SQL :: Difference Between Cursor And Normal For / While Loop?

Mar 7, 2011

what is the difference between using a cursor and using a normal for/while loop to retrieve and process the result set.

View 3 Replies View Related

PL/SQL :: Difference Between Strong Ref Cursor And Weak?

Jul 13, 2012

differnce between strong ref cursor and weak ref cursor

View 4 Replies View Related

SQL & PL/SQL :: Difference Between With Parameter And Without Cursor In Pasted Code?

Apr 7, 2012

create table eml(num numner(10),email varchar2(100));

begin
for i in 1..20 loop
insert into eml values(i,'email.'||i||'@mmm.com');
end loop;
end;

I just want to know what is the difference between below two pl/sql block, in one block i am passing the p_email_id as paramter to cursor.Both block are working fine and giving the same result.first thing , i want to know how the p_email_id is passing to cursor get_emil_info without paramter.and which is good to use, i mean without paramter or with paramter.

--without paramterised cursor

declare
v_cnt number;
p_email_id number;
TYPE email_tab is TABLE of NUMBER index by binary_integer ;
eml_id email_tab ;
CURSOR get_eml_id is select num from eml;

[code]...

--with paramterised cursor

declare
p_email_id number;
TYPE email_tab is TABLE of NUMBER index by binary_integer ;
eml_id email_tab ;
CURSOR get_eml_id is select num from eml;

[[code]...

View 3 Replies View Related

PL/SQL :: Difference Using Cursor In Specifications And Body In Packages

Jun 24, 2013

what is the difference using cursor in specifications & body in packages?

View 1 Replies View Related

SQL & PL/SQL :: Explicit Name For Index Of IOT

Feb 18, 2011

Need some Clarification on the below query:

Can we specify explicit name for the index of the IOT.

View 2 Replies View Related

Implicit Commit While Purging

Oct 24, 2011

I have written a purge package that would delete records older than 10 years. Since the data is huge, the purging was taking 14 hours plus. To improve performance, I disabled constraints , deleted records and then reanabled them. This was quite quick but the only problem is rollback. Say for some reason if enabling constraints fails there is no way to rollback as enabling and disabling constraints does an implicit rollback.

View 1 Replies View Related

Auditing And Implicit Commits

May 11, 2009

I'm working on a Java-based web application and we have unit tests that we use to test all our all code that interacts with the database or code that interacts with our DB code. The Spring framework allows us to perform some DML within a transaction before each test and then rollback the changes. For the most part, this works, however when I run the full suite of unit tests, it will randomly commit data to the database causing the rest of the tests to fail.

will Oracle's auditing let me see where this odd-ball commit is occurring? Is there another way for me to see when data is being committed?

This does not appear to be happening on any of the systems we've deployed, however this is a bit unsettling and would like to know why this is occurring so that we can prevent it from happening in production.

View 1 Replies View Related

Implicit Data Conversion / Join Conditions Between Number And Vrahchar2

Jun 17, 2013

In my Project, there are many queries have join conditions between Number and Vrahchar2. Will it give any wrong results or Invalid Number exception even the varchar2 column always contains the number data?

Example:
Select * from Table_t1 t1, table_t2 t2
where t1.num_col = t2.var_col
-- Here var_col always hold only numaric data.

View 1 Replies View Related

Precompilers, OCI & OCCI :: Error Implicit Conversion Of String Literal To (char) Is Deprecated?

Jan 30, 2011

i write a select statement in proc that contains 44 columns.

when i precompile it. it is showing the error: implicit conversion of string literal to "char *" is deprecated.when i compile the same select with 40 columns it is not showing any error.

but for more than 40 columns (41-44) it is showing the above error.

View 1 Replies View Related

Precompilers, OCI & OCCI :: Compile PC File With Created Make File - No Explicit Type Given

Jan 22, 2008

I am trying to compile a .pc file with the make file which I created. But when I try to give make command I am getting following error

>make testfile
cc -o testfile testfile.c
"testfile.c", line 117: warning: no explicit type given
"testfile.c", line 119: warning: no explicit type given
"testfile.c", line 121: warning: no explicit type given
"testfile.c", line 122: warning: no explicit type given
"testfile.c", line 123: warning: no explicit type given
Undefined first referenced
symbol in file
sqlcxt testfile.o
ld: fatal: Symbol referencing errors. No output written to testfile

*** Error code 1
make: Fatal error: Command failed for target `testfile'

View 16 Replies View Related

RMAN :: RMAN-03014 / Implicit Resync Of Recovery Catalog Failed

May 29, 2013

My oracle backup via Netbackup failing with error 6 (Netbackup) and i have checked the logs please find the RMAN log file as well.

Script /opt/rman_script/st72_oracle_full.sh
==== started on Tue May 28 15:27:46 SGT 2013 ====
RMAN: /OraBase/V10203/bin/rman
ORACLE_SID: ST72
ORACLE_USER: oracle
ORACLE_HOME: /OraBase/V10203
NB_ORA_FULL: 1
NB_ORA_INCR: 0

[code]....

View 2 Replies View Related

PL/SQL :: Calculate Difference By Which Day Count Of Difference Is Going?

Oct 17, 2012

Detail table will look like below:

Product_id issue_date action_date Force_date
1 10/10/2012 10/10/2012 10/10/2012
2 10/10/2012 10/10/2012 10/10/2012
3 10/10/2012 13/10/2012 15/10/2012
[code]....

Need the data like

Issue_date count_action_date count_Force_date (diff(action_date,force_date) 1 2 3 4 5 6(days since over)

10/10/2012 3 4 1 4 2 1 0 0

How to get the data like this? automatically how to get 123.... and how to calculate the difference by which day the count of difference is going?

View 3 Replies View Related

Precompilers, OCI & OCCI :: Pro*C - Cursor Leak With Cursor Array

Sep 7, 2007

I'm dealing with an ORA-1000 error in a Pro*C application where all the cursors are correctly closed (or so it seems to me).

Here is the code for a simple program which reproduces the problem:

Each cursor is opened in a PL/SQL package:

CREATE OR REPLACE PACKAGE emp_demo_pkg AS
TYPE emp_cur_type IS REF CURSOR;
PROCEDURE open_cur(curs IN OUT emp_cur_type, dept_num IN NUMBER);
END emp_demo_pkg;

[Code]....

While testing the initialization parameter open_cursors is set to 50.

It's my understanding that Oracle doesn't close the cursors until it needs the space for another cursor, which in my test case seems to happen when I enter a value of 50 or bigger for "number of loops". To see how oracle is reusing the cursors, while the test program is running I run SQL*Plus and query v$sesstat for the session that's running the test with the following sentence:

select name, value
from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and sid = 7
and name like '%cursor%';

Even before I enter a value for number of loops I can see that the session opened 4 cursors and closed 2 of them:

NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 4
opened cursors current 2

Entering a value of 5 for number of loops yields

NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 11 <----- 7+
opened cursors current 8 <----- 6+

With a value of 30

NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 36 <----- 25+ (apparently, Oracle reused at least 5 cursors)
opened cursors current 33 <----- 25+

With a value of 47

NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 53 <----- 17+
opened cursors current 50 <----- 17+

Now I reached the upper limit set by the initialization parameter open_cursors.

Entering a value of 48, I get the ORA-1000 error.

ORA-01000: maximum open cursors exceeded
ORA-06512: at "SCOTT.EMP_DEMO

Since I open and close the cursor in the same loop iteration, I expect to find in every iterarion 1 explicit cursor and a number of implicit cursors (the PL/SQL call along with the so-called recursive cursors), but I don't expect the sum of all of them to be greater than 50. If my understanding is correct Oracle should be reusing the 50 cursors previously marked as "closeable", not raising the ORA-1000 error.

View 1 Replies View Related

SQL & PL/SQL :: Cursor With Bind Variable And Cursor Record

Feb 25, 2011

Is it possible to:

-define a cursor with bind variables
-get a cursor record from these cursor
-and pass the bind variable in the OPEN clause

Did'nt succeed as shown in the example.

SET SERVEROUTPUT ON SIZE 900000;
DECLARE
--works fine
CURSOR c1 IS SELECT * FROM USER_TABLES WHERE rownum<3;
--doesn't work
--CURSOR c1 IS SELECT * FROM USER_TABLES WHERE rownum<:1;
crec c1%rowtype;
BEGIN
--works fine
OPEN c1;
--isn't possible ?
--OPEN c1 USING 3;

[Code]....

View 3 Replies View Related

SQL & PL/SQL :: Converting Numeric Cursor To Ref Cursor?

Sep 11, 2011

just looking around to use the new feature available in oracle 11g to convert the dbms_sql numeric cursor to reference cursor, how to do it?

parse and execute the sql string first with dbms_sql and then convert it to ref cursor?

View 1 Replies View Related

PL/SQL :: Can User Exist When Cursor Will Using For Cursor

Aug 7, 2013

Can i user exist when cursor will using For Cursor .

View 15 Replies View Related

SQL & PL/SQL :: Get Max Difference Where Each Value Can Look At Only Next?

May 28, 2013

How do I get the max difference where each value can look at only the next value(ordered by date asc) on any particular day
example first row on 20090902 is 501.25 (earliest date)

756.2 - 501.25
4735.83 - 501.25
35.83 - 501.25

[code]....

View 19 Replies View Related

How To Get Date Difference

Jul 20, 2007

How one can get date difference in days between two dates in Oracle reports ?

View 2 Replies View Related

SQL & PL/SQL :: Difference Between Dates

Jan 23, 2013

I am looking to subtract two columns and get the difference.

select to_char('06-NOV-2012 20:00','DD-MON-YYYY HH24:MI') - to_char(systimestamp,'DD-MON-YY HH24:MI') from dual;
select to_char('06-NOV-2012 20:00','DD-MON-YYYY HH24:MI') - to_char(systimestamp,'DD-MON-YY HH24:MI') from dual
*
ERROR at line 1:

ORA-01722: invalid number

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved