PL/SQL :: Getting Error - ORA-06511 - Cursor Already Open?
Jul 27, 2012
I have the following code for a Apex(Application Express) project I am developing.
declare
mail_id varchar2(100);
min_skill_cnt number;
skill_cde varchar2(30);
total_leave number;
toal_emp number;
cursor cur is
select S_EMP_EMAIL
from EMP_SKILLS_INFO where SKILLCODE='MGR' and S_EMP_EMAIL = lower(:APP_USER) ;
cursor minskill is
select skill_code,MINRQMT_AM
from skills_code_info
where skill_code in (select skillcode from emp_skills_info where S_EMP_EMAIL = lower(:APP_USER));
cursor leavecnt(v_skill IN VARCHAR2) is
select count(*) from emp_leave_info
where leave_date = :P24_LEAVE_DATE
and emp_email IN (select S_EMP_EMAIL from EMP_SKILLS_INFO where SKILLCODE = v_skill);
cursor empcnt(v_skills IN VARCHAR2) is
[code]...
Ideally this should send email to managers when a particular skill is running short when employee applies for leave. I am getting error that cursor is already open when I run this code. I am not sure which cursor or where it is picking open cursor command.
View 2 Replies
ADVERTISEMENT
Apr 13, 2012
I getting a error Ora-06511 when-new-item-instance trigger raised unhandled exception.
below is the coding for the trigger when-new-item-instance : where the cursor is open? how to close it?
if :blk_master.action_status = 'A' then
declare
doc_slno number;
[Code].....
View 3 Replies
View Related
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
Jan 13, 2011
I have a snippet of code
EXCEPTION
WHEN OTHERS THEN
H_Report_Error('sql',Sqlerrm);
end;
And I'm trying to figure out how to catch a error from a loop such as:
OPEN My_cursor FOR SQLCODEBLOCK
LOOP
FETCH a_variable INTO ResultCount;
EXIT
WHEN My_cursor % NOTFOUND ;
BEGIN
--Stuff done
End;
End Loop;
So right now I have a error in the SQLCODEBLOCK, but I can't catch that error. I've tried putting the exception in various places but it wont compile.
View 6 Replies
View Related
Oct 31, 2013
Oracle 11g For a procedure, if I use the hard-code procedure
sp_test( p_rc out sys_refcursor, p_1... p_2 ... ) is begin open p_rc for select * from TEST_TABLE... But now, I want to use the dynamically constructed sql, such as if condition1 then v_sql := ... elsif condition2 then v_sql := ... execute immedite v_sql
How can I return the the cursor p_rc in this case?
View 2 Replies
View Related
Mar 1, 2012
open cp_cursor for 'Select curtailprogramkey from curtailProgram where curtailprogramid like 'l_rec.curtailprogramid%'';
NOTE: l_rec.curtailprogramid is varible. what is wrong in the above statement?
View 2 Replies
View Related
Apr 18, 2013
I would like to mention that I won't be able to give the data,specific information(like query or table names) about the issue as this concerns the privacy of some multinational company(where I work).
We have windows server and we are currently using Oracle 10g.
Issue-
We have a ProC, which has a SQL statement where it is trying to OPEN a cursor. The cursor query is huge and fetches data from 3 tables. 3 tables have 5k, 600k and 1.7 millions records respectively but the 'where' condition in cursor query makes the total count to 3k lines only.
Now the problem is , we have a schedule reboot of the server every Saturday night and the next day(Sunday night) when the program runs it gets stuck in the query --> OPEN <cursor> . We wait for 5 hours but this OPEN cursor query never executes or completes, that means our program gets stuck here.Eventually we have to kill the program.We have tried running it after killing as well but it again gets stuck.
Now the interesting part. On Monday business happens as usual but in the night when we again run this program it runs within minutes. The records in the tables are more or less same. Open cursor does not get stuck at all. It runs every night properly but every Sunday night it gets stuck. note that the program does not run on Saturday nights. So on Sunday night it runs for the first time after server reboot.
So there is something with Open cursor statement(with so much data) after server reboot. What exactly Open cursor does in memory or database.
View 11 Replies
View Related
Jan 3, 2011
After opening a dynamic cursor, usually fetch hit record into some variables. However, if I do not want to "FETCH INTO " operate Just only skip this record.
DECLARE
TYPE weak_cur_type IS REF CURSOR;
weak_cur_1 weak_cur_type;
weak_cur_2 weak_cur_type;
vs_dsql VARCHAR2(2048);
vd_create_time DATE;
vn_count NUMBER(8);
vn_total_amount NUMBER(13);
[Code]...
View 7 Replies
View Related
Feb 26, 2013
Is it possible to bind collection while opening a ref cursor. Find below the code that I am trying. My goal is to open cursor once using collection variable. Can it be done using DBMS_SQL ?
DECLARE
TYPE typ_emp_rec_in IS RECORD
(
deptno NUMBER,
sal NUMBER
[code]......
View 4 Replies
View Related
May 10, 2013
create or replace package cognos_pk as /* Creates Package Header*/
TYPE project_type IS record( /* A record declaration is used to */
c1 NUMBER /* provide a definition of a record */
); /* that can be used by other variables*/
TYPE project_type1 IS REF CURSOR return project_type; /* Variable declaration */
procedure conosg_sp (result1 out project_type1); /* SP declaration */
end;
[code]....
View 2 Replies
View Related
May 28, 2013
In my environment found maximum open cursor exceeds error. So how can I found the open cursor list and how can I close that cursor without restarting. Any SQL commands to close the open cursor.
View 4 Replies
View Related
Jul 3, 2012
i try to open PL/SQL Developer and i get this error:
Could not locate OCI dll
OracleHomeKey:SOFTWARE\ORACLE\KEY_OraOracle11g_home1
OracleHomeDir: d:\app\...................\product\11.1.0\client_1
what can be the problem ?
View 2 Replies
View Related
Jul 13, 2013
I have Oracle 11g XE and Toad installed on Windows 7 64-bit machine. I am able to connect to database from Oracle's SQLPLUS. But when connecting from TOAD i am getting ora-06413 connection not open error.
View 1 Replies
View Related
Mar 8, 2011
I am trying to connect to a remote database (Oracle) using my Windows terminal and I am successfully able to connect. But when I try to open a SQL File is gives the following error:
SQL > @int1.sql
SP2-0310: unable to open file
I found out that this error occurs when the file name or the path is invalid. But since I am having a remote access I really don't know the location. What can be done to remove this error?
View 1 Replies
View Related
Oct 26, 2011
I have a Database that has a frontend in MS Access and a backend in Oracle. When I try to open the Oracle Table/s in the MS Access Frontend, it gives me this error message:
Reserved error (-7711); there is no message for this error.
There is some stuff on the internet about this error but none of it fixes my issue. It seems to be an MS Access error rather than an Oracle error. Only thing is this issue is only on Machines that are running on XP. I have no problem on mine which is Windows 7.
View 7 Replies
View Related
Mar 9, 2012
I am recieving errors when trying to load the control file. The errors are as follows:
SQL*Loader-500 Unable to open file (homework.ctl)
SQL*Loader-553 file not found
SQL*Loader-559 SYstem error: The system cannot find the file specified.
My control file is located directly in the C drive (C:homework.ctl). The control file contains the following
LOAD DATA
INFILE 'c:country.dat'
APPEND INTO TABLE homework
fields terminated by ',' optionally encloded by '"'
(country, month, day)
WHEN (month='April')
The command I am entering is:
sqlldr system/password control=homework.ctl
I've tried c:homework.ctl, 'c:homework.ctl', and placing the file in the BIN folder of Oracle.
View 7 Replies
View Related
Jan 12, 2011
I searched the knowledge base here and in other forums and haven't found the answer. There was a similar condition presented earlier, but it turned out the resolution was to run the >shutdown/>startup on the server rather than from a client. Not applicable in my case!
Shutdown and startup called used to work with 9i, now startup fails in 10g. ORACLE_HOME and ORACLE_SID are set and report correct values. What is different in 10g that the script (shown below) now throws a TNS listener error after shutdown?
I successfully upgraded from 9i to 10g (10.2.0.5.0) on Windows Server 2003, 32-bit last weekend. Now our backup_script.bat and oracle_stop_start.sql throw "ORA-12514: TNS:listener does not currently know of service requested in the connect descriptor"
This batch file and sql script predate me and worked with 9i for years. Now the startup command in the oracle_stop_start throws this error after executing shutdown. The backup batch file calls:
sqlplus "system/manager@wind as sysdba" @D:ptcwindchillackupsoracle_stop_start
The entire oracle_stop_start.sql is as follows:
shutdown immediate;
startup open;
quit
What is different in 10g that this now doesn't work? Shutdown works, but it will not startup due to the listener error. I have made the backups work by commenting out the call to sqlplus and I'm getting good backup dumps.
In my virtual image of the server I have found that I can duplicate the error after >shutdown. I have found that if I exit sql*plus and go back in as follows I can get the database instance to restart from the command line:
c:>sqlplus /nolog
sql>connect sys/* as sysdba
sql>startup open wind;
That does start, mount and open the database instance.
Is there a better way to be making sure that everything is stopped before executing the >exp command to create a backup dump? I will look into and test datapump export/import and RMAN for creating the backups, after I get this working again.
why this worked in sql*plus in 9i but not in 10g? Can I make TNS Listener smarter? Change or add variables to the startup command?
View 2 Replies
View Related
Apr 1, 2010
I have 3 code files written in ProC. I have precompiled, compiled, tested them in my local environment (Windows) & they work fine. But when I try in real environment, AIX 5.3, I can precompile them. But when I try to compile & link them with the libclntsh library I get errors. I am using a Makefile but just for testing this is the command -
xlc oraProcs oraProcs.c test_curva.c lib_util.c -I $ORACLE_HOME/precomp/public -L $LIBPATH -l libclntsh.a
Error - ld: 0706-006 Cannot find or open library file: -l libclntsh.a
ld: open(): A file or directory in the pathname does not exist
where LIBPATH = $ORACLE_HOME/lib
library libclntsh.a exists in $ORACLE_HOME/lib & has the right permissions
I also tried removing the library libclntsh.a & recreating it using the command - genclntsh When I run this command I get this warning more than once -ld: 0711-783 Warning TOC overflow TOC size: 66888 Max size: 65536 genclntsh creates the library libclntsh.a, just not sure if these warnings damage the library file and are related to my link erros in some way.
View 2 Replies
View Related
Jul 11, 2013
I have newly installed Oracle 11g R2 64 bits to window 8 PRo 64 bits. Able to connection successfully Oracle NEt Configuration Assistant service name.
But when I try to connect it Toad 9.6 32 bits its show error message
ORA-06413: Connection not open
View 5 Replies
View Related
Nov 1, 2006
I'm getting an error when trying to use the new Data Pump Export/Import utility.
I am able to create a directory using SQLPLus, and I get the "Directory Created" message, but no directory actually gets created on the server.
SQL> CREATE DIRECTORY datapump AS 'C:Inetpubdatafiledatapump';
Directory created. But I dont see the directory created on the server.
Then on the server:
C:Documents and SettingsAdministrator>expdp ******/****** FULL=y DIRECTORY=datapump DUMPFILE=expdata.dmp LOGFILE=expdata.log
Export: Release 10.2.0.1.0 - Production on Wednesday, 01 November, 2006 1:51:55
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation
View 5 Replies
View Related
Apr 10, 2013
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]....
View 3 Replies
View Related
Feb 2, 2011
I have this stored procedure as part of package.
PROCEDURE XCOM_X060_UPDATEOHBFAILURE( in_CALL_ID IN NUMBER,
in_SPLY_REORD_NO IN CHAR,
in_OHB_QTY_CARTONS IN NUMBER,
in_OHB_QTY_UNITS IN NUMBER,
in_SPLY_TOT_OHB_QTY IN NUMBER,
in_OHB_INPUT_CTNS_MIN IN NUMBER,
in_OHB_INPUT_CTNS_MAX IN NUMBER,
in_UNITS_PER_CARTON IN NUMBER,
in_OHB_INPUT_UNIT_CONSTANT IN NUMBER,
in_TOTAL_CARTONS IN NUMBER,
out_ALLOW_OHB_INPUT_FLAG OUT CHAR,
out_ERR_CODE OUT NUMBER,
out_ERR_MESSAGE OUT VARCHAR2)
When the stored procedure is executed it is throwing following exception OTHERS EXCEPTION in XCOM_X060_UPDATEOHBFAILURE - SQL -1001 SQLERRM: ORA-01001: invalid cursor
Here is the execution script
DECLARE
IN_CALL_ID NUMBER;
IN_SPLY_REORD_NO VARCHAR2(32767);
IN_OHB_QTY_CARTONS NUMBER;
IN_OHB_QTY_UNITS NUMBER;
IN_SPLY_TOT_OHB_QTY NUMBER;
IN_OHB_INPUT_CTNS_MIN NUMBER;
[code]...
There is no cursor used in the procedure. It just inserts records in the table for given input values.
View 10 Replies
View Related
Dec 31, 2012
I am writing a cursor and inside that cursor I am checking record exists or not and based on that I am doing my operation.But I am getting error that i can not use exception inside cursor see the below sample code (syntax may not be correct)
sample code------------------
cur c1
is select * from T1;
open c1
loop
fetch c1 into cur_id;
select name into var_name from t2 where id = cur_id;
exception
when no_data_found then
continue with next cursor value
end
update t3 set name = var_name where t3.id = cur_id;
commit;
end loop;
end;
View 6 Replies
View Related
May 27, 2013
the code as follows
create or replace
procedure Country_sel(key in varchar2)
as
cc Res_RelcountryLan.countrycode%type;
len Res_Language.langname_en%type;
lid Res_Language.langid%type;
ab Res_Language.Abrivation%type;
[code]....
when i am running this code im getting
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "RASOOL.COUNTRY_SEL", line 11
ORA-06512: at line 6
View 2 Replies
View Related
May 1, 2013
create or replace procedure upd(name in varchar2) is
cnumber number;
cursor c1(name in varchar2) is select sid from student where sname=name;
begin
open c1(name);
[code]....
it has no errors . .. . but when i try to execute it is displaying as cursor hanged . . .
View 5 Replies
View Related
Jul 7, 2012
My cursor went to the exception when its got the error " ORA-01403: no data found"
But my cursor need to run even though it got the error " ORA-01403: no data found"...This is my exception part
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (c1rec);
DBMS_OUTPUT.put_line (SQLCODE);
DBMS_OUTPUT.put_line (SQLERRM);
-- RETURN NULL;
-- code change added as part of GCA642 -- END
END get_geo_test1;
View 14 Replies
View Related
Jun 21, 2011
I want to pass a damn long query(which includes a lot of column names, tables, joins, union, where clauses etc. and whose length is more than 120000) in a Ref cursor (that's length is more than 32767). Query is stored in a LONG type variable V_QRY in stored procedure, and I am opening that ref cursor like below-
OPEN P_RPT_TEST FOR V_QRY;
at run time its giveing string buffer too small error.
View 7 Replies
View Related
Jun 19, 2013
While reading data from collection variable using ref cursor . I am getting the below two errors.
PLS-00382:Expression is of wrong type
ORA-22905 Cannot access rows from a non-nested table item.
CREATE OR REPLACE PACKAGE APPS_GLOBAL.GIIOMEGAORDERLIST
AS
TYPE BU_LIST_TYPE IS TABLE OF VARCHAR(50);
TYPE OFFER_DETAIL IS RECORD
(
GII_BU VARCHAR(50),
GII_OFFER NUMBER,
[code]........
View 4 Replies
View Related
Mar 18, 2010
I have created the below stored procedure and calling the procedure in when-button-pressed trigger. Problem here is that when I cancel the file selection in file open dialogue box its raising exception.
PROCEDURE TEST
IS
buffer_lines client_text_io.file_type;
v_outputstr VARCHAR2 (32767);
p_delimiter VARCHAR2 (10) := '","';
v_transaction_no VARCHAR2 (10);
BEGIN
[code].......
View 3 Replies
View Related
Jul 11, 2012
I want to create a wallet on RAC setup.I have two node setup.I have created the wallet directory under shared folder /u01/oradata/$ORACLE_SID/wallet
I am Unable to open wallet.I tried this using the below command
SQL> alter system set encryption key identified by "aryabhat";
alter system set encryption key identified by "aryabhat"
*
ERROR at line 1:
ORA-28353: failed to open wallet
Following is the content of sqlnet.ora file
(path : /u01/app/11.2.0/grid/network/admin/sqlnet.ora)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
ADR_BASE = /u01/app/oracle
ENCRYPTION_WALLET_LOCATION =
(SOURCE = (METHOD = FILE)(METHOD_DATA =
(DIRECTORY = /u01/oradata/$ORACLE_SID/wallet/)))
View 3 Replies
View Related