Toad World® Forums

Problem

Hi All,

Do you know how I can fix this problem? It gives me error.

CREATE OR REPLACE PROCEDURE LastNamePermute (Iteration IN INT) AS

l_seed VARCHAR2(35);

BEGIN

l_seed := TO_CHAR(SYSTIMESTAMP,‘YYYYDDMMHH24MISSFFFF’);

DBMS_RANDOM.seed (val => l_seed);

FORALL i IN 1…Iteration

INSERT INTO population (DESCRIPTION) VALUES (DBMS_RANDOM.string(‘a’,35));

COMMIT;

SELECT FIRST_NAME,LAST_NAME,FCN

FROM REGISTRANT_NAME WHERE LAST_NAME IN (SELECT DESCRIPTION FROM population );

END ;

Before this, I did this part to create my table:

CREATE TABLE population (

ID NUMBER(10) NOT NULL,

DESCRIPTION VARCHAR2(35) NOT NULL);

ALTER TABLE population ADD (

CONSTRAINT dept_pk PRIMARY KEY (ID));

CREATE SEQUENCE dept_seq;

CREATE OR REPLACE TRIGGER dept_bir BEFORE INSERT ON population

FOR EACH ROW

BEGIN

SELECT dept_seq.NEXTVAL

INTO :new.id

FROM dual;

END;

Just in case you did not know - PL/SQL allows for direct assignment of sequence values:

:new_id := dept_seq.nextval;

Just an FYI - not related to your question …

Instead of FORALL use

FOR i IN 1…Iteration

LOOP

INSERT INTO population (DESCRIPTION) VALUES (DBMS_RANDOM.string(‘a’,35));

ENDLOOP;

Also, I am not sure what the purpose of your select statement is. Either remove it, or select INTO some variables (you have to do this in PL/SQL). You’ll want to make sure that your select statement returns exactly one row. It doesn’t look like that is going to happen, judging from the syntax you selected (where last_name in…).

By the way - coding mistakes (not using newer more efficient constructs) like this are covered by Toad’s code analysis feature. There are some nearly 200 rules to check for issues like this. SO give Toad professional or xpert edition a trial :slight_smile:

Thank You, John! It works.