I read this script that assigns of a data column info into 2 binding variables.
something like this:
EXEC SQL SELECT
var1
into :v.v1:v2
from table
Shouldn't there be a comma in there? Or is this like assigning var1 into v.v1 and also into v2 with the same values?
The above script would give error only. if you want to assign value comma is required for the same.
The syntax would be :- Ex if you want to fetch Empno,Ename,Deptno,salary from EMPLOYEES.The plsql block would be as given below.
DECLARE
L_EMPNO NUMBER;
L_ENAME VARCHAR2(1000);
L_DEPTNO NUMBER;
L_SALARY NUMBER;
BEGIN
SELECT EMPNO, ENAME, DEPTNO, SALARY
INTO L_EMPNO, L_ENAME, L_DEPTNO, L_SALARY
FROM EMPLOYEES
WHERE EMPNO=100;
END;
This code is a snippet from a PRO*C program, a C program with embedded SQL.
v2 is an indicator variable. See here for info: https://docs.oracle.com/cd/B28359_01/appdev.111/b28427/pc_04dat.htm#i12463
An indicator variable will contain a value that relates to it's associated variable which in this case is v.v1 and is set after the operation in which it is used. In this case, after the select, you can test v2 and based on it's value it will tell you info about v.v1:
From the link above, if v2 equals:
0 - The operation was successful
-1 - A NULL was returned, inserted, or updated.
-2 - Output to a character host variable from a "long" type was truncated, but the original column length cannot be determined.
>0 - The result of a SELECT or FETCH into a character host variable was truncated. In this case, if the host variable is a multibyte character variable, the indicator value is the original column length in characters. If the host variable is not a multibye character variable, then the indicator length is the original column length in bytes.
I would suggest using it's other form, which would make things clear for the person that will maintain this after you (at least do that person a favor and comment this when you get your head around it). Always code for the person that will maintain after you. Don't you wish the person before you did that?!:
EXEC SQL SELECT
var1
into :v.v1 INDICATOR :v2
from table
