Ask Anantha

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.

SQL Developer for Linux JInitiator 1.3.1.28 Oracle Database 11g Release 1 (11.1.0.6.0) Standard Edition, Standard Edition One, and Enterprise Edition Oracle SQL Developer Data Modeler

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('<c...

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) SELEC...

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          Tige...

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 librar...