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


ADVERTISEMENT

Precompilers, OCI & OCCI :: Pro* C Code To Fetch Cursor And Loop

Nov 18, 2009

i HAVE THE FOLLOWING CODE WRITTEN IN A *.pc FILE. I am trying to loop to fetch data from cursor. But the code exist after it fetches the first record. Let me know what is it the right way to fetch data from cursor?

EXEC SQL BEGIN DECLARE SECTION;

char str[64];

EXEC SQL END DECLARE SECTION;

/*cursor declarations*/
EXEC SQL DECLARE Get_SQLText_Cursor CURSOR FOR

[Code]....

View 6 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

Application Express :: How To Pass A Cursor Result To Javascript Code

Jun 15, 2012

what document I should look for to pass a cursor generated by PL/SQL to java script code in APEX?

View 15 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

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 View Related

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

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 :: Ref Cursor As OUT Parameter

Aug 8, 2012

CREATE OR REPLACE PACKAGE test_package IS
TYPE refcur IS REF CURSOR;
END test_package;
CREATE OR REPLACE PROCEDURE get_info(o_cursor OUT test_package.refcur)
AS
BEGIN
OPEN o_cursor FOR
SELECT * FROM emp;
END get_info;

What is the advantage of using refcursor variable as OUT parameter in procedure. Instead of that why cannot we use variables or TYPE variables. use ref cursor as OUT parameter in procedure.

View 1 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 :: Procedure Without Parameter Is Ref Cursor

Jan 12, 2012

I have the procedure with out parameter is ref cursor.

l_sql VARCHAR2(32767);
BEGIN
l_sql := 'select query with appending procedure IN aparameters';
OPEN rc_rpt FOR l_sql;

Here procedure IN parameter is a string with comma separated value which is appended in the dynamic query IN clause.So some time the size exceeded more then 32767 and getting error.If i am using normal parametrized cursor this issue is not there,but i want to return only ref cursor for some java purpose.My oracle version is 10g.

View 4 Replies View Related

SQL & PL/SQL :: Use Cursor As Parameter For Function?

May 4, 2011

I was wondering if it's possible to use the cursor as a parameter for a function. Something like this is what I'm trying to do:

set serverouput on
declare
cursor csv_file

[Code]....

View 34 Replies View Related

SQL & PL/SQL :: Passing Parameter To Cursor?

Apr 13, 2004

I have a table Student with two columns Rno and Name and i write following PL-Sql, it is working fine, my question is that how can i pass the parameter to cursor in the following query, e.g. if i pass the roll no. 501 then it should display only the particular Name.

declare
��� cursor st_name is
����������� select rno,name from student;
����������� studentnm st_name%ROWTYPE;
begin
�� open st_name;

[code]....

View 6 Replies View Related

SQL & PL/SQL :: Cursor With Parameter Inside Function

Mar 7, 2010

I have code inside function
.....
cursor cur1 is
select *
from sarchkler
where sarchkler_appl_no = in_appl_no
begin
select max(saradap_appl_no) into in_appl_no from saradap;
for rec1 in cur1 loop
......
my question I get variable for cursor after cursor declaration

View 7 Replies View Related

SQL & PL/SQL :: How To Pass Parameter To Cursor And Print Details

Aug 15, 2010

Create a PL/SQL block that declares a cursor called DATE_CUR. Pass a parameter of DATE data type to the cursor and print the details of all the employees who have joined after that date.

DEFINE P_HIREDATE = 08-MAR-00

Test the PL/SQL block for the following hire dates: 08-MAR-00, 25-JUN-97, 28-SEP-98, 07-FEB-99.

I don't know how to pass parameters. So far I have this:

SET serveroutput ON;
DECLARE
p_HIREDATE DATE := 08-MAR-00;
CURSOR c_DATE_CUR(P_HIREDATE DATE) IS
BEGIN
OPEN c_DATE_CUR();

View 6 Replies View Related

SQL & PL/SQL :: Passing Ref Cursor To PROEDURE As INOUT Parameter

Dec 8, 2010

I need to know how to pass the ref cursor as INOUT parameter to a procedure. I have the following procedure and I need to execute it.

PROCEDURE get_site_setup_detail (
p_study_id IN SITES.study_id%TYPE,
p_proj_act_date IN VARCHAR2,

[code]....

View 2 Replies View Related

SQL & PL/SQL :: Ref Cursor As In Parameter / How To Fetch Column Values

Sep 21, 2010

i have a proc that is taking p_serial_number refsursor as in parameter. the structure of p_serial_number is required to be

mfg_prod_cdchar (3 byte)
mfg_prod_seq_no char (6 byte)

How do I need to define this ref cursor ? and when I use it in my Procedure how do I fetch the column values ?

View 6 Replies View Related

Execute / Display Results Of Procedure With Ref Cursor OUT Parameter

Apr 12, 2012

How do i execute and display results of a procedure declared with ref cursor OUT parameter.

I am using SQL Developer and Oracle 10.2.

View 4 Replies View Related

SQL & PL/SQL :: Dynamic Cursor / Loop Will Accept Table Name From Parameter

Oct 11, 2013

I have a plsql block construct where i want to use for loop dynamically , the query which for cursor for for loop will accept the table name from parameter and join them to return the result. the resultant data will iterate in loop and do the execution.

DECLARE
--initialize variables here
v_date varchar2(10);
v_rebuild_index varchar2(250);
v_sql VARCHAR2(250);
p_table_name varchar2(250) := 'DS_ABSENCE';
p_source_table varchar2(30) := 'STG_ABSENCE';
p_source_owner varchar2(30) := 'STG_SAP';
v_for_sql varchar2(1000);
[code]....

View 7 Replies View Related

Reports & Discoverer :: Bar Code Reader Will Readout Code

Feb 12, 2009

I have no knowledge about Barcode. The problem is an issue of Loyalty Cards of a Hotel and Restaurant to various customers and then these cards will be presented by the customers time to time in the Hotel as well as Restaurant. The Owner of the Hotel and Restaurant wants to generate separate barcode for each card and when this card will be presented then the bar code reader will readout the code and the system will calculate the amount of discount/rebate. Because if the data entry operator enter the code of the card through key board the it will be a chance of leakage or misuse of that card.

View 8 Replies View Related

SQL & PL/SQL :: Compare SVN Source Code With Production Code In Database

May 30, 2012

I have to compare my SVN source code (packages, views etc) with the production code in the database like views etc (actually we are not sure that what we have in the svn is the final version of production code, we have objects created in the production database, but we don't have latest scripts for that. we have to deploy the svn code in the UNIX box).

So here the comparison is between the OS files and the database objects.

I thought I would get scripts of all the packages, views etc from the production database by using DBMS_METADATA or some utility and save the code in OS files then compare one svn file with OS file manually by using some comparison tools e.g toad provide one comparison tool.

View 5 Replies View Related

SQL & PL/SQL :: Type In Code To Make Few Tables Then Run Code

Feb 13, 2012

I downloaded oracle sql developer, i type my code into a worksheet but if i use the run statement option, it asks me to make a connection. I dont want to make a connection, just test the data locally.However, even if I do try and make a connection, i get ora-12560 error (local connection).

I just want to type up some data to make some table and test to retrieve or manipulate the data. I'll use any program, command line or gui.

View 7 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

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

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 :: How To Debug A Code

Nov 2, 2011

how to debug PL/SQL code.

I mean if
procedure1 --calls---> procedure2
procedure2 --calls---> procedure3
procedure3 --calls---> procedure4

If procedure1 is my main procedure how do we know in procedure there is an unhandled exception or bug.

View 2 Replies View Related

SQL & PL/SQL :: How To Unwrap A Code

May 9, 2011

how to unwrap a PL/SQL Code???

View 2 Replies View Related







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