Return DataSet From PL/SQL Procedure in Oracle

create view/temporary table, insert the result there

btw. sample above is for dbexpress-components, but should work for ado and bde similar

We usually give  great answers From Meikl Comments.

oops, forgot a important info

the pl/sql declaration of cursor_type was (if i had it in mind correctly)

cursor_type REF CURSOR;

is this available For DBgrid Meikl ?

I accepted Your answer with 50 Extra Points if I had A question
about this issue.
i add it to this thread.

few, thats was fast

did you tried it out, it works maybe not in some cases, don't  know

what for an enviroment do u use (delphi version/oracle/connectiontype)?

>is this available For DBgrid Meikl ?
not directly,because its a readonly resultset with no go back handycap,
but you could load it into a memorydataset/clientdataset and use a dbgrid on
this dataset

what additional question(s) di have?

meikl ;-)

>what for an enviroment do u use (delphi Version/oracle/connectiontype)?

Delphi 7
Oracle 8i
Connection Type ( ODAC)

i believed that points not important For you and
Continue this question in advance if i have a problem about it.

>i believed that points not important For you and
>Continue this question in advance if i have a problem about it.

yep, ok, it depends on my time and my enviroment,
but don't hestitate to ask

>Connection Type ( ODAC)
what is odac, or do you mean odbc?

meikl ;-)

ODAC:
Oracle Data Access Component From www.crlab.com/odac site
I m sure that You Hear From this component From Mnasman Comment.
Do you foget it ?

well,
currently i have no experience with the odac-components,
but i will evaluate it soon

meikl ;-)

Hi,
is this systax is suitable For My Procedure ?
I get Error "ORA-00900" with below syntax:
>
cursor_type IS REF CURSOR;
PROCEDURE khodro.get_only_cursor( pi_statement IN VARCHAR2, pio_result IN OUT  cursor_type)
IS
BEGIN
 open pio_result for Select * from khodro.Members where Name Like Pi_Statement ;
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END khodro.get_only_cursor;
<

Ps: a mistake :
My oracel Version is 8.0.5.0

ORA-00900 -> invalid SQL statement

what is the content of pi_statement?

at first i writed this PL/Sql procedure in sql work sheet.
i dont do anything else.
How should i do for solving this problem?

you mean this eror comes up, if you compile this procedure?

yes.

well, will try it tomorrow, usual it looks ok

btw. is this procedure within a package?

>>is this procedure within a package?
No, only with Procedure.
please Try With ODAC Component If possible For you.

well ok,

you should place it into a package, because the cursor-declaration must be in a package as well,
hope i get the the package correct from scratch

Create or Replace
Package YourPackageNameHere
as

cursor_type IS REF CURSOR;
PROCEDURE khodro.get_only_cursor( pi_statement IN VARCHAR2, pio_result IN OUT  cursor_type);

end;

Create or Replace
Package Body YourPackageNameHere
as

PROCEDURE get_only_cursor( pi_statement IN VARCHAR2, pio_result IN OUT  cursor_type)
IS
BEGIN
  --i keep here the khodro-prefix, guessing its the owner and no synonyms are existing
 open pio_result for Select * from khodro.Members where Name Like Pi_Statement ;
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END get_only_cursor;

end;

was just from head, typos possible

well, will try to get it work with ODAC

meikl ;-)

sorry, got no time today, any results about the package above?

Hi,
with your Assisting and Some changing I gave A good Result
I continued these Steps
1:
Create or Replace Package CursorExam
as
Type cursor_type IS REF CURSOR;
PROCEDURE get_only_cursor( pi_statement IN VARCHAR2, pio_result IN OUT  cursor_type);
end;
----------
Compile

2:
Create or Replace Package Body CursorExam
as
PROCEDURE get_only_cursor( pi_statement IN VARCHAR2, pio_result IN OUT  cursor_type)
IS
BEGIN
open pio_result for Select * from Members where members.Name Like Pi_Statement ;
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END get_only_cursor;
end CursorExam;
-----------
compile

3:
set autoprint on;

VARIABLE X REFCURSOR;
Execute CursorExam.Get_only_cursor('hamid',:X)
-----------

Have A Proper Result On Sql Work SheeT;

----------------------------------

Finally :
Exam with ODAC and Give a good Result.


--------------------------->
But I Didnt know Why When I Mixed PL/Sql of 1 and 2 Steps in one PL/Sql(and Compiling This PL/Sql)
I gave a this Warning :
"MGR-00072: Warning: PACKAGE CURSOREXAM created with compilation error"

what is the reson of this warning?     <--------------------

thanks.

I have a question :
does Ref cursor has a bad effect on memory Usage ?
We dont Release Ref Cursor Type in procedure.
do any way Existed For freeing these cursors after sending To client
Computers.

>what is the reson of this warning?    

if you have package spec and package body in one script,
then you must signal the finish of one segment with a slash /
like


Create or Replace Package CursorExam
as
Type cursor_type IS REF CURSOR;
PROCEDURE get_only_cursor( pi_statement IN VARCHAR2, pio_result IN OUT  cursor_type);
end;
/

Create or Replace Package Body CursorExam
as
PROCEDURE get_only_cursor( pi_statement IN VARCHAR2, pio_result IN OUT  cursor_type)
IS
BEGIN
open pio_result for Select * from Members where members.Name Like Pi_Statement ;
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END get_only_cursor;
end CursorExam;
/

>does Ref cursor has a bad effect on memory Usage ?
>We dont Release Ref Cursor Type in procedure.

no, the cursor is released in delphi,
if the storedproc-object uses the close method or is freed,
within pl/sql you have to close the cursor explicitly like

Close X;

meikl ;-)

OK ,
please Add a comment For Awarding Extra Points To you
in this link:
https://www.experts-exchange.com/questions/21030199/Points-For-krertzschmar.html

Thanks For your Time.

>if the storedproc-object uses the close method or is freed,
within pl/sql you have to close the cursor explicitly lik
Close X;
<
I dont Know Where is the Exact location of  Close X instruction ?
Can you Help Me ?

any Feed Back From?
Please Fill Upper Link with a comment.

well, don't know the frontend u use,
there may possible other instructions

usual it is used in a pl/sql block like

declare
  cursor x as select * from atable;
  atable_rec atable%rowtype;
begin
  open x;
  loop
    fetch x into atable_rec;
    exit when x%nodatafound;
    -- some processing with atable_rec
    dbms_output.put_line(atable_rec.afieldname);
  end loop;
  close x;
exception
  when other then
    if x%isopen then
      close x;
    raise;
end;

just from head, typos possible
hope it helps

meikl ;-)

thanks,
You mean that it is better to return A dataset in
Temproray Table( or...) instead the Direct returning Cursor.

this depends on that what you need to do,

if it only for display-issues, then you can use a direct-cursor,
if you need a editing issue, then a temp-table would be better
(because a cursor is readonly)

meikl ;-)