Re: pl\sql. Test API?

Sybrand Bakker wrote:
> 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

Ok Sybrand, one i have done research, as i said iv'e been working on this project for two days, and two days only. Another thing I don't beleive I was ever at your home forcing you to do anything you didn't want to do. "The problem with people like you", whats that about? I said go easy on me, I learn best from examples, and this is the place to get the example that suites me best. I don't beleive i forced you to do anything you didn't want to do. As i said before i am very new to oracle programming, exactly a two day veteran, not long at all, and iv'e been doing allot of research on both PL\SQL, SQL PLUS, ASP, and Oracle. If i can't figure out the problem, I am going to post the groups, your lecture is not going to stop that.

But anyways thanks for the example, its much appreciated. From The example you gave me, i see you removed the temp. table completely. If im correct it sends a row from the databse one at a time to the ASP program?

Thanks everyone,
Jimmie Received on Thu Jul 21 2005 - 16:54:22 CDT