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
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.
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)
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
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]....
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
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 RelatedIn 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?
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.
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?
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.
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.
how to change Format mask date in Ontime Where
same
TO_CHAR(HIRE_DATE,'DD/MM'YYYY')
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]...
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
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%
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 RelatedBy 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
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.
I need to find all the tables present in DB or Schema.
View 3 Replies View RelatedI 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;
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.
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.
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?
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.
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.
is there any query to find number of primary keys present in a table.
View 13 Replies View Relatedhow 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]....
Why duplicated rows when a PK is present running Sqlldr?
View 1 Replies View RelatedI 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
