Tom:

Great explanation and I am finally clear on it.

1. I se how you can save a lot of repeating code using REF cursors. However, can I still accomplish what a REF cursor does using implicit cursors like:

If (user_input='X') then
FOR x in (select * from t1 where x = 5 and y = 120 and z = 2) LOOP
....
END LOOP;
ELSE
FOR x in (select * from t2 where c = 23)
LOOP
.....
END LOOP;
end if;

2. I did notice that when you use inline views like
select col1,col2,(select col from table2 where t1.c1=t2.c2) from table 1
you get an error with explicit or explicit cursors. You have to do it using REF cursors. Correct?

3. YOu say using NDS when you do not the query beforehand. Is this like a user querying a state column but he can enter up to 10 or 20 states to get a result set. IN this case you build a string for the query and run it using NDS?
COrrect?

Thank you,

Tom:

DO you something wrong in this code. I am trying to return a result set into powerbuilder screen.

Package PKG_WRITER
IS
TYPE writer_rec is record (
conno mchar.conno%type;
med mchar.conno%type;
auth ttlinit.auth%type;
ttl ttlinit.casub%type;
casub ttlinit.annoinit%type;
annoinit ttlinit.ccdt%type;
ccdt ttlinit.ccdt%type;
chno ttlinit%type);
TYPE cur_curwriter is ref cursor
return writer_rec;
END pkg_writer;

Procedure writer_proc (
ccdtpar IN varchar2,
initials IN varchar2,
curwriter IN OUT pcadmin.pkg_writer.cur_curwriter
)
AS
data_get curwriter%ROWTYPE;
BEGIN
OPEN curwriter FOR
SELECT mchar.conno,mchar.med, ttlinit.auth,ttlinit.ttl,ttlinit.casub,ttlinit.annoinit,to_char(ttlinit.ccdt,'mm/dd/yyyy'),ttlinit.chno
from mchar,ttlinit
where mchar.chno = ttlinit.chno and ttlinit.annoinit = initials and ttlinit.ccdt between to_date (ccdtpar,'mm/dd/yyyy') and
to_date(ccdtpar,'mm/dd/yyyy') + 6;
LOOP
fetch curwriter into data_get;
exit when curwriter%notfound;
exit loop;
close curwriter;
end;

procedure test1 as
type ref_cursor is ref cursor;

rcur ref_cursor;
arg_ccdt varchar2(20);
arg_init varchar2(10);
begin
arg_ccdt:='07/01/2002';
arg_init:='BKEL';
pcsadmin.writer_proc(arg_ccdt,arg_init,rcur);
end;

WHen I run test1 to test it I keep getting:
Error at line 9
PRA-06502:PL/SQL: numeric or value error: character string buffer too small

Do you see anything wrong?

Thank you,

Tom:

Are you saying to:

1. delete all of this :
LOOP
fetch curwriter into data_get;
exit when curwriter%notfound;
exit loop;
close curwriter;

You said before this how you write them:

open l_cursor for select * from t2 where c = 23;

loop
fetch l_cursor into ....;
exit when l_cursor%notfound;
...procedural code to process results here...
end loop;
close l_cursor;


2. DO i need to delete the type writer_rec in the package.

3. is the test1 procedure fine?

THank you,

Tom:

I did what you said and now getting this:

PCD> var x refcursor;
PCD> exec pcsadmin.writer_proc('07/01/2002','NBKEL',:x);

PL/SQL procedure successfully completed.

PCD> print x
ERROR:
ORA-24338: statement handle not executed


SP2-0625: Error printing variable "x"
PCD> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.2.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.2.0 - 64bit Production
/ $ oerr ora 24338
24338, 00000, "statement handle not executed"
// *Cause: A fetch or describe was attempted before executing a
// statement handle.
// *Action: Execute a statement and then fetch or describe the data.

Tom:

It works now. There was a close cursor statement left. I guess yo udo not close a ref cursor after you open it?

2. The output is coming fine in sql*plus. however when you run it from powerbuilder data window it returns an oracle data format error. Do you think this is related to powerbuilder screen?

Thank you

Tom:

1. Can you tell me what would you use to formulate a query for the following:

I have a web page with four inputs:
p_country,p_state,p_city,p_county

If user entered a country value then I will only search based on country.

If user entered a country value and state then I will search on both. etc.

If I did one SQL statement with AND clauses then it will give me the wrong results if one of those is NULL.

Here I have to build the where clause based on user input which means I guess use of ref cursors. However since I have 4 columns that means I have 4x3x2=24 different combinations I have to check for.

How would you implement it?

Tom:

How can I change the following standard template for ref cursor to define a custom table or return data set. Bascially I want to run several count queries with different date criteria and return it to a Crystal Report for printing.

PACKAGE TEST_PACKAGE

AS TYPE Test_Type IS REF CURSOR RETURN Test_table%ROWTYPE;

PROCEDURE Test_Procedure (
Test_Cursor IN OUT Test_Type,
.......................

PACKAGE BODY TEST_PACKAGE
AS

PROCEDURE Test_Procedure (
Test_Cursor IN OUT Test_Type,

..........

OPEN Test_Cursor FOR
SELECT * from Test_Table



This is based on "test_table". I want to change it so I want to do 4 or five queries on test table and return the results to the cursor.

Thank you,

Tom:

let us say you wanted to do the following queries on emp and return result to a ref cursor:


select count(*) into l_cnt from emp;

select count(*) into l1_cnt from emp where hiredate
between ...

select count(*) into l2_cnt from emp where salary between ....

How do I declare a ref cursor and assign these values to it so that when Crystal reports read it it will be one row record like (50, 20, 10).

Thank you,

TOm:

1. Is this an actual syntax (case when hiredate between a and b then 1 end ) or it is a 9i solution only.

Should it be it like:
select count(*), (select count(*) where ....), (select count(*) where ....) from table;

2. Do ref cursors have to be based on tables. Can I declare it like (select x,y,z from dual) and then fill out the values myself.

3. If I saved the values in temporary table, do I have to delete the data in the table before I run the counts. This is a web application and user same login from app server to database. He might run the report several times in the same session.

Thank you,

Tom:

1. I did it your way (easy). However, If you have two dates in emp table and you want to calculate the difference between these two dates and then divide it by total number of records to get "Average Processing Time". Can you do it in the same query?

2. If you want to add CNT_1+CNT_2 do you have to make this a subquery and do it in the outer select?

select count(*),
2 count( case when hiredate > sysdate-50000 then 1 end ) cnt_2,
3 count( case when sal between 1000 and 2000 then 1 end ) cnt_3
4 from emp
5 /

Tom:

What I mean:

I have a table:

lesson_log
(log_no number(10),
date_received date,
applicant_id number(10),
instructor_id number(2),
report_date date );

select
count(case when report_date is null and date_received < to_date('01-JAN-2005','DD-MON-YYYY')
then 1 end) carry_over,
count(case when date_received between to_date('01-JAN-2004','DD-MON-YYYY') and
to_date('01-JAN-2005','DD-MON-YYYY') then 1 end) Total_recvd,
count(case when report_date between to_date('01-JAN-2004','DD-MON-YYYY') and
to_date('01-JAN-2005','DD-MON-YYYY') then 1 end) Total_completed
from lesson_log where instructor_id=305


CARRY_OVER TOTAL_RECVD TOTAL_COMPLETED
---------- ----------- ---------------
1 4 3

1 row selected.

Now I want to compute the Average time between Date_Received and Report Date (when both are not null). Can I get that as a fourth column result in the above query.

2. I was thinking if I want to create a result column that is basically (carry_over+total_recvd-total_completed), Can I include it in the same SELECT statment.

Thank you,

Tom:

Thanks. Can I add a filter for the AVG just likr the cout.

For example if I want to average dates that only fall in a given date range how would you do it like the count(case)

select count(*),count(case when report_date > sysdate - 2 then 1 end),avg(date_recieved-report_date) avg_time
from table;

Tom:

Is there a way to merge/union two SQL statements that do not have the same output? I got the first summary part using the above query but I can't merge it with a second query to get details?

For example the first one gives me a summary of the overall counts as this:

select count(*),count(case when report_date > sysdate - 2 then 1
end),avg(date_recieved-report_date) avg_time
from table;

The second one gives me details on the records themselves as:

SELECT student,report_date,Trial_number from table where .....

Report Format:
Course # evaluated # certified
Instructor # evaluated # certified

Passes

Student Report Date trial Number
....................................

Failures

Student Report Date trial Number
....................................


2. Is there a SQL/oracle function that turns a number to positive if the value came out negative (i.e abs())?

Thank you,

Tom:

Thanks you gave very good idea on 1.

On #2, I meant a function that turns a negative value into 0.

func(x) = x (if x is positive)
func(x) = 0 (if x is negative)

Tom:

Can this format be used too in the following situation?

select count(*),
count( case when hiredate > sysdate-50000 then 1 end ) cnt_2,
count( case when sal between 1000 and 2000 then 1 end ) cnt_3,
count( case when hiredate > sysdate-50000 OR
sal between 1000 and 2000 then 1 end ) cnt_4
from emp


Instead of one table I need to create a report of statistics based on 5 tables (requests, requested_items,shipments, shipped_items, material)

select X.*,Y.*,Z.* from
(select count(*),count(case ..),count(case ...)
from requests a, requested_items b, material c
where a.request_id = b.request_id and
b.material_id = c.material_id) X,
select count(*),count(case ..),count(case ...)
from shipments a, shipped_items b, material c
where a.shipment_id = b.shipment_id and
b.material_id = c.material_id) Y,
select count(*),count(case ..),count(case ...)
from requests a, shipments b
where a.request_id = b.request_id ) Z


Do you always have to do a full table scan on the first count/query and apply the filters in the next sub-queries?

Tom:

Well it is realy two questions:

1. IS the query a valid format.

2. When you do
select count(*),count(case date < sysdate-10)...
from table where date > sysdate

the second count "records" has to be filtered from the first count. Is the second. third etc counts based on the result set from main first count?

I want to have a single explicit cursor definition in my package header and either open and process it or assign it to a ref cursor for another client to access.

I get a compilation error for the following:

create or replace package p1 is

cursor emp_c is select * from emp;

type emp_tt is table of emp%rowtype;

--ref cursor access
procedure access(rc in out SYS_REFCURSOR);

--pl/sql access
procedure access(t in out nocopy emp_tt);

end;
/

create or replace package body p1 is

--ref cursor access
procedure access(rc in out SYS_REFCURSOR) is
begin
open rc for emp_c;
end access;

--pl/sql access
procedure access(t in out nocopy emp_tt) is
begin

open emp_c;
fetch emp_c bulk collect into t;
close emp_c;

end access;

end p1;

/
show errors

LINE/COL ERROR
-------- ---------------------------------------
6/1 PL/SQL: Statement ignored
6/13 PLS-00382: expression is of wrong type

Questions:

1. Can I do this?

Hi Tom,

I have OUT Parameter in Cursor which is of REF CURSOR type.
I Wanted to know that can i return NULL in REF CURSOR variable.

Hi Tom,

I refered to you post a bit further up concerning the ORA-24338 error. I am still receiving the error even though I make sure I Open the Ref Cursor t01_cursor down the bottom and do not close it. Here is the code I have written. I'm not sure if the error relates to any other part of the code, but from what I have researched it is solely to do with the Ref Cursor.

By the way, I am accessing the procedure via a Crystal Report using the ODBC(RDO) driver...thanks in advance for your help


PROCEDURE "P_ACTVTY_CMPTNCY_ASMT"
(
t01_cursor in out crystal_package.t01_type,
actvty_cmptncy_asmt_parameter in number
)
as

type t_cmptncy is record (
cmptncy_cd varchar2(6),
asmt_cd varchar2(1));
type t_cmptncy_asmt is varray(17) of t_cmptncy;

-- Define the array for the comptncy code and status to reside.
a_cmptncy_asmt t_cmptncy_asmt := t_cmptncy_asmt();
v_rprt_key varchar2(50);
icursor number;
ivoid number;
v_line_nr number;
v_row_nr number;
v_lrng_actvty_id number;
v_clnt_id number;
v_last_lrng_actvty_id number;
v_last_clnt_id number;
vsql varchar2(4000);
v_sp_lrng_actvty_id varchar2(10);
v_amet_clnt_id varchar2(10);
v_cd varchar2(6);
v_sts_cd varchar2(1);
v_first_pass varchar2(1);
v_user_id varchar2(8);
v_full_nm varchar2(100);
v_eng_prf_lvl_cd varchar2(6);
v_crnt_bnd_cd varchar2(6);
v_last_sp_lrng_actvty_id varchar2(10);
v_last_amet_clnt_id varchar2(10);
v_last_full_nm varchar2(100);
v_last_crnt_bnd_cd varchar2(6);
v_last_eng_prf_lvl_cd varchar2(6);

--
-- Get the userid to create the key in the temporary table
--
cursor c_user_id is
select rec_insrtd_by_usr_id
from amet_rprt_slctn
where id = actvty_cmptncy_asmt_parameter;

--
-- The Main selection process
--
-- Read the Data from the View for processing
-- This View is a UNION which will return a
-- status code for each of the activity competencies
-- regardless as to whether the client has been
-- assessed for a competency ('N' will be returned).
--
cursor c_actvty_cmptncy_asmt is
select sp_lrng_actvty_id,
lrng_actvty_id,
amet_clnt_id,
clnt_id,
englsh_prfcncy_lvl_cd,
cd,
initcap(full_nm),
crnt_lrng_cpcty_bnd_cd,
sts_cd
from v_actvty_cmptncy_asmt
where report_id = actvty_cmptncy_asmt_parameter;

begin
dbms_output.enable(1000000);
a_cmptncy_asmt.extend(17);
begin

-- Create temporary holding table

-- dbms_output.put_line('->'||to_char(actvty_cmptncy_asmt_parameter));

open c_user_id;

fetch c_user_id into v_user_id;

close c_user_id;

-- dbms_output.put_line('->'||v_user_id);

v_rprt_key := v_user_id||to_char(sysdate, 'yyyymmddhh24miss');

exception

when others then

dbms_output.put_line('table problem');
dbms_output.put_line(vsql);
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);

return;

end;

-- Insert into temporary table

begin
--
-- Initialise variables to be used in the
-- Main Processing section
--
v_last_sp_lrng_actvty_id := null;
v_last_amet_clnt_id := null;
v_last_crnt_bnd_cd := null;
v_last_eng_prf_lvl_cd := null;
v_first_pass := 'T';
v_line_nr := 1;
v_row_nr := 0;

open c_actvty_cmptncy_asmt;

--
-- Main Processing Loop
-- Within this loop
-- Initially
-- Key information is setup and the competency code and status are
-- stored in the array.
-- Details are stored to the temporary table when:
-- There is a change to the Learning Activity / Client Id or Row count = 17 or
-- the Eng Prof level of the Competency changes.
-- The 'Row count' and 'Eng Prof level' condition are required
-- to ensure the line_nr is incremented. This will ensure these competencies
-- will appear at a later stage in the report.
--
loop
--
-- Return a row
--
fetch c_actvty_cmptncy_asmt into v_sp_lrng_actvty_id,
v_lrng_actvty_id,
v_amet_clnt_id,
v_clnt_id,
v_eng_prf_lvl_cd,
v_cd,
v_full_nm,
v_crnt_bnd_cd,
v_sts_cd;

exit when c_actvty_cmptncy_asmt%NOTFOUND;

--
-- First time through setup the major processing keys
--
if v_first_pass = 'T' then
v_first_pass := 'F';
v_last_amet_clnt_id := v_amet_clnt_id;
v_last_sp_lrng_actvty_id := v_sp_lrng_actvty_id;
v_last_eng_prf_lvl_cd := v_eng_prf_lvl_cd;
v_last_clnt_id := v_clnt_id;
v_last_lrng_actvty_id := v_lrng_actvty_id;
v_last_full_nm := v_full_nm;
v_last_crnt_bnd_cd := v_crnt_bnd_cd;
end if;

--
-- When there is a change of Activity Write out the previous key information
-- and setup values for the current row that is being processed.
--

if v_sp_lrng_actvty_id != v_last_sp_lrng_actvty_id then
-- insert row
vsql := 'insert into reports.t01 values (''' ||
v_rprt_key ||''','''||
v_last_sp_lrng_actvty_id||''','||
v_last_lrng_actvty_id||','||
v_line_nr||','''||
v_last_amet_clnt_id||''','||
v_last_clnt_id||','''||
v_last_full_nm||'''';
--
-- Move the array values to the variable then
-- clear out the array.
--
for i in 1..17 loop
vsql := vsql||','''||a_cmptncy_asmt(i).cmptncy_cd||''','''||
a_cmptncy_asmt(i).asmt_cd||'''';
a_cmptncy_asmt(i).cmptncy_cd := null;
a_cmptncy_asmt(i).asmt_cd := null;
end loop;

vsql := vsql||','''||v_last_eng_prf_lvl_cd||''','''||
v_last_crnt_bnd_cd||'''';

vsql := vsql ||')';

icursor := dbms_sql.open_cursor;
dbms_sql.parse(icursor, vsql, dbms_sql.v7);
ivoid := dbms_sql.execute(icursor);
commit;
dbms_sql.close_cursor(icursor);
--
-- Reset the variables for processing the data
--
v_row_nr := 0;
v_line_nr := 1;
v_last_sp_lrng_actvty_id := v_sp_lrng_actvty_id;
v_last_amet_clnt_id := v_amet_clnt_id;
v_last_lrng_actvty_id := v_lrng_actvty_id;
v_last_clnt_id := v_clnt_id;
v_last_full_nm := v_full_nm;
v_last_eng_prf_lvl_cd := v_eng_prf_lvl_cd;
v_last_crnt_bnd_cd := v_crnt_bnd_cd;
end if;
--
-- When there is a change of Client Write out the previous key information
-- and setup values for the current row that is being processed.
--

if v_amet_clnt_id != v_last_amet_clnt_id then
-- insert row
vsql := 'insert into reports.t01 values (''' ||
v_rprt_key ||''','''||
v_last_sp_lrng_actvty_id||''','||
v_last_lrng_actvty_id||','||
v_line_nr||','''||
v_last_amet_clnt_id||''','||
v_last_clnt_id||','''||
v_last_full_nm||'''';
--
-- Move the array values to the variable then
-- clear out the array.
--
for i in 1..17 loop
vsql := vsql||','''||a_cmptncy_asmt(i).cmptncy_cd||''','''||
a_cmptncy_asmt(i).asmt_cd||'''';
a_cmptncy_asmt(i).cmptncy_cd := null;
a_cmptncy_asmt(i).asmt_cd := null;
end loop;

vsql := vsql||','''||v_last_eng_prf_lvl_cd||''','''||
v_last_crnt_bnd_cd||'''';

vsql := vsql ||')';

icursor := dbms_sql.open_cursor;
dbms_sql.parse(icursor, vsql, dbms_sql.v7);
ivoid := dbms_sql.execute(icursor);
commit;
dbms_sql.close_cursor(icursor);
v_row_nr := 0;
v_line_nr := 1;
v_last_amet_clnt_id := v_amet_clnt_id;
v_last_clnt_id := v_clnt_id;
v_last_full_nm := v_full_nm;
v_last_eng_prf_lvl_cd := v_eng_prf_lvl_cd;
v_last_crnt_bnd_cd := v_crnt_bnd_cd;
end if;
--
-- When the 17th row or a change in Eng Prof Lvl has been reached
-- then insert the details into the temporary table
-- and increment the line_nr by one to force the next
-- set of rows to be inserted to appear at a later stage in
-- the Crystal Report.
--
if (v_row_nr = 17) or (v_eng_prf_lvl_cd != v_last_eng_prf_lvl_cd) then
-- insert row
vsql := 'insert into reports.t01 values (''' ||
v_rprt_key ||''','''||
v_last_sp_lrng_actvty_id||''','||
v_last_lrng_actvty_id||','||
v_line_nr||','''||
v_last_amet_clnt_id||''','||
v_last_clnt_id||','''||
v_last_full_nm||'''';

--
-- Move the array values to the variable then
-- clear out the array.
--
for i in 1..17 loop
vsql := vsql||','''||a_cmptncy_asmt(i).cmptncy_cd||''','''||
a_cmptncy_asmt(i).asmt_cd||'''';
a_cmptncy_asmt(i).cmptncy_cd := null;
a_cmptncy_asmt(i).asmt_cd := null;
end loop;

vsql := vsql||','''||v_last_eng_prf_lvl_cd||''','''||
v_last_crnt_bnd_cd||'''';

vsql := vsql ||')';

icursor := dbms_sql.open_cursor;
dbms_sql.parse(icursor, vsql, dbms_sql.v7);
ivoid := dbms_sql.execute(icursor);
commit;
dbms_sql.close_cursor(icursor);
v_row_nr := 0;
v_line_nr := v_line_nr + 1;
v_last_amet_clnt_id := v_amet_clnt_id;
v_last_clnt_id := v_clnt_id;
v_last_full_nm := v_full_nm;
v_last_eng_prf_lvl_cd := v_eng_prf_lvl_cd;
v_last_crnt_bnd_cd := v_crnt_bnd_cd;
end if;
--
-- For each row returned increment the count and
-- update the array with the competency code and status.
--
v_row_nr := v_row_nr + 1;

a_cmptncy_asmt(v_row_nr).cmptncy_cd := v_cd;
a_cmptncy_asmt(v_row_nr).asmt_cd := v_sts_cd;


end loop; /* End of the Major processing loop*/

--
-- Store the details for the last activity / client
-- returned from the major processing loop.
--

vsql := 'insert into reports.t01 values (''' ||
v_rprt_key ||''','''||
v_sp_lrng_actvty_id||''','||
v_lrng_actvty_id||','||
v_line_nr||','''||
v_amet_clnt_id||''','||
v_clnt_id||','''||
v_last_full_nm||'''';

for i in 1..17 loop
vsql := vsql||','''||a_cmptncy_asmt(i).cmptncy_cd||''','''||
a_cmptncy_asmt(i).asmt_cd||'''';
a_cmptncy_asmt(i).cmptncy_cd := null;
a_cmptncy_asmt(i).asmt_cd := null;
end loop;

vsql := vsql||','''||v_last_eng_prf_lvl_cd||''','''||
v_last_crnt_bnd_cd||'''';

vsql := vsql ||')';

icursor := dbms_sql.open_cursor;
dbms_sql.parse(icursor, vsql, dbms_sql.v7);
ivoid := dbms_sql.execute(icursor);
commit;
dbms_sql.close_cursor(icursor);

exception

when others then

dbms_output.put_line('insert problem');
dbms_output.put_line(substr(vsql,1,70));
dbms_output.put_line(substr(vsql,71,70));
dbms_output.put_line(substr(vsql,141,70));
dbms_output.put_line(substr(vsql,211,70));
dbms_output.put_line(substr(vsql,281,70));
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);

return;
end;

--
-- Return the data from the temporary table
-- to the open cursor for the Crystal Report.
--
open t01_cursor
for
select *
from reports.t01
where rprt_key = v_rprt_key
order by sp_lrng_actvty_id,
line_nr,
full_nm;
--
-- Delete the records in the Temporary Table
-- based on the key (userid + data and Time).
--

delete
from reports.t01
where rprt_key = v_rprt_key;

end p_actvty_cmptncy_asmt;

   exception

   when others then

   dbms_output.put_line('table problem');
   dbms_output.put_line(vsql);
   dbms_output.put_line(sqlcode);
   dbms_output.put_line(sqlerrm);

   return;

   end;

Tom,

Just out of curiosity, where does dbms_output go when it's coming from a application client, as opposed a client like sqlplus or IDE GUI tools that can interpret it?

(I have a feeling the answer to this question has a lot to with why you're not a fan of this approach ;)

The above approach using

when others then.....

dbms_output.....

No raise.

ops$tkyte%ORA11GR1> drop table t;

Table dropped.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> create table t ( x int );

Table created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> begin
  2          insert into t values ( 1 );
  3          dbms_output.put_line( 1/0 );
  4          insert into t values ( 2 );
  5  exception
  6          when others then
  7                  dbms_output.put_line( 'bummer, eh' );
  8  end;
  9  /
bummer, eh

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> select * from t;

         X
----------
         1

ops$tkyte%ORA11GR1> truncate table t;

Table truncated.

ops$tkyte%ORA11GR1> begin
  2          insert into t values ( 1 );
  3          dbms_output.put_line( 1/0 );
  4          insert into t values ( 2 );
  5  end;
  6  /
begin
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 3


ops$tkyte%ORA11GR1> select * from t;

no rows selected

Whoa what happened with that first insert? Why didn't it rollback?

Yeah i'd say that's kind of a big deal. As if I really needed another reason to avoid it, that's dangerous. If there were to be a commit in there....(which people do all the time, our vendor code has tons of stuff like that...)

Hey Tom,

Sorry mate, but I didn't quite understand your reply.

Should I add another exception method to the bottom of the code where i'm accessing the ref cursor? Or is it that the Exeception is actually causing the problem.

Thanks

Hey Tom,

Ok I took out all of the exception handling. I pinpointed the error down to the following code...Can you see any problems with the way I am handling the re cursor?...Thanks

PROCEDURE "P_ACTVTY_CMPTNCY_ASMT"
(
t01_cursor in out reports.t01%ROWTYPE,
actvty_cmptncy_asmt_parameter in number
)

open t01_cursor
for
select *
from reports.t01
where rprt_key = v_rprt_key
order by sp_lrng_actvty_id,
line_nr,
full_nm;

delete
from reports.t01
where rprt_key = v_rprt_key;

ops$tkyte%ORA10GR2> create table t as select * from all_users where rownum <= 5;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure p( c in out sys_refcursor )
  2  as
  3  begin
  4          open c for select * from t;
  5          delete from t;
  6  end;
  7  /

Procedure created.

ops$tkyte%ORA10GR2> variable x refcursor
ops$tkyte%ORA10GR2> exec p(:x)

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> print x

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
BIG_TABLE                              58 14-DEC-05
DIP                                    19 30-JUN-05
TSMSYS                                 21 30-JUN-05
LOTTOUSER                              65 30-DEC-05
MDDATA                                 50 30-JUN-05

Thanks Tom and to everone else for their input,

I did a similar test in Toad with the following code. There were no error whatsoever after the exception were removed.

I think it's definitely a crystal report problem. Unfortunately the problem with crystal report is that the errors it throws out are so generic.

Thanks again for the help.

begin
P_ACTVTY_CLNT_ATNDNC(r,661600,0);

loop
fetch r into en;
exit when r%notfound;
dbms_output.put_line(en.RPRT_KEY);
dbms_output.put_line(en.SP_LRNG_ACTVTY_ID);
dbms_output.put_line(en.LRNG_ACTVTY_ID);
dbms_output.put_line(en.LINE_NR);
dbms_output.put_line(en.AMET_CLNT_ID);
dbms_output.put_line(en.CLNT_ID);
dbms_output.put_line(en.CLNT_ASMT_ID);
dbms_output.put_line(en.AUTRSG_PGM_CD);
dbms_output.put_line(en.FULL_NM);
dbms_output.put_line(en.SEX_ALPHA_CD);
dbms_output.put_line(en.CTGRY_CD);
dbms_output.put_line(en.ASMT_STRT_DATE);
dbms_output.put_line(en.ASMT_END_DATE);
dbms_output.put_line(en.SESNCNT);
dbms_output.put_line(en.ATTENDANCE_LAST_UPDATED);
end loop;
close r;

end;

Ok,

So it was a driver problem. For anyone who ever has a problem connecting to a procedure via the ODBC(RDO) driver. Spewing out the following error:

"Query Engine Error : 'HY0000;[DataDirect][ODBC Oracle Driver][Oracle] ORA-06550: line 1, column 8 PLS-00306: wrong number or types of arguments in call to 'ProcedureName' ORA-06550: line 1, column 8: PL/S'"

Refer to this link:

http://technicalsupport.businessobjects.com/KanisaSupportSite/search.do;jsessionid=553554F5B702FD3E39A8BC01E144ABDE?cmd=displayKC&docType=kc&externalId=c2012126&sliceId=&dialogID=24634585&stateId=1%200%2024636132

Hope it will save you alot of headache.

Hi Tom,

This is regarding the ref_cursors -

I have been reading your book 'Effective Oracle by design' - in the chapter -'Effective PL/SQL ..'
You explained about the use of ref_curosrs - the difference between the time and the memory usage when we return the result set using 'COLLECTIONS' and 'REF_CURSOR'

Please correct me if my understanding is correct about the example you used in your book -

1.The PGA used is high in case of using collections - because first we get the data from database to collection variables in PL/SQL heap (PGA) and from there we send the data on the network to the Jave client - that's why the time and the memory usage (4.5 MB) is high compared to ref_cursor (244K) in your example.in case of ref_cursors - we just send the data directly over the network to the Java client , we dont store the data in the PGA.
I believe 244 KB is - just a minimal memory used in the PGA just for 'cursor' creation/managing … correct - ?
2.Ref_Cursor is a just a handle to the result set - Pl/Sql does not fetch the data - It will just pass the 'handle/pointer' to the Java client over the network , now Java will assign the ref_cursor pointer received from pl/sql to its own cursor defined in java program.
Java will fetch the data and its responsibility for the Java client to 'close' the cursor.
There are TWO cursors (pl/sql and java ) and both point to the same cursor area in PGA-Is it correct ?
3.If Java closes its own cursor - Will the Pl/sql ref_cursor also be automatically closed ? Don’t we need to close the ref_cursor in the PL/SQL ?

4.We always try to put SQL in pl/sql instead of in Java - becuase Pl/sql has some good benefits - such as -pl/sql cache, every static sql uses bind variables by default, and store the compiled code/logic in 'DATABASE' etc .. for the same reasons we ceate a ref_cursor(SQL) in pl/sql and pass the handle to the Java - Am I correct ? otherwise we can create cursors in Java only using the SQL .

Thanks a million .

MANY MANY THANKS TOM ,you are god to oracle users
because we are learning things from you CLEARLY and increasing our knowledge, so that we get a better job with better salary and live a good life - that's why you are said to be 'god' for oracle users..

so there would be two handles to the same cursor - correct ?
and if we close the cursor in java - automatically it is said to be closed in pl/sql .

Is it correct to assume that we fetch the data REALLY when we execute/fetch in JAVA from database

pl/sql
======

1.declare ref_cursor handle
2.open ref_cursor ,associate with some sql
(here the RESULT SET is 'pre-ordained' ,it means the database knows that the result-set,notes the 'SCN'/time )

3.return the ref_cursor handle to java:

Java:
=====
here java will execute/fetch -
then Server process will respond and fetch the data from database (the data modified/committed <=SCN) and give over the network to Java DIRECTLY .

ok, thanks Tom, that's clear now - one final doubt

is the result set is 'pre-ordained' when we OPEN the cursor itself ,i mean Server process will get the data <= SCN (assume SCN is - the time when the cursor is 'OPENED' )- Am I correct here ?

ops$tkyte%ORA11GR2> create table t as select * from all_users where rownum <= 5;

Table created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> variable x refcursor
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> begin open :x for select * from t; end;
  2  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> delete from t;

5 rows deleted.

ops$tkyte%ORA11GR2> commit;

Commit complete.

ops$tkyte%ORA11GR2> print x

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SYS                                     0 13-AUG-09
SYSTEM                                  5 13-AUG-09
OUTLN                                   9 13-AUG-09
DIP                                    14 13-AUG-09
ORACLE_OCM                             21 13-AUG-09

Hi Tom,

Thanks a lot for your valuable time and efforts!

I was extremely happy when I read this sentence of yours that a ref cursor is just a cursor. a ref cursor is just a cursor. a ref cursor is just a cursor.... (keep saying it over and over ;) as I felt the same way when I understood the ref cursor but this actually led me to a confusion. Eventually when both are pointers, why can't we assign a static cursor to a ref cursor variable? I might be missing something very basic in an attempt to fathom complex.

Consider the following procedure. I have defined a static cursor (which holds a pointer) and a ref cursor variable (which can also hold a pointer). Logically, shouldn't we be able to assign a value of one pointer variable to the other?

CREATE OR REPLACE PROCEDURE sv_test IS
CURSOR c1 IS
SELECT LEVEL FROM dual CONNECT BY LEVEL <= 10;
c2 SYS_REFCURSOR;
BEGIN
OPEN c1;
c2 := c1;
END;

I get the following error when I compile this procedure.

Error: PLS-00382: expression is of wrong type
Line: 7
Text: c2 := c1;

Error: PL/SQL: Statement ignored
Line: 7
Text: c2 := c1;

Error: Hint: Value assigned to 'c2' never used in 'sv_test'
Line: 7
Text: c2 := c1;

Looking forward to your explanation.