Re: how fetch into output host array

Hi Umesh,

  I am recollecting my thoughts from my earlier days in Pro*C   programming.

  Let me see...

  tempid has a integer datatype and has a array declaration. Normally,   when we have to use a array variable value in the WHERE condition,   we declare another variable (say) db_tempid integer.

  Assign the array variable value to the db_tempid variable.

• Note : you said that you are expecting 3 rows... with the INTO clause it is possible to fetch only one row at a time. If the query returns more than one row, it will result in "TOO MANY ROWS" error.

  Alternatively, if you want to get all the 3 rows in one go, declare a   cursor and using FETCH, get the details into destination variables.   Depending on the array size of the destination variables, it would be   possible to get it in one fetch or more than one fetch...

  This should work...

Regards
Rajagopal Venkataramany

----Original Message Follows----
From: <U.CHANDER_at_ponl.com> ("Umesh Chander Sharma") Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Subject: how fetch into output host array Date: Tue, 16 May 2000 06:53:52 -0800

Hi Gurus,

I am stuck in a PRO*C problem, ver. 1.5, Oracle 7.1.6 .

Have following table :

EMP

---
empid   number        primary key
name    varchar2(10)  not null

and have declared following host variable in our PRO*C program

int tempid^3];
int A_empid^3];
varchar2 A_name^3]^11];

tempid contains following values
tempid^0]=1;
tempid^1]=2;
tempid^2]=3;

above EMP table contains following records :

empid   name
-----   ----
1       jit
2       abv
3       xyz
4       jfg

PRO*C program contains following SQL :

select empid,name into :A_empid,:A_name from EMP where empid = :tempid ;

It should return 3 rows but it is returning none ?

Please advise what seems to have gone wrong as it is our requirement to run
this SQL once for all elements of tempid.

Any suggestion is highly appreciated.

TIA.

Regards,
Umesh
--
Author: Umesh Chander Sharma"
   INET: U.CHANDER_at_ponl.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

________________________________________________________________________