here' my code.
delimiter //
SELECT CONNECTION_ID()//
LOCK TABLES source.jos_daikin_control_card_fcu_model WRITE//
[Code].....
ERROR 1192 (HY000): Can't execute the given command because you have active lock
ed tables or an active transaction
Is there a way to prevent other session from accessing the tables called in the procedure?
i've created a schema, which contains 30 tables. the tables are accessed & updated through a web based application. i've made public synonym for all tables for some reasons. how can prevent any one other than application users from updating the tables from Sqlplus.
View 2 Replies View Related I am Getting the error when writing the procedure. How to Solve this Error .
Error: PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
Is there any difference behind that using of either IS or AS when writing Stored Procedure?
View 1 Replies View RelatedI am trying to implement a Houskeeping program for files generated in 4 different servers. This housekeeping program is run as a batch job and I need to use PLSQL to implement it. The files need to be housekept on the basis of the file creation date.
how I can go about doing this using a PLSQL stored procedure.
I do not want the "PL/SQL procedure successfully completed." message in my output file - I don't see/recognise an option to suppress or remove this.
-- Test Proc
--
set linesize 500
set pagesize 50000
set tab off
set wrap off
set serveroutput on
set feedback on
[code]...
PL/SQL procedure successfully completed.
In my application i have a requirement as follows.I have 3 tables table1,table2,table3.I have 4 tickets for one license number which is related to client table as follows.
table1
=====
license_nbr(pk) name address
=============== ===== =======
LicNo1 test testing
Table2
=====
ticket number(pk) amountto be paid balance_amount license_nbr(fk table2)
tk1 200 200 0 LicNo1
tk2 300 300 0 LicNo1
tk3 400 400 0 LicNo1
tk4 500 500 400 LicNo1
table3
=====
payment table
ticket_number(fk table2) amount paid payment status license_nbr(fk table1)
tk1 1000 excess paid. LicNo1
so now the excess paid amount to be adjusted for the remaining tickets through tk2 to tk4.and only tk4 should remain with 400 the balance amount should be updated accordinglyand i have a license number which is a foreignkey of client table. writing a procedure for updating the table2 based on the table3.
I want to create a procedure that kills the sessions of the user TEST.
I have coded the below, but that dont work.
CREATE OR REPLACE PACKAGE BODY SYSTEM.test AS procedure killsessions IS
v_sessions_sql varchar2(40);
--v_sessions_sql2 varchar2(40);
[Code].....
I have got 2 users as user1 and user2.I have used the following statements from user 'user1':
create role GENEVAOBJECTS;
grant select, insert, update, delete on PRODUCT to GENEVAOBJECTS;
grant GENEVAOBJECTS to user2;
In the above statements, product is a table. Now, I could able to access this table from user 'user2'. But however if I write a procedure in user2 schema accessing the table product, then the procedure is not getting compiled.
create or replace procedure test_prc as
v_test number(9);
begin
select product_id into v_test
from PRODUCT where rownum=1;
[code]...
why I cannot access that table from procedure?
Is it possible to create local temp variable based table within sql procedures, i am planning on writing few select queies,and want to store teh result of each select to a temp variable based table.
Within sql serevr, i can create temp tables within and then later after i use teh data at teh end drop the table.
Is it possible, since i don't want to create any physical tables.
how to write this query using procedure and how it can display.
SELECT iusr_id,
so_curr_assign_to,
comp_id,
[Code]....
I want to create a stored procedure where tables of two different databases are to be used.
View 4 Replies View RelatedI'm writing a Procedure which Updates or Inserts data in Multiple tables. Selected fields of 10 tables need to be updated or Inserted. For this I created a table which comprises of fields related to all 10 tables. Then I write Procedure. Under this I create a Cursor which uploads the data from the newly created table which contains different fields of 10 tables. Then I write Update and Insert statements one by one for all 10 tables.
Sample Procedure below.
-------------------------------------------
Create or replace procedure p_proc as
spidm spriden.spriden_pidm%type;
cursor mycur is select * from mytable;
begin
for rec in mycur
[code]......
----------
Note: I created table on my server because data is coming from different server. They will upload the data in the table from there I pick and update the tables. Is updating or Inserting data in different tables one by one is correct?
create a procedure so that I could get minus query of 2 table as a result.
I have two table 1- src_table_list ,2- tgt_table_list both tables have 2 columns : serial_no,table_name and 100 records each. and details mentioned in column "table_name" are actually tables name which present in my testing database.
so I need one procedure which will pick one table_name from src_table_list and one table_name from tgt_table_name each time recursively and provide minus query as a result. as below.
select c1,c2,c3,c4 from table1 --(fetched from src_table_list)
minus
select b1,b2,b3,b4 from table2 --(fetched from tgt_table_list)
create the procedure..as I have to prepare minus query for more than 200 tables and then I need to test them for integration testing..
I have an Oracle IMP that seems to complete, but there are two things that I don't understand:
1) The tables seem to load successfully in about 4 hours. After the tables are loaded, the IMP procedure appears to hang for10+ hours. I can query the tables at this time, so I'm not sure what it's doing. Is it maybe applying constraints?Is it normal for applying constraints to take 3-4X as long as importing the data?
2) I see a few of these messages in the alert log during the import.Thread 1 cannot allocate new log, sequence 2754 I don't see errors in the IMP log so are these messages informational or warnings, or will there be something wrong with my imported data?
This is my first time running a stored procedure. The procedure is already written.
We have various related table. I need to use this stored procedure and extract information from an excel sheet into the multiple tables.
I have table A.Security:
CouponIssue_nameQuantity_typeCurrencySec_TypeSec_alias
1 a 56 USD AB 676
2 b 45 INR AB 143
3 c 32 JPY BA 200
and Table B.Security:
CouponIssue_nameQuantity_typeCurrencySec_TypeSec_alias
1 a 33 USD OB 676
9 b 21 NZD AC 143
8 c 19 GBP CA 200
build a Stored Proc which compares sepecific fields in the 2 tables and reports into a new table as:
A.Security.sec_aliasB.Security.sec_aliastable fieldA-valueB-value Compare
676 676 Security Currency USD USD Match
143 143 Security Currency INR NZD Not-Match
Fields to be compared are:
Quantity_type
Currency
Sec_type
The join between the tables A and B can be made on Sec_alias.Also, this is just a single pair of tables. Further I need to compare 5 more pairs of tables after this and report out of the new table.
I am writing a procedure that will be called from a java wrapper.
The procedure do a lot of data manipulations and in between i am creating global temp table and saving the data into it for each request thats given as a parameter to the procedure. After all the processing i have to write the data from this global temp table into a physical table and atlast drop the temp table.
Create or replace proc_name ()
update table........
delete from ..........
CREATE GLOBAL TEMPORARY TABLE TSAAG
( supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50)
)
insert into............
drop table TSAAG;
End;
creating a global temp table inside a procedure is expensive...
Do we have anything like creating table before and calling the instanse of it in procedure.
Do we have any alternatives to this..
I use Oracle 11.2.0.3.I have APPOINTMENT table:(ID NUMBER PK , FROM_TIME TIMESTAMP , TILL_TIME TIMESTAMP , NOTE VARCHAR2 (200))I want to prevent end users from making an appointment that overlaps with other appointments. I created this trigger:
Create or replace TRIGGER "APPOINTMENT_CK"
BEFORE
INSERT OR UPDATE ON APPOINTMENT FOR EACH ROW
BEGIN
IF
[code]....
But, it looks like I cannot use column name here.
I want to avoid duplication on old_nic_no and new_nic_no number in a Table, for this i am using a following trigger
create or replace TRIGGER chk_ip_duplications
BEFORE UPDATE
ON cb_insured_person_bak
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
[Code]...
when i use following command
update cb_insured_person_bak set new_nic_no = '34601-0774284-9' where eobi_no = '4300G043746'
34601-0774284-9 already exist with another eobi_no , i expect error described in rise_application_error clause but i give following error
ORA-04091: Table cb_insured_person_bak is mutating..........
How i can resolve this problem.....
Form has insert and delete enabled.
I would like to prevent the deletion of a record based on some column value.
Something like:
IF (COLA value < 10) THEN
SHOW ALERT;
RECORD IS NOT DELETE
ELSE
DELETE RECORD AS NORMAL
END IF;
I want this to happen even before the record is marked for deletion, but I don't know where to put it. I tried a pre-delete trigger, but it does not work they way I want.
how can i prevent an item from being copying. I mean to say user will not able to copy the value from the item and paste in notepad.
The item will be enabled also i can not desable it.
Im trying to create a trigger to prevent transactions like insert, update and delete based on two conditions:
1) Friday
2) Between 5pm to 8am
Im my application, after login, I have a hidden & protected item whose value I am setting based on a logic. Also I would like to access this item's value in a application process, so session will have this value. Now I do not want anybody to be able to change the value of this item. For Eg, if my item was P1_RESP, I was able to change its value in the following way, I have an edit link on somr IR, where I redirect to another page passing some values and the URL looks like : [URL] After the browser loads, I can edit the above URL to below and click enter, [URL]
View 1 Replies View RelatedWe would like to create functions to insert and update our tables and would like to make it not possible to update and insert the table directly outside of the function. Is there a way to do that in the trigger?
View 1 Replies View RelatedHow can prevent DBA user from disabling any trigger ? suppose DBA user can't be able to execute following command
ALTER TRIGGER {trigger_name} DISABLE;
how can i protect it?
we are using oracle10g version. Currently my character set is WE8MSWIN1252 At present, somehow, we have non-english characters(for example spanish) in the database. We wanted to stop entering these kind of characters.. Can we prevent this by changing the current character set?
View 2 Replies View Relatedhow to know when a user was navigating away from the current open form (and I wanted to prevent the navigation with a dialog box) because they had entered a new URL in the address bar and were trying to navigate to it? I've seen a reference to it from an ADF perspective -> [URL]
but was wondering how to do it within Forms?
The reason for this is that i would want to prompt the user to confirm that they wanted to exit the form as potentially they would have a record (in the form) flagged as being updated and as such no other user (including themselves) could susbequently access it. So, I'm basically wanting to know if there is a way to catch the fact that the user is about to navigate to another web page.
Is it by using javascript and if so, how would it be implemented?
we have a table attendance_d with no constraint which have duplicate emp_id we want to stop duplicate emp_id on the same date. if employee's record already entered in today's date then duplicate Error message must show if he tries again to enter the same record. for this i have written the following code but it is not working date wise some body. i want to use on WHEN VALIDATE ITEM TRIGGER in oracle forms 6i.
DECLARE
l_count NUMBER;
BEGIN
[Code]....
i have tried my best to format the syntax of code but in preview it showing like as above i have formated in toad by using the key ctrl+shift+f.
Normally if node fails, VIP & resources are transferred to another node (clients need to failover).
if node is back again CRS detect this, make it as 'family member' and try transfer VIP & resources back to this node (clients will force to failover again).
this failover (despite of failover method I use) make each uncommitted transaction to be rolled back. client need to be disconnected from the service and VIP through they are connected to this service and they need reconnect to the new node - during this stage all DMLs modifications are gone, and need to be re execute.