Interesting question - at first I would have said an overflow, but checking, that's ORA-01426
and if it wasn't a number, ORA-01722
. Is it just datatype NUMBER
which defaults to NUMBER(38)
?
But this can be diagnosed with a trace to see what's exactly going on. If you can identify the session, or can modify the connection code, you can do:
SQL> alter session set events='6502 trace name errorstack level 12';
Session altered.
SQL> create or replace function fn_add(p1 number, p2 number) return number
2 as
3 v1 number(1);
4 begin
5 v1 := p1 + p2;
6 return v1;
7 end;
8 /
Function created.
SQL> select fn_add('10', '1') from dual;
select fn_add('10', '1') from dual
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
Now looking in the tracefile:
---- Error Stack Dump -----
ORA-06502: PL/SQL: numeric or value error: number precision too large
----- Current SQL Statement for this session (sql_id=a36psfjj50bpv) -----
select fn_add('10', '1') from dual
That should at least get you started on root cause analysis. But be warned that these trace files can get very large, very quickly!
I think these answers are all getting a) way too complicated, and b) off-track relative to what user2023861 wants to accomplish:
"I am trying to declare a variable, then select that variable." means two different things in either PL/SQL or in SQLplus, and perhaps two more things in DBArtisan. To accomplish the objective in SQLplus, use "Substitution variables"...excellent background is found here.
My example, doesn't precisely follow your SQL Server T-SQL example, but it's close and is NOT procedural (read: no anonymous PL/SQL block.)
PL/SQL with Substitution variables:
DEFINE min_salary = 1000
DEFINE mgrID = 7
select *
from employees
where
salary > &min_salary
and mgrID = &mgrID
;
All at the SQL-Plus prompt....from my fictional employees table, I got this result:
SALARY MGRID
---------- ----------
2000 7
1001 7
2 row selected.
If you want to make this semi-procedural, then you use BIND Variables and the declarative syntax of PL/SQL similar to T-SQL. (In my opinion this is a HUGE waste of effort, but is included to help clarify variable types.)
SQLPlus using PL/SQL Bind variables:
VARIABLE v_in_min_salary number;
VARIABLE v_mgrID number; /* vars are defined within SQL+ */
VARIABLE v_out_salary number;
VARIABLE v_out_mgrID number;
BEGIN
:v_in_min_salary := 1000; /* vars are assigned values in PL/SQL */
:v_mgrID := 7;
select salary, mgrID
into :v_out_salary, :v_out_mgrID
from employees
where salary > :v_in_min_salary and mgrID = :v_mgrID
;
end;
/
PRINT :v_in_min_salary /* here the in and out vars are displayed */
PRINT :v_mgrID
PRINT :v_out_salary
PRINT :v_out_mgrID
Fully-procedural approach using PL/SQL:
set serveroutput on; /* environment setting within SQLplus to see output */
DECLARE
v_in_min_salary number := 1000;
v_mgrID number :=7;
v_out_salary number;
v_out_mgrID number;
BEGIN
SELECT salary, mgrID
into :v_out_salary, :v_out_mgrID
from employees
where salary > :v_in_min_salary and mgrID = :v_mgrID
;
DBMS_OUTPUT.PUT_LINE('Salary Output is= '||:v_out_salary);
DBMS_OUTPUT.PUT_LINE('Manager ID = '||:v_out_mgrID);
END;
/
So, when do I use each case? I use SQLplus Substitution variables 75% of the time when I write SQL scripts that I want to parameterize the input for. Executing a script at the SQLplus prompt allows passing inbound arguments, so my favorite Substitution variables are &1 and &2 as in the following example:
@get_table_size.sql <schema> <table_name>
which in practice becomes
@get_table_size jason employees
and executes SQL code similar to
SELECT extents
FROM all_tables
WHERE UPPER(table_owner) = upper('&1') /* inbound first argument */
and UPPER(table_name) = upper('&2') /* inbound 2nd argument */
;
Notice how the inbound arguments are assigned into substitution variables, then used in the SQL statement. I never use the middle example, and I always use the 3rd examples (procedural PL/SQL) when writing functions, procs, and packages.
Best Answer
You forgot the comparison operator for the WHERE clause
If you really want to use dynamic SQL (which is not needed for a simple UPDATE statement like that), then you should also pass all values as parameters, not just the student ID.
The "string buffer too small" seems to happen when you are continuing a string literal (constant) over multiple lines. So it's better to open and close the string constants on each line and concatenate them using
||
rather than embedding a new line in the string as you did.