Computer_IT/DBMS 2018.08.08 17:26


DB의 테이블명이 DATA_20180801 형태로 되어있을경우 날짜가 포함된 테이블만 조회하는 정규식


SELECT * FROM ( 

        SELECT TABLE_NAME, REGEXP_SUBSTR( TABLE_NAME, '\d{4}\d{2}\d{2}') YYYYMMDD

        FROM USER_TABLES

)

WHERE YYYYMMDD IS NOT NULL

-- AND YYYYMMDD < '20180701'


결과 


posted by 고급코드 고급코드
Computer_IT/DBMS 2018.08.08 14:42

-- 테이블 COMMENT 조회

SELECT TABLE_NAME, TABLE_TYPE, COMMENTS 

FROM USER_TAB_COMMENTS

WHERE COMMENTS IS NOT NULL;


-- 컬럼별 COMMENT 조회

SELECT * 

FROM USER_COL_COMMENTS

WHERE COMMENTS IS NOT NULL;


-- 테이블별 COMMENT 쿼리문

SELECT 'COMMENT ON TABLE ' || TABLE_NAME || ' IS ''' || COMMENTS || ''';' 

FROM USER_TAB_COMMENTS

WHERE COMMENTS IS NOT NULL;

COMMENT ON TABLE TABLE_NAME IS 'COMMENTS text';


-- 컬럼별 COMMENT 쿼리문

SELECT 'COMMENT ON COLUMN ' || TABLE_NAME || '.' || COLUMN_NAME || ' IS ''' ||  COMMENTS || ''';' 

FROM USER_COL_COMMENTS 

WHERE COMMENTS IS NOT NULL;

COMMENT ON COLUMN TABLE_NAME.COLUMN_NAME IS 'COMMENTS';



posted by 고급코드 고급코드
Computer_IT/DBMS 2007.03.19 12:49

사용자 삽입 이미지

SharedServer, Dispatcher 확인


Shared Server 구성확인

SQL> SELECT name, status FROM v$shared_server;

Dispatcher 확인

SQL> SELECT name, status FROM v$dispatcher;
posted by 고급코드 고급코드
Computer_IT/DBMS 2007.03.16 15:50
ORACLE 에서 잠긴 계정 풀기

사용자 삽입 이미지


SQL> alter user hr identified by hr account unlock;
posted by 고급코드 고급코드
Computer_IT/DBMS 2007.03.13 14:08
사용자 삽입 이미지


명령 : sc delete 서비스명 <-엔터
posted by 고급코드 고급코드
TAG Delete, oracle, sc
세상살면서 2006.08.09 16:03

Test Sites

Address

View Test Site Web SiteGBTEC CO., LTD
DAEGU
Phone:혻53-428-2093 Site Code:SK30
6, 7F DACOM BUILDING
#295-1 DONGIN-DONG 1-GA
JUNG-GU

View Test Site Web SiteHONAM INFORMATION MEDIA CENTER (HIMEC)
GWANG JU 502817
Phone:혻623808433 Site Code:SKJ
948-5 SSANGCHON-DONG
SEO-GU

View Test Site Web SiteCHEJU NATIONAL UNIVERSITY IT CENTER
JEJU-SI, Jejudo 690-756
Phone:혻064-754-2265 Site Code:SK63
ARA-1DONG #1,

View Test Site Web SiteKOREA INTERNATIONAL TRADE ASSOCIATION (KITA) TEST CENTER
Kangnam-gu 135-729
Phone:혻026000 5189 Site Code:SK52
KITA IT Training Center
COEX Office 4th Floor
World Trade Centre
Samsung-dong,

View Test Site Web SiteSAMSUNG MULTICAMPUS
SEOUL 135080
Phone:혻2-3429-5160 Site Code:SKF
SAMSUNG MULTICAMPUS BUILDING
2TH FL 718-5 YOKSAM DONG
KANGNAM GU

View Test Site Web SiteCHOONGANG COMPUTER INSTITUTE
SEOUL 135-080
Phone:혻0혻Site Code:SK3
DONGIN BLDG 2TH FL
826-22 YOKSAM DONG
KANG NAM GU

View Test Site Web SiteCADBANK
SEOUL 110-550
Phone:혻22530780 Site Code:SK69
JINSUN B/D 2F 280 BUNJI,
SEUNG IN DONG JONG RO GU,

View Test Site Web SiteITWILL CO., LTD
SEOUL 135748
Phone:혻62558000 Site Code:SK62
7F YEOSAM B/D, 648-23,
YOKSAM DONG, KANGNAM GU,

View Test Site Web SiteYEUNGJIN JUNIOR COLLEGE
TAE GU 702-721
Phone:혻940 5161 Site Code:SK21
YEUNG JIN EDUCATION COLLEGE
218 BOK HYUN DONG
BUK GU

View Test Site Web SiteHANNAM UNIVERSITY
TAEJON 306791
Phone:혻629 7764 Site Code:SK36
133 OJUNG-DONG
DAEDUK-
posted by 고급코드 고급코드
Computer_IT/C++ 2006.08.07 13:35

  1. /*
  2. *  sample1.pc
  3. *
  4. *  Prompts the user for an employee number,
  5. *  then queries the emp table for the employee's
  6. *  name, salary and commission.  Uses indicator
  7. *  variables (in an indicator struct) to determine
  8. *  if the commission is NULL.
  9. *
  10. */
  11. #include <stdio.h>
  12. #include <string.h>
  13. /* Define constants for VARCHAR lengths. */
  14. #define     UNAME_LEN      20
  15. #define     PWD_LEN        40
  16. /* Declare variables.No declare section is needed if MODE=ORACLE.*/
  17. VARCHAR     username[UNAME_LEN]
  18. /* VARCHAR is an Oracle-supplied struct */
  19. varchar     password[PWD_LEN];   
  20. /* varchar can be in lower case also. */
  21. /*
  22. Define a host structure for the output values of a SELECT statement.
  23. */
  24. struct {
  25.   VARCHAR   emp_name[UNAME_LEN];
  26.   float     salary;
  27.   float     commission;
  28. } emprec;
  29. /*
  30. Define an indicator struct to correspond to the host output struct. */
  31. struct
  32. {
  33.   short     emp_name_ind;
  34.   short     sal_ind;
  35.   short     comm_ind;
  36. } emprec_ind;
  37. /*  Input host variable. */
  38. int         emp_number;
  39. int         total_queried;
  40. /* Include the SQL Communications Area.
  41.   You can use #include or EXEC SQL INCLUDE. */
  42. #include <sqlca.h>
  43. /* Declare error handling function. */
  44. void sql_error();
  45. main()
  46. {
  47.   char temp_char[32];
  48. /* Connect to ORACLE--
  49. * Copy the username into the VARCHAR.
  50. */
  51.   strncpy((char *) username.arr, "SCOTT", UNAME_LEN);
  52. /* Set the length component of the VARCHAR. */
  53.   username.len = strlen((char *) username.arr);
  54. /* Copy the password. */
  55.   strncpy((char *) password.arr, "TIGER", PWD_LEN);
  56.   password.len = strlen((char *) password.arr);
  57. /* Register sql_error() as the error handler. */
  58.   EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--\n");
  59. /* Connect to ORACLE.  Program will call sql_error()
  60. * if an error occurs when connecting to the default database.
  61. */
  62.   EXEC SQL CONNECT :username IDENTIFIED BY :password;
  63.   printf("\nConnected to ORACLE as user: %s\n", username.arr);
  64. /* Loop, selecting individual employee's results */
  65.   total_queried = 0;
  66.   for (;;)
  67.   {
  68. /* Break out of the inner loop when a
  69. * 1403 ("No data found") condition occurs.
  70. */
  71.        EXEC SQL WHENEVER NOT FOUND DO break;
  72.        for (;;)
  73.        {
  74.            emp_number = 0;
  75.            printf("\nEnter employee number (0 to quit): ");
  76.            gets(temp_char);
  77.            emp_number = atoi(temp_char);
  78.            if (emp_number == 0)
  79.                break;
  80.            EXEC SQL SELECT ename, sal, NVL(comm, 0)
  81.                INTO :emprec INDICATOR :emprec_ind
  82.                FROM EMP
  83.                WHERE EMPNO = :emp_number;
  84. /* Print data. */
  85.            printf("\n\nEmployee\tSalary\t\tCommission\n");
  86.            printf("--------\t------\t\t----------\n");
  87. /* Null-terminate the output string data. */
  88.            emprec.emp_name.arr[emprec.emp_name.len] = '\0';
  89.            printf("%-8s\t%6.2f\t\t",
  90.                emprec.emp_name.arr, emprec.salary);
  91.            if (emprec_ind.comm_ind == -1)
  92.                printf("NULL\n");
  93.            else
  94.                printf("%6.2f\n", emprec.commission);
  95.            total_queried++;
  96.        }  /* end inner for (;;) */
  97.        if (emp_number == 0) break;
  98.        printf("\nNot a valid employee number - try again.\n");
  99.   } /* end outer for(;;) */
  100.   printf("\n\nTotal rows returned was %d.\n", total_queried);
  101.   printf("\nG'day.\n\n\n");
  102. /* Disconnect from ORACLE. */
  103.   EXEC SQL COMMIT WORK RELEASE;
  104.   exit(0);
  105. }
  106. void sql_error(msg)
  107. char *msg;
  108. {
  109.   char err_msg[128];
  110.   int buf_len, msg_len;
  111.   EXEC SQL WHENEVER SQLERROR CONTINUE;
  112.   printf("\n%s\n", msg);
  113.   buf_len = sizeof (err_msg);
  114.   sqlglm(err_msg, &buf_len, &msg_len);
  115.   printf("%.*s\n", msg_len, err_msg);
  116.   EXEC SQL ROLLBACK RELEASE;
  117.   exit(1);
  118. }
 

'Computer_IT > C++' 카테고리의 다른 글

[VC] 인라인 ASM 으로 작성한 연산  (1) 2006.08.14
[VC] MMX support 여부  (0) 2006.08.14
Example Program: A Simple Query  (0) 2006.08.07
Const Member변수 초기화 특성  (0) 2006.04.24
LinkedList 자료  (0) 2006.04.06
VC++ 단축키 모음  (2) 2006.03.19
posted by 고급코드 고급코드
TAG C, Database, oracle
Computer_IT/DBMS 2006.08.07 13:27

Oracle® Database SQL Reference 10g Release 1 (10.1)

'Computer_IT > DBMS' 카테고리의 다른 글

[Oracle] Cluster 구성  (0) 2006.11.01
[ORACLE] Oracle9i 실행계획 보기  (0) 2006.09.18
ORACLE USER KILL  (0) 2006.09.18
CBO  (0) 2006.08.07
Oracle® Database SQL Reference  (0) 2006.08.07
[MySQL] 사용자 추가  (0) 2006.07.09
posted by 고급코드 고급코드
TAG 10g, oracle, SQL