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?
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>
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.
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?
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.
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.
I have strange problem when i try to return a ref cursor holding data from a select on a oracle global temporary table. If i iterate through the cursor , i can see the values but the function as such returns nothing through the ref cursor. I tried the temporary table as both delete on commit and preserve on commit
create or replace
PACKAGE BODY BILL AS
FUNCTION FILTERI RETURN BILL.refcursor IS
testcursor BILL.refcursor;
ttstatus INT;
iSuccess INT;
returns INT;
TruncatedSQL1 VARCHAR2(32767);
BEGIN
[code].........
what is the default return type for weak ref cursor?
View 6 Replies View RelatedAfter 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]...
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]......
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]....
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].....
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 RelatedIn Apex 4.2, the item validation of "Function Returning Boolean" and "Function Returning Error Text"; They seam to be backwards.
Is there a simple statement that can be used to fix this in the apex dictionary?
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/)))
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?
Can i user exist when cursor will using For Cursor .
View 15 Replies View Relatedi need to have an SQL statement that will return only in one row.
in this SELECT statement,
select NVL(a.jj_crdr,'CR'),
nvl(a.CR_cnt,0) CR_cnt,
NVL(a.CR_amt,0) CR_amt,
NVL(b.jj_crdr,'DR'),
NVL(b.DR_cnt,0) DR_cnt,
NVL(b.DR_amt,0) DR_amt
[Code]...
it returned
Tot_cr tot_cr_amt tot_dr tot_dr_amt
1 100 0 0
because there is record for CR and there is no DR.
but when there's record in DR and no in CR, there's no record returned.
because the outer join is in the 2nd Select. i need to change the condition to: 'where a.jj_creator(+) = b.jj_creator' in order to return a record.
what i need is to be flexible so that it will return records even if one of the source doesn't have record.
I have some XML being returned from a web service, and it returns almost 900 variables. Whilst I am familiar with how to return these in a single row, do I can return a row for each variable? My DBA is very uncomfortable with creating a table with almost 900 columns, for obvious reasons. However, we already have plenty of tables with tens of millions of rows, so he's fine with that. I'll try and expand on the requirement. Below is some XML from the data returned to us:
<APPLICANT app_no="1">
<APPLSUMMARY>
<MAIN W="ZZ" X="{ND}"/>
<COUNTS Z="3" AB="0" BB="3" CB="0" DB="3" EB="3" FB="3" GB="0"/>
</APPLSUMMARY>
</APPLICANT>
I would like to be able to return a new row for each variable, For example:
VARIABLE | VALUE
----------------
W | ZZ
X | {ND}
Z | 3
And so on.
This query returns 2 rows and the output is displayed as well. how I can return just the first row where the max end_date is 4/30/2011?
select
pt.customer_number,
pt.customer_name,
lease.lease_number,
lease.lease_name,
lease.property_name_disp,
lease.location_code_disp,
MAX(pt.end_date) end_date,
pt.attribute1 Disabled
[code]...
Client managerCont. Start DateCont. End Date
abcman11-Jan-0830-Jun-08
abcman21-Jul-0831-Dec-08
abcman11-Jan-0930-Jun-09
abcman11-Jul-0931-Dec-09
abcman21-Jan-1031-Mar-10
abcman21-Apr-1031-Aug-10
I need to code a SQL statement (Not PL/SQL) to display following records:
Client managerCont. Start DateCont. End Date
abcman11-Jan-0830-Jun-08
abcman21-Jul-0831-Dec-08
abcman11-Jan-0931-Dec-09
abcman21-Jan-1031-Aug-10
I am fetching records from many voluminous tables having multiple joins based on filter criteria filled from frond end application. As per the selected criteria, I want to have a pre-check if query would return more than 1000 rows then I have to show user a message saying that he should refine the search. Is there any performant way to query db and dynamically find record count and stops executions if it is going return more than the specified no of rows.
View 5 Replies View RelatedMy database is in open and noarchive log mode which was working fine but since from last 2 to 3 days it is throwing me an error
ORA-00308: cannot open archived log with ora -00600
ORA-00308: cannot open archived log 'D:\HFTEST\ARCHIVE\ARC1_779994432.1'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
ORA-00600: internal error code, arguments: [kewrsp_split_partition_2], [87], [902828405], [11905], [], [], [], [], [], [], [], []
I dont understand why it so, i googled it but i didnt find anything.
I install Forms 11 g . how i an use my Old Fmb files ? (my forms designed in forms6 i )
View 2 Replies View RelatedI have two Oracle instances, both are 10.2.0.3..Executing the following results in two different results:
select N'Test" "result" from dual;
On one the output is:
resu
----
Test
and on the other the output is
result
----------------
Test
This seems to suggest that one is returning "Test" as a char(16) and the other as a varchar2 or nvarchar2. In both cases, NLS_CHARACTERSET is WE8ISO8859P1 and NLS_NCHAR_CHARACTERSET is AL16UTF16.
Not being an Oracle DBA, I am not sure where to look.
The function definition in PL/SQL has IN OUT parameter as well as return statement. Using both we can return the values. Basic definition of a function is function can return only one value at a time.
The question is, Can we return a number thru return statement and a char value thru INOUT parameter. Is it possible to return two different values using these?
i am storing image in label how to return for calling place.
View 1 Replies View RelatedSELECT
REF.REFERRAL_TO,
REF.LOCAL_PATIENT_NUM,
REF.REFERRAL_REFERENCE_NUM,
TO_CHAR (REF.REFERRAL_DTE, 'DD/MM/YYYY') REFDATE,
TO_CHAR (OPC.CONTACT_DATE, 'DD/MM/YYYY') CONTACTDATE,
TO_DATE (OPC.CONTACT_DATE, 'DD/MM/YYYY') - TO_DATE (REF.REFERRAL_DTE, 'DD/MM/YYYY')
FROM
PAS.REFERRAL_DETAILS REF
FULL JOIN
PAS.OUTPATIENT_CONTACT OPC
[Code]...
This calculates the difference between a set date and a contact date, I only want it to return the minimum of the results set for each patient, how can this be achieved? At the moment it pulls off several records for each patient as there are several contacts.