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;
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
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?
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
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 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;
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();
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.
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.
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.
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.
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.
-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;
