where condition argument for for loop variable in oracle-CodePudding

declare
  cursor abc is
    select USER_NAME from dba_users;
begin
  for i in abc
  loop
    select status
      into person_status
      from dba_users
     where USER_NAME := i;

    insert status into table_1;
    commit;
  end loop;
end;
/

It's giving error as below:

select status into person_status  from dba_users where USER_NAME := i;
ERROR at line 18:
ORA-06550: line 18, column 77:
PL/SQL: ORA-00920: invalid relational operator
ORA-06550: line 18, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 19, column 82:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 19, column 1:
PL/SQL: SQL Statement ignored

Can some one help me to pass for loop variable to where condition of select statement?

Thanks.

CodePudding user response：

You have used := in where condition

USER_NAME := i;

use it without : like

USER_NAME = i;

Alternatively You can use a single command for this work.

Create table table_1 (name varchar2(30),sts varchar2(100));
insert into table_1 (name,sts) (select USER_NAME,PERSON_STATUS FROM DBA_USERS);
COMMIT;

but, I described DBA_USERS on my DB there was no Person_status field in it it was having ACCOUNT_STATUS field.

CodePudding user response：

It is not just invalid where clause; there's bunch of other errors.

Presuming that this is a target table:

SQL> CREATE TABLE table_1
  2  (
  3     user_name   VARCHAR2 (128),
  4     status      VARCHAR2 (32)
  5  );

Table created.

PL/SQL code (read comments):

SQL> DECLARE
  2     CURSOR abc IS SELECT username FROM dba_users; --> USERNAME, not USER_NAME
  3
  4     person_status  dba_users.account_status%TYPE; --> declare a local variable
  5  BEGIN
  6     FOR i IN abc
  7     LOOP
  8        SELECT account_status                      --> it is ACCOUNT_STATUS, not just STATUS
  9          INTO person_status                       --> select value into a local variable
 10          FROM dba_users
 11         WHERE username = i.username;              --> reference column name in cursor variable
 12
 13        INSERT INTO table_1 (user_name, status)    --> valid INSERT statement
 14             VALUES (i.username, person_status);
 15     END LOOP;
 16
 17     COMMIT;                                       --> commit outside of the loop
 18  END;
 19  /

PL/SQL procedure successfully completed.

Result:

SQL> select * from table_1 where rownum <= 5;

USER_NAME            STATUS
-------------------- --------------------------------
VAL_ADV              OPEN
VAL_SEP              OPEN
ISPOL                OPEN
LGRE                 OPEN
TADMIN               OPEN

SQL>

On the other hand (as Jain already said), you should directly insert rows, without PL/SQL:

SQL> INSERT INTO table_1 (user_name, status)
  2     SELECT username, account_status FROM dba_users;

83 rows created.

SQL>