Re: pl\sql. Test API?

Comments embedded

On 21 Jul 2005 11:21:40 -0700, jimi_xyz_at_hotmail.com wrote:

>OK, I have created a package. Im kind of new at this stuff so stupid
>mistakes are going to happen. My question is how do you check to see if
>your API code is working correctly. Whenever i run it from sql plus,
>the procedure is created with errors..
>

So it didn't compile. Packages need to be compiled before they can run. You need to fix the compilation errors first.

>
>SQL> @SUBMITQUERY_API
>
>Warning: Package created with compilation errors.
>
> 44
>
>SQL> show errors
>Errors for PACKAGE SUBMITQUERY_API:
>
>LINE/COL ERROR
>--------
>-----------------------------------------------------------------
>6/13 PLS-00103: Encountered the symbol "OUT_PROJECTID" when
>expecting
> one of the following:
> := . ) , @ % default character
> The symbol "," was substituted for "OUT_PROJECTID" to
>continue.
>
>8/0 PLS-00103: Encountered the symbol "end-of-file" when expecting
> one of the following:
> end function package pragma private procedure subtype type
> use <an identifier> <a double-quoted delimited-identifier>
> form current cursor
>
>ALso im not to sure where i should put the declare section in the
>procedure. I need the cursor to grab the rows of the table and put it
>into another table along with the score of each query. Here is what i
>have so far...
>
>CREATE OR REPLACE PACKAGE SUBMITQUERY_API IS
>
>PROCEDURE query (
> in_search_string IN VARCHA2,
> out_score OUT NUMBER

there is missing a , after NUMBER. VARCHA2 needs to be VARCHAR2

> out_projectid OUT NUMBER)
>;
>/

A package spec definition concludes with END <package name>;
/

>--------------------------------------------------------------------------------
>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)
>IS

There is *no* DECLARE keyword starting the declaration section in procedures.

>
>DECLARE
> CURSOR c1 IS
> select score(1) score, projectid
> from project
> where contains(objectives, 'research', 1) > 0;
>
>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;
>
>--------------------------------------------------------------------------------
>
>Please any help will be appreciated,
>Thank you,
>Jimmie

Why do you think you need to make your application unscalable and store everything in a temporary table?

Forget *ALL* you learned using other sql 'implementations' !!!

--
Sybrand Bakker, Senior Oracle DBA