Re: pl\sql. Test API?

From: <jimi_xyz_at_hotmail.com>
Date: 21 Jul 2005 12:25:58 -0700
Message-ID: <1121973958.215739.171360@g49g2000cwa.googlegroups.com>

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 Received on Thu Jul 21 2005 - 14:25:58 CDT