Dynamic SQL With Select Update For
Jun 21, 2012
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].......
View 3 Replies
ADVERTISEMENT
Jul 4, 2010
i want to select dynamic column names in my select statement in my function.
View 4 Replies
View Related
Jan 23, 2012
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
View 4 Replies
View Related
Jul 12, 2013
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.
Here is the code :
DECLARE
v_VerTabName VARCHAR2(30);
v_Minor NUMBER(2);
[Code]....
But when I compile this I get ORA-00936 : Missing expression at the EXECUTE IMMEDIATE line.
View 3 Replies
View Related
Oct 19, 2011
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?
View 4 Replies
View Related
Jul 20, 2011
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.
View 21 Replies
View Related
Apr 16, 2010
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
Table1
Col1prodvalue1rank
1A21
2A62
3A53
4B61
5B32
6B73
7C41
8C22
9C13
10C74
Output
Col1prodvalue1rank
1A51
2A52
3A33
4B101
5B02
6B63
7C51
8C52
9C03
10C44
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.
View 9 Replies
View Related
Oct 25, 2010
I want to convert the below SQL to a dynamic sql to be executed from execute immediate statement.
UPDATE transaction SET loannum = lpad(loannum,12,'0')
View 15 Replies
View Related
Jul 25, 2010
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.
View 4 Replies
View Related
Nov 8, 2012
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.
Plan code shows no value in the session.
View 4 Replies
View Related
Oct 30, 2013
Can we use select statement in case statment for an update?
Example
UPDATE x SET field = CASE WHEN
(SELECT field1
FROM Y
WHERE field3 = x.field3
IS NOT NULL
THEN 'B'
ELSE 'C'
where cny# = 112160
View 3 Replies
View Related
Jul 16, 2007
We have this statement in our database:
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.
View 2 Replies
View Related
Jul 7, 2010
how can i insert and update to a table in oracle database 10g through a select statement. not using merge.
View 2 Replies
View Related
Jan 20, 2011
I need to generate a report by showing the select, insert, update transactions count per day.
I have been use V$SYSSTAT veiw but there is not include my requested data exactly!
[URL]
View 3 Replies
View Related
Jun 6, 2012
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)
);
[Code]...
id ticket_type is_sold
------------ -------------------- -----
10000004 1 1
10000005 2 1
10000006 1 0
10000007 1 0
10000008 2 0
10000009 2 0
SQL> select * from customer_ticket;
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.
View 13 Replies
View Related
Dec 17, 2010
How to get all the name of tables that a user can select, insert, update or delete?
View 2 Replies
View Related
Jul 3, 2013
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?
View 9 Replies
View Related
Aug 3, 2011
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.
View 18 Replies
View Related
Apr 8, 2010
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.
View 5 Replies
View Related
Apr 8, 2013
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]....
Is there a solution or bypass?
View 3 Replies
View Related
May 14, 2010
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?
View 4 Replies
View Related
Sep 7, 2010
I have to update 20 and 60 million records of a table. The update statement are
1> 20 million recs
update mycustomer set update_time=add_months(sysdate,240) where seq_num = 1;
commit;
2> 60 million recs
update mycustomer set update_time=sysdate-seq_num where seq_num <> 1;
commit;
Q1> Is there any way to improve performance
Q2> Will parallel dml improve performance
Q2> Would a pl/sql cursor make any difference in speed.
View 1 Replies
View Related
Aug 17, 2010
This is my query:
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.
View 9 Replies
View Related
May 29, 2012
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;
/
View 3 Replies
View Related
Mar 6, 2013
create table test1
( ID NUMBER(11)
,MEMBER_NMBR NUMBER(10)
,CODE_NMBR NUMBER(7)
,ROW_EFCTV_DT DATE
,ROW_TRMNTN_DT DATE
[code]....
insert into test1 values (11007,7462,32,'30/sep/2012','31/dec/9999',3,'25/sep/1998','AUTUMN',1,0,344);
insert into test1 values (11007,7462,32,'30/oct/2012','31/dec/9999',3,'25/sep/1998','AUTUMN',1,0,344);
IDMEMBER_NMBRCODE_NMBRROW_EFCTV_DTROW_TRMNTN_DTFLAG_NMBRBRTH_DTNAMECLAIM_CDAMT1AMT2
1100774623209/30/2012 00:0012/31/9999 00:00309/25/1998 00:00AUTUMN10344
1100774623210/30/2012 00:0012/31/9999 00:00309/25/1998 00:00AUTUMN10344
I have to update the row_trmntn_dt of first row to row_efctv_dt of 2nd row which is 30th Oct 2012 - 1 day i.e. 29th Oct 2012
View 10 Replies
View Related
May 30, 2013
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.
View 5 Replies
View Related
Mar 21, 2011
I have one doubt about update command in sql. How to update the multiple rows with different values using update statment.
Eg:-
SQL> set linesize 500;
SQL> set pagesize 500;
SQL> select * from emp;
SQL> select empno,ename,sal from emp;
SQL> select empno,ename,sal from emp;
EMPNO ENAME SAL
---------- ---------- ----------
7839 KING 5000
7698 BLAKE 2850
7782 CLARK 2450
7566 JONES 2975
7654 MARTIN 1250
[Code]....
The above table contains 14 records. Now i would like to update the salary column with different values like
EMPNO SAL
===========
7839 18000
7698 20000
7782 5000
...
...
...
7934 25000
How to update above values with single update query.
View 11 Replies
View Related
Jan 25, 2012
After many tests I can't make work and update of the same table inside the same table.
Trying to avoid Mutating Table Error now I have
ORA-00036: maximum number of recursive SQL levels (50) exceeded
Sample Data :
create table test_compound (USERID VARCHAR2(10),APP VARCHAR2(15),LAST_UPDATED_ON TIMESTAMP);
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.
View 13 Replies
View Related
Jul 19, 2011
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).
View 4 Replies
View Related
Mar 3, 2011
I have a table with around 80 columns. All i need is to select first 40 columns.
Is there any way to select first 40 columns without giving all the 40 Column Names in select clause.
View 2 Replies
View Related