Possible Feature
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