CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
Srijit
Srijit

Reputation: 505

What do the : and ' operators mean

I have to make modifications to an Oracle stored procedure that has the following lines.

      InsStmt = 'INSERT INTO EMPLOYEE (Emp_cd, Emp_lst_nm, Emp_fst,nm) VALUES
                (:Emp_cd, :Emp_lst_nm, :Emp_fst_nm);';
    varExec :='
    DECLARE
      var1 VARCHAR2(100);
    BEGIN
      var1 := :Emp_cd||:Emp_lst_nm||:Emp_fst_nm;
      '||InsStmt||'
    END;';
   EXECUTE IMMEDIATE varExec USING ip_param_cd, ip_param_lnm, ip_param_fnm;

I have only basic understanding of Oracle stored procedures. After some research I found out that the || operator is for concatenating strings.

But I'm still wondering what does the below statement mean

var1 := :Emp_cd||:Emp_lst_nm||:Emp_fst_nm;
      '||InsStmt||'

I went through the tutorial at http://docs.oracle.com/cd/B28359_01/appdev.111/b28843/tdddg_procedures.htm#CIHGDECD but could not find any help.

Upvotes: 0

Views: 78

Answers (1)

Sebas
Sebas

Reputation: 21532

Outside of the trigger context, the column : is used to bind variables within a statement.

For example:

EXECUTE IMMEDIATE 'UPDATE mytable SET age = 25 WHERE age = :1' 
     USING IN localVarAge;

In this case, the :1 value would be replaced by the value of localVarAge. The order the ':' variables appear in the prepared statement matter, not their actual labels.

In your code there's clearly a piece missing, this part var1 := :Emp_cd||:Emp_lst_nm||:Emp_fst_nm; should be within quotes. That would make sense anyway since you have right after that a closing quote and a concatenation.

Upvotes: 2

Related Questions