I wrote a simple procedure to copy the create timestamp and create user name to update timestamp and update user name of the same record. (See code below)
This works fine for a hard-coded table and primary key column. However, I cannot figure out how to get this to work with dynamic sql.
All my other procs, which don't use SELECT UPDATE FOR work fine with dynamic sql.
Proc that works:
CODECREATE OR REPLACE PROCEDURE proc_set_upd_columns
IS
CURSOR c1 IS
SELECT *
FROM mytable
FOR UPDATE
ORDER BY mycolumn;
c1rec c1%ROWTYPE;
[code].........
Partial proc that does NOT work:
CODECREATE OR REPLACE PROCEDURE LDEVORE.proc_set_upd_columns (
p_input_table_name IN VARCHAR2,
p_pk_id_col_name IN VARCHAR2)
IS
v_qry_str VARCHAR2(1000);
v_cursor_str VARCHAR2(1000);
v_create_tmstmp TIMESTAMP;
v_create_user_name VARCHAR2(30);
[code].......
i have creatd view,in which i need to show the entry of year in column wise,though the data is in row /record wise.By iteration i can manipulate the year in decode function like " (DECODE(a.Year,(select distinct max(year)-1 from BI_BALANCE_SHEET), a.Balance))",but is there any way to chachge the alias name /column name,here is the query
select SUM(DECODE(a.Year,(select distinct max(year)-1 from BI_BALANCE_SHEET), a.Balance)) as year2010, SUM(DECODE(a.Year,(select distinct max(year)from BI_BALANCE_SHEET) , a.Balance))as Year2011 from DEMO a
I do not know the table name but have a query that for sure returns the table name and I want to select a column value from the table into my PLSQL variable.
I have a question regarding the optimal way to code a dynamic SELECT INTO statement. Below are the 2 posiibilities I know of:
1. Dynamically executing the SELECT statement and making use of the INTO clause of the EXECUTE IMMEDIATE statement CREATE OR REPLACE FUNCTION get_num_of_employees (p_loc VARCHAR2, p_job VARCHAR2) RETURN NUMBER IS v_query_str VARCHAR2(1000);
[code]...
2. Encapsulating the SELECT INTO statement in a block and dynamically exectuting the block
CREATE OR REPLACE FUNCTION get_num_of_employees (p_loc VARCHAR2, p_job VARCHAR2) RETURN NUMBER IS v_query_str VARCHAR2(1000);
[code]...
which way would be preferred? I know the second method uses a bind variable for the INTO clause, but does the first one also use bind varialbes (no semi-colon)? Any differences in terms of efficiency or speed?
I am trying to run a dynamic select statement form a function and return the result into a variable, everything goes fine but the return is always null!
CREATE TABLE AFESD.MAJOR_ACCOUNT ( NUMBER0 NUMBER(2) NOT NULL, SHORT_NAME CHAR(35 BYTE) NOT NULL, FULL_NAME CHAR(50 BYTE) )
--Actually any table can do
CREATE OR REPLACE FUNCTION F_GEN_SELECT_INT (S_APP_USER IN VARCHAR2, I_MODULE_ID IN NUMBER, S_TABLE IN VARCHAR2, S_COLUMNS IN VARCHAR2) RETURN NUMBER AS I_RETURN NUMBER; S_SQL VARCHAR2(300); --S_DB_ERROR VARCHAR2(100);
[code]....
B.S. I didnt delete the commented lines to have your review comments.
I am having a table with 4 columns as mentioned below
For a particular prod the value greater less than 5 should be rounded to 5 and value greater than 5 should be rounded to 10. And the rounded quantity should be adjusted with in a product starting with order by of rank with in a prod else leave it
I have taken all the records in to a cursor. Once after rounding the request of 1st rank and adjusting the values of next rank is done. Trying to round the value for 2nd rank as done for 1st rank. Its not taking the recently updated value(i,e adjusted value in rounding of 1st rank).
This is because of using a cursor having a value which is of old value. Is there any way to handle such scenario's where cursor records gets dynamically updated when a table record is updated.
I have a problem that i have hard coded the username.tablename in each select statement of all forms of my application. Now i want to use a dynamic variable in place of username in each select statement throughout the application. The example is:
select * from scott.emp and i want to write it as: select * from variable.emp
But at compilation of the form the compiler should know the above variable name.
I have tried to use following select statement but it does not work.
select user into :global.username from user_users
I think perhaps my problem would be solved with Dynamic SQL Statement but i have no experience by using this statement.
When a user selects a Plan Type (Parent Select list) Plan codes (Child select list) to be filtered, working OK. When a plan code is selected I need to do conditional display of regions. Example: Plan code = A then display Region A Plan code = B then display Region B etc.
I have a DA on Plan code select list but it's not working as it's being a cascaded (child) select list and the 'Change' event is already fired when I selected the Plan type, I also tried with other event options with no success.
select <primary key> from <table> where <primary key> = 1 for update.
Issuing the above statement in 9i, results in a ROW-S (SS) shared type of lock on the table. However, in an identical database in 10g, it results in ROW-X (SX)type of lock. we see all that by qeurying view DBA_DML_LOCKS. Why the different behavior in 10g? Or is it an ORA.INI setting that causes this behavior?
In addition, in 10g the select for update blocks other users trying to access different rows in the table thru oracle forms 6i application. We see this because DBA_DML_LOCKS reports "Blocking" in column "BLOCKING_OTHERS" for the specific row.
I'm looking for a solution to select the first row that is not currently locked in a table and insert a record to another table that reference that first row. this is my scenario:
create table ticket ( id number(10) not null,, ticket_type number(1) not null,, is_sold number(1) not null, CONSTRAINT ticket_pk PRIMARY KEY (id) );
cust_id cust_name ticket_id ------------ -------------------- ---------- 1 John 10000004 2 Sara 10000005
my goal is finding the first free ticket ( not sold ) in the ticket table and insert buyer information of that ticket in customer_ticket table. at last I will mark that ticket as a sold one in ticket table with update.
Problem is that the first transaction locks the the first row in ticket table and the second transaction running the same query goes to wait untill the first transaction commit or rollback. However when first transaction finish successfully, second transaction select duplicate id from ticket table that was selected by the first transaction!
I tried to solve problem with "skip locked" and "nowait" options with select for update, but they didn't work.
I'm having a strange case of ORA-01002 Fetch out of sequence when I perform a process in the application.
Application logs, trace files (produced with dbms_monitor), A system trigger to capture the error - all proved that the culprit is a single SQL, *Not a PL/SQL block* That caused the issue:
Select COL1, COL2, COL3, COL4, rowid FROM TAB1 WHERE COL1 = :1 AND COL2 = :2 AND COL3 = :3 ORDER BY COL1 ASC, COL2 ASC, COL4 ASC FOR UPDATE NOWAIT;
binds are: 'AAA' , 10000 , 0
Also, My trigger looks like this:
CREATE OR REPLACE TRIGGER after_error AFTER SERVERERROR ON DATABASE DECLARE pragma autonomous_transaction;
[code]...
I've read some about this error and everywhere it says that it has to do with fetching from an invalid cursor,And all the examples I've observed are of PL/SQL block - never seen any example/explanation of how it happens in a SQL query.
if the OCI maybe is somehow doing things different than the logs/triggers show?
My code executes a Select For Update before updating a table. In some cases the network is disconnected and it causes the lock to hang. Then, I must kill the session in order to realese this lock.
I want to do it automatically. I would like to create a job that kills session that has a lock due to Select For Update that is not alive.
i have two tables test1 and test2. i want to update the column(DEPT_DSCR) of both the tables TEST1 and TEST2 using select for update and current of...using cursor.
I have a code written as follows :
DECLARE v_mydept1 TEST1.DEPT_CD%TYPE; v_mydept2 TEST2.DEPT_CD%TYPE; CURSOR C1 IS SELECT TEST1.DEPT_CD,TEST2.DEPT_CD FROM TEST1,TEST2 WHERE TEST1.DEPT_CD = TEST2.DEPT_CD AND TEST1.DEPT_CD = 'AA' FOR UPDATE OF TEST1.DEPT_DSCR,TEST2.DEPT_DSCR; [code].......
The above code when run says that it runs successfully. But it does not updates the desired columns[DEPT_DSCR].
It only works when we want to update single or multiple columns of same table...i.e. by providing these columns after "FOR UPDATE OF" I am not sure what is the exact problem when we want to update multiple columns of different tables.
I'm using dynamic sql (DBMS_SQL) to define columns of ref cursor. It works Ok but the problem is when i'm using PL/SQL CURSOR in the REF CURSOR. Then,I'm getting :
Error at line 3 ORA-00932: inconsistent datatypes: expected NUMBER got CURSER ORA-06512: at "SYS.DBMS_SQL", line 1830 ORA-06512: at "TW.PRINT_REF_CURSOR", line 28 ORA-06512: at line 9
Here is my code: set serveroutput on exec DBMS_OUTPUT.ENABLE(1000000); declare l_cursor sys_refcursor; begin [code]....
I have a base table (Table A) block with multiple records displayed. I need to track audits to this underlying table in the following way:
If user updates a field in the block I want the pre-changed record's audit fields to be set and I need to create a copy of the record with the changed values. Basically any changes will result in the record being logically deleted, and a copy record created with the newly changed values.
Tried to implement in the block's pre-update trigger which will call a package to directly update Table A then Insert into Table A, then requery the block. Is there a clean and efficient way to do this?
UPDATE t_tt_hours a SET a.sak_request = ( SELECT b.sak_request FROM t_requests b, co c
[Code]...
The problem I am having is that it is updating all rows even when it is pulling back a null value for b.sak_request. I've tried adding b.sak_request is not null to the select statement like this:
UPDATE t_tt_hours a SET a.sak_request = ( SELECT b.sak_request FROM t_requests b, co c WHERE b.nam_eds_tracking_id = c.id_dir_track_eds
[Code]...
but it doesn't seem to make a difference. The reason I need to do this is that the difference between where it matches with a valid (non-null) value is 396 rows vs. 12,484 rows which is too time consuming to run on my page.
i want to create a trigger that will update a table when there is an insert or update.i can't across this error that i don't even know what it means "table %s.%s is mutating, trigger/function may not see it".
*Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.
CREATE OR REPLACE TRIGGER set_date_end BEFORE INSERT OR UPDATE OF issued ON shares_amount FOR EACH ROW DECLARE BEGIN INSERT INTO shares_amount(date_end) VALUES(SYSDATE); END set_date_end; /
i am reading the columns value from different table but i want to update it with single update statement. such as how to update multiple columns (50 columns) of table with single update statement .. is there any sql statement available i know it how to do with pl/sql.
insert into test_compound values ('user1','1',systimestamp); insert into test_compound values ('user2','2',systimestamp-4); insert into test_compound values ('user3','3',systimestamp-6);
CREATE OR REPLACE TRIGGER trigger_test FOR UPDATE ON test_compound COMPOUND TRIGGER TYPE t_tab IS TABLE OF VARCHAR2(50); l_tab t_tab := t_tab(); [code].......
When I execute :
update test_compound set last_updated_on=systimestamp where userid='user1' and app='1';
The trigger should update the first row and all the data from test_compound table where userid='user1'. Maybe the problem is that updating the same table inside the trigger is firing in a recursive way the trigger.
I have a column "empno" in EMP table and "deptno" in DEPT table . I want to update both the columns with single UPDATE statement. With out a creation of stored procedure or view(updating it through view).
