I have a functional index.. which doesn't allow duplicate based on condition.I want to catch the exception and print the customized message but it always override my message.
create or replace function i_func
(tt_id in number,
d_date date,
stat in varchar2)
return varchar2
[code].....
output: ORA-00001: unique constraint (abb.TEST_IDX) violated
How can i catch the exception thrown by a PLSQL_TABLE.POPULATE_BLOCK. Actually i want to display alert messages if POPULATE_BLOCK doesn't contain any records. i wrote the PLSQL_TABLE.POPULATE_BLOCK(bk_data, 'block name');
In query procedure after that if i write any code for alert message then it is not getting executed it may be because of exception is handled by PLSQL_TABLE.POPULATE_BLOCK
What will be the solution to this problem ?
How can i turn this into functional index.
WHERE (IPC_DISP_ID IS NOT NULL AND DISP_EXIST > 0)
AND (IPC_UPU_BCD IS NOT NULL AND RECPT_EXIST > 0)
AND ( (IPC_ITM_ID IS NOT NULL AND ITM_EXIST > 0) OR IPC_ITM_ID IS NULL )
index types like cluster,bitmap,binary tree,functional. Specify the differences between these types
View 5 Replies View RelatedI'm altering a column length to increase the size and getting "ORA-30556: functional index is defined on the column to be modified".
On searching more about this error, it seems like the function index must be dropped before altering the column.The table I'm dealing with is huge.
Question 1:In case of dropping and recreating the index, should the following steps be done:
- Drop Index
- Alter the column to increase the size
- Recreate the index with NOLOGGING and NOPARALLEL clause
- ALTER INDEX to turn on LOGGING
- Gather Statistics on that index
Question 2:Is there anything else that should be done when the index is dropped and re-created?
Question 3:What are the side-effects of carrying out the above steps in a huge table with around 15 million rows?
Question 4:Would it work if I disable the index, alter the column and reenable the index?Do I have to rebuild the index and gather Stats upon reenabling it?
I have a procedure
procedure PROC1(id number) is
begin....
exception when others dbms_output.put_line('other exception');
when no_data_found
dbms_output.put_line('no data');
end;
now I have an other procedure
Porcedure PROC2
begin
PROC1(23412);
end;
now if the PROC1 returns an exception message I would like to assign the message to variable in PROC2 and display.
i want to print a message just before asking for input to a bind variable
SQL> SET serveroutput on;
SQL> DECLARE
2 pname varchar2(20);
[Code].....
but i want to print 'hello world' before asking value for bind variable like:
hello world
Enter value for num: 1
old 9: WHERE ID = #
new 9: WHERE ID = 1;
then how to achive it?
I have the following chunk of code, which could return no rows, one row or many rows. When it returns one or many rows the DBMS_OUTPUT.PUT_LINE prints out my values. When no data is found 'Hello' is not printed?
Suggest a way I can change the code to make the exception print when no data is found?
SET SERVEROUTPUT ON;
BEGIN
FOR prec IN ( select * from xxx.part_maint where drop_partition = 'Y' )
LOOP
DBMS_OUTPUT.PUT_LINE (prec.SCHEMA_NAME || ' ' || prec.OBJECT_NAME);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('hello');
END;
how to catch a null value with C# code?
My Asp.Net/C# application is connected to an Oracle database where it retrieves database values and also values calculated/returned from Oracle functions. My problem is that sometimes an Oracle function I calling returns a null value because of not correct input parameters which will cause Oracle function to (I think) return an null value. My problem and question is how to catch/obtain if he Oracle function returns a null value? Oracle function should return a decimal value if correct input parameters. Below is my row of code that crashes when Oracle function returns a null value in case of incorrect input parameters.
decGrossFreightRevenue = (decimal)(OracleNumber)cmdCalculateRevenueCall.ExecuteOracleScalar();
how to change my code to catch a null value and avoid a crash. I have tried several checks (if statements) without success.
APEX 4.2Oracle 11g Database We are using the standard exception handler that was introduced in APEX 4.1, and we have code in packages & procedures in the database (following proper processes of keeping code in the database where possible). When an exception is found in the procedures/ packages/functions, should the APEX application level exception handler catch any errors that occur or should they be handled in the package/procedure/function they occurred in? Why I ask if, we right now have exception handling code in the pl/code bodies BUT they write their errors to the same table that Apex's Exception handler does, but the errors are NOT presented to the user using the APEX exception handling mechanism.
View 2 Replies View RelatedI have to implement exception handling in the exception block of a trigger, Quote:exception
when ora_java.java_error then
message( 'Unable to call out to java, ' || ora_java.last_error );
ORA_JAVA.CLEAR_EXCEPTION;
when ORA_JAVA.EXCEPTION_THROWN then
ex := ORA_JAVA.LAST_EXCEPTION;
message( Exception_.toString(ex));
-- lv_exception := Exception_.getMessage(ex);
I get an error for the line: 'message( Exception_.toString(ex));'I have imported the java classes FException et IObject with their methods.
I have to create a Web Service Client, so I wonder if the paragraph Quote:when ORA_JAVA.EXCEPTION_THROWN then ex := ORA_ JAVA. LAST_ EXCEPTION; is mandatory.
How to catch or store the sql statement that caused the trigger to fire in DMl Triggers.
View 11 Replies View RelatedI have this SQL statement:
WITH data
AS (SELECT user_id,
jc_name,
[Code]....
I wish to do something like
if results > 10 print an error message (and no results)
if results < 10 print the results/output
I got a task to send some alters based on the user query data .As user is not a SQL expert, so we have to provide him a query builder. Now to store user query, I decided to build a data model and keeping in mind the reusability of previously made queries (so that we can use them directly in further queries).Like a user made two queries:
1. Find patient id, name, sugar_level, pulse_rate, weight for those patients whose diabitic level is greater than 'X'
2. Find patient id, pulse_rate for all those patients whose diabitic level is greater than 'X' and weight > 90
So first query can be directly used in second query. (Just a very simple example)But I am pretty much confused with the decision of columns for each table to store query and how to relate them.
Tables I am able to identify is :
1. Table to store query level details, i.e. query_id, who created, query_short_name, query_description, operator used (SET, GROUP BY, HAVING, etc), distinct indicator, etc.
2. select clause table or columns selected table
3. From clause table
4. table to store group by clause
5. table to store having clause
I am trying to store seprate parts of the query into seprate tables, but not getting which columns to have in each table, how to relate them... and after that, for a given query_id, procedure to construct a whole query, and then validate/parse and execute it using dbms_sql and scheduler.
SQL > conn ashish/pass@conn_string
ERROR:
ORA-28002: the password will expire within 1 days
----------------------------------------------------------
I want to catch this eror in forms: catch the error code ORA-28002 in ON-ERROR trigger and ON-Message trigger, however its not getting caught in the trigger. how to catch the warning/error ORA-28002 in oracle forms?
I have customized system on oracle 10g ( English interface and reports ) , the company want to make the system in both (Arabic and English) , i make every thing is okay , but the problem when i change the PDF subset in the file uifont.ali to include Arabic fonts , the report server every 2 hours stopped and give me the message ( server is in progress ) ,
View 1 Replies View RelatedI am looking for a query which can select all existing Oracle predefined users and roles from db.I need it because one of my other scripts needs to know a given user name or role is a customised one or a system one.It looks like sys.user$ does not have any column that can be used to distinguish predefined users/roles.
I could find all the list of Oracle predefined users and roles by googling and hardcoded them in a query. I can also collect all the customised users and roles as well. However, I prefer a query which can automatically reports that and minimise the maintenance cost as new names and roles, either Oracle predefined or customised, may come out at anytime.
My company has a "generic information management platform" based on Oracle,
including:
Oracle Identity Manager 10g
Lotus
Oracle Internat Directory 10g
Oracle Portal 10g
Oracle E-Business Employee
Oracle E-Business User Management
Microsoft Active Directory
Mail System
Oracle Database
All above these systems constitute the generic information management platform.
---------------------------------------------------------------------------------------
There are 60,000 user records in the Oracle Database, lots of user records are duplicate, invalid and redundant. We want delete them by Oracle Identity Manager. But Oracle Identity Manager has no bulk deletion of users function!!!
So, we want customized development this generic information management platform, specifically customized development Oracle Identity Manager 10g's administrator interface.
We want add a JSP page, use this JSP page to bulk delete users. (user number > 60,000)
----------------------------------------------------------------------------------------
What's should we do? We can't directly manipulate the database to delete the users, all users are associated by API and exist in many systems.
----------------------------------------------------------------------------------------
I have a Batch file(.bat) where I get the user name password ans schema name as a input . Based on that input I connect the sql and run some update query from bat file itself. Now I have issue ,when the user give wrong credintials it ask for the credintials again.
Is there any way to check the credintials of oracle and give the invalid credintial error via bat file. The sample of my Bat file is as follows.
@echo off
for /f %%i in ('sqlplus -s usename/password@db @H: est_db_connection.sql') do @set count=%%i
echo %count%
IF %count% EQU 1 ECHO ("Database connection working fine")
IF %count% NEQ 1 ECHO ("Not able to connect to database")
This is not working fine.
I am having a application which is on Widows NT Operating system.I am using Oracle 10g Database, Developer suite 10g And 10g Application server.
My application uses Run_report_object to generate reports.Run_Report_Object is called in forms and Report server is configured in the same Application server where FMX are placed.
The report generation was working fine as long as oracle default login was used. When i created a form to handle login on my own, Report server is throwing "Unable to connect to database" error. My login forms takes the parameters from user and calls default logon command in on_Logon trigger.
the literature equates dimension hierarchies with fuctional dependencies between the levels. I like to tst the strength of this assumption with the implementation of 'CREATE DIMENSION' which allows you to create roll-up hierarchies.
My question to put it simply is this: Given:
CREATE DIMENSION location_dim
LEVEL location IS (location.loc_id)
LEVEL city IS (location.city)
LEVEL state IS (location.state)
HIERARCHY geog_rollup (
location CHILD OF
city CHILD OF
state CHILD
)
Can I insert the following rows into the dimension:
loc_id, city, state
1, Epping, NSW
2, Epping, VIC
Please note that the two Eppings are different cities.
Given the roll-up hierarchy City -> State, will it require that for every city there can be only one state in which case the FD between City and State cannot hold. Or, is it that the roll-up hierarchy defined here has nothing to do with FD.
The second part of the question is if the answer to the above question is that the roll-up is not the same as FD, then is the ATTRIBUTE clause meant to define the n:1 (functional dependency) instead?
What is functional diffrence between type, %rowtype and record.
View 3 Replies View RelatedI would like to develop a functional document for existing package. How to prepare the document if posted the whole code here?
View 1 Replies View RelatedI would like to design a functional design document for existing package. Let me know is there any tool which will build the design document?
View 8 Replies View RelatedIn my production environment, I have a mostly working Oracle 12c Cloud Control environment, managing several database instances. On all of the databases, I am unable to use the Compare period ADDM feature ( Instance > Performance > AWR > Compare Period ADDM).
When I select that menu option, I see the message "To be able to use this feature some PL/SQL packages need to be loaded into the target database's monitoring schema, DBSNMP." I have been searching for further information in the specific packages that need to be loaded per the message, but neither Orafaq, [URL] nor Google seem to have those details.
Create small functional indexes for special cases in very large tables.
When there is a column having one values in 99% records and another values that have to be search for, it is possible to create an index using null value. Index will be small and the rebuild fast.
Example
create index vh_tst_decode_ind_if1 on vh_tst_decode_ind
(decode(S,'I','I',null),style)
It is possible to do index more selective when the key is updated and there are many records to create more levels in b-tree.
create index vh_tst_decode_ind_if3 on vh_tst_decode_ind
(decode(S,'I','I',null),
decode(S,'I',style,null)
)
To access the record can by like:
SQL> select --+ index(vh_tst_decode_ind_if3)
2 style ,count(*)
3 from vh_tst_decode_ind
4 where
5 decode(S,'I','I',null)='I'
6 group by style
7 ;
[code]....
I would like to Center Align application login page(Syslogin) oracle froms 11gr2. The below code is not working in forms 11g2, it's working in Forms 6i
PROCEDURE CenterWindow (win_name VARCHAR2) IS
w_mdi NUMBER;
h_mdi NUMBER;
w_win NUMBER;
h_win NUMBER;
[code]...
I am on 11.2.0.3 Enterprise Edition. We are using the new feature "Composite Domain Index" for a Domain index on a very large table (>250.000.000 rows). It really works with mixed queries. We added two number columns using FILTER BY.We have lots of DML on this table. Therefore, we are executing synchronize and optimize once the week. The synch behaves pretty normal. But "optimize_index" takes a very very long time to complete. I have switsched on 'logging' for the optimize process. The $I table takes some time but is finished normally. But the optimization of the $S table (that is the table created for the CDI feature) is running over 12 hours now - and far from being finished. From the logfile, I can see that it optimizes 1000 rows every 20 minutes. Here is the output of the logfile:
Oracle Text, 11.2.0.3.0
14:33:05 06/26/12 begin logging
14:33:05 06/26/12 event
14:33:05 06/26/12 process $N for optimize: SEQDEV.GEN_GES_DESCRIPTION_CTX_I
14:33:16 06/26/12
14:33:16 06/26/12
[code]....
I haven't found a recommendation from Oracle not to use "optimize_index" for Domain Indexes with CDI. But in my case, it would be much faster just to drop and recreate the Domain Index in question.
I have a huge table (about 60 gb) partition over range. The index on this table is global index created on 4 columns together. I have a query which is running very slowly. The explain plan is showing the use of this global index.Explain plan is not showing pstart and pend because the index is global.
View 6 Replies View RelatedI have a global index and I want to convert it to local index.Is there a way to recreate local index with out dropping the global index.
I can create a local index first and then drop the global index. But is there a way to create it with out dropping the global index, just convert it.
