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

PL/SQL :: Order By Date Column With A Format Mask

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

SQL & PL/SQL :: Values Which Are Not Present In Column

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

SQL & PL/SQL :: Check If Element Present In Defined Column Of Cursor?

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

JDeveloper, Java & XML :: How To Add Some Value In Already Present Node In Clob Column

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

Reports & Discoverer :: How To Present Report In XLS Format With Proper Column Headings

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

SQL & PL/SQL :: Add A Mask To Field

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

SQL & PL/SQL :: How To Mask Data In Oracle Table

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

Forms :: Date Format Mask

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

Any Inbuilt Procedure To Mask Table Columns

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

Forms :: Format Mask To Char Field

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

Forms :: Format Mask Allows To Click Anywhere On The Field

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

Forms :: How To Change Format Mask Date In Ontime Where

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

Server Utilities :: SQL Loader Date Mask For Data?

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

Security :: How To Mask Data In Oracle 11g Database Release 1

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

Application Express :: Format Mask For Percent Sign

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

Security :: Masking Options / Techniques To Mask Few Columns

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

Application Express :: Number Mask Format With Tabular Form (Text Field)

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

How To Run All Scripts Present In A Folder

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

SQL & PL/SQL :: Find All The Tables Present In Database?

Jun 8, 2010

I need to find all the tables present in DB or Schema.

View 3 Replies View Related

SQL & PL/SQL :: Execute A Procedure Present In Different Schema?

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

SQL & PL/SQL :: Query To Find Values Are Not Present In Table

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

SQL & PL/SQL :: Find Deptno Present In Dept Table?

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

Forms :: Record No Longer Present After Commit In Form?

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

SQL & PL/SQL :: Write A Query Which Shows Data Of One Table Not Present In Other?

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

Wallet - While Creating A Table Says Master Encryption Key Is Not Present?

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

SQL & PL/SQL :: Query To Find Number Of Primary Keys Present In A Table?

May 17, 2011

is there any query to find number of primary keys present in a table.

View 13 Replies View Related

Forms :: How To Count Number Of Items Present In A Data Block

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

Server Utilities :: Why Duplicated Rows When A PK Is Present Running Sqlldr

Dec 10, 2010

Why duplicated rows when a PK is present running Sqlldr?

View 1 Replies View Related

Row Locking In Production

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







Copyrights 2005-15 www.BigResource.com, All rights reserved