SQL & PL/SQL :: Values Using USER TABLE?
Apr 19, 2011
I am using pay user tables to store the balance values.
Example:
IF rec.balance_name = 'Gross Earnings'
THEN
l_gross := bal_function(g_assg_id,rec.balance_name,'_ASG_GRE_YTD',p_effct_date);
END IF;
from the above I should not hardcode ('Gross Earnings ) value, so I placed this value in pay user table that can be changed by different clients. how do i refer user table values for the right earnings/net/overtime pay values that can be placed in user table.
View 6 Replies
ADVERTISEMENT
Jun 17, 2009
I am trying to write an update statement which updates the User IDs in one table with the User IDs in another table. However I need to update statement to ignore any duplicates that are in the tables.
View 4 Replies
View Related
Dec 23, 2010
My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.
The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.
fixing the errors or if there is any new logic that can be implemented.
DECLARE
i_e NUMBER(10);
BEGIN
FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL)
LOOP
FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)
[Code]...
View 9 Replies
View Related
Apr 1, 2009
I have a table in my oracle database named user_details in which i have two fields
a) username varchar(30),
b) userpass varchar(30)
i have inserted some data in it
i want my values in user pass to be in encrypted form .
View 1 Replies
View Related
Oct 28, 2013
I am searching the simplest way for ad hoc MINUS.I do:
SELECT *
FROM uam_rss_user_XXXXXXX
WHERE host_name IN
('XXX0349',
'XXX0362',
'XXX0363',
'XXX0343',
'XXX0342',
'XXX0499',
[code]....
and look in the table which values are missing (values that are in host_name IN but not in actual table).is there a simpler way for doing an ad hoc MINUS? I know to insert values in temp. Table. How are experienced Oracle pros doing this task?
View 6 Replies
View Related
Apr 23, 2008
how to retrieve values if user entered in Mixed case means (case insesitive)?
We don't have control when user enters values and it might be SMITH or Smith or smith or SmIth.
How can write the query to get this kind of mixed case data?
View -1 Replies
View Related
Dec 18, 2012
I am customizing a standard form to allow user to copy values. It works fine when I open the form, query a record and hit copy. But if I copy something to the clipboard from the records before I hit copy button, the form behaves differently and throw errors. Can I do something before the copy procedure work to eliminate this error?
View 3 Replies
View Related
Nov 22, 2012
resolve issue while modified the user function code for returns the values as timestamps
---function code
create or replace
function fun_test_timestamp(P_HOUR varchar2) return varchar2
is
sql_stmt varchar2(1000);
begin
[Code].....
Input:-
select fun_test_timestamp('5') from dual;
Output:-
SELECT CURRENT_TIMESTAMP - INTERVAL '5' HOUR FROM DUAL;
Modified Fun Code:
create or replace
function fun_test_timestamp(P_HOUR varchar2) return timestamp
is
sql_stmt varchar2(1000);
[Code]...
Input:-
select fun_test_timestamp('5') from dual;
Output:-
ORA-00911: invalid character
ORA-06512: at "NETVERTEXTRUNK.FUN_TEST_TIMESTAMP", line 8
00911. 00000 - "invalid character"
*Cause: identifiers may not start with any ASCII character other than letters and numbers. $#_ are also allowed after the first character. Identifiers enclosed by doublequotes may contain
any character other than a doublequote. Alternative quotes (q'#...#') cannot use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL Language Reference Manual.
*Action:
View 6 Replies
View Related
Jul 30, 2012
I stumbled about some weird 11gR2 behavior (running on AIX).When I performed a join between a table with user based content (parts belonging to an sourcing scope) and a base table (parts available) whereas the parts have to fulfill a special regular expression, it showed that the same query is faster when using outer join than inner join (about 0.7sec vs. 20sec; which makes me believe that regexp_like works wrong when involved in an inner join).
i tried the same statement with a standard like (but not fulfilling the same condition).This time performance was as expected (inner join outperforming outer join).
Oracle version information
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
[code]...
I can see it, the execution plan for the "inner join" doesn't show so much more costs than the one for the outer (but why at all is does an inner join cost more?) ...The execution plan for both "not like" is the same and (surprisingly ;-) ) similar to "outer-regexp".
I hope sample data are not needed as there would be needed a lot...this is the second time I came across the "plan worse but execution time better" phenomenon.
View 10 Replies
View Related
May 21, 2011
I have got two tables emp_dtl and iou_tab. i have already made entries i.e booking no, emp_cd, emp_name etc in emp_dtl snc its my master table. I want to retrieve the booking nos through lov in iou_tab which are generated in emp_dtl and corresponding info of emp_cd and emp_name should come in the respected fields in iou_tab.
View 1 Replies
View Related
Aug 31, 2010
I have the below data in table test_1.
select * from test_1
IDNameTotal
-----------
1A100
2B100
3C100
4D100
test_2 table contains the concatination of ID's with comma seperated. Actually in this table ID column is of datatype varchar2.
select * from test_2
ID
----
1,2,3
My requirement is to select the data from test_1 table where the id values in this table exists in test_2 table. I tried with the belowselect statement, but could not get any data.
SELECT * FROM test_1 WHERE to_char(id) IN (SELECT id FROM test_2)
create table test_1 (id number, name varchar2(100), total number)
create table test_2(id varchar2(100))
insert into test_1 values (1,'A',100)
insert into test_1 values (2,'B',100)
insert into test_1 values (3,'C',100)
insert into test_1 values (4,'D',100)
View 4 Replies
View Related
Sep 6, 2011
i need to grant a user to see all table into another table and not to use this option (grant any table)
View 9 Replies
View Related
Aug 3, 2012
I have a table AUD$ (containts some data) and aud_new(exactly structure same as AUD$ table with no data)
i want to schedule a job which will insert all data from AUD$ into table aud_new$ from aud$ table on next day again data from aud$ should be append to aud_new table and aud$ table should be truncated again.
The job should run on every day at midnight at 2 am
(note : If insertion in table aud_new get fail due to any reason , the aud$ table must not get truncated )
View 3 Replies
View Related
Dec 19, 2012
I've tried to Update table with values from another table, but I couldn't.Is there a single way to do that with just one query?Below goes an example:
CREATE TABLE TEST1 (
COD NUMBER,
DCR VARCHAR2(10) );
CREATE TABLE TEST2 (
COD NUMBER,
DCR VARCHAR2(10),
DCR2 VARCHAR2(10) );
[code]....
I want to UPDATE the field DCR of the table TEST1 with the VALUE of the field DCR2 of the table TEST2.At the end, after the update, the table TEST1 would be like that:
SELECT * FROM TEST1
COD --- DCR
------------------
1 'TESTE3'
View 6 Replies
View Related
Jul 11, 2012
I have two user a and b. I have to Grant select update insert Delete Permission to all tables of user b To User a. how can it possible?
View 5 Replies
View Related
Jul 20, 2011
Im using oracle forms 10g. i want to take backup of user's table.
i.e, scott user has 4 tables. if i login as scott user and select the tables what i have to take backup using check box and click a backup button,the selected tables backup and stored in .MDB(MS Access).
How to achive this?
View 4 Replies
View Related
Jan 1, 2012
How to Insert all user names into a table from sql for example when i use select * from all_users command it displays all user names i want that how we transfer these user names into a table.
View 11 Replies
View Related
Jan 30, 2013
on 11G R2,
We want to grant truncate any table to a user.
How ?
We should create the following procedure ?
1. Create the procedure to truncate the table.
create or replace procedure truncate_table (
table_name varchar2,
storage_type varchar2)
[Code]....
grant execute on <procedure_name> to <user>Is it for only one table ? If yes how to do that for all tables ? Or the tables of a schema ?
View 6 Replies
View Related
Jul 16, 2011
I have an issue on selecting data from a nested table owned by other user.
I have given select access on stc_irb_usr.stc_loy_settlebillpay_map_main to anthr user adminofs.
But when I try to query, it says insufficient privileges. adminofs is able to select other columns which are not nested.
I have given "grant execute on STC_IRB_USR.STC_BILLPAY_MAP_TAB_TYPE to adminofs;" but it did not
select * from stc_irb_usr.stc_loy_settlebillpay_map_main where rownum<3;
ERROR: OCI-21700: object does not exist or is marked for delete
SQL> desc stc_irb_usr.stc_loy_settlebillpay_map_main
Name Null? Type
----------------------------------------- -------- ----------------------------
BATCH_NUM NOT NULL NUMBER
ACCOUNT_NUM NOT NULL VARCHAR2(40)
PAY_BILL_SEQ_MAP STC_IRB_USR.STC_BILLPAY_MAP_
[Code]....
Name Null? Type
----------------------------------------- -------- ----------------------------
ALLOCATING_CREDIT_SEQ NUMBER
ALLOCATION_ENTITY_SEQ NUMBER
ALLOCATING_CREDIT_DATE DATE
ALLOCATING_CREDIT_TYPE VARCHAR2(20)
ALLOCATION_ENTITY_DATE DATE
[Code]...
View 2 Replies
View Related
Dec 3, 2008
I'm having a table which has a column which has values given inside square brackets.
[[[123412]]] ,[[[[werer34]]]],'[[qw2_w3rt]]
Now, the requirement is to get the values which are inside the innermost square brackets.Such as in
First case : 123412
Second one: werer34
Third : qw2_w3rt
Only the values inside are needed. Is there any way to achieve it using Pl-Sql or just using Sql ?
View 3 Replies
View Related
Mar 12, 2013
I have to see the information of DATA1,DATA2,DATA3,DATA4,DATA5 .
How can I write the select statement to see THE above AS follows
datas--column name
DATA1
DATA2
DATA3
DATA4
DATA5
View 17 Replies
View Related
Dec 20, 2012
I need to add values of one column values from a table to another table each value as a column. Below i am considering only for 3 values in real time i have more than 50 values
CREATE TABLE TEST_REG ( VAL VARCHAR2(1));
INSERT INTO TEST_REG VALUES ('A');
INSERT INTO TEST_REG VALUES ('B');
INSERT INTO TEST_REG VALUES ('C');
CREATE TABLE TEST_HOLD ( COL1 VARCHAR2(1),COL2 VARCHAR2(1),COL3 VARCHAR2(1)); -- in realtime i have 100 columns
Now i need the output as:
when i
select * from test_hold;
COL1 COL2 COL3
A B C
View 6 Replies
View Related
Jan 29, 2013
i have a table which has 2 columns.1st column has userId and the other contains an xml data as a link.on clicking that link a new file opens containing the data in xml format.
<fields>
<field key="Public Email">piyush@chand.com</field>
<field key="Location">bangalore</field>
<field key="Website" />
<field key="Birthday">0001-01-01 00:00:00</field>
<field key="Gender">Male</field>
<field key="Language">English</field>
</fields>
i need to access location of a particular userId.How can i do that?
View 6 Replies
View Related
May 7, 2010
I have a table called 'price' and want to update it with a table called 'update'. See workbook in the attachment.
The idea is to update the table 'price' with values from 'update' as long as the values from 'update' are different then the values in 'price' or the value in price with 'enddat' empty is not present in 'update'. you can still follow this...
Example:
Enddat for df_location 4 from 'price' is empty and df_location 4 is not present in 'update' so 'enddat' for this df_location should be filled with any date from 'update' (there is always going to be just one distinct date).
I have tried something like this:
proc sql;
insert into work.price (enddat) where enddat = ''
select (date) where df_location not in (select
df_location from work.update);
;
quit;
Well this obviously doesn't work. I'm trying to pass 2 criteria (when enddat is empty and df_location is not present in 'update') but I'm not sure where to put them...
View 3 Replies
View Related
Oct 12, 2010
Below is my trigger:
CREATE OR REPLACE
TRIGGER AR_BANDS_TEST
BEFORE INSERT OR DELETE OR UPDATE ON FEE_BANDS
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
[code]...
I want the old and new values to come in a single row of the table while inserting in trigger like below:
OLD_FEE_RU OLD_FEE_BANDS_ID OLD_RATE_NUMBER OLD_RATE_PERCENT NEW_FEE_RU NEW_FEE_BANDS_ID NEW_RATE_NU
---------- ---------------- --------------- ---------------- ---------- ---------------- -----------
TESTALL 1 0 1 TESTALL 1 0
View 7 Replies
View Related
Oct 6, 2012
I need to insert rows in a table all the columns are have same value except one column i.e.
insert into table values( 'a','b','b','b');
insert into table values( 'b','b','b','b');
insert into table values( 'c','b','b','b');
is there any short cut to insert because there are thousands of records to be inserted.
View 3 Replies
View Related
Jul 26, 2013
I have a table EMP having columns User_Id, ENO,Org_ID, Dept_ID.Now I would like to insert values into this EMP table using below conditions.Insert into EMP
(user_seq.nextval,(select empno from employees where empno in(....(empnumbers), (select org_id from organizations where org_name=' XXXXXXXXXX'), (select dept_id from DEPT where dname in ('MANAGER','ANALYST','SALESMAN') ))
provide me the query for the above requirement.
View 40 Replies
View Related
Mar 11, 2011
i got a prob in executing a query in my oracle.TRANSACTIONLIST is the table and my query is..
select * from TRANSACTIONLIST where USER_ID = '07751A1247'the table has 2 records with that user id.
But it is given result as "no data found".
View 4 Replies
View Related
Dec 27, 2012
I've created a type as
CODETYPE TEST_DWH_PROD AS OBJECT
(WO_RELEASE_DATE DATE,
SITE_CODE__MFG CHAR(5),
SITE_CODE__PARENT CHAR(5),
ORDER_TYPE CHAR(3),
MFG_TYPE VARCHAR2(10),
[code]....
the above query is giving the value of current QTD(on the basis of V_REPORT_DATE)..I need to integrate the same as user want the output as table type and he can also give some filter conditions.
View 1 Replies
View Related
Oct 13, 2009
That is I have created the User Defined Data Type as following. CREATE OR REPLACE TYPE Bit_Type AS OBJECT(Bit NUMBER(1,0));
After completing this creation of new UDT, I am trying to create the table with this UDT as follows, CREATE OR REPLACE TABLE Sample_Bit ( RegID Bit_Type);
I received an Error Message like:
SQL Error: ORA-22913: must specify table name for nested table column or attribute
22913. 00000 - "must specify table name for nested table column or attribute"
*Cause: The storage clause is not specified for a nested table column or attribute.
*Action: Specify the nested table storage clause for the nested table column or attribute.
View 1 Replies
View Related
