Re: pl\sql. Test API?

On 21 Jul 2005 12:25:52 -0700, jimi_xyz@xxxxxxxxxxx wrote:

>Dear Mr. Bakker,
>I knew you would dig into one of my posts, sooner or later. Anyways
>Where exactly should i put the cursor declaration? Also, im using a
>temporary table because im not quite sure on how to send the score and
>projectID number back to the front ASP program? Im open to any ideas
>with some examples. One last thing how do i go about placing the
>VARCHAR search_string in the where contains clause, instead of the hard
>wired word 'research'?
>
>here is my program updated 7-21-05 (3:25 New York) also instead of
>erros i get warnings when i run it through sql plus,
>example...
>SQL>@SUBMITQUERY_API
>-------- ------------------------------------------
>9/5 PLS-00103: Encountered the symbol "CURSOR"
> the following:
> := . ) , @ % default character
>
>
>CREATE OR REPLACE PACKAGE SUBMITQUERY_API IS
>
>PROCEDURE query (
> in_search_string IN VARCHA2,
> out_error_code OUT NUMBER,
> out_error_message OUT VARCHAR2
> CURSOR c1 IS
> select score(1) score, projectid
> from project
> where contains(objectives, 'research', 1) > 0;)
>;
>/
>--------------------------------------------------------------------------------
>CREATE OR REPLACE PACKAGE BODY SUBMITQUERY_API IS
>
>CONST_PACKAGENAME CONSTANT VARCHAR2(15) := 'submitquery_api';
>
>PROCEDURE query(
> in_search_string IN VARCHAR2,
> out_error_code OUT NUMBER,
> out_error_message OUT VARCHAR2
> CURSOR c1 IS
> select score(1) score, projectid
> from project
> where contains(objectives, 'research', 1) > 0;)
>IS
>BEGIN
>
>DELETE FROM list; --make table list null
> OPEN c1;
> LOOP
> FETCH c1 INTO score_temp, projectid_temp;
> INSERT INTO list(id, score)
> VALUES(projectid_temp, score_temp);
> EXIT WHEN c1%NOTFOUND;
> END LOOP;
> CLOSE c1;
> COMMIT;
>
>out_error_code := 0;
>out_error_message := SQLERRM;
>
>EXCEPTION
>
> WHEN OTHERS THEN
> out_error_code := SQLCODE;
> out_error_message := '[' || CONST_PACKAGENAME || '.query] ' ||
>SQLERRM;
> ROLLBACK;
>
>END query;
>END SUBMITQUERY_API;
>/
>--------------------------------------------------------------------------------
> Also I have never programmed PL\SQL this day two of working with
>PL\SQL, so go easy on me Sybrand
>
>Thanks in advance,
>Jimmie


The usual method to return a resultset to a calling program is using a
REF CURSOR.
Also your parameter declaration is syntactically not correct (as you
already noticed)
so that would turn the package body into

CREATE OR REPLACE PACKAGE BODY SUBMITQUERY_API IS
>
CONST_PACKAGENAME CONSTANT VARCHAR2(15) := 'submitquery_api';


type curtype is ref cursor;

PROCEDURE query(
p_objective IN VARCHAR2,
cv IN OUT curtype,
out_error_code OUT NUMBER,
out_error_message OUT VARCHAR2

IS
BEGIN
out_error_code := 0;
out_error_message := '';

OPEN cv
for
'select score(1) score, projectid from project '
|| ' where contains(objectives, :b1, 1) > 0' using p_objective;

EXCEPTION
when no_data_found then null;
WHEN OTHERS THEN
out_error_code := SQLCODE;
out_error_message :=
'[' || CONST_PACKAGENAME || '.query] '||SQLERRM;
END query;
END SUBMITQUERY_API;
/

Your calling asp program is responsible for looping through the
resultset.

In sql*plus you can test by the following stub


variable cv ref cursor;
declare
errcode number(10);
errmsg varchar2(255);
begin
subqmitquery_api.query('research', :cv, errcode, errmsg);
end;
/
print cv


REF CURSORs are documented in the PL/SQL reference manual, and they
are probably explained at http://asktom.oracle.com and they have been
discussed here over and over and over again. The problem with people
like you is that they never seem to do any research prior to posting
and always force people to respond to FAQs.

Hth

--
Sybrand Bakker, Senior Oracle DBA
.

Relevant Pages

  • Re: Dealing with a new codebase
    ... Once it had finished updating, I felt like I was watching a movie every time I moved the cursor or typed anything. ... Most importantly is that when you put the cursor on a symbol, the bar at the top shows the declaration. ... If you hover over a symbol, the down arrow appears, clicking it gives you a menu with "Find References" in it. ...
    (microsoft.public.vc.mfc)
  • Re: Is there a cleaner way to do this?
    ... The cursor declaration uses the same SELECT ... SET sentmail = null ... > update SENDCALL70C ...
    (microsoft.public.sqlserver.programming)
  • RE: More on closing Oracle ref cursors
    ... cursor is closed, but you're saying they stay in existence. ... I created a statement handle to return a ref cursor to 'select object_name from user_objects'. ... I have searched metalink, google, and otn for some reference on this issue and found nothing that indicates there is a way to close a ref cursor object prior to disconnect. ...
    (perl.dbi.users)
  • Re: Orphan REF cursor returned to VB.NET from Oracle PL/SQL stored procedure?
    ... > How do you loop through the cursor? ... Not true - the cursor is but a pointer to a memory location in the PGA (for ... memory - destroying all cursor infor, and invalidating all REF CURSOR ... are Oralce packages to programatically recover the memory. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: C#-Oracle Ref Cursor?
    ... How can i encapsulate my ref cursor in a stored procedure? ... But i have a problem about maximum number of opened cursors. ...
    (microsoft.public.dotnet.framework.adonet)