i have created a procedure to open a query using a reference sys_refcursor as output parameter. i referenced the cursor in a pl/sql and is working correctly. however it throws error "ORA-01001: invalid cursor ORA-01403: no data found" when use on the oracle forms builder.
create or replace procedure mrch.prc_proj_list (p_add_clause in varchar2, p_rec_set out sys_refcursor) as
begin
Open p_rec_set for
'Select mp2.REGION region, '||
' mp2.ID proj_id, '||
' mppm2.PHASE phase, '||
' mm2.muni muni_id, '||
' mm2.MUNINAME muni_name, '||
' nvl((select Sum(ma.AMOUNT) From MRCH.MRC_AGREEMENT ma '||
' Where ma.PROJECT_ID = mp2.ID '||
' And ma.PHASE = mppm2.phase '||
' And ma.MUNI = mm2.MUNI),0) agreement, '||
' nvl((Select Sum(NVL(ma.GREEN_BOOK_AMOUNT,0) + NVL(ma.GB_ADJUST,0)) From MRCH.MRC_ALLOCATION ma '||
[code]....
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?
procedure vieworderstatus(in_CALL_ID IN NUMBER , in_DAYS_OF_HISTORY in number , out_SUPLY_REORDR_STATUS_LST OUT supplyreordrlist , out_ERR_CODE OUT NUMBER, out_ERR_MESSAGE out varchar2);
This procedure takes retrievs cust_id for given input call_id. The procedure then retrieves order information for that customer and returns using the out ref cursor. I need to test the scenario where the user passes invalid call_id.
The following query returns no data for call_id = 12345 ( invalid call_id) select fk_cust_id into v_cust_id from xcom_call where call_id = in_CALL_ID;
in such cases; I just need to return null ref cursor and log the error but right now when I pass invalid call_id ; I am getting "invalid cursor" error.How do I handle this ?
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.
using dbms_profiler for a package having procedure returning sys_refcursor.
I have 2 procedure in a package. let say, proc1 and proc2 ( also proc1 is called inside from proc2)
proc1 has no parameter
proc2 has 2 paramter proc2(p_num in integer, p_data out sys_refcursor) [ in which we pass the p_num (ex: 1) and it run to get the data from the tab le and return that data through sys_refcursor.
for proc1, I am able to use dbms_profiler as below and it is working fine
i have a procedure which outputs 10 refcursors. It is called called by a .NET utility which then writes the data to 18 different flat files. The proc is of below format
CREATE OR REPLACE PROCEDURE FILE_EXTRACT (OUTREC1 OUT SYS_REFCURSOR, OUTREC2 OUT SYS_REFCURSOR,) AS BEGIN OPEN OUTREC1 FOR SELECT /*+PARALLEL(A,8)*/ COL1,COL2 FROM TABLE A; [code]....
I have used parallel hint because all the tables used in the queries a huge tables and this is a nightly batch job. The issue here is that i have expected oracle to use 8 processes for this execution so all my select statements have parallel hint with degree 8 , but unusaully the procedure is erroring out on production databases because of maximum number of connections are being spawned and the database is not able to create any new session. When i debuged and did a quick test proc where i used only one out refcursor it ran only 9 threads . then i ran by adding one more out ref cursor it spawn 17 threads. I think its the way .NET is fetching data from each of these cursors.When i print the first refcursor i do see queries which are running for the second along with the first causing the parallel queries run more than expected. The cause of the problem here is all the refcursors are executed and waiting to return data. SO when .net starts reading first cursor the other queries also run.
For my task I tried to write a package that has sys_refcursor in it.It compiled well.Now I want to see the results of it and wrote a anonymous block which gave errors.
I'm trying to build a dynamic sql inside an Oracle procedure, and return it as a SYS_REFCURSOR to ADO.NET using ODP.NET in Oracle 11R2. Below is my table:
CREATE TABLE "TFWDB_ENTIDADE" ( "CD_ENTIDADE" NUMBER(10,0) NOT NULL, "DC_ENTIDADE" VARCHAR2(100 BYTE) NOT NULL, "NM_ENTIDADE" VARCHAR2(255 BYTE) NOT NULL, "CD_TIPO" NUMBER(5,0) NOT NULL, "ID_STATUS" CHAR(1 BYTE) DEFAULT ('1') NOT NULL, CONSTRAINT "PKFWDB_ENTIDADE" PRIMARY KEY ("CD_ENTIDADE"), CONSTRAINT "CHFWDB_ENTIDADE" CHECK (ID_STATUS IN ('0', '1')) );
Below is the procedure I build to SELECT it:
create or replace procedure spfwdb_sl_entidade_1( p_cd_entidade in number, p_dc_entidade in varchar2, p_nm_entidade in varchar2, p_cd_tipo in number, p_id_status in char, [code]........
I'm calling this code from C#, and I get an empty Result Set. What am I doing wrong? Can I output the dynamic sys_refcursor that I created? Below is my C# code using ODP.NET:
My Application is running on the unix server - user oracle. My DB is in different unix box. I had created the folder called OUT with the permission of 777 in DB server. I created ORacle Directory DMPDIR for that folder /OUT. and executed grant read/write to that schema.
1) when i use the below code in sqlplus its writing the file into that folder. DECLARE vInHandle utl_file.file_type; vNewLine VARCHAR2(4000);
[Code].....
But, when the same piece of code is executed from the package ( which called from the application)its throwing the ORA-29283: invalid file operation.
even i am able to copy the files into that folder with the user oracle.
I am using this query in MERGE statement , result is also shows here
select TO_CHAR(emp_id) EMP_ID ,TO_DATE(at_date,'RRRR/MM/DD') AT_DATE,TO_CHAR(time_in) TIME_IN,TO_CHAR(time_out) TIME_OUT,TO_CHAR(status) STATUS from ( select emp_code emp_id,at_date,at_time Time_in, Null Time_out, status from ( select a.*,substr(data_row,24,5) Emp_code,substr(data_row,3,8) AT_Date, substr(data_row,11,4) At_Time, substr(data_row,15,2) Shift, substr(data_row,17,2) STatus from data_load a
[code].....
using this query in MERGE statement giving a error ora-00911 invalid character
MERGE INTO Attendance I USING ( select TO_CHAR(emp_id) EMP_ID ,TO_DATE(at_date,'RRRR/MM/DD') AT_DATE,TO_CHAR(time_in) TIME_IN,TO_CHAR(time_out) TIME_OUT,TO_CHAR(status) STATUS from ( select emp_code emp_id,at_date,at_time Time_in, Null Time_out, status from ( select a.*,substr(data_row,24,5) Emp_code,substr(data_row,3,8) AT_Date, substr(data_row,11,4) At_Time,
earlier the query was running on same record group but i hav to delete some code so i did but after made such changes itreturns this error.but still query is correct i complied on toad.
I created 3 radio buttons and one list item. Different options will appear according to the radio button the user has clicked on. i've created 3 recordgroups and 3 LOV. the first two for seven_eleven and mannings work fine, but not for GNC. is it because there is only one option for list item in GNC? Must it be more than one? I've tried to another option as space but it still doesnt work.
I want to fetch the data through the cursor and cursor is getting the value of group_code through the variable 'a'. but when i am writing the code like this it is not coming.
My code is like this :
declare a varchar2(400):=''; cursor c1 is select ref_no,ref_code,company_id from stock_detail where company_id=:global.company_id
i am migrating from forms 6i to forms 10g but i am facing a problem. when i enter to my new form and i try to navigate thru the items with my mouse the validation of the item doesn't work properly. the cursor change position but the functionality works like it is still in the first item. the validation work when you use enter or tab.the same thing when you try to fetch the LOV, the LOV of the first item is populated not where the cursor is positioned.
CURSOR studgrade_cur IS SELECT g.stud_id, g.grade, subj_code, s.description FROM studgrades g JOIN subjects s ON(g.subj_code = s.subj_code) WHERE stud_id = :Studentprofile.student_id;
but i got an error, saying:
Quote:encountered the symbol JOIN when expecting one of the following:
,; for group having intersect minus order start union where connect
Is it not allowed to use JOIN statement in a cursor?
when I updated a record in my form check my screenshot: and clicked save button the result was:
as you can see in the message at the bottom, it has 18 records. And since my original data(subjects) are just 9. And also the grades that I input didn't display all.
This is my code in when-new-block-instance trigger: DECLARE CURSOR studgrade_cur IS SELECT e.student_id, s.subject_code --, g.grade
for Inserting records created below cursor, records are inserted and I can see on my screen (form) also.
DECLARE CURSOR InvestIS select * from Tempinvest; BEGIN OPEN Invest; GO_BLOCK('ReturnReport'); last_record; [code]......
2nd step:-
Updating records I wan to update some columns in this table so I created a 2nd cursor to update records but this cursor is not working accordingly. my requirement is: before Update first of all Find inv_co_code & inv_fnd_code when find then update column Redunits
2nd Cursor
DECLARE ddate date; refdate date; co_code number; co_name varchar2(50); fnd_code number; fnd_name varchar2(50); units number; amount number; stat varchar2(1); CURSOR bnr IS
select Ddate,refdate,co_code,co_name,fnd_code,fnd_name,units,amount,stat from ( select inv_date DDate,Vdate RefDate,inv_co_code Co_code,inv_co_name Co_name,inv_fnd_code Fnd_code,inv_fnd_name Fnd_name, inv_nofu Units,inv_amount Amount,Status Stat from ( ------------- Bonus ------------------- select inv_date,vdate,inv_co_code,inv_co_name,inv_fnd_code,inv_fnd_name,inv_nofu,Inv_amount,inv_uprice,'B' Status from investment where code is null and inv_date <= :dd1 ---------------- Redemption -------------- Union All select red_date,null,red_co_code,red_co_name,red_fnd_code,red_fnd_name,red_nofu,red_amount,red_uprice,'R' Status from redemption where red_date <= :dd1 )) where co_code = 13 and fnd_code = 1 order by co_code,fnd_code,ddate ; ------------------------------------------ vddateddate%type; vrefdate refdate%type; vco_codeco_code%type; vco_nameco_name%type; [code].......
I have a multi-record block like amount and date columns, its not able to move the cursor to another column if pressing enter button,(eg: amount column to date column), i have given 'NEXT_ITEM' in that field too, but oops not working
I have used a dynamic cursor for fetching value from different tables hence table name assigned dynamically during run time but i face an error ORA-06562 type of argument must match type of column and bind variable with error ORA-06212 But i frequently check the table structure and Declared variable there is no mismatch between them.
I would like to update the particular employee name by using the cursor ..For that I would like to show the steps which I did . as in the below screen it updated John but it removed the other ename ...I like to update John only and remaining ename should be as it ..
The code which I have written on KEY-COMMIT is below.
