set ORACLE_HOME="c:\ora\instantclient_x64_11.1.0.7.0_basiclite"- Double-click sd.bat to start SQL Developer
start sqldeveloper.exe
The jar is all of the thin driver and the java portion of the OCI driver. In order toThick URL: jdbc:oracle:oci8:@<database_name>
run in OCI mode, you would use this same jar, giving it the OCI URL instead of the thin
URL, and you would have to have the whole Oracle client installed on your machine,
and your OS environment set up to point to the OCI DLLs as well as the standard C
libraries. Note that the version of the OCI libraries that works with one driver jar
may not work with another driver jar, so if you go the OCI route, you may have to
install a whole new OCI client just to get a particular driver fix.
SQL Error: ORA-30657: operation not supported on external organized table30657.0000 - "operation not supported on external organized table"The ORACLE_DATAPUMP type has to be used to export data from Oracle using an external table. Unfortunately, this data cannot be humanly read or imported into any other tool then an Oracle database.
*Cause: User attempted on operation on an external table which is
not supported.
*Action: Don't do that!
If you do not specify the CHARACTERSET parameter, the database character set is used to interpret the input file. As the input file is in WE8MSWIN1252, the ORACLE_LOADER driver gets confused trying to interpret single-byte WE8MSWIN1252 codes as multibyte AL32UTF8 codes.
The character set of the input file depends on the way it was created. Even on US Windows, you can create text files in different encodings. Notepad allows you to save the file in ANSI code page (=WE8MSWIN1252 on US Windows), Unicode (=AL16UTF16LE), Unicode big endian (=AL16UTF16), and UTF-8 (=AL32UTF8). The Command Prompt edit.exe editor saves the files in the OEM code page (=US8PC437 on US Windows).
-- Sergiusz
Bootrec is the "fdisk /mbr" for Windows Vista and Windows 7.
If you got here and you still have a C drive letter assigned and that drive is accessible, then the suggestions above might work. I suggest you try them first.
If you don't have a system drive letter, then the resolution is fairly simple:
Start the repair option from a Windows 7 installation disk.
It detected that there were issues with the current Vista installation and fixed them. We rebooted the system and all was well again. No data loss and still running Vista.
We used a Windows 7 Ultimate x64 disk to fix a Windows Vista Ultimate x32 OS.
Examples of different types of cursors. Each has its own pros and cons. Example 1 is the most straightforward and easiest to understand. The variants in example 3 perform better. I've put them here for reference.
-- 1) implicit cursor
DECLARE l_old_tbs VARCHAR(100) := 'USERS';
BEGIN
FOR item IN (
SELECT OWNER, TABLE_NAME FROM ALL_TABLES WHERE TABLESPACE_NAME = l_old_tbs )
LOOP
dbms_output.put_line(item.TABLE_NAME);
END LOOP;
END;
-- 2) explicit cursor
DECLARE l_old_tbs VARCHAR(100);
CURSOR c1 IS (SELECT OWNER, TABLE_NAME FROM ALL_TABLES WHERE TABLESPACE_NAME = l_old_tbs);
BEGIN
l_old_tbs := 'USERS'; -- set variable here, otherwise it is NULL!
FOR item IN c1 LOOP
dbms_output.put_line(item.TABLE_NAME);
END LOOP;
END;
-- 3a) local collection variable with multiple fields using BULK COLLECT and FORALL
CREATE TABLE TEST1 (OWNER VARCHAR(30), TABLE_NAME VARCHAR(30));
DECLARE l_old_tbs VARCHAR(100);
TYPE r_tmp IS RECORD (OWNER ALL_TABLES.OWNER%TYPE, TABLE_NAME ALL_TABLES.TABLE_NAME%TYPE);
TYPE t_tmp IS TABLE OF r_tmp;
tmp t_tmp;
BEGIN l_old_tbs:= 'USERS';
-- fill local collection "tmp" with one bulk operation
SELECT OWNER, TABLE_NAME BULK COLLECT INTO tmp FROM ALL_TABLES WHERE TABLESPACE_NAME = l_old_tbs;
-- update/insert/delete with one bulk operation; select not permitted.
-- no "loop" keyword, because following statement is performed as one batch. Check dbms_output, this is only one line.
-- always use "commit" statement
FORALL i IN tmp.FIRST..tmp.LAST
INSERT INTO TEST1 VALUES tmp(i);
dbms_output.put_line('first: 'tmp.FIRST', last: 'tmp.LAST);
COMMIT;
END;
-- 3b) local collection variable with single field using BULK COLLECT and FORALL
DECLARE l_old_tbs VARCHAR(100);
TYPE t_tmp IS TABLE OF ALL_TABLES.TABLE_NAME%TYPE;
tmp t_tmp;
BEGIN
l_old_tbs:= 'USERS';
-- fill "tmp" with one bulk operation
SELECT TABLE_NAME BULK COLLECT INTO tmp FROM ALL_TABLES WHERE TABLESPACE_NAME = l_old_tbs;
-- update/insert/delete with one bulk operation; select not permitted.
-- no "loop" keyword, because following statement is performed as one batch.
-- always use "commit" statement
FORALL i IN tmp.FIRST..tmp.LAST
INSERT INTO TEST1 (TABLE_NAME) SELECT tmp(i) FROM DUAL;
COMMIT;
END;
-- 3c) local collection variable with multiple fields using BULK COLLECT and FOR
SET SERVEROUTPUT ON;
SET SERVEROUTPUT ON SIZE UNLIMITED;
DECLARE
l_old_tbs VARCHAR2(100);
TYPE r_tmp IS RECORD (OWNER ALL_TABLES.OWNER%TYPE, TABLE_NAME ALL_TABLES.TABLE_NAME%TYPE);
TYPE t_tmp IS TABLE OF r_tmp;
tmp t_tmp;
BEGIN
l_old_tbs:= 'USERS';
-- fill local collection "tmp" with one bulk operation
SELECT OWNER, TABLE_NAME BULK COLLECT INTO tmp FROM ALL_TABLES WHERE TABLESPACE_NAME = l_old_tbs;
-- update/insert/delete with one bulk operation; select not permitted.
-- with "loop" statement. Check dbms_output, there are as much lines as tmp.LAST.
-- always use "commit" statement
FOR i IN 1..tmp.COUNT
LOOP
--INSERT INTO TEST1 VALUES tmp(i);
dbms_output.put_line('tablename: '||tmp(i).TABLE_NAME||', last: '||tmp.LAST);
COMMIT;
END LOOP;
END;
-- 3d) local collection variable with single field using BULK COLLECT and FOR
SET SERVEROUTPUT ON;
SET SERVEROUTPUT ON SIZE UNLIMITED;
DECLARE
i INT := 0;
l_tabname VARCHAR2(100);
TYPE r_tmp IS RECORD (TABLE_NAME USER_TABLES.TABLE_NAME%TYPE);
TYPE t_tmp IS TABLE OF r_tmp;
tmp t_tmp;
BEGIN
-- fill local collection "tmp" with one bulk operation
SELECT TABLE_NAME BULK COLLECT INTO tmp FROM USER_TABLES WHERE UPPER(TABLE_NAME) LIKE 'TTMP_%';
-- update/insert/delete with one bulk operation; select not permitted.
-- with "loop" statement. Check dbms_output, there are as much lines as tmp.LAST.
-- always use "commit" statement
FOR i IN 1..tmp.COUNT
LOOP
dbms_output.put_line(i);
l_tabname := 'DROP TABLE ' || tmp(i).TABLE_NAME;
dbms_output.put_line(l_tabname);
EXECUTE IMMEDIATE l_tabname;
COMMIT;
END LOOP;
END;
-- 4) use a temp table
CREATE GLOBAL TEMPORARY TABLE tmp (OWNER VARCHAR(30), TABLE_NAME VARCHAR(30);