Handling Text Types in Stored Procedures

Handling Text Types in Stored Procedures

Post by Steve Rose » Tue, 16 May 1995 04:00:00

I was wondering if somebody might be able to sched some light for me on
handling text types in stored procedures.  We are trying to build
a library of similar procedures to perform specific operations for
each table in our database.  These routines would be called from
4GL applications and ESQL/C.

The problem is that (IMHO), Sybase doesn't handle text types very well.
You cannot pass a variable of type text to a stored procedure and
you cannot operate on variables of type text inside.  You have
to do it using text pointers which is a major pain and very awkward.

For instance, how would a user pass in a piece of text for insertion
into a table?  Seems to me he would have to create a temporary table,
store the text in there and pass the textptr to that row to be read.

The writetext statement does not appear to accept variables as
the last parameter containing the actual text to be written, only
constants. Huh???  Am I missing something here?


1. How to handle Table%rowtype of store procedure return type

I Got a Function which return row of table,
is it possible to retrieve those return into a ResultSet ?

OracleCallableStatement l_stmt = (OracleCallableStatement)
connection.prepareCall("{ call ?:=GET_HOTELS(?) }");

l_stmt.registerOutParameter(4, ??? ,???);
                                                  ^ ^

What I should put in above parameters???
There is no OracleType.RowType!!

======== Store Procedure Code ===============
create or replace
function get_hotelS(pi_id in number)
return hotels%rowtype
cursor cr_hotels is
select ID
from hotels
where id = pi_id;
rec_hotels hotels%rowtype;
open cr_hotels;
fetch cr_hotels into rec_hotels;
if cr_hotels%notfound then
rec_hotels.ID := pi_id;
rec_hotels.name := null;
end if;
close cr_hotels;
return rec_hotels;

2. Multi-Protocol Encryption and VB

3. How to handle return type of Table%RowType in Store Procedure by JDBC

4. How much should we get paid?

5. How to handle Text Columns in Stored Procedures?

6. Transactions with 2nd CDatabase connection?

7. Stored Procedures - Using data type text

8. Error 10061???

9. text data type support for output parameter for stored procedure

10. Example of stored procedure with OUTPUT parameter of type TEXT

11. Stored Procedures and TEXT data type

12. Inserting TEXT data type through Stored Procedure

13. stored procedures and text data type