Possible Feature
Paul Watson proposed a feature: SPL RETURNING LIKE table.column, since LIKE works for parameters and variables but not for return values. Vitorino Ribeiro agreed, noting column size changes force dropping and recreating procedures and permissions, and also wanted CREATE OR REPLACE. DA Morgan pointed to Oracle's %TYPE and %ROWTYPE with examples; Paul found the first example irrelevant. No Informix answer was given.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
I haven't checked the manuals but I don't believe we can do this :) Just done a series of big DB changes and needed to recompile all the SPL code. I use 'LIKE' for the input parameters and local variables but you can not do a RETURNING LIKE <tabname>.<colname> syntax Would anyone else find that useful??? Cheers Paul Paul Watson Tel: +44 1414161772 Mob: +44 7818003457 Web: www.oninit.com GO FURTHER with DB2 GET THERE FASTER with Informix. Attend the IDUG 2006 European Conference. Vienna, Austria. 2-6 October 2006 Visit http://www.iiug.org/conf for more information.
You're right, you can't do that. And I agree with you: Having the RETURNING LIKE <tabname>.<colname> would be a great feature: If I have a column of datatype CHAR(20) and I later change it to, for example, CHAR(40), and I have a Stored Procedure or Function that returns a value mapped to that column, then I have to: a.) drop the procedure/function. b.) recreate the Stored Procedure or Function with the changed returning clause ("RETURNING CHAR(40);" instead of "RETURNING CHAR(20);"). c.) recreate the permissions of the Stored Procedure or Functuion. ... by the way the "CREATE OR REPLACE PROCEDURE / FUNCTION / VIEW / ..." syntax (like Oracle has) would be a greate feature, too. Paul Watson escreveu: > I haven't checked the manuals but I don't believe we can do this :) > Just done a series of big DB changes and needed to recompile all the SPL > code. I use 'LIKE' for the input parameters and local variables but you > can not do a > > RETURNING LIKE <tabname>.<colname> syntax > > Would anyone else find that useful??? > > Cheers > Paul > > Paul Watson > Tel: +44 1414161772 > Mob: +44 7818003457 > Web: www.oninit.com > > GO FURTHER with DB2 > GET THERE FASTER with Informix. > Attend the IDUG 2006 European Conference. > Vienna, Austria. 2-6 October 2006 > Visit http://www.iiug.org/conf for more information.
Vitorino Ribeiro wrote:
> You're right, you can't do that.
>
> And I agree with you:
>
> Having the RETURNING LIKE <tabname>.<colname> would be a great feature:
>
> If I have a column of datatype CHAR(20) and I later change it to, for
> example, CHAR(40), and I have a Stored Procedure or Function that
> returns a value mapped to that column, then I have to:
> a.) drop the procedure/function.
> b.) recreate the Stored Procedure or Function with the changed
> returning clause ("RETURNING CHAR(40);" instead of "RETURNING
> CHAR(20);").
Rather than reinventing the wheel why not copy what Oracle many years
ago and define variables as <schema_name>.<table_name>.<column_name>%TYPE.
If the column changes in data type, length, precision, and/or scale the
variable does too?
Oracle also allows the definition of record variables as %ROWTYPE where
the array dynamically matches a row in a table or cursor.
For example this snippet:
CREATE OR REPLACE PROCEDURE demo IS
TYPE myarray IS TABLE OF parent%ROWTYPE;
l_data myarray;
CURSOR r IS
SELECT part_num, part_name
FROM parent;
BEGIN
OPEN r;
LOOP
FETCH r BULK COLLECT INTO l_data LIMIT 1000;
FORALL i IN 1..l_data.COUNT
INSERT INTO child VALUES l_data(i);
EXIT WHEN r%NOTFOUND;
END LOOP;
COMMIT;
CLOSE r;
END demo;
/
Note how myarray was defined. I can alter the parent table and
the procedure does not break.
BTW: This construct easily inserts 100,000 records per second on a laptop.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
As interesting as that code snippet was I don't see the relevance to my
question and my requirement of having the returning values defined
against column datatypes. Your code doesn't appear to return anything.
Paul Watson
Tel: +44 1414161772
Mob: +44 7818003457
Web: www.oninit.com
GO FURTHER with DB2
GET THERE FASTER with Informix.
Attend the IDUG 2006 European Conference.
Vienna, Austria. 2-6 October 2006
Visit http://www.iiug.org/conf for more information.
-----Original Message-----
From: DA Morgan [mailto:damorgan@psoug.org]
Posted At: 19 August 2006 19:12
Posted To: comp.databases.informix
Conversation: Possible Feature
Subject: Re: Possible Feature
Vitorino Ribeiro wrote:
> You're right, you can't do that.
>
> And I agree with you:
>
> Having the RETURNING LIKE <tabname>.<colname> would be a great
feature:
>
> If I have a column of datatype CHAR(20) and I later change it to, for
> example, CHAR(40), and I have a Stored Procedure or Function that
> returns a value mapped to that column, then I have to:
> a.) drop the procedure/function.
> b.) recreate the Stored Procedure or Function with the changed
> returning clause ("RETURNING CHAR(40);" instead of "RETURNING
> CHAR(20);").
Rather than reinventing the wheel why not copy what Oracle many years
ago and define variables as
<schema_name>.<table_name>.<column_name>%TYPE.
If the column changes in data type, length, precision, and/or scale the
variable does too?
Oracle also allows the definition of record variables as %ROWTYPE where
the array dynamically matches a row in a table or cursor.
For example this snippet:
CREATE OR REPLACE PROCEDURE demo IS
TYPE myarray IS TABLE OF parent%ROWTYPE; l_data myarray;
CURSOR r IS
SELECT part_num, part_name
FROM parent;
BEGIN
OPEN r;
LOOP
FETCH r BULK COLLECT INTO l_data LIMIT 1000;
FORALL i IN 1..l_data.COUNT
INSERT INTO child VALUES l_data(i);
EXIT WHEN r%NOTFOUND;
END LOOP;
COMMIT;
CLOSE r;
END demo;
/
Note how myarray was defined. I can alter the parent table and the
procedure does not break.
BTW: This construct easily inserts 100,000 records per second on a
laptop.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Paul Watson wrote:
> As interesting as that code snippet was I don't see the relevance to my
> question and my requirement of having the returning values defined
> against column datatypes. Your code doesn't appear to return anything.
>
>
> Paul Watson
> Tel: +44 1414161772
> Mob: +44 7818003457
> Web: www.oninit.com
>
> GO FURTHER with DB2
> GET THERE FASTER with Informix.
> Attend the IDUG 2006 European Conference.
> Vienna, Austria. 2-6 October 2006
> Visit http://www.iiug.org/conf for more information.
>
>
>
> -----Original Message-----
> From: DA Morgan [mailto:damorgan@psoug.org]
> Posted At: 19 August 2006 19:12
> Posted To: comp.databases.informix
> Conversation: Possible Feature
> Subject: Re: Possible Feature
>
>
> Vitorino Ribeiro wrote:
>> You're right, you can't do that.
>>
>> And I agree with you:
>>
>> Having the RETURNING LIKE <tabname>.<colname> would be a great
> feature:
>> If I have a column of datatype CHAR(20) and I later change it to, for
>> example, CHAR(40), and I have a Stored Procedure or Function that
>> returns a value mapped to that column, then I have to:
>> a.) drop the procedure/function.
>> b.) recreate the Stored Procedure or Function with the changed
>> returning clause ("RETURNING CHAR(40);" instead of "RETURNING
>> CHAR(20);").
>
> Rather than reinventing the wheel why not copy what Oracle many years
> ago and define variables as
> <schema_name>.<table_name>.<column_name>%TYPE.
>
> If the column changes in data type, length, precision, and/or scale the
> variable does too?
>
> Oracle also allows the definition of record variables as %ROWTYPE where
> the array dynamically matches a row in a table or cursor.
>
> For example this snippet:
>
> CREATE OR REPLACE PROCEDURE demo IS
>
> TYPE myarray IS TABLE OF parent%ROWTYPE; l_data myarray;
>
> CURSOR r IS
> SELECT part_num, part_name
> FROM parent;>
> BEGIN
> OPEN r;
> LOOP
> FETCH r BULK COLLECT INTO l_data LIMIT 1000;
>
> FORALL i IN 1..l_data.COUNT
> INSERT INTO child VALUES l_data(i);>
> EXIT WHEN r%NOTFOUND;
> END LOOP;
> COMMIT;
> CLOSE r;
> END demo;
> /
>
> Note how myarray was defined. I can alter the parent table and the
> procedure does not break.
>
> BTW: This construct easily inserts 100,000 records per second on a
> laptop.
> --
> Daniel A. Morgan
> University of Washington
> damorgan@x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org
I'll be more explicit
CREATE OR REPLACE FUNCTION demo RETURN all_tables.table_name%TYPE IS
x all_tables.table_name%TYPE;
BEGIN
SELECT table_name
INTO x
FROM all_tables
WHERE rownum = 1;
RETURN x;
END;
/
Note that the value returned is defined by column data type as is the
variable x.
Sorry if the first example didn't demonstrate exactly what you asked.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org