[ORA-01001~ORA01197]Error

[펌] [ORA-01001~ORA01197]Error | 낙서장	2005/06/30 22:49
http://blog.naver.com/case0062/80014560164
출처블로그 : 배설물(排泄物)
ORA-01001 invalid cursor Cause: Either a host language program call specified an invalid cursor or the value of the MAXOPENCURSORS option in the precompiler command were too small. All cursors must be opened using the OOPEN call before being referenced in any of the following calls: SQL, DESCRIBE, NAME, DEFINE, BIND, EXEC, FETCH, and CLOSE. The Logon Data Area (LDA) must be defined by using OLON or OLOGON. If the LDA is not defined, this message is issued for the following calls: OPEN, COM, CON, ROL, and LOGOFF. Action: Check the erroneous call statement. Specify a correct LDA area or open the cursor as required. If there is no problem with the cursor, it may be necessary to increase the MAXOPENCURSORS option value before precompiling. ORA-01002 fetch out of sequence Cause: In a host language program, a FETCH call was issued out of sequence. A successful parse-and-execute call must be issued before a fetch. This can occur if an attempt was made to FETCH from an active set after all records have been fetched. This may be caused by fetching from a SELECT FOR UPDATE cursor after a commit. A PL/SQL cursor loop implicitly does fetches and may also cause this error. Action: Parse and execute a SQL statement before attempting to fetch the data. ORA-01003 no statement parsed Cause: A host language program call referenced a cursor with no associated parsed SQL statement. A SQL call (for example, OSQL3) must be used to pass a SQL statement to Oracle and to associate the statement with an open cursor. A cursor must already have an associated SQL statement if referenced in any of the following calls: DESCRIBE, NAME, DEFINE, BIND, EXECUTE, and FETCH. Action: Do the SQL call, for example, OSQL, to pass the required SQL statement before referencing the cursor. ORA-01004 default username feature not supported; logon denied Cause: An attempt was made to use automatic logon on a system not supporting this feature. Action: Provide the complete username and password to log on to Oracle. ORA-01005 null password given; logon denied Cause: An invalid password was given when logging on. Action: Provide a valid password. ORA-01006 bind variable does not exist Cause: A program issued a BIND call for a variable not listed in the associated SQL statement. Only those variables prefixed by either a colon (:) or ampersand (&) in the SQL statement may be referenced in a BIND call, OBIND or OBINDN. This error may also be caused by a mismatch between a Precompiler program and the related library, SQLLIB. Action: Modify the BIND call to reference one of the substitute variables specified in the associated SQL statement. ORA-01007 variable not in select list Cause: A reference was made to a variable not listed in the SELECT clause. In OCI, this can occur if the number passed for the position parameter is less than one or greater than the number of variables in the SELECT clause in any of the following calls: DESCRIBE, NAME, or DEFINE. In SQL*Forms or SQL*Report, specifying more variables in an INTO clause than in the SELECT clause also causes this error. Action: Determine which of the problems listed caused the problem and take appropriate action. ORA-01008 not all variables bound Cause: A SQL statement containing substitution variables was executed without all variables bound. All substitution variables must have a substituted value before the SQL statement is executed. Action: In OCI, use an OBIND or OBINDN call to substitute the required values. ORA-01009 missing mandatory parameter Cause: A host language program call did not pass all required parameters. Action: Check the syntax for the call and enter all required parameters. ORA-01010 invalid OCI operation Cause: One of the following: You attempted an invalid OCI operation. You are using an Oracle client application linked with release 7.1 (or higher) libraries, the environment variable ORA_ENCRYPT_LOGIN is set to TRUE, and you attempted to connect to a release 7.0 (or lower) Oracle database server. You are connected to a release 7.1 (or higher) Oracle database server, the DBLINK_ENCRYPT_LOGIN initialization parameter is set to TRUE, and you attempted to use a database link pointing to a release 7.0 (or lower) Oracle database server. You are connected to a release 9.0.2(or higher) Oracle database server and you attempted to use a database link pointing to a release 9.0.1 (or lower) Oracle database server for distributed autonomous transactions. Action: For the above causes: Do not use the invalid OCI operation. If you do not wish to use encrypted connect passwords in your distributed database, then set ORA_ENCRYPT_LOGIN to FALSE. If you wish to use encrypted connect passwords, then you must upgrade all Oracle database servers to release 7.1 (or higher). If you do not wish to use encrypted database links in your distributed database, then set DBLINK_ENCRYPT_LOGIN to FALSE. If you wish to use encrypted database links, then you must upgrade all Oracle database servers to release 7.1 (or higher). Do not attempt distributed autonomous transactions on release 9.0.1 (or lower) Oracle database servers. ORA-01011 cannot use v7 compatibility mode when talking to v6 server Cause: An attempt was made to use Oracle7 compatibility mode when accessing Oracle version 6. Action: Use V6 SQL language type. You may need to use the SET COMPATIBILITY V6 command. ORA-01012 not logged on Cause: A host language program issued an Oracle call, other than OLON or OLOGON, without being logged on to Oracle. This can occur when a user process attempts to access the database after the instance it is connected to terminates, forcing the process to disconnect. Action: Log on to Oracle, by calling OLON or OLOGON, before issuing any Oracle calls. When the instance has been restarted, retry the action. ORA-01013 user requested cancel of current operation Cause: The user interrupted an Oracle operation by entering CTRL-C, Control-C, or another canceling operation. This forces the current operation to end. This is an informational message only. Action: Continue with the next operation. ORA-01014 ORACLE shutdown in progress Cause: A user tried to log on to Oracle while an instance shutdown was in progress. Oracle logons are disabled while Oracle is being shut down. Action: Wait until Oracle is brought back up before attempting to log on. ORA-01015 logon called recursively Cause: This is an internal error message not usually issued. Action: Contact Oracle Support Services. ORA-01016 This function can be called only after a fetch Cause: The cursor is in an invalid state. Action: Ensure the appropriate OCI/UPI function is called after the fetch and before the offending function. ORA-01017 invalid username/password; logon denied Cause: An invalid username or password was entered in an attempt to log on to Oracle. The username and password must be the same as was specified in a GRANT CONNECT statement. If the username and password are entered together, the format is: username/password. Action: Enter a valid username and password combination in the correct format. ORA-01018 column does not have a LONG datatype Cause: An attempt was made to fetch data using the LONG fetch option, but the specified column was not LONG. Action: Re-execute the fetch without the LONG fetch option or create the table with a LONG column. ORA-01019 unable to allocate memory in the user side Cause: The user side memory allocator returned an error. Action: Increase the size of the process heap or switch to the old set of calls. ORA-01020 unknown context state Cause: This is an internal error message not usually issued. Action: Contact Oracle Support Services. ORA-01021 invalid context size specified Cause: This is an internal error message not usually issued. Action: Contact Oracle Support Services. ORA-01022 database operation not supported in this configuration Cause: The attempted database operation does not conform to the user programming interface (UPI) for the two communicating Oracle servers. Action: You may need to upgrade one or more of your Oracle servers or re-link your user side application with new libraries. Report the problem to Oracle Support Services. ORA-01023 Cursor context not found (Invalid cursor number) Cause: The cursor number is not a valid open cursor. Action: Make sure that the cursor is open. ORA-01024 invalid datatype in OCI call Cause: An OCI program call specified an invalid datatype. In OCI calls, Oracle datatypes are specified as numbers between 1 and 7. Action: Check the datatype description and enter the correct number for the datatype. ORA-01025 UPI parameter out of range Cause: An integer parameter to a UPI function is out of range. Action: This usually indicates an error in a tool built on top of the Oracle DBMS. Report the error to Oracle Support Services. ORA-01026 multiple buffers of size > 4000 in the bind list Cause: More than one long buffer in the bind list. Action: Change the buffer size to be less than 4000 for the bind variable bound to a normal column. ORA-01027 bind variables not allowed for data definition operations Cause: An attempt was made to use a bind variable in a SQL data definition operation. Action: Such bind variables are not allowed. ORA-01028 internal two task error Cause: Received send long message but do not have the cursor context. Action: Contact Oracle Support Services. ORA-01029 internal two task error Cause: Received a request to send the long again when there is no long Action: Contact Oracle Support Services. ORA-01030 SELECT ... INTO variable does not exist Cause: The SELECT... INTO specified in the bind call does not correspond to a variable in the SQL statement. Action: If it is not possible to correct the statement, then contact Oracle Support Services. ORA-01031 insufficient privileges Cause: An attempt was made to change the current username or password without the appropriate privilege. This error also occurs if attempting to install a database without the necessary operating system privileges. Action: Ask the database administrator to perform the operation or grant the required privileges. ORA-01032 no such userid Cause: This is an internal error message related to Export/Import. Action: Contact Oracle Support Services. ORA-01033 ORACLE initialization or shutdown in progress Cause: An attempt was made to log on while Oracle is being started or shut down. Action: Wait a few minutes. Then retry the operation. ORA-01034 ORACLE not available Cause: Oracle was not started. Possible causes include the following: The SGA requires more space than was allocated for it. The operating-system variable pointing to the instance is improperly defined. Action: Refer to accompanying messages for possible causes and correct the problem mentioned in the other messages. If Oracle has been initialized, then on some operating systems, verify that Oracle was linked correctly. See the platform-specific Oracle documentation. ORA-01035 ORACLE only available to users with RESTRICTED SESSION privilege Cause: Logins are disallowed because an instance started in restricted mode. Only users with RESTRICTED SESSION system privilege can log on. Action: Request that Oracle be restarted without the restricted option or obtain the RESTRICTED SESSION system privilege. ORA-01036 illegal variable name/number Cause: Unable to find bind context on user side. Action: Make sure that the variable being bound is in the SQL statement. ORA-01037 maximum cursor memory exceeded Cause: An attempt was made to process a complex SQL statement which consumed all available memory of the cursor. Action: Simplify the complex SQL statement. Alternatively, you can use the NO_EXPAND hint which prevents the cost-based optimizer from considering OR-expansion for queries having OR conditions or INLISTS in the WHERE clause. Usually, the optimizer considers using OR expansion and uses this method if it decides the cost is lower than not using it. Refer to the Oracle9i Database Performance Tuning Guide and Reference for information about using hints. ORA-01038 cannot write database file version string with ORACLE version string Cause: An attempt was made to write datafile headers in an old format. The new format cannot be used until after the database has been verified as being compatible with this software version. Action: Open the database to advance to the new file formats, then repeat the operation. If the operation is required before the database can be opened, then use the previous software release to do the operation. ORA-01039 insufficient privileges on underlying objects of the view Cause: An attempt was made to explain plan on other people's view without the necessary privileges on the underlying objects of the view. Action: Get necessary privileges or do not perform the offending operation. ORA-01040 invalid character in password; logon denied Cause: There are multibyte characters in the password or some characters in the password are not in US7ASCII range. Action: Resubmit the password with valid characters. ORA-01041 internal error. hostdef extension doesn't exist Cause: Pointer to HSTDEF extension in HSTDEF is null. Action: Contact Oracle Support Services. ORA-01042 detaching a session with open cursors not allowed Cause: An attempt was made to detach a session which had open cursors. Action: Close all the cursors before detaching the session. ORA-01043 user side memory corruption [string], [string], [string], [string] Cause: The application code corrupted some of the user memory used by Oracle. Action: Make sure that the application code is not overwriting memory. ORA-01044 size string of buffer bound to variable exceeds maximum string Cause: An attempt was made to bind a buffer whose total size would exceed the maximum size allowed. Total array size for arrays is calculated as: (element_size)*(number of elements) Action: Reduce the buffer size. ORA-01045 user string lacks CREATE SESSION privilege; logon denied Cause: A connect was attempted to a userid which does not have create session privilege. Action: Grant the user CREATE SESSION privilege. ORA-01046 cannot acquire space to extend context area Cause: Oracle could not extend the current area any further because the operating system would not supply any more space. A system-specific message should appear following this message. Action: Close some cursors and try again or check operating system quotas to use more virtual memory. See your operating system-specific Oracle documentation. ORA-01047 The above error occurred in schema=string, package=string, procedure=string Cause: This message is displayed after a previous message to provide more specific information. Action: See the cause and action for the previous message. ORA-01048 Couldn't find the specified procedure in the given context Cause: The procedure user specified in deferred RPC does not exist. Action: Check to make sure that the procedure exists and is visible to the replication process. ORA-01049 Bind by name is not supportted in streamed RPC Cause: This error occurs when a newer server version requests from an older server version an operation that is not supported. Action: You may need to upgrade one or more of your Oracle servers or relink your user application with new libraries. ORA-01050 cannot acquire space to open context area Cause: Oracle could not open a new context area because the operating system would not supply any more space. A system-specific message should appear following this message. Action: Close some cursors and try again or check operating system quotas to use more virtual memory. See your operating system-specific Oracle documentation. ORA-01051 deferred rpc buffer format invalid Cause: The deferred RPC data in SYS.DEF$_CALL is corrupted. Action: Contact Oracle Support Services. ORA-01052 required destination LOG_ARCHIVE_DUPLEX_DEST is not specified Cause: A valid destination for parameter LOG_ARCHIVE_DUPLEX_DEST was not specified when parameter LOG_ARCHIVE_MIN_SUCCEED_DEST was set to 2. Action: Either specify a value for parameter LOG_ARCHIVE_DUPLEX_DEST, or reduce the value for parameter LOG_ARCHIVE_MIN_SUCCEED_DEST to 1. ORA-01053 user storage address cannot be read Cause: A bind variable or other user area could not be read by Oracle. Action: Check that binds are done correctly on valid user buffers, then retry the operation. ORA-01054 user storage address cannot be written Cause: A define, FETCH... INTO or SELECT... INTO, variable or other user area could not be written to by Oracle. Action: Check that INTO variables and indicators are correctly specified. Then retry the operation. ORA-01057 invalid or ambiguous block.field reference in user exit Cause: The reference to a block.field identifier in a user exit is incorrect or ambiguous, probably because it is misspelled or incomplete. Action: Check syntax and identifier spelling. Then correct the reference. ORA-01058 internal New Upi interface error Cause: Attempt to delete non-existent HSTDEF extension. Action: Contact Oracle Support Services. ORA-01059 parse expected before a bind or execute Cause: The client application attempted to bind a variable or execute a cursor opened in a PL/SQL block before the statement was parsed. Action: Ensure the statement is parsed before a bind or execute. ORA-01060 array binds or executes not allowed Cause: The client application attempted to bind an array of cursors or attempted to repeatedly execute against a PL/SQL block with a bind variable of type cursor. Action: Bind a single cursor or execute the PL/SQL block once. ORA-01061 cannot start up a V8 server using a V7 client application Cause: You are using an Oracle client application linked with version 7 (or lower) libraries and you attempted to start up a V8 (or higher) server. Action: Use a client application linked with V8 (or higher) libraries. ORA-01062 unable to allocate memory for define buffer Cause: Exceeded the maximum buffer size for current platform. Action: Use piecewise fetch with a smaller buffer size. ORA-01070 Using an old version of Oracle for the server Cause: An attempt was made to run an older, obsolete Oracle Server. Action: Upgrade the server. ORA-01071 cannot perform operation without starting up ORACLE Cause: An attempt was made to perform an operation before Oracle was started. Action: Start Oracle. Then retry the operation. ORA-01072 cannot stop ORACLE; ORACLE not running Cause: An attempt was made to stop Oracle, but Oracle was not running. Action: No action required. ORA-01073 fatal connection error: unrecognized call type Cause: An illegal internal operation was attempted. Action: Contact Oracle Support Services. ORA-01074 cannot shut down ORACLE; inside a login session - log off first Cause: An attempt was made to shut down Oracle inside a logon session. Action: Log off before shutting down Oracle. ORA-01075 you are currently logged on Cause: An attempt was made to log on while already logged on. Action: No action required. ORA-01076 multiple logons per process not yet supported Cause: Oracle does not support multiple logons per process. Action: No action required. ORA-01077 background process initialization failure Cause: A failure occurred during initialization of the background processes. Action: Refer to the diagnostic information in the accompanying message stack or in the trace file and take appropriate action. ORA-01078 failure in processing system parameters Cause: Failure during processing of initialization parameters during system startup. Action: Further diagnostic information should be in the error stack. ORA-01079 ORACLE database was not properly created, operation aborted Cause: There was an error when the database or control file was created. Action: Check what error was signaled when the database was first created or when the control file was re-created. Take appropriate actions to re-create the database or a new control file. ORA-01080 error in shutting down ORACLE Cause: A failure occurred during system shutdown. Action: Refer to the diagnostic information in the accompanying message stack and take appropriate action. ORA-01081 cannot start already-running ORACLE - shut it down first Cause: An attempt was made to start Oracle while it was already running. Action: Shut down Oracle first, if you want to restart it. ORA-01082 'row_locking = always' requires the transaction processing option Cause: "row_locking = always" is specified in the INIT.ORA file. This feature is not supported by ORACLE without the transaction processing option. Action: Remove it from INIT.ORA file or set it to "default" or "intent". ORA-01083 value of parameter "string" is inconsistent with that of other instances Cause: The value of the given initialization parameter is required to be the same for all instances in the cluster database configuration. Action: Change the value of the initialization parameter in the initialization parameter file to match that of other cluster database instances. ORA-01084 invalid argument in OCI call Cause: The failing OCI call contains an argument with an invalid value. Action: Use valid argument values. For more information, see the Oracle Call Interface Programmer's Guide and the appropriate programming language supplement. ORA-01085 preceding errors in deferred rpc to "string.string.string" Cause: Errors were encountered when the named procedure was executed as a deferred remote procedure call. Action: Correct the cause of the preceding errors. ORA-01086 savepoint 'string' never established Cause: An attempt was made to roll back to a savepoint that was never established. Action: No action is required. ORA-01087 cannot start up ORACLE - currently logged on Cause: An attempt was made to start up Oracle by a user who is currently logged on. Action: Log off. Then issue the STARTUP command. ORA-01088 cannot shut down ORACLE while active processes exist Cause: Users are still logged into the instance. Action: Either wait for all users to logoff or use SHUTDOWN IMMEDIATE. ORA-01089 immediate shutdown in progress - no operations are permitted Cause: The SHUTDOWN IMMEDIATE command was used to shut down a running Oracle instance, terminating any active operations. Action: Wait for the instance to be restarted or contact the database administrator. ORA-01090 shutdown in progress - connection is not permitted Cause: The SHUTDOWN command was used to shut down a running Oracle instance, disallowing any connects to Oracle. Action: Wait for the instance to restart or contact the database administrator. ORA-01091 failure during startup force Cause: Unable to destroy the old SGA. Action: Manually remove the old SGA. See your operating system-specific Oracle documentation for instructions. Then retry the STARTUP command. ORA-01092 ORACLE instance terminated. Disconnection forced Cause: The instance connected to was terminated abnormally, probably due to a SHUTDOWN ABORT. The current process was forced to disconnect from the instance. Action: Examine the alert log for more details. Contact the database administrator to determine when the instance is restarted. Attempt to reconnect after the instance is running again. ORA-01093 ALTER DATABASE CLOSE only permitted with no sessions connected Cause: There is at least one more session, other than the current one, logged in to the instance. ALTER DATABASE CLOSE is not permitted when other sessions are active. Action: Find the other sessions and disconnect them. Then reissue the ALTER DATABASE CLOSE statement. Also, issue the SHUTDOWN IMMEDIATE command to force users off the system, or issue the SHUTDOWN ABORT command to shut down the database without waiting for users to be forced off. ORA-01094 ALTER DATABASE CLOSE in progress. Connections not permitted Cause: An attempt was made to connect while the database is being closed with the ALTER DATABASE CLOSE statement. Action: Attempt to connect again when the database is open. Contact the database administrator to find out when the database will be open. ORA-01095 DML statement processed zero rows Cause: A DML cursor from the OTEX() call processed 0 rows when executed. Action: Caller can either do a rollback or ignore the message and execute the rest of the cursors in the cursor array. ORA-01096 program version (string) incompatible with instance (string) Cause: The program was linked with a different version of the server than the instance to which it is attempting to connect. Action: Relink the program against the same version of Oracle as the instance or restart (startup force) the database using the program's version of the SERVER. ORA-01097 cannot shutdown while in a transaction - commit or rollback first Cause: An attempt was made to shut down the database while a transaction was in progress. Action: Either commit or rollback the current transaction and then attempt to shut down the database. ORA-01098 program Interface error during Long Insert Cause: The application program interface could not insert or update a LONG column because of an internal error when a TTCGETV call fails. For example, a network read error or one of the parameters passed to the call is invalid. Action: Retry the insert or update. ORA-01099 cannot mount database in SHARED mode if started in single process mode Cause: An attempt was made to mount a database in parallel mode with the initialization parameter SINGLE_PROCESS set to TRUE. Action: Either mount the database in EXCLUSIVE mode or set the initialization parameter SINGLE_PROCESS to FALSE before starting the instance in parallel (shared) mode. ORA-01100 database already mounted Cause: An attempt was made to mount a database with the name of a currently mounted database. Action: No action is required. ORA-01101 database being created currently mounted by some other instance Cause: Some other instance has the database of same name currently mounted and you are trying to create it. Action: Either change the database name or shut down the other instance. ORA-01102 cannot mount database in EXCLUSIVE mode Cause: Some other instance has the database mounted exclusive or shared. Action: Shut down the other instance or mount in a compatible mode. ORA-01103 database name 'string' in controlfile is not 'string' Cause: The database name in the control file does not match your database name. Action: Either find the correct control file or change your database name. ORA-01104 number of controlfiles (string) does not equal string Cause: The number of control files used by this instance disagrees with the number of control files in an existing instance. Action: Check to make sure that all control files are listed. ORA-01105 mount is incompatible with mounts by other instances Cause: An attempt to mount the database discovered that another instance mounted a database by the same name, but the mount is not compatible. Additional errors are reported explaining why. Action: See accompanying errors. ORA-01106 database must be closed before dismounting Cause: An attempt was made to dismount a database before it was closed. Action: Close the database, then retry the operation. ORA-01107 database must be mounted for media recovery Cause: An attempt to perform media recovery was made but the database is not mounted. Action: Mount the database. ORA-01108 file string is in backup or media recovery" Cause: Either media recovery is actively being applied to the file, or it is being backed up while the database is in NOARCHIVELOG mode. It cannot be used for normal database access or crash recovery. Action: Complete or cancel the media recovery session or backup. ORA-01109 database not open Cause: A command was attempted that requires the database to be open. Action: Open the database and try the command again. ORA-01110 data file string: 'string' Cause: This message reports the file name involved with other messages. Action: See the associated messages for a description of the problem. ORA-01111 name for data file string is unknown - rename to correct file Cause: The datafile was missing from a CREATE CONTROLFILE command or BACKUP CONTROLFILE RECOVERY was done with a control file that was saved before the file was created. Action: Rename the MISSING file to the name of the real file. ORA-01112 media recovery not started Cause: An attempt to continue media recovery is being made but media recovery was not started. Action: No action is required. ORA-01113 file string needs media recovery Cause: An attempt was made to open a datafile that is in need of media recovery. Action: First apply media recovery to the datafile identified in the message, then retry the operation. ORA-01114 IO error writing block to file string (block # string) Cause: The device on which the file resides is probably offline. If the file is a temporary file, then it is also possible that the device has run out of space. This could happen because disk space of temporary files is not necessarily allocated at file creation time. Action: Restore access to the device or remove unnecessary files to free up space. ORA-01115 IO error reading block from file string (block # string) Cause: The device on which the file resides is probably off line. Action: Restore access to the device, then retry the operation. ORA-01116 error in opening database file string Cause: Usually the file is not accessible. Action: Make sure the datafile is in the expected location and can be accessed properly, then retry the operation. ORA-01117 adding file 'string' with illegal block size: string; limit is string Cause: An attempt was made to add a datafile with a block size that is greater than the maximum block size allowed. Action: Retry the operation with a smaller block size. ORA-01118 cannot add any more database files: limit of string exceeded Cause: An attempt to add a datafile failed because the limit for such files had already been reached. Action: If more database space is required, export the database and re-create it with a higher limit for datafiles and perhaps increased file size. ORA-01119 error in creating database file 'string' Cause: Insufficient space on device. Action: Decrease the size of the requested file or allocate a file on another device with more available space. If a file size was not specified in the statement, then specify a file size smaller than the available space on the device. ORA-01120 cannot remove online database file string Cause: An attempt was made to drop a datafile when it is online. Action: Take file offline before dropping. ORA-01121 cannot rename database file string - file is in use or recovery Cause: An attempt was made to use ALTER DATABASE RENAME to rename a datafile that is online in an open instance or is being recovered. Action: Close database in all instances and end all recovery sessions. ORA-01122 database file string failed verification check Cause: The information in this file is inconsistent with information from the control file. See accompanying message for reason. Action: Make certain that the database files and control files are the correct files for this database. ORA-01123 cannot start online backup; media recovery not enabled Cause: An attempt to start backup of an online tablespace failed because media recovery is not enabled. Action: Enable media recovery and retry this operation. ORA-01124 cannot recover data file string - file is in use or recovery Cause: An attempt to do media recovery found that the file was not available for recovery. Either it is online and the database is open in some instance, or another process is currently doing media recovery on the file. Action: Do not do media recovery. ORA-01125 cannot disable media recovery - file string has online backup set Cause: An attempt to disable media recovery found that an online backup is still in progress. Action: End the backup of the offending tablespace and retry this command. ORA-01126 database must be mounted EXCLUSIVE and not open for this operation Cause: An operation failed because the database was not mounted in exclusive mode. Action: Shut down Oracle and mount the database in exclusive mode. ORA-01127 database name 'string' exceeds size limit of string characters Cause: The specified database name is too long. Action: Shorten the database name to eight characters or fewer, then retry the operation. ORA-01128 cannot start online backup - file string is offline Cause: An attempt to start an online backup found that one of the files is offline. Action: Bring the offending files online and retry this command or do a cold backup. ORA-01129 user's default or temporary tablespace does not exist Cause: The user's default or temporary tablespace was dropped. Action: Reassign the default or temporary tablespace. ORA-01130 database file version string incompatible with ORACLE version string Cause: The named datafile was created under an incompatible version of Oracle, or the file is invalid or non-existent. Action: Shut down and restart the correct version of Oracle or check the references to the datafile and make sure it exists. ORA-01131 DB_FILES system parameter value string exceeds limit of string Cause: The specified value of the initialization parameter DB_FILES is too large. Action: Reduce the value of the DB_FILES parameter and retry the operation. ORA-01132 length of database file name 'string' exceeds limit of string characters Cause: The specified datafile name is too long. Action: Shorten the file name and retry the operation. ORA-01133 length of log file name 'string' exceeds limit of string characters Cause: The specified redo log file name is too long. Action: Shorten the file name and retry the operation. ORA-01134 database mounted exclusive by another instance Cause: An attempt to access a database failed because it is mounted in exclusive mode by another instance. Action: Either shut down the other instance or wait for the other instance to either close the database or mount it in parallel mode, before retrying the operation. ORA-01135 file string accessed for DML/query is offline Cause: An attempt was made to access a datafile that is offline. Action: Bring the datafile back online. ORA-01136 specified size of file string (string blocks) is less than original size of string blocks Cause: A file size was specified in the AS clause of the ALTER DATABASE CREATE DATAFILE statement, and the size was smaller than the size needed. Action: Create the file with a larger size. ORA-01137 data file string is still in the middle of going offline Cause: It was not possible to get the lock for a file that is offline when attempting to bring it online. The most likely cause is that the lock is still held by the instance that is took it offline. Action: Wait for the other instance to release the lock on the file. ORA-01138 database must either be open in this instance or not at all Cause: The requested operation cannot be done when the database is mounted but not open in this instance, and another instance has the database open. Action: Execute the operation in an open instance, open the database in this instance, or close the database in the other instances. ORA-01139 RESETLOGS option only valid after an incomplete database recovery Cause: The RESETLOGS option was given in ALTER DATABASE OPEN, but there has been no incomplete recovery session. Action: Reissue the ALTER DATABASE OPEN statement without specifying '. ORA-01140 cannot end online backup - all files are offline Cause: All the files were found to be offline when attempting to end an online backup. Action: No action required. Online backup does not need to be ended for this tablespace. ORA-01141 error renaming data file string - new file 'string' not found Cause: An attempt to change a datafile's name in the control file failed because no file was found with the new name. Action: Make sure that the datafile has been properly renamed by the operating system and retry. ORA-01142 cannot end online backup - none of the files are in backup Cause: None of the files were found to be in online backup when attempting to end an online backup. Action: No action required. Online backup does not need to be ended for this tablespace. ORA-01143 cannot disable media recovery - file string needs media recovery Cause: An attempt to disable media recovery found a file that needs media recovery, thus media recovery cannot be disabled. Action: Recover the offending file or drop the tablespace it belongs to and retry this command. ORA-01144 File size (string blocks) exceeds maximum of string blocks Cause: Specified file size is larger than maximum allowable size value. Action: Specify a smaller size. ORA-01145 offline immediate disallowed unless media recovery enabled Cause: ALTER TABLESPACE ... OFFLINE IMMEDIATE or ALTER DATABASE DATAFILE ... OFFLINE is only allowed if the database is in ARCHIVELOG mode. Action: Take the tablespace offline as usual or do a shutdown abort before attempting the required operation. It is worthwhile reconsidering the backup strategy for the database. The attempted operations can be done if ARCHIVEREDO LOG mode is enabled. ORA-01146 cannot start online backup - file string is already in backup Cause: An attempt was made to start an online backup, but an online backup was already started for one of the datafiles. Action: End the first backup before beginning another. ORA-01147 SYSTEM tablespace file string is offline Cause: A file belonging to the SYSTEM tablespace has been marked offline by the database administrator. The database cannot be started until all SYSTEM tablespace files are online and can be opened. Action: Bring the file online. ORA-01148 can not mount database PARALLEL without a lock manager installed Cause: An attempt was made to mount the database PARALLEL without the existence of a Lock Manager. Action: Mount the database in EXCLUSIVE mode. ORA-01149 cannot shutdown - file string has online backup set Cause: An attempt to shut down normally found that an online backup is still in progress. Action: End the backup of the offending tablespace and retry this command. ORA-01150 cannot prevent writes - file string has online backup set Cause: Tablespace cannot be taken offline or made read-only when online backup is running. Ending the backup entails writing the file header, which this operation, were it permitted, would prevent. Action: End the online backup and then take the tablespace offline or make it read-only. ORA-01151 use media recovery to recover block, restore backup if needed Cause: Crash recovery or instance recovery could not apply a change to a block because it was not the next change. This can happen if the block was corrupted and then repaired during recovery. This message is usually accompanied by ORA-01172. Action: There is additional information for ORA-01172. Perform a RECOVER datafile for the file containing the block. If this does not resolve the problem, restore the file from a backup and recover it again. ORA-01152 file string was not restored from a sufficiently old backup Cause: An incomplete recovery session was started, but an insufficient number of redo logs were applied to make the database consistent. This file is still in the future of the last redo log applied. The most likely cause of this message is forgetting to restore the file from backup before doing incomplete recovery. Action: Apply additional redo log files until the database is consistent or restore the datafiles from an older backup and repeat recovery. ORA-01153 an incompatible media recovery is active Cause: Attempted to start an incompatible media recovery or open resetlogs during media recovery or RMAN backup . Media recovery sessions are incompatible if they attempt to recover the same data file. Incomplete media recovery or open resetlogs is incompatible with any media recovery. Backup or restore by RMAN is incompatible with open resetlogs. Action: Complete or cancel the other media recovery session or RMAN backup. ORA-01154 database busy. Open, close, mount, and dismount not allowed now Cause: Some operation is in progress that expects the state of the instance to remain open or mounted. Action: Wait for the operation to complete and try again. If attempting a normal database shutdown, try SHUTDOWN ABORT. ORA-01155 the database is being opened, closed, mounted or dismounted Cause: The attempted operation will not succeed while the instance is in one of the states mentioned in the message. Action: Wait for the open, close, mount, or dismount to complete and try again. If attempting a normal database shutdown, try SHUTDOWN ABORT. ORA-01156 recovery in progress may need access to files Cause: Either media or instance recovery is in progress. The recovery may need access to the files that the attempted operation tried to use. Action: Wait for the recovery to complete and try again. ORA-01157 cannot identify/lock data file string - see DBWR trace file Cause: The background process was either unable to find one of the data files or failed to lock it because the file was already in use. The database will prohibit access to this file but other files will be unaffected. However, the first instance to open the database will need to access all online data files. The accompanying error from the operating system describes why the file could not be identified. Action: At the operating system level, make the file available to the database. Then either open the database or do an ALTER SYSTEM CHECK DATAFILES. ORA-01158 database string already mounted Cause: Another instance has a database by this name mounted. Action: Find which instance is still running. Perhaps you have not lost the control files after all. ORA-01159 file is not from same database as previous files - wrong database id Cause: Not all of the files specified in CREATE CONTROLFILE are from the same database. The database ID of this file does not match that from the first file specified. Action: Check the list of files provided to the CREATE. ORA-01160 file is not a string Cause: The named file in the DATAFILE or LOGFILE section of the CREATE CONTROLFILE command does not appear to be as stated. Action: Check the mentioned file. ORA-01161 database name string in file header does not match given name of string Cause: The database name given at the command line does not match the database name found in the file header. Action: Chance are good that the database name specified at the command line is incorrect. Resolve the discrepancy, and resubmit the command. If you are attempting to change the database name, be sure to use the SET DATABASE option. ORA-01162 block size string in file header does not match configured block sizes Cause: CREATE CONTROLFILE discovered that the block size for this file is incompatible with any of the configured cache blocksizes in the initialization parameter file. Action: Configure the appropriate cache for this block size using one of the (DB_2K_cache_size, DB_4K_cache_size, DB_8K_cache_size, DB_16K_cache_size, DB_32K_cache_size) parameters. ORA-01163 SIZE clause indicates string (blocks), but should match header string Cause: The size specified in bytes in the SIZE clause of the CREATE CONTROLFILE statement does not equate to the number of blocks recorded in the header. Action: Specify the correct file name and size (in bytes). ORA-01164 MAXLOGFILES may not exceed string Cause: MAXLOGFILES specified on the command line too large. Action: Resubmit the command with a smaller value for MAXLOGFILES. ORA-01165 MAXDATAFILES may not exceed string Cause: MAXDATAFILES specified on the command line too large. Action: Resubmit the command with a smaller value for MAXDATAFILES. ORA-01166 file number string is larger than string (string) Cause: File mentioned in CREATE CONTROLFILE has a file number which is larger than that specified for MAXDATAFILES or MAXLOGFILES. Action: Increase the maximum specified on the command line. ORA-01167 two files are the same file/group number or the same file Cause: There is an overlap of file numbers in the files specified on the command line or the same file is specified twice. If they are not the exact same file then one is likely to be a backup of the other. If they are two members of the same log they must be specified together in a group file specification. This message will also appear if the same control file appears more than once in the CONTROL_FILES parameter in the INIT.ORA file. If this happens, check for additional error messages. Action: Confirm that the file mentioned is not a repeat of a file already mentioned in the command. If they are different files then omit the earlier backup. If they are members of the same log, insure they are in the same group file specification. If this message appears because of a duplicate control file, check the CONTROL_FILES parameter in the INIT.ORA file and see if a file is specified more than once. If all files names appear to be unique, check to make sure that the actual control files themselves are unique. For example, in UNIX check for a symbolic or a hard link to another control file in the list. ORA-01168 physical block size string does not match size string of other members Cause: The file is located on a device with a different physical block size than the other members in the group. Action: Use a physical device with matching block size. ORA-01169 DATAFILE number 1 not found. Must be present Cause: Datafile number 1 was not specified in a CREATE CONTROLFILE command. Action: Locate datafile number 1 and resubmit the CREATE CONTROLFILE command. ORA-01170 file not found 'string' Cause: A file specified in the CREATE CONTROLFILE statement was not found. All datafiles and all redo log files, if NORESETLOGS was used, must be accessible by the process that issues the CREATE CONTROLFILE statement. Action: Check the statement for a typing mistake in the file name and check for the existence of all files. Then issue the statement again after correcting the file names. ORA-01171 datafile string going offline due to error advancing checkpoint Cause: The checkpoint in the file header could not be advanced. See accompanying errors for the reason. The datafile will be taken offline the same as for a write error of a data block. Action: See accompanying errors for details. Restore access to the file, do media recovery, and bring it back online. ORA-01172 recovery of thread string stuck at block string of file string Cause: Crash recovery or instance recovery could not apply a change to a block because it was not the next change. This can happen if the block was corrupted and then repaired during recovery. Action: Do a RECOVER DATAFILE for the file containing the block. If this does not resolve the problem, then restore the file from a backup and recover it. ORA-01173 data dictionary indicates missing data file from system tablespace Cause: One of the following: The database is recovered to a point in time in the future of the control file. A datafile from the system tablespace is omitted from the issued CREATE CONTROLFILE statement. Action: One of the following: Recover the database from a more recent control file. Re-create the control file, ensuring all datafiles are included for the system tablespace in the command line. ORA-01174 DB_FILES is string buts needs to be string to be compatible Cause: The maximum number of database files supported by this instance is not the same as for the other instances. All instances must be able to open all the files any instance can open. Action: Change the value of the DB_FILES parameter to be compatible. ORA-01175 data dictionary has more than the string files allowed by the instance Cause: The data dictionary is found to have more files than that which can be supported by this instance. Action: Shut down the instance and restart with a larger value for DB_FILES. ORA-01176 data dictionary has more than the string files allowed by the controlfie Cause: After a CREATE CONTROLFILE, the data dictionary was found to have more datafiles than that supported by the control file. Action: Re-create the control file with a larger value for MAXDATAFILES. ORA-01177 data file does not match dictionary - probably old incarnation Cause: When comparing the control file with the data dictionary after a CREATE CONTROLFILE or OPEN RESETLOGS, it was noted that this datafile was inconsistent with the dictionary. Most likely the file is a backup of a file that was dropped from the database, and the same file number was reused for a new file. It may also be that an incomplete recovery stopped at a time when this file number was used for another datafile. Action: Do a CREATE CONTROLFILE with the correct file or none at all. ORA-01178 file string created before last CREATE CONTROLFILE, cannot recreate Cause: An attempt was made to use ALTER DATABASE CREATE DATAFILE to re-create a datafile that existed at the last CREATE CONTROL file command. The information needed to re-create the file was lost with the control file that existed when the file was added to the database. Action: Find a backup of the file, and recover it. Do incomplete recovery to time before file was originally created. ORA-01179 file string does not exist Cause: During datafile recovery, a file was listed that was not part of the database. Action: Recheck the file name. Remember to use single quotation marks at the SQL*DBA command line. Remember also that the file name is translated in the operating environment of SQL*DBA. ORA-01180 can not create datafile 1 Cause: An attempt was made to create datafile 1 using ALTER DATABASE CREATE DATAFILE. Action: Recover file from a backup or re-create the database. ORA-01181 file string created before last RESETLOGS, cannot recreate Cause: An attempt was made to use ALTER DATABASE CREATE DATAFILE to re-create a datafile that existed at the last RESETLOGS database open. Action: Find a backup of the file, and recover it. Do incomplete recovery to time before file was originally created. ORA-01182 cannot create database file string - file is in use or recovery Cause: An attempt was made to use ALTER DATABASE CREATE DATAFILE to re-create a datafile that is online in an open instance or is being recovered. Action: Close database in all instances and end all recovery sessions. ORA-01183 cannot mount database in SHARED mode Cause: Some other instance has the database mounted exclusive. Action: Shut down the other instance and then mount shared. ORA-01184 logfile group string already exists Cause: An ALTER DATABASE ADD LOGFILE statement specified a log number for the new log which is already in use. Action: Specify a different log file number, or let the database choose an unused value. ORA-01185 logfile group number string is invalid Cause: An ALTER DATABASE ADD LOGFILE statement specified a log number for the new log which is too large. Action: Specify a correct log file number. ORA-01186 file string failed verification tests Cause: The datafile did not pass the checks to insure it is part of the database. See the accompanying error messages for the reason the verification failed. Action: Make the correct file available to the database. Then, either open the database, or execute ALTER SYSTEM CHECK DATAFILES. ORA-01187 cannot read from file string because it failed verification tests Cause: The datafile did not pass the checks to insure it is part of the database. Reads are not allowed until it is verified. Action: Make the correct file available to the database. Then, either open the database, or execute ALTER SYSTEM CHECK DATAFILES. ORA-01188 Block size string in header does not match physical block size string Cause: A log file member given to CREATE CONTROLFILE is on a physical device that has a different block size than the device originally used to create the log. Action: Move the file to a device with the correct block size or use the RESETLOGS option to CREATE CONTROLFILE. ORA-01189 file is from a different RESETLOGS than previous files Cause: In a CREATE CONTROLFILE command, either this file or all previous files were backups from before the last RESETLOGS. This may also occur if this is a file that is offline and has been offline since before the last RESETLOGS. Action: If the file was taken offline normal before the last RESETLOGS, and is still offline, omit it from the CREATE CONTROLFILE command. Rename and online the file after the database is open. Otherwise find the version of the mentioned file consistent with the rest of the datafiles and resubmit the command. ORA-01190 controlfile or data file string is from before the last RESETLOGS Cause: An attempt was made to use a datafile when the log reset information in the file does not match the control file. Either the datafile or the control file is a backup that was made before the most recent ALTER DATABASE OPEN RESETLOGS. Action: Restore file from a more recent backup. ORA-01191 file string is already offline - cannot do a normal offline Cause: When attempting to do a normal tablespace offline, it was discovered that one of the files in the tablespace was already offline. Action: Either bring the datafile online first, or use another tablespace offline option. ORA-01192 must have at least one enabled thread Cause: At least two redo log files from at least one thread must be specified in the CREATE CONTROLFILE command line. Action: Find the missing redo log files and retry the command with the newly found redo log files included in the command line. ORA-01193 file string is not the same file seen at start of recovery Cause: A different copy of the file was accessed the last time media recovery looked at the file header. A backup of the file was restored or the meaning of the file name changed during recovery. Action: Ensure the correct file is available, and then retry recovery. ORA-01194 file string needs more recovery to be consistent Cause: An incomplete recovery session was started, but an insufficient number of logs were applied to make the file consistent. The reported file was not closed cleanly when it was last opened by the database. It must be recovered to a time when it was not being updated. The most likely cause of this error is forgetting to restore the file from a backup before doing incomplete recovery. Action: Either apply more logs until the file is consistent or restore the file from an older backup and repeat recovery. ORA-01195 online backup of file string needs more recovery to be consistent Cause: An incomplete recovery session was started, but an insufficient number of logs were applied to make the file consistent. The reported file is an online backup which must be recovered to the time the backup ended. Action: Either apply more logs until the file is consistent or restore the database files from an older backup and repeat recovery. ORA-01196 file string is inconsistent due to a failed media recovery session Cause: The file was being recovered but the recovery did not terminate normally. This left the file in an inconsistent state. No more recovery was successfully completed on this file. Action: Either apply more logs until the file is consistent or restore the backup again and repeat recovery. ORA-01197 thread string only contains one log Cause: During CREATE CONTROLFILE all threads represented in the logs must be represented by at least two logs. A "last log" and a second log. The named thread

ODP.NET을 통한 Oracle 액세스

Developer개발자 ODP.NET

ODP.NET을 통한 Oracle 액세스

By Robert P. Lipschutz and Gregg D. Harrington

이제 .NET 개발자들은 Oracle 고유의 데이타 액세스를 사용하여 애플리케이션 성능을 개선할 수 있습니다.

Oracle이 지원하는 기업용 애플리케이션으로부터 기대했던 효과와 고급 데이타베이스 기능을 얻으려면, 일반 데이타베이스 연결에 대해 Oracle이 지정한 연결을 선택해야 합니다. Java 개발자들은 자신들에게 주어진 오랫동안의 옵션 즉 JDBC를 확장하고 개발자들에게 LOB(large objects) 및 참조 커서와 같은 고급 Oracle 기능에 액세스할 수 있도록 해주는 API를 사용함으로써, Oracle 데이타베이스에 연결할 수가 있습니다.

2002년 2월 Microsoft는 3개 언어(C# .NET, Visual Basic .NET, C++ .NET)를 사용하는 Visual Studio .NET과 기본 .NET 프레임워크를 출시했습니다. Microsoft .NET은 엔터프라이즈 급 데스크톱, 웹, 클라이언트/서버 애플리케이션을 구축하는 데 필요한 객체 지향적인 개발 플랫폼을 제공합니다. 그러나 유감스럽게도 이러한 Microsoft 언어와 .NET 프레임워크를 사용하는 개발자들은 Oracle이 지정하는 데이타베이스 연결 옵션을 사용할 수가 없습니다.

이러한 문제를 시정하기 위해 Oracle은 Microsoft .NET 환경에서 사용할 수 있는 Oracle 데이타베이스 액세스 API와 .NET용 Oracle Data Provider(ODP.NET)를 작성했습니다. 본문에서 우리는 ODP.NET의 사용법, 기능 및 개선된 성능에 대해, 그리고 ODP.NET를 사용함으로써 얻을 수 있는 획기적인 이점에 대해 논하게 될 것입니다.

ODP.NET의 기본 기능

우리는 Oracle 데이타베이스 연결을 위해 ODP.NET을 사용하여 Microsoft Visual Studio .NET에서 dejavu라는 간단한 C# 애플리케이션을 구축하였습니다. 자세한 코드는 목록 1과 목록2에 나와 있습니다. 본문에 나오는 모든 예제는 기본적으로 이 애플리케이션과 그에 수반하는 데이타베이스를 사용하여 만들어 졌습니다.

데이타베이스의 각 레코드에는 직원들에 대한 정보가 들어 있습니다. 이 애플리케이션에는 표 1에 간략하게 소개되어 있는 4개의 필드와 함께 기본 테이블이 사용되었습니다.

여러분은 ODP.NET을 사용하면 기본 INSERT와 SELECT 문을 비교적 쉽게 연결하고 사용할 수 있다는 점을 알게 될 것입니다. 이 작업은 JDBC 또는 OLE DB .NET을 사용하는 경우와 유사합니다.
dejavu 작성 시 ODP.NET에 액세스하려면, 먼저 다음과 같은 기본 단계를 실행해야 합니다.

1. ODP.NET 소프트웨어를 설치합니다.
   
2. Visual Studio의 Solution Explorer에서, Oracle.DataAccess DLL에 참조 사항을 추가합니다.
   
3. C# 클래스에서, 절(Oracle.DataAccess.Client, Oracle.DataAccess.Types)을 사용하여 2개의 이름 공간을 코드 파일 상단에 추가합니다. (Visual Basic .NET 또는 다른 .NET 순응 언어를 사용하는 경우, 이름 공간을 추가하기 위한 구문이 약간 다를 수도 있습니다.)

ODP.NET에 액세스한 후에는, 다음과 같이 기본 삽입을 위한 단계를 실행합니다.

1. 데이타베이스 연결을 생성하고 엽니다.
   
2. 명령을 생성합니다.
   
3. 위의 명령을 실행합니다.

여기에서는 먼저 데이타베이스 연결을 생성하고 여십시오.

OracleConnection dbConn = new OracleConnection(
	"Data Source=" + txtDataSource.Text + ";" + 
	"User Id=" + txtUsername.Text + ";" + 
	"Password=" + txtPassword.Text + ";");
dbConn.Open();

그런 다음 OracleConnection object를 사용하여 데이타베이스에 연결하십시오. 이 객체의 인스턴스를 생성하는 경우, OracleConnection 객체는 Oracle 데이타베이스 서버를 찾고 인증하기 위해 사용하는 연결 문자열을 입력합니다.

이 경우 연결 문자열은 세 가지 연결 매개변수인 Data Source, User Id, Password를 갖으며, Oracle Data Provider는 OCI(Oracle Call Interface)를 사용합니다. 따라서 Data Source는 Oracle 클라이언트 소프트웨어가 사용하는 tnsnames.ora 파일로부터 사용할 수 있는 TNS 이름이 되고, User Id는 이름이 지정된 Data Source에 사용할 수 있는 사용자 이름이 되며, Password는 제공되는 User Id와 일치하는 암호 값이 됩니다.

그런 후, 다음과 같이 OracleConnection 객체에서 Open() 메소드를 호출하여 연결을 열고, 이미 생성한 OracleConnection 객체와 관련된 OracleCommand 객체를 생성해 보십시오.

OracleCommand cmd = new OracleCommand(
	"insert into dejavu_employee values " + 
	"(1, 'Gregg D. Harrington', empty_blob())", dbConn);

OracleCommand 객체를 생성하는 경우에는 생성자에게 2개의 매개변수를 제공해야 합니다. 첫 번째 매개변수는 실행하고자 하는 SQL 명령어로, 여기에는 문자열로서 작성된 유효한 SQL 문을 사용할 수 있습니다. 이 경우 사원을 DEJAVU_EMPLOYEE 테이블에 삽입합니다. 두 번째 매개변수는 데이타베이스에 연결된 OracleConnection 객체입니다.

마지막으로, 다음과 같이 OracleCommand 객체를 실행해 보십시오.

int rows = cmd.ExecuteNonQuery();
ShowInfo(rows + " where added to the 
database");

이 경우, ExecuteNonQuery() 메소드를 사용할 수 있도록 데이타베이스에 행 하나를 삽입하십시오. 이 메소드는 SQL INSERT, UPDATE, 또는 DELETE 문과 같이 행을 반환하는 질의가 예상되지 않는 경우 사용합니다(SELECT 문의 경우, ExecuteReader() 메소드가 사용됩니다). ExecuteNonQuery() 메소드는 우리가 생성한 SQL 문을 취하여, 실행을 위해 데이타베이스로 보내고, 관련된 행 번호를 반환합니다.

이제 데이타베이스에 삽입한 레코드를 읽고, 사용자에게 이름 열을 표시해 보이십시오. 또한 데이타베이스에 새로 연결하지 않고 기존의 연결과 명령어 객체를 계속 사용하십시오.

SQL 문을 변경하려면, 다음과 같이 실행하고자 하는 새 SQL 문의 텍스트로 OracleCommand 객체의 CommandText 속성을 변경해 보겠습니다.

cmd.CommandText = "select * from dejavu_employee where " + 
"id = 1";

이 방법은 단일 데이타베이스 연결을 사용하여 하나의 OracleCommand 객체로 여러 가지 SQL 문을 실행하고자 하는 경우에 더욱 유용합니다.

그런 다음, 명령을 실행하고 그 결과를 읽어 보십시오.

OracleDataReader reader = cmd.ExecuteReader();
reader.Read();

ShowInfo("The selected user's name was " + 
	reader["name"].ToString());
dbConn.Close();

여기에서 새로운 객체 OracleDataReader를 볼 수 있습니다. 이 객체는 하나 이상의 레코드를 다시 읽는 경우 사용됩니다. 예제에서는 기본 키 열에 있는 하나의 특정 값을 가져왔기 때문에 단 하나의 레코드만이 반환되었습니다. ExecuteReader() 메소드는 하나 이상의 결과 레코드를 포함하는 OracleDataReader 객체를 반환합니다.

OracleDataReader 객체를 일단 갖게 되면 원하는 데이타 즉, 위의 경우 이름 열을 얻을 수 있습니다. OracleDataReader를 처음 생성하는 경우, 이것은 첫 번째 레코드에 위치하지 않기 때문에 첫 번째 레코드를 읽기 전에 Read() 메소드를 먼저 불러 와야 합니다. Read() 메소드는 다음 레코드로 이동하고, 다른 레코드가 있는 경우 참 부울 값을 반환합니다.

다음으로, OracleDataReader 객체를 위해 기본 문자열-배열 속성을 사용하여, 이름 열 값을 가져와 이 이름을 원하는 열(위의 예제의 경우 name 열)에 전달하십시오. 마지막으로 OracleConnection 객체에서 Close() 메소드를 호출하고 연결을 닫으십시오.

기본 ODP.NET 클래스와 동종 클래스의 비교

ODP.NET에 사용되는 기본 클래스는 다른 연결 옵션에서 사용되는 클래스와 유사합니다. 메소드 이름과 구현 방법이 다른 경우에도 기본 논리는 같습니다. 표 2에는 객체를 표시하기 위해, ODP .NET, OLE DB .NET 및 Oracle thin JDBC 드라이버가 사용하는 객체와 몇몇 데이타베이스 작업이 나열되어 있습니다.

고급 ODP.NET의 기능

지금까지 ODP.NET의 기본 기능을 살펴 보았습니다. 이제 ODP.NET을 강력하게 만들어 주는 주요 기능인 LOB 및 참조 커서 처리 기능에 대해 살펴보도록 하겠습니다.

Oracle LOB, BLOB 및 Bfile. Oracle LOB는 그림과 텍스트 문서를 포함하는 다양한 종류의 대형 문자(large character)나 바이너리 데이타를 저장하는데 사용됩니다. ODP.NET은 LOB를 가져오고 관리하는 고유 메소드를 제공함으로써, LOB를 처리하고 개발자들의 코드 작성 절차를 보다 쉽게 만들어 주는 코드 성능을 개선시켜 줍니다.

ODP.NET에는 OracleBlob 객체와 OracleClob 객체가 포함되며, 이 객체들은 System.IO.Stream 객체의 기능을 확장시켜 줍니다. 개발자들은 이러한 데이타 유형을 사용함으로써 적은 성능 오버헤드만을 허용하여 데이타베이스에 LOB를 쉽게 작성하고 읽을 수 있습니다.

BLOB(Binary Large Objects) 및 CLOB(Character Large Objects)를 처리하는 ODP.NET의 능력을 보려면, 간단한 예제를 참고하십시오. 이 예제에서 우리는 사진 파일(photo file)을 열고 이 파일을 데이타베이스에 작성할 수가 있습니다. 이 프로세스의 처음 두 단계 즉, 데이타베이스 연결을 생성하고 개시하는 단계와 명령어를 생성하는 단계는 이전 예제에서 실행한 방법으로 실행합니다.

그런 다음, 다음과 같이 트랜잭션을 생성해 보십시오.

OracleTransaction trans = dbConn.BeginTransaction();

BLOB를 업그레이드하는 경우, 데이타 무결성을 유지하기 위해 트랜잭션의 내부에서 모든 조작을 실행해야 합니다. (단순히 BLOB 데이타베이스 객체를 가져오는 경우에는 트랜잭션이 필요없습니다.) 지역 트랜잭션을 생성하려면, OracleConnection 객체의 BeginTransaction() 메소드를 호출하고, 그러면 변수에 할당한 OracleTransaction 객체가 이후 사용을 위해 반환됩니다.

이제 다음과 같이 레코드를 열고 OracleBlob 객체를 얻어 보도록 하겠습니다.

cmd.CommandText = "select * from dejavu_employee where " + 
	"id = 1 for update";

OracleDataReader reader = cmd.ExecuteReader();
reader.Read();
OracleBlob lob = reader.GetOracleBlob(2); // ordinal position of blob

위의 예제에서는, 사진 파일에서 스트림하기 위해 OracleBlob 객체를 가져왔습니다. 먼저 원하는 레코드인 ID가 1인 사원 레코드를 선택합니다. BLOB를 업데이트할 것이기 때문에 데이타베이스 행은 잠겨 있어야 합니다. 여기서, SELECT 문의 업데이트 절(update clause)을 사용하여 이 작업을 실행합니다.
그런 다음, OracleDataReader를 열고 Read() 메소드를 불러 처음 레코드로 이동하십시오. 마지막으로 OracleDataReader.GetOracleLob() 메소드를 사용하여 OracleDataReader로부터 OracleBlob 객체를 얻습니다.

이제 목록 3에서 보는 바와 같이 사진을 서버에 삽입할 준비가 되었습니다. 이 때 애플리케이션은 사용자에게 데이타베이스에 업로드하고자 하는 파일 이름을 입력할 것을 요구합니다. 그러면 Visual Studio 형식 디자이너에서 생성된 OpenFileDialog 객체(위의 예제의 경우, ofdPicture)를 사용하십시오. 그런 다음, System.IO.FileStream 객체를 사용하여 파일을 열고, WHILE 루프가 있는 Read() 및 Write() 메소드를 사용하여 FileStream 객체로부터 OracleBlob 객체로 데이타를 복사하십시오.

OracleClob 객체는 OracleBlob 객체와 유사하게 사용됩니다. CLOB 데이타베이스 객체를 읽는 경우, OracleClob 객체를 사용하여 데이타베이스로부터 문자 데이타(character data)를 읽을 수 있습니다. OracleClob 객체는 Read() 메소드를 우선적으로 적용하고, 개발자에게 CLOB를 갖는 문자열이나 문자 배열을 채울 수 있는 방법을 제공합니다.
ODP.NET은 또한 Oracle 데이타베이스로부터 Bfile 데이타베이스 객체를 읽는 데 필요한 OracleBlob과 유사한 객체를 제공합니다. 규모가 큰 파일을 저장하는 데 유용한 Bfile은 파일 시스템의 데이타베이스 외부에 저장되는 LOB입니다.

참조 커서와 저장된 프로시저. 참조 커서는 저장된 프로시저를 통해 열리고, 출력 매개변수를 사용하여 애플리케이션에 전달됩니다.

ODP.NET에서의 참조 커서 사용에 대한 자세한 내용은 목록 4의 PL/SQL 코드를 참조하십시오. 이 코드는 매개변수의 n_empid 및 n_empid2에 근거하여 DEJAVU_EMPLOYEE 테이블에서 2개의 레코드를 불러 온 다음, 출력 매개변수 io_cursor, io_cursor2를 통해 참조 커서를 반환합니다.

개발자들은 OracleDataReaders와 같이 io_cursor 및 io_cursor2 와 같은 커서 출력 매개변수를 사용할 수 있기 때문에 ODP.NET를 사용하여 참조 커서를 쉽게 작성할 수 있습니다. 참조 커서를 불러 오려면, 첫 번째 예제에서와 같이 먼저 데이타베이스 연결을 열고, 명령어를 생성하여 저장된 프로시저를 불러 온 다음, 목록 5에 있는 필요한 매개변수를 전달하십시오. 이 목록에서는 우리에게 이미 친숙한 객체인 OracleCommand을 사용하여 저장된 프로시저를 불러 오고, 그에 따른 참조 커서를 얻으십시오. 그러나 SQL 문을 사용하여 데이타베이스로부터 레코드를 얻는 대신 저장된 프로시저의 출력 매개변수를 사용하십시오.

목록 5의 코드를 행별로 살펴 보겠습니다. 먼저, 1행에서는 새로운 OracleCommand 객체를 생성하십시오. 그러나 SQL 문을 포함하는 문자열을 사용하는 대신 문자열 이름, curspkg.open_two_cursor를 입력하여 저장된 프로시저를 불러오십시오.

2행에서는 OracleCommand 객체를 SQL 문 호출이 아닌 저장된 프로시저 호출로 지정하십시오. 이 명령이 저장된 프로시저에 대한 호출임을 지시하려면 OracleCommand 객체의 CommandType 속성을 적절하게 설정해야 합니다. CommandType 속성은 여러 가지 허용 가능한 값을 갖습니다. 이 값은 CommandType 열거 객체를 통해 액세스할 수 있습니다. 우리의 예제에서는 CommandType.StoredProcedure를 사용하고 있습니다.

3행과 4행에서는 반환되는 참조 커서에 액세스하기 위해 필요한 2개의 매개변수 변수를 생성하십시오. 이 변수는 데이타베이스의 저장된 프로시저로부터 참조 커서 결과에 대한 참조를 얻는 데 사용됩니다.

5행에서 8행까지는 4개의 필요한 매개변수를 OracleCommand 객체에 추가하십시오. cmd.Parameters.Add() 메소드가 있는 행에 2개의 n_empid 매개변수를 위한 OracleParameter 객체를 생성하십시오.

OracleParameter 생성자는 2개의 매개변수를 취합니다. 첫 번째 매개변수는 PL/SQL 저장 프로시저 매개변수 이름이며, 이것은 저장 프로시저 매개변수가 선언된 순서와 다른 순서로 매개변수를 융통성 있게 추가할 수 있음을 의미합니다. 두 번째 매개변수는 PL/SQL 저장 프로시저 매개변수의 데이타 유형입니다. 여기서 Value 속성은 Value 매개변수가 OracleDbType 열거 객체에 위치해 있기 때문에 받아들일 수 있는 유형으로 해석되어야 합니다. 이 두 가지 생성자 매개변수는 OracleParameter 객체 및 OracleCommand 객체에 저장된 프로시저를 호출하는 방법과 저장된 프로시저로부터 반환되는 값을 해석하는 방법을 알려 줍니다.

다음 단계 ODP.NET 다운로드 otn.oracle.com/tech/windows/odpnet ODP.NET 예제코드 otn.oracle.com/sample_code/tech/windows/odpnet ODP.NET 세미나 참여 oracle.com/iseminars .NET 용 Oracle Data Provider 사용하기

본문의 예제에서는 4개의 OracleParameter 객체를 OracleCommand 객체에 추가하였습니다. 이 중 두 가지는 open_two_cursor 저장된 프로시저를 위한 n_empid 및 n_empid2 input 매개변수이고, OracleDbType.Int64 유형입니다. 그 밖에도 OracleParameter 객체의 Value 속성을 사용하여 매개변수 값을 1과 2로 설정해야 합니다.

세 번째 및 네 번째 매개변수(io_cursor와 io_cursor2)는 open_two_cursor 저장 프로시저의 출력 매개변수입니다. io_cursor 매개변수인 경우, 값을 저장 프로시저로 전달하지 않고, 대신 저장 프로시저가 참조 커서를 반환합니다. 이 단계를 처리하기 위해 OracleParameter 객체를 OracleDbType.RefCursor유형으로 추가합니다. OracleCommand 객체가 이 객체를 출력 매개변수로 인식하도록 하려면, OracleParameter 객체의 Direction 속성을 ParameterDirection.Output으로 설정하십시오.

그런 다음, 목록 6에서와 같이 저장 프로시저를 실행하고 그 결과를 불러 오십시오. 목록 6의 1행에서는 OracleCommand 객체의 ExecuteNonQuery() 메소드를 호출하십시오. 이 메소드는 저장된 프로시저를 호출하고, 그 결과를 적절한 OracleParameter 객체로 다시 읽어 들입니다.
2행과 3행에서는 반환 값을 OracleRefCursor 객체로 전달하십시오. ODP.NET이 값을 다시 전달하는 저장된 프로시저를 실행하면, 관련된 OracleParameter 객체의 Value 속성에 이 값이 저장됩니다.
4행에서 9행까지에서는 OracleDataReader 객체들을 생성하고 이 객체들을 참조 커서 결과에 설정하며, Read() 메소드를 호출하여 첫번째 레코드로 이동시키고, OracleDataReader 객체로부터 이름 열을 불러오십시오.
OracleDataReader 객체에는 NextResult()라고 불리는 메소드가 포함되어 있습니다. 이 메소드는 ExecuteReader() 메소드와 함께 사용할 수 있으며 저장된 프로시저로부터 참조 커서를 통해 결과를 연속적으로 반복할 수 있습니다. NextResult() 메소드를 사용하면, 한 가지 OracleDataReader 객체를 사용하여 모든 참조 커서에 액세스할 수 있습니다. 그러나 이렇게 하면 연속적으로 참조 커서에 액세스할 수가 없습니다. 여러 참조 커서 결과에 액세스하는데 사용되는 메소드는 애플리케이션에 따라 다릅니다.

결론

.NET용 Oracle Data Provider는 사용하기 쉬우며 강력한 API를 제공할 뿐 아니라, OLE DB .NET에 대해 획기적으로 개선된 성능을 제공합니다. 새로운 Oracle 데이타베이스나 기존의 Oracle 데이타베이스에 액세스가 가능한 .NET 프레임워크에서 애플리케이션을 개발하고자 하는 경우에는, 이 신기술을 친숙하게 사용함으로써 여러분의 삶을 보다 편리하게 만들 수 있습니다.

Robert P. Lipschutz (rob@thing7.com) 씨는 기술 백서와 프로토타입을 전문적으로 다루는 Thing 7의 대표이며, Gregg D. Harrington (gregg@thing7.com) 씨는 Thing 7의 선임 이사입니다.

표 1: DEJAVU_EMPLOYEE 테이블
FIELD	TYPE	PURPOSE
ID	NUMBER(10)	직원들의 ID 보유(데이타베이스의 기본 키)
NAME	VARCHAR2(32)	직원 이름 보유
PHOTO	BLOB	직원들의 사진 보유
BIGNUMBER	NUMBER(38,37)	High-precision(고밀도) 번호 보유

표 2: 널리 사용되는 Oracle 연결 옵션 간의 기본 옵션 명령 비교
작업	ODP.NET	OLE DB .NET	ORACLE THIN JDBC DRIVER
연결	OracleConnection	OleDbConnection	OracleConnection database
SQL 생성	OracleCommand	OleDbCommand	OracleStatement commands
데이타 읽기	OracleDataReader	OleDbDataReader	OracleResultSet

Oracle용 .NET Framework Data Provider Oracle뿐 아니라 Microsoft에서도 또한 Oracle용 .NET Framework Data Provider라 불리는 Oracle을 위한 데이타베이스 드라이버를 출시하였습니다. .NET용 Oracle 데이타베이스 연결은 매우 중요하기 때문에 Oracle과 Microsoft가 모두 이와 관련된 문제를 해결하고자 노력하는 것은 놀라운 일이 아닙니다. Oracle의 ODP.NET과 관련하여 Microsoft 제품은 보다 구체적인 Oracle 기능을 제공하고 있으며, OLE DB .NET 보다 나은 성능을 제공하고 있습니다. 그러나 Microsoft 공급자는 ODP.NET보다 못한 기능을 제공하고 있는 것 또한 사실입니다. 예를 들어, ODP.NET API는 트랜잭션 저장점, 고유의 참조 커서 데이타 유형, 프록시 사용자 인증, 글로벌 기능을 지원합니다. 또한 ODP.NET는 Oracle BLOB와 CLOB 데이타베이스 객체에 대해 특정한 객체를 공급하는 반면, Microsoft 솔루션은 동일한 방식으로 모든 Oracle LOB의 데이타베이스 객체를 처리하는 OracleLob 객체를 제공합니다. 이는 ODP.NET이 Oracle 데이타 유형에 대해 보다 나은 지원을 제공한다는 것을 의미합니다. 예를 들어, OracleClob 객체는 read 메소드를 우선적으로 적용하여 문자 배열을 채웁니다. 이 기능은 유니코드와 멀티바이트 문자를 처리할 때 더 유용합니다. Microsoft의 솔루션과 ODP.NET는 모두 작성 및 LOB 실행 측면에서 가장 뛰어난 OLE DB .NET보다 더 나은 데이타베이스 액세스 성능을 제공합니다. Microsoft는 Visual Studio .NET의 다음 버전에 새로운 버전의 .NET Framework Data Provider를 포함시킬 예정입니다.

태그저장 취소