You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
I tried to use this new feature with procedure all_langs. see below standard
SET TERM ^ ;
ALTER PROCEDURE ALL_LANGS
RETURNS (
CODE Varchar(5),
GRADE Varchar(5),
COUNTRY Varchar(15),
LANG Varchar(15) )
AS
BEGIN
FOR SELECT JOB_CODE, JOB_GRADE, JOB_COUNTRY FROM JOB
INTO :CODE, :GRADE, :COUNTRY
DO
BEGIN
FOR SELECT LANGUAGES FROM SHOW_LANGS
(:CODE, :GRADE, :COUNTRY) INTO :LANG DO
SUSPEND;
/* PUT NICE SEPARATORS BETWEEN ROWS */
CODE = '=====';
GRADE = '=====';
COUNTRY = '===============';
LANG = '==============';
SUSPEND;
END
END^
SET TERM ; ^
I change CODE type from varchar(5) to JOBCODE and COUNTRY from varchar(15) to COUNTRYNAME and it's ok
ALTER PROCEDURE ALL_LANGS
RETURNS (
CODE JOBCODE,
GRADE Varchar(5),
COUNTRY COUNTRYNAME,
LANG Varchar(15) )
AS
BEGIN
..
End
but when I change GRADE from varchar(5) to JOBCODE, I got an error because the implicit cast from smallint (jobgrade type) to varchar(5) doesnt work
ALTER PROCEDURE ALL_LANGS
RETURNS (
CODE JOBCODE,
GRADE JOBCODE,
COUNTRY COUNTRYNAME,
LANG Varchar(15) )
SQL> select * from all_langs;
CODE GRADE COUNTRY LANG
====== ====== =============== ===============
Statement failed, SQLCODE = -625
validation error for variable GRADE, value "1"
-At procedure 'ALL_LANGS' line: 10, col: 3
SQL>
The text was updated successfully, but these errors were encountered:
ALTER PROCEDURE ALL_LANGS
RETURNS (
CODE JOBCODE,
GRADE JOBCODE,
COUNTRY COUNTRYNAME,
LANG Varchar(15) )
AS
BEGIN
FOR SELECT JOB_CODE, JOBGRADE2, JOB_COUNTRY FROM JOB
INTO :CODE, :GRADE, :COUNTRY
SQL> select * from all_langs;
CODE GRADE COUNTRY LANG
====== ====== =============== ===============
Statement failed, SQLCODE = -625
validation error for variable GRADE, value " "
-At procedure 'ALL_LANGS' line: 10, col: 3
SQL>
I create a domain similar to JOBCODE but without check
CREATE DOMAIN JOBCODEVC5 AS VARCHAR(5);
ALTER PROCEDURE ALL_LANGS
RETURNS (
CODE JOBCODE,
GRADE JOBCODEVC5,
COUNTRY COUNTRYNAME,
LANG Varchar(15) )
AS
BEGIN
FOR SELECT JOB_CODE, JOB_GRADE, JOB_COUNTRY FROM JOB
INTO :CODE, :GRADE, :COUNTRY
and no problem !
the error is throwed by JOBCODE check clause : CHECK (VALUE > '99999')
so I don't know if it's really a bug !
when you choose a domain for a variable, you have to be careful.
Ok thanks, I should have read the release notes more thoroughly !
Using domains could be a real time saver when you verify data.
Just add check constraint (evident ones : positive number, dates after 01/01/1900) and execute procedures, if it fails, there's something wrong.
Submitted by: Alexandre Moradell (amoradellc)
I tried to use this new feature with procedure all_langs. see below standard
SET TERM ^ ;
ALTER PROCEDURE ALL_LANGS
RETURNS (
CODE Varchar(5),
GRADE Varchar(5),
COUNTRY Varchar(15),
LANG Varchar(15) )
AS
BEGIN
FOR SELECT JOB_CODE, JOB_GRADE, JOB_COUNTRY FROM JOB
INTO :CODE, :GRADE, :COUNTRY
DO
BEGIN
FOR SELECT LANGUAGES FROM SHOW_LANGS
(:CODE, :GRADE, :COUNTRY) INTO :LANG DO
SUSPEND;
/* PUT NICE SEPARATORS BETWEEN ROWS */
CODE = '=====';
GRADE = '=====';
COUNTRY = '===============';
LANG = '==============';
SUSPEND;
END
END^
SET TERM ; ^
I change CODE type from varchar(5) to JOBCODE and COUNTRY from varchar(15) to COUNTRYNAME and it's ok
ALTER PROCEDURE ALL_LANGS
RETURNS (
CODE JOBCODE,
GRADE Varchar(5),
COUNTRY COUNTRYNAME,
LANG Varchar(15) )
AS
BEGIN
..
End
but when I change GRADE from varchar(5) to JOBCODE, I got an error because the implicit cast from smallint (jobgrade type) to varchar(5) doesnt work
ALTER PROCEDURE ALL_LANGS
RETURNS (
CODE JOBCODE,
GRADE JOBCODE,
COUNTRY COUNTRYNAME,
LANG Varchar(15) )
SQL> select * from all_langs;
CODE GRADE COUNTRY LANG
====== ====== =============== ===============
Statement failed, SQLCODE = -625
validation error for variable GRADE, value "1"
-At procedure 'ALL_LANGS' line: 10, col: 3
SQL>
The text was updated successfully, but these errors were encountered: