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

Forms :: Ora-06511 - Cursor Already Open - Trigger Raised Unhandled Exception

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

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 :: OPEN FOR Cursor Handling?

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

PL/SQL :: Open / Return Cursor?

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

SQL & PL/SQL :: Used Variable In Open Cursor Statement

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

SQL & PL/SQL :: Open Cursor Statement Getting Stuck?

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

SQL & PL/SQL :: How To Skip Some Records In Open Dynamic Cursor

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

PL/SQL :: Open Ref Cursor Using Collection As Bind Variable

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

PL/SQL :: PLS-00455 / Cursor CUR_1 Cannot Be Used In Dynamic SQL OPEN Statement

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

Server Administration :: How To Find And Close Open Cursor List Without Restarting

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

Getting Error When Try To Open PL / SQL Developer

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

Ora-06413 Connection Not Open Error

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

Connecting To Remote DB - Getting Error When Try To Open File

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

SQL & PL/SQL :: Open Oracle Table In MS Access Frontend - Gives Error?

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

Server Utilities :: SQL Loader Unable To Open File Error?

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

Server Administration :: Startup Open After Shutdown Throws ORA-12514 Error?

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

Precompilers, OCI & OCCI :: Error ID 0706-006 Cannot Find Or Open Library File

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

Client Tools :: ORA-06413 - Connection Not Open Error Oracle On 64-bit Windows

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

Server Utilities :: Data Pump Error - ORA-39070 / Unable To Open The Log File

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

SQL & PL/SQL :: Error While Using Cursor

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

SQL & PL/SQL :: Invalid Cursor Error In Procedure

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

SQL & PL/SQL :: Error Handling Inside Cursor

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

PL/SQL :: How To Clear Runtime Error In Ref Cursor

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

PL/SQL :: Error Displaying As Cursor Is Hanged?

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

PL/SQL :: Cursor Went To Exception When It Got Error / No Data Found

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

SQL & PL/SQL :: Very Long Query With Ref Cursor - Buffer Too Small Error

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

SQL & PL/SQL :: Error In Reading Data From Collection Variable For Ref Cursor

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

Forms :: Getting Error When Cancel File Selection In File Open Dialogue

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

Real Application Clusters :: Unable To Open Wallet / ORA-28353 / Failed To Open Wallet

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







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