SQL*Plus에서 LONG형을 다음처럼 select하는 경우에는 다음과 같은 오류가 발생 합니다.
SQL> CREATE TABLE test
2 (
3 emp_id NUMBER CONSTRAINT emp_pk PRIMARY KEY,
4 lname VARCHAR2(12),
5 fname VARCHAR2(12),
6 job_descr LONG
7 );
SQL> INSERT INTO emp VALUES (1,'SIMKOVSKY','ANDREW','DATABASE ADMINISTRATOR');
1 row created.
SQL> INSERT INTO emp VALUES (2,'SIMKOVSKY','ANDREW','DATABASE ADMINISTRATOR');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT DISTINCT lname, job_descr
2 FROM emp;
SELECT DISTINCT lname, job_descr
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
다른 방법도 있겠지만 다음과 같이 PL/SQL 함수를 만들어서 이용하는 것도 가능 합니다.
SQL> CREATE OR REPLACE FUNCTION long_to_varchar2 (empID NUMBER)
2 RETURN VARCHAR2 IS
3
4 varcharVal VARCHAR2(4000);
5 varcharLength NUMBER;
6 cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
7 fetchIt PLS_INTEGER;
8
9 BEGIN
10
11 DBMS_SQL.PARSE
12 (cur,'SELECT job_descr FROM emp WHERE emp_id = '||empId, DBMS_SQL.NATIVE);
13
14 DBMS_SQL.DEFINE_COLUMN_LONG(cur,1);
15
16 fetchIt := DBMS_SQL.EXECUTE_AND_FETCH(cur);
17
18 DBMS_SQL.COLUMN_VALUE_LONG(cur,1,4000,0,varcharVal,varcharLength);
19
20 DBMS_SQL.CLOSE_CURSOR(cur);
21
22 RETURN varcharVal;
23
24 END;
25 /
Function created.
SQL> column job_descr format a30
SQL> SELECT DISTINCT lname, LONG_TO_VARCHAR2(emp_id) job_descr
2 FROM emp;
LNAME JOB_DESCR
------------ ------------------------------
SIMKOVSKY DATABASE ADMINISTRATOR
SQL> CREATE TABLE test
2 (
3 emp_id NUMBER CONSTRAINT emp_pk PRIMARY KEY,
4 lname VARCHAR2(12),
5 fname VARCHAR2(12),
6 job_descr LONG
7 );
SQL> INSERT INTO emp VALUES (1,'SIMKOVSKY','ANDREW','DATABASE ADMINISTRATOR');
1 row created.
SQL> INSERT INTO emp VALUES (2,'SIMKOVSKY','ANDREW','DATABASE ADMINISTRATOR');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT DISTINCT lname, job_descr
2 FROM emp;
SELECT DISTINCT lname, job_descr
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
다른 방법도 있겠지만 다음과 같이 PL/SQL 함수를 만들어서 이용하는 것도 가능 합니다.
SQL> CREATE OR REPLACE FUNCTION long_to_varchar2 (empID NUMBER)
2 RETURN VARCHAR2 IS
3
4 varcharVal VARCHAR2(4000);
5 varcharLength NUMBER;
6 cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
7 fetchIt PLS_INTEGER;
8
9 BEGIN
10
11 DBMS_SQL.PARSE
12 (cur,'SELECT job_descr FROM emp WHERE emp_id = '||empId, DBMS_SQL.NATIVE);
13
14 DBMS_SQL.DEFINE_COLUMN_LONG(cur,1);
15
16 fetchIt := DBMS_SQL.EXECUTE_AND_FETCH(cur);
17
18 DBMS_SQL.COLUMN_VALUE_LONG(cur,1,4000,0,varcharVal,varcharLength);
19
20 DBMS_SQL.CLOSE_CURSOR(cur);
21
22 RETURN varcharVal;
23
24 END;
25 /
Function created.
SQL> column job_descr format a30
SQL> SELECT DISTINCT lname, LONG_TO_VARCHAR2(emp_id) job_descr
2 FROM emp;
LNAME JOB_DESCR
------------ ------------------------------
SIMKOVSKY DATABASE ADMINISTRATOR
[개강확정강좌]오라클자바커뮤니티에서 운영하는 개발자 전문교육 ,개인80%환급(www.onjprogramming.co.kr)
[주말]
[10/26]C#,ASP.NET마스터
[11/2]Spring3.X, MyBatis, Hibernate실무과정
[11/2]JAVA&WEB프레임워크실무과정
[평일야간]
[10/29]C#,ASP.NET마스터
[10/31]JAVA&WEB프레임워크실무과정
[11/1]Spring3.X, MyBatis, Hibernate실무과정
[주간]
[11/4]Spring3.X, MyBatis, Hibernate실무과정
[주말]
[10/26]C#,ASP.NET마스터
[11/2]Spring3.X, MyBatis, Hibernate실무과정
[11/2]JAVA&WEB프레임워크실무과정
[평일야간]
[10/29]C#,ASP.NET마스터
[10/31]JAVA&WEB프레임워크실무과정
[11/1]Spring3.X, MyBatis, Hibernate실무과정
[주간]
[11/4]Spring3.X, MyBatis, Hibernate실무과정
[기타 다른 강좌는 아래 해당 카테고리를 클릭해주세요]
댓글 없음:
댓글 쓰기