SQL & PL/SQL :: Mask A Column Value Present In Production
Nov 27, 2012
How we can mask a column value present in the production which is very sesitive like bank a/c no,Credit card nos etc while replicating to the test/development envirnoments in PL/SQL.Things that need to be considered is referential integrity,data types,length of the column and performace while execuing the code.
View 2 Replies
ADVERTISEMENT
May 16, 2013
10g- 10.2.0
This is my query
select to_char(exp_Date,'Mon-YYYY') dt, count(*) from exp_main
where exp_type like 'Income%Photo%'
group by to_char(exp_Date,'Mon-YYYY')
order by exp_date
When I run this I get :not a GROUP BY expression
If I remove the order by, it works fine.
Is there a way to order by since the output of the query is character-sorted.
I did search online most have suggested to use order by column_name. But does not work for me.
View 6 Replies
View Related
Jun 2, 2011
I am trying to get only those values which are not present in the column. I have tried but i think that i am wrong in logic.
SELECT dummy
FROM DUAL
WHERE dummy IN
('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12',
'13')
AND NOT EXISTS (
SELECT pay_list_serial
FROM per_requests_master
WHERE request_status IN ('4', '5')
AND pay_list_no = :list_no)
View 12 Replies
View Related
Apr 29, 2010
i want to produce a function who return XmlType element if she checks that a parameter (that i passed to here is contained in a row in a defined table)
for example
create table sec_form(
form_code VARCHAR2(8)
, sec_code VARCHAR2(5)
) ;
insert into sec_form values ('CSA','section1');
insert into sec_form values ('CSA','section2');
insert into sec_form values ('CSA','section3');
then i got my function
create or replace function checkIfSectionEnabled (elem XmlType,name_section varchar2)
return XmlType
is v XmlType;
CURSOR myCursor IS select * from sec_form where form_code = 'CSA';
begin
open myCursor;
loop
[code]....
now i'm gonna call the function (elem XmlType,name_section varchar2) and pass here "section1" for example ; and she must return me my xmltype element if she find "section1" finds section1 present;
when i try t compile i got:
Error(18,13): PL/SQL: Statement ignored
Error(18,26): PLS-00225: subprogram or cursor 'MYCURSOR' reference is out of scope
View 3 Replies
View Related
Jan 31, 2012
adding some value in xml type clob column, for ex: xml_tab table has more than 100 rows, below i have created some rows
Quote:create table xml_tab(id number, ex_xml clob);
select * from xml_tab;
1 <CData CI="1111" <Dist> <value desc="25">25 </value> <value desc="30">30 </value> <value desc="35">35 </value> <value desc="40">40 </value> </Dist> <Year IsReadOnly="Y"> <value descr="Old"><1995 </value> <value>1995 </value> <value>1996 </value> <value>1997 </value> <value>1998 </value> <value>1999 </value> <value>2000 </value> <value>2001 </value> <value>2002 </value> <value>2003 </value> <value>2004 </value> <value>2005 </value> <value>2006 </value> <value>2007 </value> <value>2008 </value> <value>2009 </value> <value>2010 </value> </Year> </CData>
[Code]....
View 5 Replies
View Related
Dec 12, 2011
I have a report and require the output in xls/xlsx (Excel) format. Currently following properties under System Parameters in Report Builder 6i have been made
Desname = The path for e.g: \C:sz-serverc$ihelp_workingxls
pt_sample_report.xls
Desformat = Delimited
Destype = File
The rest parameters has default settings I have not changed any of the rest User parameter.The report comes up in excel format but totally in distorted manner. i.e; All the column names of report are displayed first in vertical format followed by actual rows for the report.
The requirement is like this
[Select all] [Show/ hide]
Student ID Student Name Fathers Name Guardian Name
1 sample1 sample2 sample3
2 sample4 sample5 sample6
And it is displaying like this
Student ID Student Name Fathers Name Guardian Name 1 sample1 sample2 sample3
Student ID Student Name Fathers Name Guardian Name 2 sample4 sample5 sample6
View 2 Replies
View Related
Jun 23, 2010
need to add a mask to my field. I'm trying this:TO_CHAR (IMPORTADOR.RUC, .000.000./0000-00 '00 ') IMP_RUC,
View 2 Replies
View Related
Aug 4, 2011
In my project I am getting production dump for performance testing and I need to mask few columns with sensitive information.
How can I mask the data in Oracle?
View 5 Replies
View Related
May 12, 2011
In 10G forms the date format mask in all the items in forms are removed.
As the nls_date_format in database is (dd-mon-rr) the same is expected to come in Oracle 10G forms. Now it is coming as (DD-MON-YYYY) in 10G forms. I tried looking into the nls_session_date format in runtime in forms it is showing as (dd-mon-rr) but in item it displays as(DD-MON-YYYY). How will i display the date format set in the DB or were is the setting done in Oracle 10G forms.
View 2 Replies
View Related
Aug 11, 2012
i never try masking of oracle columns before, and i will want to try masking some columns in oracle database. Just to check if the columns are masked and user using sqlplus or sql developer will not be able to access the columns.
However my application is require to get the values in the back end. Is it possible for my web application to unmask the column values? does my web application requires to call any procedure(create by oracle) in order to unmask the values? I have google and found that oracle can use VPD to mask the columns.Just to check if VPD is a inbuilt procedure by oracle to perform masking? can VPD perform unmasking of values? can it be call by web application?
View 1 Replies
View Related
Jun 16, 2010
I want to add format mask on my field...
my field datatype is char in forms and in database VARCHAR2(30) and I want to add format mask as follows
12345-1234567-1
in format mask of this field property I give it to format mast like as
FMAAAAA"-"AAAAAAA"-"A
when I run the form and when I write and exit from this field then it returns the error.
FRM-40209: Field must be of form FMAAAAA"-"AAAAAAA"-"A
EDIT by VK: Seems your CAPS lock is on or the Shift is stuck.
View 5 Replies
View Related
Oct 8, 2012
I have one NUMBER field with size 15. I have set format mask 999,999,999,999,999 on this field. This is formatting the data I am entering in that field. But the problem is if I enter a value in that field and moves to another field. And if I want to edit the previous field again I have to keep the cursor at the left end of the value.
For example I enter 3,000 in the field. If I want to edit the field again, I have to edit it from left like 1,233,000. I cannot edit it at the end(like 3,000,123).
Even I can click anywhere in that field where I have the format mask. check the screen shot in the attachment. In the red square, you can find a value with space in between.
View 2 Replies
View Related
Feb 11, 2010
how to change Format mask date in Ontime Where
same
TO_CHAR(HIRE_DATE,'DD/MM'YYYY')
View 6 Replies
View Related
Jan 8, 2011
I am trying to load my data from a CSV file to an oracle table using SQL LOADER. What should be the syntax of DATE MASK for the below kind of data.
02MAR1948:00:00:00.000
24SEP1950:00:00:00.000
I am using the following control file
LOAD DATA
INTO TABLE TW_EXTDATA.PATIENT_VISITS_MERGE_39950
REPLACE
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
[code]...
View 2 Replies
View Related
Oct 16, 2012
how to mask data in oracle 11g database release 1
my environment is
Database: 11g release 1
os: AIX 6 (64 bit)
GC:10g release 1
View 12 Replies
View Related
May 16, 2013
Is there no format mask for Percent sign. My customer is creating a Computed field and wants to have the percent sign added to the result. (like a dollar sign). I saw some posts about adding jQuery..??? Is this going to be added to the APEX code some day?
Just like the Money format, FML999G999G999G999G990D00, where did FML come from? it translates to a dollar sign. Isn't there something that could translate to a percent sign
990D000PCT 75.328%
View 3 Replies
View Related
Nov 26, 2012
I am looking for Masking options/techniques to mask few columns. I am aware of the option Oracle Data Masking Pack. But its costly.
View 6 Replies
View Related
Aug 24, 2012
By default number that "starts" with a leading zero with decimals - zero is removed. 0.49 => .49
I'm adding for e.g. the mask "FM990D0999". 0.49 => 0.49
Ok this is fixed.But if I have an integer without decimal. 1 => 1.0
If I change the mask to FM990D9999 1=> 1.
I would like to have a zero leading when it's 0.49 but also no decimal when it's an integer => 1
APEX 4.0 with 11g
View 2 Replies
View Related
Dec 22, 2011
My directory has around 100 sql files to run via sqlplus. For test purpose, I took only 2.I tried following code in the batch file to process those 2 files:
for /f %%a IN ('dir /b *.sql') do ( sqlplus -S sys/ankit@orcl2 as sysdba @%%a )But as the following output shows, only 1 was processed :
C:\Documents and Settings\Administrator\Desktop>cd C:\temp\Copy_WMGIS_DWH\6DBlinks\
C:\temp\Copy_WMGIS_DWH\6DBlinks>for /F %a IN ('dir /b *.sql') do (sqlplus -S sys/ankit@orcl2 as sysdba @%a )
C:\temp\Copy_WMGIS_DWH\6DBlinks>(sqlplus -S sys/ankit@orcl2 as sysdba @PUBLIC.MAXIMO.sql )
Database link dropped.
Database link created.
It stops after this. But when I press ctrl+C , it asks me if to stop (Y/N). I choose 'N', it runs second file as shown below:
Terminate batch job (Y/N)? n
C:\temp\Copy_WMGIS_DWH\6DBlinks>(sqlplus -S sys/ankit@orcl2 as sysdba @PUBLIC.VENTYX.sql )
Database link dropped.
Database link created.
It should run second one itself, not via user interaction.
View 3 Replies
View Related
Jun 8, 2010
I need to find all the tables present in DB or Schema.
View 3 Replies
View Related
Apr 27, 2010
I have two schema SCHEMA_A and SCHEMA_B in the same Database.
I want to execute a procedure UPDATE_CONTACT() which is present in SCHEMA_A from SCHEMA_B.
let me know whether the following SQL is correct:
BEGIN
SCHEM_A.UPDATE_CONTACT();
END;
View 4 Replies
View Related
Nov 28, 2010
I have table called test script for table is given below
create table TEST
(
col1 Number
);
[Code]...
For these values Query is not returning values(3 and 4). So i want generic query to get this result. I am working on it but not able to generate proper query.
View 2 Replies
View Related
Jun 23, 2013
In dept table there is deptno=10,20,30,40
in emp table there is deptno=10,20,30
i want to find the deptno which present in dept table but not
present in emp table.
View 4 Replies
View Related
Mar 17, 2010
I have a form reading record information from a flat file and inserting data into a number of varied tables. At the end of file (last line read) the form issues a commit to make all inserts/updates permanent.
There is a record that disappears in particular and this everytime I re-run the test. I mean than in Debug mode, I find it in the right table everytime I query that table until the moment the commit is issued in the code - which is definitely the opposite of what should happen. I understand that for some reason a rollback happens for that record (others are ok after the commit) but my point is that if for some constraint reasons, the record did not qualify, an error should have popped up right from the Insert that added that record, right? Then How comes I find it in the table up until the moment of the commit ?? ...is the deferrable constraint property a clue for digging further?
View 3 Replies
View Related
Apr 3, 2011
I am writing following query
SELECT DISTINCT a.list_type_code, a.list_type_name
FROM jls_list_type a, jls_list_control b
WHERE b.jalsa_srl = :jalsa_srl
AND b.list_no != a.list_type_code
ORDER BY list_type_code
I just want to display only those records from JLS_LIST_TYPE which is not present in other table JLS_LIST_CONTROL ... for this i wrote above query but it is not working.
View 9 Replies
View Related
Jan 22, 2013
I have created a wallet (11g R2 OEL 5.5) using the OWM.Tried opening the wallet (encryption_wallet_location set in sqlnet.ora). then while creating a table it said the master encryption key is not present. Have created the master key using the following command.
alter system set encryption key identified by "Password";
Here the strange thing i observed is that when we create a wallet using the OWM, it asks for the password and when i open the same wallet the master key is not created and it allows the master key to be generated with the same password that i have created the wallet in the first place with the OWM, with any other passwords it says that the wallet is not open.
After creating the wallet and creating the master key... I have the following questions, and its becoming quite hard to find the solutions as well.
1. Can we have multiple encryption keys... say i want to encrypt a table or column with one key and other with an another key.
2. How many keys can we have for objects in the table? or can we have only one key and many certificates.
3. wallet created, and encrypted tables present, the wallet is not in auto open mode, but somehow the database open after it is shutdown, here no encrypted tablespaces are present.
4. while creating an encrypted tablespace the default storage (encrypt ) has to be added to the add tablespace clause.
View 2 Replies
View Related
May 17, 2011
is there any query to find number of primary keys present in a table.
View 13 Replies
View Related
Aug 20, 2011
how to count how many items present in a particular data block in oracle forms 10g.whether it is a text_item or display_item or list_item etc is there any method to do this.
i have written this when-button pressed but the problem is how to get the next item name in the block
DECLARE
V_COUNT NUMBER:=0;
V_FIRSTITEM VARCHAR2(100):='';
BEGIN
GO_BLOCK('BLOCK_ITEMCOUNT');
V_FIRSTITEM := GET_BLOCK_PROPERTY('BLOCK_ITEMCOUNT', FIRST_ITEM);
LOOP
[code]....
View 3 Replies
View Related
Dec 10, 2010
Why duplicated rows when a PK is present running Sqlldr?
View 1 Replies
View Related
Jan 10, 2013
I am facing the row lock issue in production. I have been trying to resolve the issue but i coud'nt. I traced out by using different queries which sql query is locking which but everything looks good.
And i also checked for connections open and close everything is in good place but unable to resolve the issue. we are running a batch file which runs in every night some of the records are processing and if any one record is failed it is blocking another records.My oracle version is oracle 10.2.0
View 1 Replies
View Related