I’m trying to run the following insert command in Oracle SQL Developer:
insert into work_comp_rates (company_id, work_comp_rt)
values ('101', 0.11);
Which gives me this error: «ORA-06502: PL/SQL: numeric or value error: number precision too large»
There is a trigger attached:
create or replace
TRIGGER APPS.work_codes_trig
before insert or update ON APPS.WORK_COMP_RATES for each row
begin
if inserting then
if :NEW.RID is null then
:NEW.RID := it_api.gen_pk;
end if;
:NEW.CREATED_ON := sysdate;
end if;
if updating then
:NEW.MODIFIED_ON := sysdate;
end if;
end;
If I replace
:NEW.RID := it_api.gen_pk;
with
:NEW.RID := 599;
the insert statement works.
IT_API Body:
create or replace
package body it_api
as
-- generates and returns unique number used for primary key values
function gen_pk
return number
is
l_pk number := 0;
begin
for c1 in (
select to_number(sys_guid(),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') pk
from dual )
loop
l_pk := c1.pk;
exit;
end loop;
return l_pk;
end gen_pk;
end it_api;
I don’t know Oracle very well and that script was written by somebody else. So any help is appreciated!
I’m trying to run the following insert command in Oracle SQL Developer:
insert into work_comp_rates (company_id, work_comp_rt)
values ('101', 0.11);
Which gives me this error: «ORA-06502: PL/SQL: numeric or value error: number precision too large»
There is a trigger attached:
create or replace
TRIGGER APPS.work_codes_trig
before insert or update ON APPS.WORK_COMP_RATES for each row
begin
if inserting then
if :NEW.RID is null then
:NEW.RID := it_api.gen_pk;
end if;
:NEW.CREATED_ON := sysdate;
end if;
if updating then
:NEW.MODIFIED_ON := sysdate;
end if;
end;
If I replace
:NEW.RID := it_api.gen_pk;
with
:NEW.RID := 599;
the insert statement works.
IT_API Body:
create or replace
package body it_api
as
-- generates and returns unique number used for primary key values
function gen_pk
return number
is
l_pk number := 0;
begin
for c1 in (
select to_number(sys_guid(),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') pk
from dual )
loop
l_pk := c1.pk;
exit;
end loop;
return l_pk;
end gen_pk;
end it_api;
I don’t know Oracle very well and that script was written by somebody else. So any help is appreciated!
ORA-06502 means that PL/SQL engine cannot convert a character-typed string into a number or a subset of arithmetic for overall evaluation. Mostly, it’s because of the following problems:
- Numeric Type Conversion
- Numeric Operator Precedence
A. Numeric Type Conversion
ORA-06502 tells you that PL/SQL engine cannot convert a string into a number. Which means, an arithmetic, numeric, string, conversion, or constraint error occurred. Let’s see a normal case first.
SQL> set serveroutput on;
SQL> declare
2 v_num number;
3 begin
4 v_num := 123;
5 dbms_output.put_line('The number is ' || v_num);
6 end;
7 /
The number is 123
PL/SQL procedure successfully completed.
A number 123 is assigned to variable V_NUM which accept only NUMBER type. So there’s no conversion needed. But what if we assign a string to the variable?
SQL> declare
2 v_num number;
3 begin
4 v_num := '123';
5 dbms_output.put_line('The number is ' || v_num);
6 end;
7 /
The number is 123
PL/SQL procedure successfully completed.
As you can see, PL/SQL engine converted the string into a number, then assigned it into the variable.
Now, let’s try some basic arithmetic expressions.
SQL> declare
2 v_num number;
3 begin
4 v_num := 2 + 2;
5 dbms_output.put_line('The number is ' || v_num);
6 end;
7 /
The number is 4
PL/SQL procedure successfully completed.
OK, the variable accepts value, the result of evaluation, no ORA-06502. What if we use it as a string?
SQL> declare
2 v_num number;
3 begin
4 v_num := '2 + 2';
5 dbms_output.put_line('The number is ' || v_num);
6 end;
7 /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 4
PL/SQL engine tried to convert the string into a number, but it failed with ORA-06502. This time, V_NUM cannot accept the result.
The solution to this type of error is to avoid implicit type conversion if possible.
B. Numeric Operator Precedence
To better understand ORA-06502, let’s see a more advanced topic about operator precedence in Oracle database. In the following example, we tried to output a string that concatenate an arithmetic.
SQL> begin
2 dbms_output.put_line('The number is ' || 2 + 2);
3 end;
4 /
begin
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 2
ORA-06502 was thrown eventually. Since || (concatenation) and + (addition) operators are at the same level of operator precedence, PL/SQL engine will evaluate them in the order of presence.
First, it concatenated «The number is » and «2» into «The number is 2», which was successful, but when it tried to add the last value «2», it failed to convert the former string into a number and threw ORA-06502.
Solutions
1. Rearrange the Output
We should make PL/SQL engine deal with the numeric evaluation first, then the concatenation by rearranging the output.
SQL> begin
2 dbms_output.put_line(2 + 2 || ' is the number.');
3 end;
4 /
4 is the number.
PL/SQL procedure successfully completed.
This time, the expression is good because the order of presence of operators has been changed.
2. Override Operator Precedence
Beside rearranging the order of presence, how can we make the latter take the precedence over the former to fix the problem? Here is the trick for our PL/SQL block of codes.
SQL> begin
2 dbms_output.put_line('The number is ' || (2 + 2));
3 end;
4 /
The number is 4
PL/SQL procedure successfully completed.
As you can see, we used a parenthesis to override operator precedence. The evaluation will start from the highest precedence which is 2 + 2 numeric value inside the parentheses to the rest according to their operator precedence defined in Oracle. This is how we escape from ORA-06502.
In PL/SQL, if multiple parentheses are used in your expression, the evaluation will start from the inner to the outer.
A very similar error that you might see in your statements is ORA-01722: invalid number, which is also related to conversion issues of numeric values.
I am getting a ‘ORA-06502: PL/SQL: numeric or value error: character string buffer too small error’, trying to execute a stored procedure that has an output parameter with all 50 US states and the abbreviations.
Any suggestions on what I am doing wrong. Oracle database version is 11.2.0.1.0. I am also searching to see if I can find anything online, but thought of asking here in case someone had this problem in the past that was addressed.
CREATE TABLE STATE_CDS (STATE VARCHAR2(2) NOT NULL, STATE_NAME VARCHAR2(25 BYTE));insert into state_cds values('AL', 'ALABAMA');insert into state_cds values('AK', 'ALASKA');insert into state_cds values('AZ', 'ARIZONA');insert into state_cds values('AR', 'ARKANSAS');insert into state_cds values('CA', 'CALIFORNIA');insert into state_cds values('CO', 'COLORADO');insert into state_cds values('CT', 'CONNECTICUT');insert into state_cds values('DE', 'DELAWARE');insert into state_cds values('DC', 'DISTRICT OF COLUMBIA');insert into state_cds values('FL', 'FLORIDA');insert into state_cds values('GA', 'GEORGIA');insert into state_cds values('HI', 'HAWAII');insert into state_cds values('ID', 'IDAHO');insert into state_cds values('IL', 'ILLINOIS');insert into state_cds values('IN', 'INDIANA');insert into state_cds values('IA', 'IOWA');insert into state_cds values('KS', 'KANSAS');insert into state_cds values('KY', 'KENTUCKY');insert into state_cds values('LA', 'LOUISIANA');insert into state_cds values('ME', 'MAINE');insert into state_cds values('MD', 'MARYLAND');insert into state_cds values('MA', 'MASSACHUSETTS');insert into state_cds values('MI', 'MICHIGAN');insert into state_cds values('MN', 'MINNESOTA');insert into state_cds values('MS', 'MISSISSIPPI');insert into state_cds values('MO', 'MISSOURI');insert into state_cds values('MT', 'MONTANA');insert into state_cds values('NE', 'NEBRASKA');insert into state_cds values('NV', 'NEVADA');insert into state_cds values('NH', 'NEW HAMPSHIRE');insert into state_cds values('NJ', 'NEW JERSEY');insert into state_cds values('NM', 'NEW MEXICO');insert into state_cds values('NY', 'NEW YORK');insert into state_cds values('NC', 'NORTH CAROLINA');insert into state_cds values('ND', 'NORTH DAKOTA');insert into state_cds values('OH', 'OHIO');insert into state_cds values('OK', 'OKLAHOMA');insert into state_cds values('OR', 'OREGON');insert into state_cds values('OU', 'OUT OF COUNTRY');insert into state_cds values('PA', 'PENNSYLVANIA');insert into state_cds values('RI', 'RHODE ISLAND');insert into state_cds values('SC', 'SOUTH CAROLINA');insert into state_cds values('SD', 'SOUTH DAKOTA');insert into state_cds values('TN', 'TENNESSEE');insert into state_cds values('TX', 'TEXAS');insert into state_cds values('UT', 'UTAH');insert into state_cds values('VT', 'VERMONT');insert into state_cds values('VA', 'VIRGINIA');insert into state_cds values('WA', 'WASHINGTON');insert into state_cds values('WV', 'WEST VIRGINIA');insert into state_cds values('WI', 'WISCONSIN');insert into state_cds values('WY', 'WYOMING');create or replace procedure P_state_CODES (cd_type in varchar2, cd_desc out varchar2) is cursor states is select state, state_name from state_cds order by state_name desc;begin if cd_type = 'STATES' then for recs in states loop cd_desc := recs.state||';'||recs.state_name||'+'||cd_desc; end loop; end if;end;ORA-06502: PL/SQL: numeric or value error: character string buffer too smallORA-06512: at "KBNIS.P_STATE_CODES", line 11ORA-06512: at "KBNIS.P_STATE_CODES", line 11ORA-06512: at line 7
Thank you for the help.
Sandeep.
Learn the cause and how to resolve the ORA-06502 error message in Oracle.
Description
When you encounter an ORA-06502 error, the following error message will appear:
- ORA-06502: PL/SQL: numeric or value error
Cause
You tried to execute a statement that resulted in an arithmetic, numeric, string, conversion, or constraint error.
The common reasons for this error are:
- You tried to assign a value to a numeric variable, but the value is larger than the variable can handle.
- You tried to assign a non-numeric value to a numeric variable and caused a conversion error.
Resolution
Let’s look at three options on how to resolve the ORA-06502 error:
Option #1 — Value too large
In our first option, this error occurs when you try to assign a value to a numeric variable, but the value is larger than the variable can handle.
For example, if you created a procedure called TestProc as follows:
SQL> CREATE OR REPLACE PROCEDURE TestProc 2 AS 3 v_number number(2); 4 BEGIN 5 v_number := 100; 6 END; 7 / Procedure created.
This procedure was successfully created. But when we try to execute this procedure, we will get an ORA-06502 error as follows:
SQL> execute TestProc(); BEGIN TestProc(); END; * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: number precision too large ORA-06512: at "EXAMPLE.TESTPROC", line 5 ORA-06512: at line 1
The first line of the error message (ie: ORA-06502) indicates the error that occurred, while the second line of the error message (ie: ORA-06512) indicates that the error occurred at line 5 of the PLSQL code.
In this example, you’ve tried to assign a 3 digit number to a variable called v_number that can only handle 2 digits. You could correct this error by redefining the v_number variable as number(3).
SQL> CREATE OR REPLACE PROCEDURE TestProc 2 AS 3 v_number number(3); 4 BEGIN 5 v_number := 100; 6 END; 7 / Procedure created.
And now when we execute our TestProc procedure, the ORA-06502 error has been resolved.
SQL> execute TestProc(); PL/SQL procedure successfully completed.
Option #2 — Conversion error
In our second option, this error occurs if you are trying to assign a non-numeric value to a numeric variable.
For example, if you created a procedure called TestProc as follows:
SQL> CREATE OR REPLACE PROCEDURE TestProc 2 AS 3 v_number number(2); 4 BEGIN 5 v_number := 'a'; 6 END; 7 / Procedure created.
This procedure was successfully created. But when we try to execute this procedure, we will get an ORA-06502 error as follows:
SQL> execute TestProc(); BEGIN TestProc(); END; * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at "EXAMPLE.TESTPROC", line 5 ORA-06512: at line 1
In this example, the value of ‘a’ does not properly convert to a numeric value. You can correct this error by assigning the variable called v_number a proper numeric value.
SQL> CREATE OR REPLACE PROCEDURE TestProc
2 AS
3 v_number number(2);
4 BEGIN
5 v_number := ASCII('a');
6 END;
7 /
Procedure created.
And now when we execute our TestProc procedure, the ORA-06502 error has been resolved.
SQL> execute TestProc(); PL/SQL procedure successfully completed.
Option #3 — Assigning NULL to a NOT NULL constrained variable
In our third option, this error occurs if you are trying to assign a NULL value to a NOT NULL constrained variable.
For example, if you created a procedure called TestProc as follows:
SQL> CREATE OR REPLACE PROCEDURE TestProc 2 AS 3 v_non_nullable_variable VARCHAR2(30) NOT NULL := '5'; 4 v_null_variable VARCHAR2(30) := NULL; 5 BEGIN 6 v_non_nullable_variable := v_null_variable; 7 EXCEPTION 8 WHEN OTHERS THEN 9 dbms_output.put_line(SQLERRM); 10 END; 11 / Procedure created.
This procedure was successfully created. But when we try to execute this procedure, we will get an ORA-06502 error as follows:
ORA-06502: PL/SQL: numeric or value error
In this example, you can not assign a NULL value to the variable called v_non_nullable_variable. You can correct this error removing NOT NULL from the variable declaration of the v_non_nullable_variable as follows:
SQL> CREATE OR REPLACE PROCEDURE TestProc 2 AS 3 v_non_nullable_variable VARCHAR2(30) := '5'; 4 v_null_variable VARCHAR2(30) := NULL; 5 BEGIN 6 v_non_nullable_variable := v_null_variable; 7 EXCEPTION 8 WHEN OTHERS THEN 9 dbms_output.put_line(SQLERRM); 10 END; 11 / Procedure created.
ORA-06502: PL/SQL: numeric or value error: character string buffer too small error occurs when the length of the character string exceeds the length of the declared character type variable,. The value cannot be assigned to the variable if the size of the value passed in the database exceeds the size of the variable declared. The error ORA-06502: PL/SQL: numeric or value error: character string buffer too small would be thrown by the oracle. The error occurs because the output value saved in that variable is longer than it was declared.
The length of the string should not exceed the size of the data type declared in the variable. The string can be stored in the variable in this case. If the length of the character string exceeds the declared variable size, the character string cannot be saved. If the character is attempted to be assigned to the attribute, an exception would be thrown.
Exception
The error will be described as follows. The line number identifies the location of the error. The variable data size is larger than the value size. The following error has been thrown.
declare
empid varchar2(3);
begin
empid := 'A101';
end;
Error report -
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4
06502. 00000 - "PL/SQL: numeric or value error%s"Two ORA errors can be seen in the error stack trace. The first error code is shown alongside the error message. The second error code indicates which line the error happened on. The error indicates that the declared string variable’s size is insufficient in comparison to the value assigned to it.
Problem
The character string cannot be allocated if the length of the string exceeds the size of the declared data type variable. The error can be repeated in this scenario. The database is attempting to assign the variable a string. The error would be thrown since the string is longer than the variable’s length.
In the example below, the value has four characters. The variable is declared to be three characters long. The length of the string value exceeds the length of the declared variable. The error ORA-06502:PL/SQL: numeric or value error: character string buffer too small would be thrown if the value is assigned to a variable that is smaller in size.
declare
empid varchar2(3);
begin
empid := 'A101';
end;Output
declare
empid varchar2(3);
begin
empid := 'A101';
end;
Error report -
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4
06502. 00000 - "PL/SQL: numeric or value error%s"Cause
An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).
Action
Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.
Solution 1
The size of the value passed in Oracle PS./SQL exceeds the declared character data type size. To accommodate the value, the variable data type should be modified. The character data type’s size should be increased. If the size of the character data type is reached to maximum size of the data type, the different data type should be used to accommodate the larger value.
declare
empid varchar2(4);
begin
empid := 'A101';
end;Output
PL/SQL procedure successfully completed.Solution 2
It’s essential to double-check the PL/SQL value. It’s possible that the value was passed to the variable inappropriately or that there was an error in the method. The value will be stored in the variable if it is corrected.
declare
empid varchar2(4);
begin
empid := '101';
end;Output
PL/SQL procedure successfully completed.Solution 3
In most instances, the value assigned would be within the declared data type’s range. The length of the value sometimes reaches the declared data type size. We can’t adjust the data type size in this situation. The exception should be handled and taken action in the PL/SQL code.
declare
empid varchar2(3);
begin
empid := 'A101';
exception
WHEN OTHERS THEN
empid :=0;
end;Output
PL/SQL procedure successfully completed.Я получаю такую ошибку:
ORA-06502: PL / SQL: ошибка числа или значения: ошибка преобразования символа в число ORA-06512: в строке 146 06502. 00000 — «PL / SQL: ошибка числа или значения% s»
А вот мой код:
SET SERVEROUTPUT ON;
DECLARE
SearchId NUMBER := 0;
SearchMethod VARCHAR2(100) := '';
CritExpression VARCHAR2(100) := '';
SubstringStartPosition NUMBER := 0;
SubstringLength NUMBER := 0;
CritDescription VARCHAR2(100) := '';
CriteriaSequenceId NUMBER := 1;
CriteriaId NUMBER := 0;
CritCount NUMBER := 0;
FUNCTION InsertSrchCriteria
(
SearchMethod IN VARCHAR2,
CritExpression IN VARCHAR2,
SubstringStartPosition IN NUMBER,
SubstringLength IN NUMBER,
CritDescription IN VARCHAR2,
CriteriaSequenceId IN NUMBER,
SearchId IN NUMBER
)
RETURN NUMBER
IS
C_Id NUMBER := 0;
BEGIN
SELECT COUNT (*) INTO CritCount FROM criteria_table WHERE search_id = SearchId AND criteria_sequence_id = CriteriaSequenceId;
IF CritCount = 0
THEN
INSERT INTO criteria_table
(
criteria_sequence_id,
search_id,
search_method,
expression,
substring_start_position,
substring_length,
description
)
VALUES
(
CriteriaSequenceId,
SearchId,
SearchMethod,
CritExpression,
SubstringStartPosition,
SubstringLength,
CritDescription
)
RETURNING criteria_id INTO C_Id;
IF C_Id > 0
THEN
DBMS_OUTPUT.PUT_LINE ('Inserted ' || 'SearchId: ' || SearchId || ' @' || CriteriaSequenceId || ' successfully');
ELSE
DBMS_OUTPUT.PUT_LINE ('Not Inserted ' || 'SearchId: ' || SearchId || ' @' || CriteriaSequenceId);
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE ('Already exists ' || 'SearchId: ' || SearchId || ' @' || CriteriaSequenceId);
END IF;
RETURN C_Id;
END InsertSrchCriteria;
BEGIN
SearchId = 5;
CriteriaSequenceId := 1;
SearchMethod := 'XPath';
CritExpression := '//Expression/text()';
SubstringStartPosition := null;
SubstringLength := null;
CritDescription := '';
CriteriaId := InsertSrchCriteria ****
(
CriteriaSequenceId,
SearchId,
SearchMethod,
CritExpression,
SubstringStartPosition,
SubstringLength,
CritDescription
);
END;
Ошибка возникает в строке с **. Я не совсем уверен, что вызывает эту ошибку, любая помощь?
Думаю, что вам уже кажется, что с процедурами и их параметрами мы разобрались, а вот и нет! Есть еще кое-что. Думаю, кто-то из вас заметил, что при написании формального параметра процедуры, например, определяя ее как VARCHAR2 или NUMBER я никогда не делал вот так:
CREATE OR REPLACE PROCEDURE some_proc(NUM IN OUT NUMBER(3,2), DT OUT VARCHAR2(100))
И вот почему. Накладывать ограничения на формальные параметры функций в PL/SQL — ЗАПРЕЩЕНО! Например, вот такой пример, приведет к ошибке компиляции:
CREATE OR REPLACE PROCEDURE TESTINOUT(NUM IN OUT NUMBER(3,2), DT OUT VARCHAR2(100)) IS BEGIN SELECT COMPANY INTO DT FROM customers WHERE customers.CUST_NUM = NUM; SELECT CUST_REP INTO NUM FROM customers WHERE customers.CUST_NUM = NUM; END TESTINOUT; /
В результате получите:
SQL> CREATE OR REPLACE PROCEDURE TESTINOUT(NUM IN OUT NUMBER(3,2), DT OUT VARCHAR2(100)) 2 IS 3 4 BEGIN 5 6 SELECT COMPANY INTO DT FROM customers 7 WHERE customers.CUST_NUM = NUM; 8 9 SELECT CUST_REP INTO NUM FROM customers 10 WHERE customers.CUST_NUM = NUM; 11 12 END TESTINOUT; 13 / Предупреждение: Процедура создана с ошибками компиляции.
Что и требовалось доказать. Можете убрать неверные объявления и еще раз перекомпилировать процедуру для того, чтобы она осталась исправной. А, вот вам еще один подводный камешек. Запишем вот такую процедуру:
CREATE OR REPLACE PROCEDURE PTEST(I_PAR IN OUT NUMBER, II_PAR IN OUT VARCHAR2) IS BEGIN I_PAR := 15.6; II_PAR := 'POIUYTREWQLKJHGFDSA'; END PTEST; /
Компилируем:
SQL> CREATE OR REPLACE PROCEDURE PTEST(I_PAR IN OUT NUMBER, II_PAR IN OUT VARCHAR2) 2 IS 3 4 BEGIN 5 6 I_PAR := 15.6; 7 II_PAR := 'POIUYTREWQLKJHGFDSA'; 8 9 END PTEST; 10 / Процедура создана.
Вот теперь I_PAR и II_PAR получили неявное ограничение посредством объявлений:
I_PAR := 15.6; II_PAR := 'POIUYTREWQLKJHGFDSA';
т.е. получилось, что то вроде:
CREATE OR REPLACE PROCEDURE PTEST(I_PAR IN OUT NUMBER(3.4), II_PAR IN OUT VARCHAR2(19))
Теперь, если произвести вот такой вызов:
DECLARE V_STR VARCHAR2(10); V_NUM NUMBER(3,4); BEGIN PTEST(V_NUM, V_STR); END; /
Получаем, что-то довольно странное:
SQL> DECLARE 2 3 V_STR VARCHAR2(10); 4 V_NUM NUMBER(3,4); 5 6 BEGIN 7 8 PTEST(V_NUM, V_STR); 9 10 END; 11 / DECLARE * ошибка в строке 1: ORA-06502: PL/SQL: : буфер символьных строк слишком маленький ошибка числа или значения ORA-06512: на "MILLER.PTEST", line 7 ORA-06512: на line 8 SQL>
Не сразу ясно, что происходит, так? А все очень просто, V_STR VARCHAR2(10) переопределила ограничение переменной II_PAR при ее явном вызове и запись строки длинной 19 символов в переменную всего в 10 символов привело к ошибке! Очень важно это понимать, иначе в дальнейшем вы запутаетесь совсем! Здесь ошибку вызвала сама вызывающая программа, а не код процедуры, как может показаться! Так вот во избежание ошибок, подобных ORA-06502 при создании процедур документируйте все ограничения налагаемые на фактические параметры — вносите в хранимые процедуры комментарии, а так же кроме описания каждого параметра записывайте функции выполняемые самой процедурой! Вот тогда я думаю, у вас все получится!
Так же единственным способом наложения ограничения на формальный параметр функции является использование оператора %TYPE. Мы с вами о нем говорили. В свете этого можно переписать нашу функцию пример — скажем, вот так:
CREATE OR REPLACE PROCEDURE PTEST(
I_PAR IN OUT CUSTOMERS.CUST_NUM%TYPE,
II_PAR IN OUT CUSTOMERS.COMPANY%TYPE)
IS
BEGIN
I_PAR := 15.6;
II_PAR := 'POIUYTREWQLKJHGFDSA';
END PTEST;
/
Такой способ удобен тем, что при изменении полей таблицы автоматом меняются параметры процедур, что облегчает сопровождение кода хранимых процедур, не нужно менять все параметры связанные с данным полем! Получаем:
SQL> CREATE OR REPLACE PROCEDURE PTEST( 2 I_PAR IN OUT CUSTOMERS.CUST_NUM%TYPE, 3 II_PAR IN OUT CUSTOMERS.COMPANY%TYPE) 4 IS 5 6 BEGIN 7 8 I_PAR := 15.6; 9 II_PAR := 'POIUYTREWQLKJHGFDSA'; 10 11 END PTEST; 12 / Процедура создана.
Ошибок нет! Значит, все прошло успешно! Как работать с параметрами это дело вкуса, а на него, как говорится, товарищей совсем не бывает! Вот пока можете все это переварить, а я пойду попью чаю! 🙂