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.
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.
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]....
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]....
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
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();
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]...
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]....
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 ?
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.
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]....
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.
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]....
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?
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>
Can i user exist when cursor will using For Cursor .
View 15 Replies View Relatedi have a program like this
DECLARE
V_VARIABLE1 VARCHAR2(10);
CURSOR_ABC IS select...............;
BEGIN
OPEN CURSOR_ABC;
LOOP
FETCH CURSOR_ABC INTO V_VARIABLE1;
EXIT WHEN CURSOR_ABC%NOTFOUND;
WHAT IF I WANT TO INSERT ANOTHER SAME KINDA CURSOR LOOP HERE LIKE*
CURSOR_XYZ IS select...............;
BEGIN
OPEN CURSOR_XYZ;
LOOP
FETCH CURSOR_XYZ INTO V_VARIABLE2;
EXIT WHEN CURSOR_XYZ%NOTFOUND;
END LOOP;
CLOSE CURSOR_XYZ;
END LOOP;
CLOSE CURSOR_ABC;
I am gettting the below mentioned error in my alert log file
The value (167) of MAXTRANS parameter ignored.
I have search this error on google, but i didnt find anything every post is telling that this is the impdp's or expdp's error.
But i havent use impdp and expdp here.
let's say a parameter changed in the database ex: alter system set retention_target= 1500; and i want to know what was the old value before it has been changed ,
View 1 Replies View RelatedFrom ETL tool i will be passing two parameters to a procedure. I want to know whether this parameter can be used in a select statement of a correlated query.
Query to be in the procedure :
begin
update table1 set table1.col1 = (select parameter2 from
table2 where table2.x = table1.x and table2.x = parameter1) ;
commit;
end;
parameter2 will be having the columnname to be passed. And everytime different columnname will be passed to this procedure.
I am writing a procedure in which I have a input string parameter in the following way..('NYC,ATL,OKC,KAC,LA'). I need to use that string to search values in the table.
Example
create or replace procedure search_city(p_string varchar2)
/*
paramater will be like ('NYC,ATL,OKC,KAC,LA')
*/
is
v_city varchar2(40)
begin
for i in (select city_name from cities
where city_code in (p_string )
) loop
v_city := i.city_name;
end loop;
end ;
if function having retrun statement, why we need to use out or inout parameters.
View 16 Replies View Related I need to test a parameter in a script that must adhere to what is currently in the database.For example, when a user enters the value when prompted from this script, ("Enter the schema copy desired: " ) the value must be of the current values in the db:, it would be something like this:
Values currently in DB: copyofschema201110 (the 2011 is the year, and 10 would be the month of oct).
copyofschema201109
copyofschema201108
So, if the user enters the value 'abc' (an incorrect value), then I would like the script to stop the user, and kick back a message, say from dbms_output.put_line, and force the user not to go forward, but re-enter a correct value.
I thought that you are not supposed to be able to assign the value of an out parameter to a variable (Feuerstein, 5th ed) and yet I was just
able to do this without any compilation error messages. Is Feuerstein wrong or am I missing something? Has the functionality changed?
PROCEDURE Parse_HC
(p_timestamp_string IN VARCHAR2,
p_timestamp_date OUT DATE)
AS
v_date DATE;
[Code]....
Here is my banner:
BANNER
----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 – Production
"CORE 11.1.0.7.0 Production"
TNS for Linux: Version 11.1.0.7.0 – Production
NLSRTL Version 11.1.0.7.0 - Production
I have written this code, its throwing error.
begin
declare tmdate DATE;
select TO_DATE(TO_CHAR(MAX(DATERANGE),'DD-MON-YYYY')) into tmdate from table_check_date_range;
dbms_output.put_line(tmdate);
end;
We receive hand punches (clock data) every day. Normally a person badges in(hand punch) which creates a row in the clock_tran_processed table. The information from that hand punch is the employee id (emp_id) the date hand punch occurred and a work_summary id (wrks_id). At the end of the day, the employee badges out (hand punch out) and another entry in the clock_tran_processed table is created. The new row will have the emp_id (employee name), date the hand punch occurred and the same work summary id from the morning.
Normally hand punches should occur in pairs. One in, one out... or one in, out for lunch, in for lunch, out for day. I am seeing intervals of three and five. Meaning the employee clocked in twice and out once, or in once and out twice. This shouldn't happen.
I am writing a report that will show number of clocks per for all employees that have three(3) or five(5) clock entries.I wrote a ref cursor that gives all the employees that have a count of 3 or 5 and the employee id (emp_id). I need to pass that employee id to another query that will then get me the dates of the clocks.Here is the ref cursor thus far (I am printing those with a count of 3 and 5).
CODEDECLARE
TYPE ClockTran_Refcur IS REF CURSOR;
ClockTran_cur ClockTran_Refcur;
NumClock_num number :=0;
NumClock_name clock_tran_processed.emp_id%TYPE;
[code]...
How do I pass that variable to the get the clktranpro_time's? I am using "Easy Oracle PL/SQL Programming" but I am not seeing this type of example(pgs 140-148).
In our environment we have db link to fetch data from other database. Whenever we try to fetch data using the dblink we receive the wait event "cursor: pin S wait on X" and we do not get any result. The db link works fine. what could be the issue.
DB Version : 11.1.0.7 OS: AIX
i am using the Oracle 10 g 10.2 with the windows 2003 3 sessions from a single User shows the currenncy on the top activity graph of the enterprice manager they are not getting so much resources of system....when i go in the detail of the session i get
cursor: pin S wait on X
i am unable to kill these sessions how could i get rid of these sessions
My task has more lines of code,so i tried to present here only few lines of code where i am getting the error.The following cursor needs to select data from two tables with some conditions that are included in cursor and place those in other table.I used BULK collect.
CURSOR c_arch_trk (p_run_date DATE,
p_nbr_days_arch1 NUMBER,
p_nbr_days_arch2 NUMBER )
IS
SELECT a.SIS_PGM_START_DATE,a.SIS_PGM_END_DATE,a.PGM_MSTR_NBR,a.PGM_TRK_NBR,a.CNTL_LOCN,a.CMPNY_VNDR_NBR,a.AGRMNT_MSTR_NBR,
a.SLS_CONT_NBR,b.PGM_NAME,b.PGM_BASIS,b.AGRMNT_CNTL_LOCN
[code]....
What is the advantage of collections over CURSOR? What is the use for collections and when collections is used?
View 1 Replies View Related