Ask Anantha

** Solved ** You will encounter this error when a tablespace is full or cannot extend automatically. These are the possible solutions (if there are any other ways add a comment): Solution 1: Resize Tablespace alter database datafile ' ' resize M; Solution 2: Add Datafile alter tablespace tablespace_name add datafile ' ' size M autoextend M maxsize M|off>;

I got a following query from Suvankar Das. "I need to unload few Oracle tables with average data volume of 15M records to flat files. Is the SQL* Plus spool a good option for this volume of data from performance point of view OR is it better to do through PL/SQL UTL_FILE option." And this is how I responded: "SQL* Plus will have the fastest result to the flat file. Only use UTL_FILE if you need some more programmatic control in between the spooling. To make sure first take a sample set of data (some thousands of records) for spooling using SQL *Plus and do the same using UTL_FILE. Compare both the timings. Also based on the sample estimate the time for the entire 15M records." The Verdict: There is no hard rule that says either SQL *Plus or UTL_FILE will perform faster. It varies from environment to environment. Where SQL *Plus/UTL_FILE could perform faster SQL *Plus is a client program which is accessing Oracle database. Therefore it highly depends on the network. ...

Today I was answering one of the queries, this question seriously made my interest. The question was "I have 15 million records in my table. What do you suggest a best method for unloading them from Oracle table to a flat file". Seriously till that time I had never done this. The need had not come. I clearly know two ways of doing this: Using SQL Plus and SPOOL command Using UTL_FILE built-in package UTL_FILE vs SPOOL I know that SPOOL command will be faster in execution than UTL_FILE package , but also I reminded myself that this may not be the case always. SPOOL commands operates in the client machine and depending upon the network traffic it might take time for the SPOOL operation than UTL_FILE.  SO I ADVICE TO TEST BOTH OPTIONS IN YOUR ENVIRONMENT BEFORE FINALISING ON ONE OF THE ABOVE. I will soon publish routines for this purpose. Your patience is appreciated. More resources for your inquisitive mind A little surfing did a wonderful job in putting this article up. In ask...

This error mainly occurs due to one of the following: 1. If you assign NULL value to a NOT NULL field. 2. If you assign number which is larger than the precision of the field. 3. If you assign character values greater than the field width. Simply stating the value being assigned is not a valid value which can be assigned to the string/numeric field. For example: declare test varchar2(1); begin test := 'I am here'; end; The above block gives the error: ORA-06502: PL/SQL: numeric or value error: character buffer too small ORA-06512: at line 4 Of course in this you are getting more information as to the character variable is too small to hold the value you are assigning. Now consider the following example: declare test number(2); begin test := 100; end; The above block gives this error: ORA-06502: PL/SQL: numeric or value error: number precision too large ORA-06512: at line 4 So now you know more about this frequently occuring error. Some tips to avoid this error: 1. If...

In this article we will discuss what people end up doing erroneously. As data type Oracle stores DATE in a separate format then Character. But when passing values between two programs, the format may cease to be a Date anymore. For example there are two independent systems, one which is a Oracle system (which we are going to see) and another foreign system which uses some other technology. In this case let us also assume that the foreign system is our front-end to the user and Oracle system is the back-end part of the system. Say the front-end system is passing some date value which has been input from user to the database. The value will be interpreted as a Character by the Oracle system. To convert the Character value to a Oracle-specific date data type, we will be using the function to_date(). The function to_date() accepts a Character value and will convert it into a Date (as understood by Oracle). We also needs to tell to the function what is the format in which the date value is ...

Have you ever encountered error "Implementation Restriction: 'XX.XX': Cannot directly access remote package variable or cursor" while compiling oracle forms? If yes read below to find a method of creating a generic way of assigning values to variables from oracle forms. Here in this article we will see how to avoid the error "Cannot directly access remote package variable or cursor" from Oracle forms through an example. We have created a package for test purposes. The name of the package is testpck. Assume it has a lot of variables, say some 200 and you need to assign values to each of the variables from front-end. One way is to create a procedure which assigns values using a rowtype variable. But in this approach we would be writing unnecessary code to attain the logic. In this example listed below we have used only one variable in the package specification. The variable name is l_variable. For simplicity of discussion I am restricting the variables of type...

If you are unable to compile your reports or plls in Oracle 10g Version, due to the error like PL SQL ERROR 201: SRW.___ must be declared, then check the existence of the following file and its privilege: rwrun.jar in /Oracle10gAS/reports/jlib folder. Where Oracle10gAS is the folder where 10g Application server is installed. You will normally get this error only in UNIX/Linux flavors, because this is related to privilege rights. If you are getting this error in Windows also, then it is quietly possible that the file is missing. The file must have the privilege 755 or -rwx-r-x-r-x.