Oracle stored procedure to generate random code

Question

We are running a system to provide traceablity service to small householders in Vietnam. The system run on Oracle 12i DB and it has a stored procedure to generate random code to make QR code space for the system as below:

PROCEDURE GENCODE 
(
  PI_NUM_CODE IN NUMBER 
) AS 
ind number;
code_gen varchar2(10);
BEGIN
  for ind in 1..PI_NUM_CODE loop
      begin
        code_gen := dbms_random.string('X', 10);
        insert into tb_code_gen(s_code) values(code_gen);
        exception when others then
          dbms_output.put_line(code_gen);
      end;
    end loop;
END GENCODE;

Can you please be so kind to explain the IN NUMBER statement for me with thanks. I am trying to help the partners to fix this code to make the system run.

Can you please explain and help me to understand the statement with thanks.

Answer 1

pi_num_code is number of rows (QR codes, as you said) you want to generate when procedure is executed. That's - in procedure - number of times FOR loop runs.

Here's an example.

Target table:

SQL> CREATE TABLE tb_code_gen
  2  (
  3     s_code   VARCHAR2 (20)
  4  );

Table created.

Procedure:

SQL> CREATE OR REPLACE PROCEDURE gencode (pi_num_code IN NUMBER)
  2  AS
  3     ind       NUMBER;
  4     code_gen  VARCHAR2 (10);
  5  BEGIN
  6     FOR ind IN 1 .. pi_num_code       --> "run code within the loop PI_NUM_CODE number of times"
  7     LOOP
  8        BEGIN
  9           code_gen := DBMS_RANDOM.string ('X', 10);
 10
 11           INSERT INTO tb_code_gen (s_code)
 12                VALUES (code_gen);
 13        EXCEPTION
 14           WHEN OTHERS
 15           THEN
 16              DBMS_OUTPUT.put_line (code_gen);
 17        END;
 18     END LOOP;
 19  END gencode;
 20  /

Procedure created.

Enable output (otherwise you wouldn't see if something is displayed with dbms_output.put_line in exception handling section):

SQL> SET SERVEROUTPUT ON

Let's generate 3 codes:

SQL> BEGIN
  2     gencode (pi_num_code => 3);
  3  END;
  4  /

PL/SQL procedure successfully completed.

Here they are:

SQL> SELECT * FROM tb_code_gen;

S_CODE
--------------------
G31O4H7M90
SXTESE3HYJ
PPCL6PAU17

SQL>

---

On the other hand, you don't need a loop (nor a procedure) - insert can do that:

SQL> INSERT INTO tb_code_gen (s_code)
  2         SELECT DBMS_RANDOM.string ('X', 10)
  3           FROM DUAL
  4     CONNECT BY LEVEL <= 3;      --> this "3" is value you passed to procedure

3 rows created.                     --> another 3 rows created

SQL> SELECT * FROM tb_code_gen;

S_CODE
--------------------
G31O4H7M90
SXTESE3HYJ
PPCL6PAU17
GDEU7I42PZ
JGLGY11URW
EN2T4NTCI6

6 rows selected.

SQL>