Forms :: How To Check Table Handlers Correctly

Apr 11, 2013

I have a block based on a view. The view is a join on 2 tables, the first table always brings back 4 records for each parameter passed to it in the where clause. The second table is outer joined to the first table and may contain no matched records or some matched records. In some cases there will be a 1:1 match to the first table.

The problem is how to create the table handler procedure correctly. I need to update 2 tables in the table handler procedure.

The block is only enabled for update (to preserve the 4 rows), however some values on the block correspond to values from the second table. When you update a row in the block, how do you know if you are actually inserting a row into the second table or updating an already existing record on the 2nd table.

View 1 Replies


ADVERTISEMENT

Streams :: Applying Conflict Handlers On One Table?

Aug 15, 2012

I create following update conflict handlers, one after other. These are working correctly, my problem is when i create 2nd it replace first one, and when i create 3rd one it replace 2nd one. I want to put all three in action simultaneously. How can i do this.   

{code}DECLAREcols DBMS_UTILITY.NAME_ARRAY;BEGINcols(1) := 'MODIFIED_DATE';cols(2) := 'MODIFIED_BY';cols(3) := 'RATE_CHANGE_DATE';cols(4) := 'PENSION_AMOUNT';cols(5) := 'EASYPAISA_WORK_ON';cols(6) := 'PPO_NO';cols(7) := 'NEW_NIC_NO'; cols(8) := 'OLD_NIC_NO';cols(9) := 'CLAIM_NO';DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(object_name => 'CORE_BUSINESS.CB_PENSIONER',method_name => 'OVERWRITE',resolution_column => 'CLAIM_NO',column_list => cols);END;{code}  {code}DECLAREcols DBMS_UTILITY.NAME_ARRAY;BEGINcols(1) := 'MODIFIED_DATE';cols(2) := 'MODIFIED_BY';cols(3) := 'RATE_CHANGE_DATE';cols(4) := 'PENSION_AMOUNT';cols(5) := 'CLAIM_NO';DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(object_name =>

[code]....

View 3 Replies View Related

Forms :: Put One Check Box To Check All The Check Boxes?

Jul 30, 2011

I want to put one check box to check all the check boxes.how can i do this?

View 5 Replies View Related

SQL & PL/SQL :: How To Exit From For Loop On Cursor Correctly

Jul 25, 2011

I would like to exit from a cursor loop based on certain conditional checking. I am checking for a lot of different parameters and if they fail, I want to bypass it and fetch the next record in the cursor. I tried just putting an 'Exit' statement in the logic, but it fails. An example of my code is below:

For Row1 in cursor1
Loop
If amount < 0 then
balance := 0;
Else

[code]...

View 8 Replies View Related

SQL & PL/SQL :: Oracle HttpUriType - Not Getting Output Correctly?

Aug 13, 2012

The thing is I am not getting any output when i run your procedure.I need to set any nls language? .. even i try to run below qry from the another thread,

column french format a20
with t as (
select 'fish' txt from dual union all
select 'dog' txt from dual union all

[code]...

My output is blank for the french values. I am sure that I missing something, But i dont know what I am missing .currently my CHARACTER SET WE8MSWIN1252

View 3 Replies View Related

Alert Log Not Displaying Timestamp Correctly

Oct 25, 2012

I am using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production installed in IBM AIX 6.1.0.0

I find this peculiar situation that the alert log is not getting update with proper timestamp for example i see an update now under my alert log of this fashion

Tue Oct 23 08:38:46 2012
ARC2 started with pid=37, OS id=50
Tue Oct 23 08:38:46 2012
ARC3 started with pid=38, OS id=178
Tue Oct 23 08:38:46 2012

[Code]...

But the current system time is

Tue Oct 23 18:40:20 GMT+05:30 2012

My memory_target parameter value is 2GB

SQL> SELECT TZ_OFFSET(SESSIONTIMEZONE), TZ_OFFSET(DBTIMEZONE) FROM DUAL;

TZ_OFFS TZ_OFFS
------- -------
+05:30 -05:30

I am clueless.

View 3 Replies View Related

SQL & PL/SQL :: Passing Bind Char Correctly

Dec 7, 2010

I tried google and most of bind example using number instead of character, ?

I have the code as below:

variable oldId varchar2(5)
exec :oldId :='&1'
variable newId varchar2(5)
exec :newId :='&2'

[Code]...

Since in TAB1, ID is defined as varchar2(5), and I have defined both oldId and newId as varchar2(5) in my code, I wonder why it doesn't work.

It returned this error:

DECLARE
*
ERROR at line 1: ORA-01722: invalid number ORA-06512: at line xx

After supply the value for oldId(val2) and newId(val1)

SQL > @thisScript val1 val2

I use dbms_output.put_line to print the command executed, it seems it is doing

UPDATE TABLE1 SET ID = val1 WHERE ID = val2

without the single quote ('val1'), ('val2'). But if I put in single quotes, the output statement

UPDATE TABLE1 SET ID = ':newId' WHERE ID = ':oldId'

The same bind logic works in SQL prompt

SQL > variable oldId varchar2(5);
SQL > variable newId varchar2(5);
SQL > exec :oldId :='val2'
SQL > exec :newId :='val1'
SQL > UPDATE TABLE1 SET ID = :newId WHERE ID = :oldId;
1 row updated.

I am wondering what should I do then? With or without single quotes?

View 9 Replies View Related

Spooling Column Headers Correctly

Sep 13, 2012

Im trying spooling a file in DB with below mentioned details.

SET SERVEROUTPUT OFF
set heading on
SET PAUSE OFF
SET FEED OFF
SET PAGES 0
[code]........

file .sql has 20 odd columns.

When file is spooled, it shows no columns headers at the top, why is it happening, I don't want to spool column headers separately.

View 12 Replies View Related

Application Express :: How To Correctly Trap PL/SQL Errors

Aug 14, 2012

I have a page process that calls back end PL/SQL. I have a page item that is populated by said PL/SQL procedure, P35_PROCESSING_MSG. As you know the Process allows me to specify a message for Success and another for Failure. My desire is that, if there is a back end failure, the error will display in the dialog area, using the built-in "Process Error Message" functionality. Instead, after execution, the page item still has its default value and the following, ugly, error displays in the tabular report region.

report error:
ORA-20001: Error fetching column value: ORA-01403: no data found
ORA-06510: PL/SQL: unhandled user-defined exception

I just don't get why it displays here instead of in the usual failure area. Moreover, I don't understand why the error is "*+unhandled+* user-defined exception" when, as you will see, I have handled it.

I have a page process to perform custom MRD for a tabular form. (I don't think this is really germane to the more generic issue but, I bring it up as an explanation for the following code sample). I am deliberately causing a DIVIDE BY ZERO error to test the error handling form the back end.

PROCEDURE PROCESS_MARGIN_CALL_DELETES( as_StatusMsg OUT VARCHAR2 )  
IS
lb_InnerErrorOccured    BOOLEAN := FALSE;
ln_DeleteTargetCnt      NUMBER := 0;
ln_DeleteTargetRow      NUMBER := 0;
BEGIN
[code].......       

View 20 Replies View Related

PL/SQL :: Values Not Coming Correctly Using Case In Query?

Oct 15, 2012

I am using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production version

I am having the data in following table -

drop table stud_fact;
create table stud_fact(stud_NM, LVL_CD,ST_DT_DIM_KEY,OVRNK) as select
'ABG Sundal','H','20110630','175' from dual union all select

[Code].....

View 8 Replies View Related

Enterprise Manager :: Doesn't Upload Data Correctly?

Jul 1, 2010

I have a problem with my DB agent. it doesn't upload data correctly, so I can't see updated information in first page of EM ( it doesn't show cpu usage, Active Sessions, Listener properties,...). when I checked the logs, I found that emagent.trc shows this error: "exceed max amount of upload data: 77 files, 52M, disabling collections warn : collector : disable collector"

( I use Oracle Database 10g on Suse Linux 10.2)

View 3 Replies View Related

Express Edition (XE) :: Oracle Doesn't Start Correctly With 3G Router

Oct 7, 2013

I have used a HUAWEI dongle directly connected to my PC. First, the operating system and Oracle start up, then I connect to the internet using the Huawei Mobile Partner utility. Any application can connect to Oracle and use it correctly. All OK. Now I have added a 3G router which is connected to the PC by ethernet cable and has the Huawei dongle connected to a USB port on the router. All works correctly if I follow the sequence: 

1) Start the PC with the router switched off, and wait until Oracle has fully started. 

2) Power the router up and wait 2 minutes until Windows says the 100 Mb ethernet link is open. Oracle.exe in Task Manager is about 210 Mb, and sometimes more, depending on activity. 

However, if I switch on the router first and then start the PC, Oracle does not start correctly. It rejects connection requests forever more, indicating Network Adapter could not establish the connection: Code 17002.In Task Manager Oracle.exe occupies only 145Mb. What can I do to get it to start correctly ? I can dig into Listener.ORA, TNSNAMES.ORA and HOSTS without any problems, and have often modified them manually (not the case here), but logs and traces escape me !

View 3 Replies View Related

Application Express :: 4.2.2 - Import / Export Particular Page Are Not Working Correctly

Aug 21, 2013

I am running my oracle apex application on 4.2.2 on apex.oracle.com. Exporting the application seems to work but when I go to import the application as a copy the regions within a particular page are not working correctly.  I want to be able to export the application and import a new copy as a DEV/TEST application and have it function the same way.  On the original application the region will display to the right when hovered over the edit button but in the new one it will go to the left and not format correctly.How I would like it is just for the copy application to perform the same way the original copy does.  I selected to install all dependent objects and don't know what steps I have missed to have the regions not working like they do on the original application. related to CSS not exporting/importing correctly?

View 0 Replies View Related

How To Check A Table If Exists

Oct 12, 2005

I have a script like this:

------------------------------------------------------------

DROP TABLE CON_TEST CASCADE CONSTRAINTS ;

CREATE TABLE CON_TEST (
IDI NUMBER(10,0),
USERID VARCHAR2(10),
PWD VARCHAR2(10),
NOTE VARCHAR(100)
)

----------------------

But i think if table CON_TEST doen't exist, an error message will appear. I know that in SQL Server we can check if table exists or not. So, i wonder if we can do that in Oracle?

By the way, is there any way to run a file script that contents TABLES, STORED, ... on a developed PC connect to oracle db server? (in case, i'm developing on PC, using Net Service Name to conect to Oracle DB Server)

View 9 Replies View Related

SQL & PL/SQL :: Check Every Table For Data?

Oct 30, 2012

I'm working on a feature that needs to check every table for data. At the moment I am roundtripping every time and running the query like this:

SELECT 1 FROM dual WHERE EXISTS(SELECT 'x' FROM table_name)

Is there a better way that allows me to get the result for all tables in one trip? I've tried a correlated subquery like this:

SELECT table_name, (SELECT 1 FROM dual WHERE EXISTS(SELECT 'x' FROM table_name) FROM all_tables

but I think that doesn't work because Oracle looks for a table called table_name rather than the value from the outer query.

View 13 Replies View Related

SQL & PL/SQL :: How To Check Table Properties

Feb 24, 2013

How to check table properties? (desc only shows if it is null)If I do desc emp, it only shows the Employee description. But Doesnt tell if table is having primary key or not?

View 2 Replies View Related

Adding Check After Table Creation

May 23, 2009

I created a table but I want to add the Unique check to it as I forgot to apply it to the table when I created it.Is it possible to make the field Unique after having created the table or do I have to drop the table and re-create it?

View 3 Replies View Related

Table Level Check Constraints

Feb 16, 2011

can a table level check constraints have conditional checking (if else clause or case conditional structures) and checks which are limited through something like a where clause which inside the table level check constraints.And can a table level check constraints refer to a column in another table column which should have a the same value.

View 1 Replies View Related

SQL & PL/SQL :: Check Existing Entries In Table

Apr 3, 2012

I am abolute new in PL SQL and I am working on following topic:

I have to check if an entry exists in a table.

- if it exists ...... do something
- if not ...... simply go on

I tried the following:

select Count(*)from products p
where p.productNbr = temp_Nbr
Group By t.produkt;

I fetched the result into a variable entries

if entries > 0 .....

MY problem is the following:

If there are entries (entries>0) --> it works
If there are no entries, entries is not 0 but 'no data found' --> crash

I cannot work with Exceptions because this all happens in a Loop which must go on in both cases!

View 14 Replies View Related

SQL & PL/SQL :: How To Check Record Exist In Table

Feb 13, 2013

Usually we check 1 row of data in table and compare if they exist, i want to compare different set of row all together to comapre and take the decisions

eg.

emp table_source
d_key emp_no invol
1 103499 1
1 99262 2
2 103499 1
3 103499 1
3 99262 2

target table

emp_no invol
103499 1
99262 2
103499 1

in the above example i have to check for d_key for set of row exist in target or not, so by above example d_key 1 and 3 has save value in 2 different rows so i want to check if the combination of rows

103499 1
99262 2

as a set appear in source then do nothing else insert so first time d_key 1 both records will insert but for d_key has same set in 2 rows will not be inseted. the number of row can be more than 2 also . so for the given key if we have 3 rows in source then i have to compare all the 3 same set of row in target exist of . in the target i don't have any extra column.

View 3 Replies View Related

SQL & PL/SQL :: How To Check If User Has (create Table) Privilege

Jun 29, 2011

1.How to check a user has 'create table' privilege?

2.how to check a user has privilege to grant 'create table' privilege to other user ?

View 6 Replies View Related

SQL & PL/SQL :: How To Check And Delete Duplicate Data From Table

Nov 19, 2010

how i can chk & delete duplicate rows from a table

View 3 Replies View Related

SQL & PL/SQL :: Use A Trim (col_name) To Check NULL Value In A Table?

Aug 8, 2013

Can i use a trim(col_name) to check a NULL value in a table? as IS NULL function is taking a long time.

View 8 Replies View Related

PL/SQL :: How To Check Where Table Field Was Used As Foreign Key In Database

May 9, 2013

i have a field in my table office i got field office_code ,this field is been used in diffirent tables as foreign key is there a sql i can wirte to see all tables who have used this field as foreign key.

View 12 Replies View Related

Forms :: How To Check Record Status

Sep 7, 2011

I am manually committing the form by writing commit statement in key - commit trigger for some reason.

Everything is working fine. I want to display a message if some necessary fields are not filled properly, like if a user filled half table and mistakenly he press the commit button. Here I want to show him a message that you can't saved the data because the table is not completely filled.

View 17 Replies View Related

Forms :: Date Overlapping Check In 10g

Mar 30, 2013

I have two field date from and date to... i want to enter an employee in date range that range should not be enter again.

View 8 Replies View Related

Forms :: Unable To Put A Check For A Form?

Jul 5, 2010

I have a leave form where I need to put a check which prevents the user from entering 'sick leave' if the employee hasn't completed 3 months in the company.This is the code I've written:

SELECT ABS(MONTHS_BETWEEN(t.start_date,sysdate)) INTO l_date
FROM hrp_per_all_people_f t
WHERE t.company_id = :parameter.p_company_id
AND t.employee_number = :parameter.p_employee_number

[code]...

The absence_type_id = 21 is for 'sick leave'. I can't figure out why this is not working. When I do enter 'sick leave' for a new employee I do not get any checks and neither am I able to save the changes.

View 6 Replies View Related

Forms :: Want To Check For Checkbox Status With One More Corresponding Value

Aug 3, 2011

I was working with forms and found a problem related to accessing of status of checkbox. In a block with multi record to be checked, if we want to check all the checkbox status which is already clicked, Then what should i do.

Now i am using the function Checkbox_check as:

if Checkbox_Checked('FVA.STATUS')

But the problem with this function, it is giving status of last clicked checkbox only.

View 17 Replies View Related

Forms :: Query Update Check

Nov 19, 2010

We have a form which contain the multiple record.

I require to disable the record during the query, if the certain flag (Y) is activiate. And their as allow to display the next record without the flag (N) is editable.

how to done it.

View 1 Replies View Related

Backup & Recovery :: Check The DB Size Exclude Particular Table A?

Aug 3, 2012

I am trying to calculate the compressed size of backup of my database by excluding table space ( and all the tables in that table space).

I know how to calculate the size of particular user DB size.

select sum(bytes/1024/1024)"size" from dba_segments where owner='ABC';

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved