Re: pl\sql. Test API?

On 21 Jul 2005 12:25:52 -0700, jimi_xyz_at_hotmail.com 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