[ORACLE-에러]👩ORA-00904: invalid identifier 부적합한 식별자 오류 와 SQL 계정생성

✅ ORA-00904: invalid identifier 오류

ORCLE SQL Developer 쿼리 실행 중 금일은 부적합한 식별자 오류 잔치 였다.

[JOIN 실습 문제]

--급여등급별 최소급여 보다 많이 받는 직원들의  

--사원명, 직급명, 급여, 연봉(보너스포함)을 조회하시오. 연봉에 보너스 포인트를 적용

--연봉나타내는데 NVL 이용해서 보너스가 없으면 0

[쿼리작성]

조인은 각 테이블 전체를 조회해서 서로 연결되는 걸 찾는다

SELECT *FROM EMPLOYEE;
SELECT *FROM JOB;
SELECT *FROM LOCATION;
SELECT *FROM DEPARTMENT;
SELECT *FROM SAL_GRADE;

연결된 중점은 가장많은 컬럼을 포함한 employee 였다.

SELECT E.EMP_NAME, J.JOB_NAME, E.SALARY, (E.SALARY+NVL(E.BONUS,0)) *12 AS  "연봉"

FROM EMPLOYEE E

JOIN SAL_GRADE S ON E.SAL_LEVEL = S.SAL_LEVEL

--SLA_GRADE 테이블과 EMPLOYEE 테이블을 조인하기 위해 서로 값이 같은 컬럼을 연결

JOIN JOB J ON E.JOB_CODE = J.JOB_CODE

--JOB 테이블과 EMPLOYEE 테이블 조인하기 위해 서로 값이 같은 컬럼을 연결

WHERE e.SALARY > s.NIN_SAL;

--직원의 급여가 최소 급여보다 큰 직원들만 선택할 수 있도록 조건 설정

작성한 쿼리에서 오타가 발생하면 위와같이 'invalid identifier'와 어느 부분에서 오타가 났는지 알려준다.

그런데 테이블이 나오지 않고 오류코드가 나오면 자꾸 당황하게 된다...

마지막 쿼리 구문 WHERE e.SALSARY > s.NIN_SAL;  →  WHERE e.SALARY > s.MIN_SAL;

[수정된 결과]

 

✅ ORA-00923: FROM keyword 가 있어야 할 곳에 없습니다. FROM keyword not found where expected  

ORA-00923의 경우 'FROM키워드가 있어야 할 곳에 없습니다. 라는 에러사유가 출력되며, 보통 SELECT 값 세팅 후 FROM 키워드가 나오기 전 SQL 문법에 맞지 않은 오류가 있을 경우 발생한다.

나는 FROM을 FORM이라고 적어 오류가 발생하였다. 변명을 하자면 FORM도 예약어처럼 파란색 글씨로

나와서 찾지 못했다. 변명을 인정하고, 오류를 인정해야지. 무슨말이지?? 암튼 오타 였구나~~^^;

위 쿼리는 SELF JOIN을 사용하여 동일테이블 E1,E2 구분하여 나타 냈다!

 

[어려웠던 문제]

--보너스포인트가 없는 직원들 중에서 직급코드가 J4 J7인 직원들의 사원명, 직급명,급여를 조회하시오

--단 JOIN, IN 사용할 것!

SELECT E.EMP_NAME, J.JOB_NAME, E.SALARY

FROM EMPLOYEE E

JOIN JOB J ON E.JOB_CODE = J.JOB.CODE

WHERE E.BONUS IS NULL

AND E.JOB_CODE IN('J4','J7');

[헷갈렸던 부분]

--직급코드가 J4 J7인 직원들을 쿼리로 어떻게 구현해야 할지 감이 않왔고 보너스포인트가 없는 쿼리도 좀 헷갈렸다

[결과]

EMPLOYEE테이블에서 직급코드(J4,J7)인 직원들의 이름과 직급, 급여가 조회되었다!

✅ORA-00904: "DEPT_CODEE": invalid identifier 부적합한 식별자

--노옹철 사원과 같은 부서, 같은 직급인 사원을 조회하시오. (단, 노옹철 사원은 제외)

--사번, 이름, 부서코드, 직급코드, 부서명, 직급명

SELECT E.EMP_ID, E.EMP_NAME, E.PHONE, E.HIRE_DATE, D.DEPT_TITLE, J.JOB_NAME

FROM EMPLOYEE E

JOIN DEPARTMENT D ON E.DEPT_CODE = D.DEPT_ID

JOIN JOB J ON E.JOB_CODE = J.JOB_CODE

WHERE E.DEPT_CODE = (SELECT DEPT_CODE FROM EMPLOYEE WHERE EMP_NAME = '노옹철')

               AND E.JOB_CODE = (SELECT DEPT_CODE FROM EMPLOYEE WHERE EMP_NAME = '노옹철')

               AND E.EMP_NAME != '노옹철';

[오류결과]

DEPT_CODE가 부적합한 식별자로 나왔다!!

[해결]

WHERE절이 문제였다~

WHERE E.DEPT_CODE = (SELECT DEPT_CODE FROM EMPLOYEE WHERE EMP_NAME = '노옹철')  --1

               AND E.JOB_CODE = (SELECT DEPT_CODE FROM EMPLOYEE WHERE EMP_NAME = '노옹철')  --2

 

→ 1번에서 노옹철의 코드가 있는곳은 부서코드이고 2번에서 노옹철의 코드는 직급코드에서 찾는다~

     따라서 2번에서 노옹철이 있는 곳 DEPT_CODE가 아닌 JOB_CODE에서 찾는다~

[수정]

WHERE E.DEPT_CODE = (SELECT DEPT_CODE FROM EMPLOYEE WHERE EMP_NAME = '노옹철')

               AND E.JOB_CODE = (SELECT JOB_CODE FROM EMPLOYEE WHERE EMP_NAME = '노옹철')

[결과]

✅ SQL 계정생성

① CMD 창

SALPLU SYS AS SYSDBA;

② 접속해서 유저 생성

③ 생성된 계정에 접속 + 기본 자원관리 권한 추가 VIEW 추가

C:\Users\user1>sqlplus sys as sysdba;

SQL*Plus: Release 11.2.0.2.0 Production on 금 6월 7 14:10:15 2024

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password:

//비밀번호는 미노출된다.

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL>CREATE USER KH_USER IDENTIFIED BY KH!234;

ERROR at line 1:
ORA-00922: missing or invalid option

 

위와 같이 계정생성시 오류가 나는 원인을 찾아 보았다!

확인 시, 비밀번호에 특수문자 '!'가 포함되어 있기 때문일 가능성이 있었다

즉 ORCLE에서는 특수 문자가 포함된 비밀번호를 지정할 때 비밀번호를 큰 따옴표(' " ')로 감싸야 한다.

CREATE USER KH_USER IDENTIFIED BY "KH!234";

 

계정생성 시 위 사항을 알지 못하고 특수문자 제외 후 아래와 같이 계정 생성 하였다.

SQL> CREATE USER KH_USER IDENTIFIED BY KH234;

User created.

SQL> GRANT CONNECT, RESOURCE TO KH_USER;

Grant succeeded.

[VIEW 추가]

SQL>GRANT CREATE VIEW TO KH_WOUKBOOK;

Grant succeeded.

SQL> GRANT CREATE VIEW TO KH_USER;

Grant succeeded.

 

✅ SQL과  GIT 연결

① CMD 창(아래 탐색기 주소창에서 cmd를 입력한다)

② C:\Users\user1\sql_workspace>git add sql [탭 키를 친다]

C:\Users\user1\sql_workspace>git add SQL_개념정리 까지 나오면 끝에 /*을 입력한다.

③ 금일 배운 내용을 적어준다.

C:\Users\user1\sql_workspace>git commit -m "pl_문자대소문자구분,결합/*

④ git push를 진행한다.

 

⑤ git add sql [탭키] 입력한다

C:\Users\user1\sql_workspace>git add SQL_코드/*리

⑥ C:\Users\user1\sql_workspace>git commit -m "join실습_subquery"

⑦  C:\Users\user1\sql_workspace>git push

 

🟩 금일 배운 가장 중요한 SEQUENCE

SEQUENCE(순서,연속)

- 순차적으로 일정한 간격의 숫자(번호)를 발생시키는 객체 (자동번호생성기)

[작성법]

CREATE SEQUENCE 시퀀스 이름

[START WITH T숫자]       -- 처음 발생시킬 시작값 지정, 생략하면 자동으로 1이 기본값으로 설정

[INCREAMENT BY 숫자]    --다음 값에 대한 증가값을 설정, 생략하면 자동으로 1씩 증가로 설정

[MAXVALUE 숫자 | NOMAXVALUE]  --발생시킬 최대값 지정 ex) 최대 1000 명만 기록하겠다.

[MINVALUE 숫자 | NOMINVALUE] --발생시킬 최소값 지정 ex)21번부터 다시 시작하겠다.

[CYCLE | NOCYCLE] --값 순환 여부 지정

[CACHE 바이트크기 | NOCACHE] --캐쉬메모리 기본값은 20BYTE 최소값은 2BYTE

▶SEQUENCE는 쉼표를 쓰지 않고 마지막에 ; 세미콜론을 붙여준 후 실행한다

[사용방법]

시퀀스명.NEXTVAL : 다음 시퀀스 번호 얻어옴

시퀀스명.CURRVAL : 현재 시퀀스 번호를 얻어옴

                                      주의할 점 : 시퀀스가 생성되자마자 호출할 경우 오류 발생

                                       --> 마지막으로 호출한 NEXTVAL 값 반환

[ TB_TEST 테이블 생성]

CREATE TABLE TB_TEST(

        TEST_NO NUMBER PRIMARY KEY,      --ORACLE 지정한 최대값 설정 38자리

        TEST_NAME VARCHAR2(30) NOT NULL

       );

[ SEQ_TEST_NO 시퀀스생성]

CREATE SEQUENCE SEQ_TEST_NO

START WITH 100    -- 시작번호 100

INCREMENT BY 5   --NEXTVAL 호출마다 5씩 증가

MAXVALUE 150       --증가 가능한 최대값 150

NOMINVALUE        --최소값 없음

NOCYCLE              --반복 없음

NOCACHE             --미리 생성해두는 시퀀스 번호 없음

;

▶위 작성된 내용 중 START 부터 NOCACHE까지 모두 생략 가능, SQL에서 설정한 기본값이 자동으로 매겨짐

CREATE SEQUENCE SEQ_TEST_NO; 실행하여도 아래와 같이 계정 생성됨

 

[TB_TEST테이블 INSERT 시 시퀀스 사용하기]

INSERT INTO TB_TEST VALUES(SEQ_TEST_NO.NEXTVAL,'홍길동');

INSERT INTO TB_TEST VALUES( SEQ_TEST_NO.NEXTVAL,'김영희');

INSERT INTO TB_TEXT VALUES( SEQ_TEST_NO.NEXTVAL,'박철수');

 

[시퀀스 값 변경 (ALTER)]

▶ CREATE 구문과 똑같지만 START WITH 옵션 제외됨

ALTER SEQUENCE 시퀀스 이름

[INCREAMENT BY 숫자]               -- 다음 값에 대한 증가값을 설정, 생략하면 자동으로 1씩 증가로 설정

[MAXVALUE 숫자 | NOMAXVALUE]    --발생시킬 최대값 지정 ex) 최대 1000명만 기록하겠다.

[MINVALUE 숫자 | NOMINVALUE]      --발생시킬 최소값 지정 ex) 21번 부터 다시 시작하겠다.

[CYCLE | NOCYCLE]                            --값 순환 여부 지정

 

[SEQ_TEST_NO 시퀀스에서 최대값 200으로 변경]

ALTER SEQUENCE SEQ_TEST_NO

MAXVALUE 200;

 

🟩 오류찾기 두번째 문제 

SELECT DEPT,SUM(SALARY) AS "합계",
FLOOR(AVG(SALARY)) AS"평균",
COUNT(*) AS "인원수"

FROM EMPLOYEE
WHERE SALARY > 2800000
GROUP BY DEPT
ORDER BY DEPT ASC;

ORA-00904: invalid identifier 오류

[해결]

SELECT *FROM EMPLOYEE;   > 테이블 조회시 DEPT 컬럼명이 잘못되었다.

아래와 같이 수정하니 정상적으로 짜잔 나타났다