I am trying to export a 10.2.0.3 database, and I am getting an error. I understand that one of the most common causes of these errors is that I am using a higher version of the export utility compared to my RDBMS version. But this is not the case. My database version is 10.2.0.3 and the exp version 10.2.0.3
EXP-00056: ORACLE error 6550 encountered ORA-06550: line 1, column 41:PLS-00302: component 'SET_NO_OUTLINES' must be declared ORA-06550: line 1, column 15: PL/SQL: Statement ignoredEXP-00000: Export terminated unsuccessfully
>> Possbile Solutions :
It could be that your Data Dictionary is not set to support this higher version of export. Sign on to your database as SYS and run the @?/rdbms/admin/catexp.sql script. If that doesn't fix the problem, try rerunning @?/rdbms/admin/catalog.sql and @?/rdbms/admin/catproc.sql scripts.
XML Type conversions in Oracle
Oracle suports XML handling through the built in datatype XMLTYPE. Here is an example of handling XML data to convert xml to object types and viceversa.
Consider the following XML
<customer>
<id>100</id>
<name>XEROX</name>
<country>FRANKFURT</country>
<status>ACTIVE</status>
</customer>
Create a sample object type customer which contains the same elements as the XML data using the following command:
CREATE OR REPLACE TYPE CUSTOMER
AS OBJECT
( ID VARCHAR2(10) ,
NAME VARCHAR2(50) ,
COUNTRY VARCHAR2(50),
STATUS VARCHAR2(15))
/
The following pl/sql block will convert the xml data into one XMLTYPE variable and then populates the object variable v_in which is of type customer and displays the object contents.
DECLARE
v_in customer;
v_xml xmltype;
BEGIN
v_xml :=XMLTYPE('<customer></customer>
<id>100</id><name>XEROX</name><country>GERMANY</country>
<status>ACTIVE</status>'); xmltype.toobject(v_xml,v_in);
DBMS_OUTPUT.PUT_LINE('Customer Id ' v_in.id ' Name 'v_in.name);
END;
Output :
Customer Id 100 Name XEROX
PL/SQL procedure successfully completed
And here, for converting the customer object to XML data, use XMLTYPE() function. See the following pl/sql code:
DECLARE
v_in customer;
v_xml xmltype;
BEGIN
v_in:= new customer('100','XEROX','GERMANY','ACTIVE');
v_xml := xmltype(v_in);
DBMS_OUTPUT.PUT_LINE(v_xml.getclobval());
END;
Output :
<customer><id>100</id><name>XEROX</name>
<country>GERMANY</country><status>ACTIVE</status></customer>
PL/SQL procedure successfully completed
Consider the following XML
<customer>
<id>100</id>
<name>XEROX</name>
<country>FRANKFURT</country>
<status>ACTIVE</status>
</customer>
Create a sample object type customer which contains the same elements as the XML data using the following command:
CREATE OR REPLACE TYPE CUSTOMER
AS OBJECT
( ID VARCHAR2(10) ,
NAME VARCHAR2(50) ,
COUNTRY VARCHAR2(50),
STATUS VARCHAR2(15))
/
The following pl/sql block will convert the xml data into one XMLTYPE variable and then populates the object variable v_in which is of type customer and displays the object contents.
DECLARE
v_in customer;
v_xml xmltype;
BEGIN
v_xml :=XMLTYPE('<customer></customer>
<id>100</id><name>XEROX</name><country>GERMANY</country>
<status>ACTIVE</status>'); xmltype.toobject(v_xml,v_in);
DBMS_OUTPUT.PUT_LINE('Customer Id ' v_in.id ' Name 'v_in.name);
END;
Output :
Customer Id 100 Name XEROX
PL/SQL procedure successfully completed
And here, for converting the customer object to XML data, use XMLTYPE() function. See the following pl/sql code:
DECLARE
v_in customer;
v_xml xmltype;
BEGIN
v_in:= new customer('100','XEROX','GERMANY','ACTIVE');
v_xml := xmltype(v_in);
DBMS_OUTPUT.PUT_LINE(v_xml.getclobval());
END;
Output :
<customer><id>100</id><name>XEROX</name>
<country>GERMANY</country><status>ACTIVE</status></customer>
PL/SQL procedure successfully completed
Multitable Inserts using INSERT ALL
Multitable inserts allow a single INSERT INTO .. SELECT statement to conditionally, or non-conditionally, insert into multiple tables. This statement reduces table scans and PL/SQL code necessary for performing multiple conditional inserts compared to previous versions. It's main use is for the ETL process in data warehouses where it can be parallelized and/or convert non-relational data into a relational format.
--Unconditional Insert into all tables
INSERT ALL
INTO ap_cust VALUES (customer_id, program_id, delivered_date)
INTO ap_orders VALUES (order_date, program_id)
SELECT program_id, delivered_date, customer_id, order_dateFROM airplanes;
-- Pivoting insert to split non-relational data
INSERT ALL
INTO Sales_info VALUES (employee_id,week_id,sales_MON)
INTO Sales_info VALUES (employee_id,week_id,sales_TUE)
INTO Sales_info VALUES (employee_id,week_id,sales_WED)
INTO Sales_info VALUES (employee_id,week_id,sales_THUR)
INTO Sales_info VALUES (employee_id,week_id, sales_FRI)
SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE,
sales_WED, sales_THUR,sales_FRI FROM Sales_source_data;
-- Conditionally insert into ALL tables
INSERT ALL
WHEN SAL>10000 THEN INTO sal_history VALUES(EMPID,HIREDATE,SAL)
WHEN MGR>200 THEN INTO mgr_history VALUES(EMPID,MGR,SYSDATE)
SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR
FROM employees WHERE employee_id > 200;
-- Insert into the FIRST table with a matching condition
INSERT FIRST
WHEN SAL > 25000 THEN INTO special_sal VALUES(DEPTID,SAL)
WHEN HIREDATE like ('%') THEN INTO hiredate_history_00 VALUES(DEPTID,HIREDATE)
WHEN HIREDATE like ('%99%') THEN INTO hiredate_history_99 VALUES(DEPTID,HIREDATE)
ELSE INTO hiredate_history VALUES(DEPTID, HIREDATE)
SELECT department_id DEPTID, SUM(salary) SAL, MAX(hire_date) HIREDATE
FROM employees GROUP BY department_id;
--Unconditional Insert into all tables
INSERT ALL
INTO ap_cust VALUES (customer_id, program_id, delivered_date)
INTO ap_orders VALUES (order_date, program_id)
SELECT program_id, delivered_date, customer_id, order_dateFROM airplanes;
-- Pivoting insert to split non-relational data
INSERT ALL
INTO Sales_info VALUES (employee_id,week_id,sales_MON)
INTO Sales_info VALUES (employee_id,week_id,sales_TUE)
INTO Sales_info VALUES (employee_id,week_id,sales_WED)
INTO Sales_info VALUES (employee_id,week_id,sales_THUR)
INTO Sales_info VALUES (employee_id,week_id, sales_FRI)
SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE,
sales_WED, sales_THUR,sales_FRI FROM Sales_source_data;
-- Conditionally insert into ALL tables
INSERT ALL
WHEN SAL>10000 THEN INTO sal_history VALUES(EMPID,HIREDATE,SAL)
WHEN MGR>200 THEN INTO mgr_history VALUES(EMPID,MGR,SYSDATE)
SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR
FROM employees WHERE employee_id > 200;
-- Insert into the FIRST table with a matching condition
INSERT FIRST
WHEN SAL > 25000 THEN INTO special_sal VALUES(DEPTID,SAL)
WHEN HIREDATE like ('%') THEN INTO hiredate_history_00 VALUES(DEPTID,HIREDATE)
WHEN HIREDATE like ('%99%') THEN INTO hiredate_history_99 VALUES(DEPTID,HIREDATE)
ELSE INTO hiredate_history VALUES(DEPTID, HIREDATE)
SELECT department_id DEPTID, SUM(salary) SAL, MAX(hire_date) HIREDATE
FROM employees GROUP BY department_id;
Deleting/Listing duplicate records without ROWID
I was hunting for an article when my friend came up and asked me this question.
How to list/delete duplicate records without using ROWID?
I never asked why he do not want to use ROWID, because I was caught in some thinking.
I done some googling, and derived the method, but first the ROWID method
First how to delete records with ROWID, and why?
ROWID is the unique record identifier within Oracle, and it is easy to get the minimum of the ROWID for the duplicating criteria and delete the rest of them.
We will take the example of EMP table for simplicity. Assume that there is no primary key in the Employee_Id column due to some data migration requirements. Once the data migration is over I need to check the existense of duplicate records. This is how traditionally we used to achieve this:
Suppose the data after migration stands like this:
Employee_Id Employee_Name
10 Scott
11 Tiger
10 Amanda
10 Nagpal
20 Rawat
It is clear from this data that there are duplicate records, but in a normal data migration there will be millions of records from which identifying the duplicate records will be a nightmare. This is where the following query will save our day:
SELECT Employee_Id, Employee_Name
FROM Employee e
WHERE ROWID NOT IN (SELECT MIN(ROWID)
FROM Employee f
WHERE e.Employee_Id = f.Employee_Id);
The above query will list the following duplicate records:
Employee_Id Employee_Name
10 Amanda
10 Nagpal
We will not get into the decision of what to do with the duplicate records as it is not our discussion topic.
Now what does the MIN(ROWID) implies in the query? It implies that the first record inserted cannot be a duplicate record, and the rest of the data are. Simple isn't it?
Now how to select the same data without ROWID?
Consider the following select statement:
SELECT Employee_Id, Employee_Name,
decode(row_number() over( PARTITION BY employee_id ORDER BY employee_id),1,'Not duplicate','Dupilcate') Dupicate_Criteria
FROM employee;
The following is the data from the query:
EMPLOYEE_ID EMPLOYEE_NAME DUPICATE_CRITERIA
10 Scott Not duplicate
10 Amanda Dupilcate
10 Nagpal Dupilcate
11 Tiger Not duplicate
20 Rawat Not duplicate
In this, you can see the usage of row_number() function with over() clause. In the over() clause we are partitioning the data on basis of the duplicate criteria, that is the Employee_Id. The Order By clause is mandatory so we will use the same Employee_Id. Now the row_number() function assigns running numbers for each Employee_Id. If there are any duplicate records, the row_number() function will return any value greater than 1 (in the running order).
So it is very easy for us to identify the usage of row_number() function and to find an alternative method of getting duplicate rows without using ROWID in our query. Now it is in your hands to do more research and find out more usages.
Thanks for Subesh Kumar who prompted me for a short research in this topic, and hence this article.
How to list/delete duplicate records without using ROWID?
I never asked why he do not want to use ROWID, because I was caught in some thinking.
I done some googling, and derived the method, but first the ROWID method
First how to delete records with ROWID, and why?
ROWID is the unique record identifier within Oracle, and it is easy to get the minimum of the ROWID for the duplicating criteria and delete the rest of them.
We will take the example of EMP table for simplicity. Assume that there is no primary key in the Employee_Id column due to some data migration requirements. Once the data migration is over I need to check the existense of duplicate records. This is how traditionally we used to achieve this:
Suppose the data after migration stands like this:
Employee_Id Employee_Name
10 Scott
11 Tiger
10 Amanda
10 Nagpal
20 Rawat
It is clear from this data that there are duplicate records, but in a normal data migration there will be millions of records from which identifying the duplicate records will be a nightmare. This is where the following query will save our day:
SELECT Employee_Id, Employee_Name
FROM Employee e
WHERE ROWID NOT IN (SELECT MIN(ROWID)
FROM Employee f
WHERE e.Employee_Id = f.Employee_Id);
The above query will list the following duplicate records:
Employee_Id Employee_Name
10 Amanda
10 Nagpal
We will not get into the decision of what to do with the duplicate records as it is not our discussion topic.
Now what does the MIN(ROWID) implies in the query? It implies that the first record inserted cannot be a duplicate record, and the rest of the data are. Simple isn't it?
Now how to select the same data without ROWID?
Consider the following select statement:
SELECT Employee_Id, Employee_Name,
decode(row_number() over( PARTITION BY employee_id ORDER BY employee_id),1,'Not duplicate','Dupilcate') Dupicate_Criteria
FROM employee;
The following is the data from the query:
EMPLOYEE_ID EMPLOYEE_NAME DUPICATE_CRITERIA
10 Scott Not duplicate
10 Amanda Dupilcate
10 Nagpal Dupilcate
11 Tiger Not duplicate
20 Rawat Not duplicate
In this, you can see the usage of row_number() function with over() clause. In the over() clause we are partitioning the data on basis of the duplicate criteria, that is the Employee_Id. The Order By clause is mandatory so we will use the same Employee_Id. Now the row_number() function assigns running numbers for each Employee_Id. If there are any duplicate records, the row_number() function will return any value greater than 1 (in the running order).
So it is very easy for us to identify the usage of row_number() function and to find an alternative method of getting duplicate rows without using ROWID in our query. Now it is in your hands to do more research and find out more usages.
Thanks for Subesh Kumar who prompted me for a short research in this topic, and hence this article.
What is PRESENTATION=RO in tnsnames.ora file
Check the entry in tnsnames.ora file:
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
In this article we will be discussing about the PRESENTATION clause in the entry.
Little history
The whole tnsnames.ora file is meant to be information for client softwares which will be connecting to the Oracle server (database).
The client must know where the database resides, which PROTOCOL to use for connection, unique identifier of the database etc.
Back to EXTPROC_CONNECTION_DATA
But in this particular tnsnames.ora entry, Oracle uses this for connecting to external procedures. The examples of external procedures are the procedures written in C/C++/VB which are compiled as available as shared libraries (DLLs).
PRESENTATION in connect descriptor
There must be a presentation layer between client and server, if in case the charactersets of both are different. This layer ensures that information sent from within application layer of one system is readable by application layer of the other system.
The various presentation layer options available are
1. Two-Task Common (TTC)
2. JavaTTC
3. FTP
4. HTTP
5. GIOP (for IIOP)
6. IMAP
7. POP
8. IM APSSL (6, 7, and 8 are for email) etc
9. RO
TTC
TTC/Two-Task Common is Oracle's implementation of presentation layer. It provides characterset and datatype conversion between different charactersets or formats on the client and server. This layer is optimized on a per connection basis to perform conversion only when required.
Information obtained from: http://download-west.oracle.com/docs/cd/A87860_01/doc/network.817/a76933/architec.htm#1007271
JavaTTC
This is a Java implementation of TTC for Oracle Net foundation layer capable of providing characterset and datatype conversion.
It is responsible for
a. Negotiating protocol version and datatype
b. Determining any conversions
c. SQL statement execution
RO
For external procedures the PRESENTATION layer value will be normally RO, meaning for "Remote Operation". By this parameter the application layer knows that a remote procedure call (RPC) has to be made.
The other layers will be discussed later.
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
In this article we will be discussing about the PRESENTATION clause in the entry.
Little history
The whole tnsnames.ora file is meant to be information for client softwares which will be connecting to the Oracle server (database).
The client must know where the database resides, which PROTOCOL to use for connection, unique identifier of the database etc.
Back to EXTPROC_CONNECTION_DATA
But in this particular tnsnames.ora entry, Oracle uses this for connecting to external procedures. The examples of external procedures are the procedures written in C/C++/VB which are compiled as available as shared libraries (DLLs).
PRESENTATION in connect descriptor
There must be a presentation layer between client and server, if in case the charactersets of both are different. This layer ensures that information sent from within application layer of one system is readable by application layer of the other system.
The various presentation layer options available are
1. Two-Task Common (TTC)
2. JavaTTC
3. FTP
4. HTTP
5. GIOP (for IIOP)
6. IMAP
7. POP
8. IM APSSL (6, 7, and 8 are for email) etc
9. RO
TTC
TTC/Two-Task Common is Oracle's implementation of presentation layer. It provides characterset and datatype conversion between different charactersets or formats on the client and server. This layer is optimized on a per connection basis to perform conversion only when required.
Information obtained from: http://download-west.oracle.com/docs/cd/A87860_01/doc/network.817/a76933/architec.htm#1007271
JavaTTC
This is a Java implementation of TTC for Oracle Net foundation layer capable of providing characterset and datatype conversion.
It is responsible for
a. Negotiating protocol version and datatype
b. Determining any conversions
c. SQL statement execution
RO
For external procedures the PRESENTATION layer value will be normally RO, meaning for "Remote Operation". By this parameter the application layer knows that a remote procedure call (RPC) has to be made.
The other layers will be discussed later.
Subscribe to:
Posts
(
Atom
)