Re: ASP + PL\SQL problems

From: <jimi_xyz_at_hotmail.com>
Date: 28 Jul 2005 05:20:25 -0700
Message-ID: <1122553225.294175.274960@g44g2000cwa.googlegroups.com>

Sybrand Bakker wrote:
> On 27 Jul 2005 12:37:44 -0700, jimi_xyz_at_hotmail.com wrote:
>
> >I am trying to pass an array from ASP into the pl\sql api.. which then
> >is put into a variable of type table, but i keep getting an error. Any
> >ideas here is my code.
>
> Care to disclose which error? Or should we just guess and you will
> send some beer, when we guessed right?
>
>
> --
> Sybrand Bakker, Senior Oracle DBA

Mr. Bakker,
Sorry, as soon as i posted to the group i noticed i forgot the error message, also I made a couple of changes to the post i made earlyer, so i'll just repost. You figure out whats wrong with my code, and i'll send you your favorite type of beer or Vodka :) Thanks,
Jimmie

error:
 APIIntFace - SUBMITQUERY_API.query]:
[424]
[Object required]

ORA-01008: not all variables bound

ASP CODE----------------------------------------

sqlproc = "SUBMITQUERY_API.query"

sqlparams = array( "word_to_search", searchstring, ORAPARM_INPUT,

   ORATYPE_VARCHAR2, _

		"location_array",	loc,		ORAPARM_INPUT,	ORATYPE_TABLE, _
		"cv",    		null,    	ORAPARM_OUTPUT,   ORATYPE_CURSOR, _
  		"out_error_code",    	0,    		ORAPARM_OUTPUT,   ORATYPE_NUMBER, _
   		"out_error_message",    "",    		ORAPARM_OUTPUT,

ORATYPE_VARCHAR2 ) sqlretvals = APIIntFace( sqlproc, gobjConn, sqlparams, true )

PL\SQL----------------------------------------

CREATE OR REPLACE PACKAGE SUBMITQUERY_API AS
type curtype is ref cursor;
type charArray is table of VARCHAR2(24);

PROCEDURE query(

                        word_to_search IN VARCHAR2,
						location_array IN charArray,
                        cv IN OUT curtype,
                        out_error_code OUT NUMBER,
                        out_error_message OUT VARCHAR2);

END;
/
show errors

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

---

PROCEDURE query(

                        word_to_search IN VARCHAR2,
						location_array IN charArray,
                        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 distinct score(1) score, projectid from project ' || ' where contains(ctx_dummy, :b1, 1) > 0 ' using word_to_search;

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;
/

show errors Received on Thu Jul 28 2005 - 07:20:25 CDT