I have this 3rd party tool, which are running SQL queries. I need to see what queries the tool is running and capture them. I enabled tracing but that's not working, as the tool doesn't establish connection, it connects when it has to run the query and then gets disconnected.
View 2 Replies
There is a requirement in my database that I want to restrict the user from directly running queries on database from third party tools such as pl/sql developer and toad.
There is a utility in SQL product_user_profile through which this can be done but it is only restricted if you run the query through sql plus. If I want to restrict and (give suppose select,insert) to a user for directly running queries through PL/SQL.
I'm in a bit of a pickle with some SQL syntax, and while my Oracle-fu is weak, an associate with SQL skills is also stuck. I am developing a database for my department that is backed by Oracle Enterprise 11g and the front end is ChemAxon (basically, chemistry database software). The short of it is that I have one large table with every compound from different groups. Each row has a flag (column PI) on it that indicates which group it belongs to (let us say 'Smith' and 'Jones' for example). The software can apply row-level filtering which will basically only show the rows that a particular username are allowed to see.
I have a FLAG_TABLE table, which contains two columns: USERNAME and FLAG. An example set up is this:
USERNAME FLAG
------------- ----------
smith_minion Smith
jones_minion Jones
jones_minion Smith
The software automatically applies a SQL filter that begins with:
SELECT DISTINCT CHEMAXON.STRUCTURES."ID" FROM CHEMAXON.STRUCTURES WHERE
I can set up filtering to work dandy, such that when smith_minion logs in, he can only see rows with the Smith flag (or jones_minion can see both Jones and Smith) by using the filter:
STRUCTURES.PI IS NULL or STRUCTURES.PI IN(SELECT ALL FLAG from FLAG_TABLE where USERNAME = '__IJC_USERNAME__')
('__IJC_USERNAME__' is how the third party software passes the logged in username into SQL)
But we have a new problem: there also exists a master user (chemaxon) who needs to see every row no matter what the flag. The row filtering is applied no matter who logs on, so we need to set up the SQL filter to basically say "If chemaxon, then select all rows, otherwise, select rows based on the username". This is proving a problem as the select statement must be prefaced with SELECT DISTINCT CHEMAXON.STRUCTURES."ID" FROM CHEMAXON.STRUCTURES WHERE.
I've tried using DECODE in a few capacities, but I am always thwarted. My last attempt was:
STRUCTURES.PI IS NULL or STRUCTURES.PI IN (DECODE('__IJC_USERNAME__','chemaxon',(!='something'),(SELECT ALL FLAG from FLAG_TABLE where USERNAME = __IJC_USERNAME__));
But this throws a ORA-00936 missing expression error, with a * under the != portion (I test it by replacing '__IJC_USERNAME__' with 'CHEMAXON').
I had third party reporting system,in which i could not avoid time selection in the screen. So if a user selets date and time or date alone .i should get only in date foarmat i.e(DD-MM-YYYY)
i had tried from third party tool liket0_char (P_date,'DD-MM-YYYY').But i get error ORA-01841: (full) year must be between -4713 and +9999, and not be 0
our system has always been running on mysql database and recently we have switched to oracle. As the current system is coded using mysql query syntax, when i run this program using oracle database, i got a error. The language that I'm using is JSP.
this is the error message:
The following query could not run on oracle. To convert these mysql queries to oracle compatible queries.
SELECT productID,productName FROM products order by productName;
select newsID,newsDate,newsHeadLine1 from news order by newsDate Desc limit 3
SELECT fuji_products.productID, productName_Display FROM products,products_availability where products_availability.productID=products.productID and (product_status='enabled' or product_status='all') AND category='12'
SELECT catID, catSub1 from category where catSub = '"+ prodCat +"' AND catSub1 is not null group by catSub1 order by catSub1
How to Check for party names contains standalone four length keywords of consecutive letters(both upper and lower case)
For.e.g. "VMWARE ABCD"
"Jack XWyz jon"
output shoud be: abcd
ABCD,
XWYZ,
like this i want to remove the consecutive characters.
Upgrading one of the 9i database to 11g that supports a 3rd party software - ***Vendor provided an over-simplified documentations*** and recommends moving from 9i to 10g before going to 11g. A few changes from 9i to 10g.
1) db_block_size
2) character sets
etc.
Anyway, created the database DBUPGTEST on 10.2.0.1 (ultimately moving to 11gR2, so no point patching to 10.2.0.5, is there?) with all the parameter changes. At this point, these are the 2 db in play:
Current production db: Oracle 9i - PROD dbname => 2048K db block size
Current migrating db to: Oracle10g - DBUPGTEST dbname => 8192k db block size
Steps
According to vendor notes / documentation,
1) create db
2) exp full from 9i
3) imp full to 10g
Problems
1) import ended with completed unsuccessful.
2) user accounts are imported (because their default tablespace is USERS - which had already been created during DB creation); but, user accounts (schema accounts) with a different default tablespace are not imported.
Looking at the imp.log - seems like it's complaining about the db_block_size during tablespace creation - which explains why the schema accounts are not imported; because the tablespace was not created.
My questions
1) How do I import to 10g? Can I create all the tablespace in 10g first? Then import? Will it crap out because it already exists? Or will it import the objects in the schema?
2) How do I refresh data from PROD? Remember this is 9i and most of the expdp functionalities are not available. And I cannot re-exp and re-imp because there are steps (sql to run) after moving to 10g to fix some software upgrade table mappings. If I re-exp from 9i and re-imp to 10g, won't I have to re-run all those steps before the apps will run?
I'm a Data Manager recently hired at a small company that uses InForm as our data management tool. We are currently not set up with any type of SQL tool to query the database. Consequently, I feel like I'm trying to clean data with one hand tied behind my back. I have two questions:
1) Is there any sort of built in feature in InForm for using SQL statement to query the data? (I'm not really a fan of the built in Cognos feature.)
2) Any tool that is easy to install/configure?
I'm facing some resistance here, so I'm looking for a really easy/cheap solution. I've used SQL Developer before, and I know that's free, but is it easy to set-up?
i want hide the content shown in the picture
View 6 Replies View RelatedCan i use Text tool in programming...
i just want to change color at run time of text tool....
i attached a picture must see...
Any tool or way to view the schema and relations between tables of an Oracle database?
View 1 Replies View RelatedI want to debug the procedure using PLSQL Developer tool?How to use the debug option in that tool?.
View 1 Replies View RelatedHow to find the oracle form development tool in oracle 9i?
View 1 Replies View RelatedAre there any GUI based tools that can auto generate a CTL file based off a CSV input? I'd love something like this since I have quite a few SQL*LDR projects coming up!
View 2 Replies View RelatedI have created searching tool using oracle forms 6i. while searching the cursor move from text field (A) to other block query the data according to the field (A) and come back again to field (A). Everything is working fine but the problem is, when the cursor move back to the field (A) the existing text is highlighted and when user right something in it. It's overwriting the existing text.
I want when the cursor moving bank to the field (A) the text should not be highlighted and when the user writes something that will be added to the existing text.
Is there a free software or tool for oracle reports version control? I need to maintain versions for Oracle reports and PL/SQL codes but not sure how to proceed.
View 2 Replies View RelatedIf it possible to display Tool Tip of a Column Require to Display the Data contain in that Column.
View 11 Replies View Relatedwe have a two different databases at different locations and on different servers, like one in our company with SID='A' and remote database with SID='B', We have recently implemented new module in database 'A' by creating lots of tables, functions, indexes, sequences, synonyms etc and now we need to install this on 'B' but the problem is we have not documented which tables we created, first we need to create a DB link between these two and then we need a tool to compare what are all the tables that we need to create in database 'B' , is there a tool for doing all this.
View 7 Replies View Related1)How to monitor alert log file (using any tool or manual)?
2) What are the implications of a database with multiple data block sizes?
I am trying to use the datapump tool to migrate a 10g db to 11g. Everything works fine except for the "nameless" check constraints.
View 7 Replies View RelatedI am returning a refcursor as OUT parameter in my stored procedure. I would like to capture a no data found for the refcursor. Is there a way I can raise the exception without compromising the performance?
I have tried the below options that are not working.
1. If I run a SELECT query to check for records and then OPEN the refcursor for that SELECT, then it takes a performance hit as I am reading the table twice.
2. I can FETCH a refcursor into a table type and check the count in the table to raise exception.But once I fetch from a refcuror, the data is gone. So, this option does not work either.
OS : Windows 2003 DB : 10.2.0.4 I am doing capture and replay first time.I want to take 2 captures at a time. 1st capture for 2 schemas and 2nd capture for other schemas.Is it possible?I have searched on internet but didn't get any clue about it..
View 4 Replies View RelatedThe sqlcache is getting over-written.I would like to capture information from AWR snapshots and feed those (as a workload) into the DBMS_ADVISOR.I can't see where it's possible (other than manually creating my workload from AWR information)
View 3 Replies View RelatedI am running 11.2. Within a DB trigger, I need to capture the IP address of the client making the change. I see there is a SYS_CONTEXT('USERENV','IP_ADDRESS') built in. Is this the correct way to capture the IP of the client making the change?
View 2 Replies View RelatedI was reading about Oracle SPM feature, but i have questions in mind for automatic feature..
* if we have set both capture and use parameter true. Oracle will automatically capture Plans, but will it automatically evolve new plans to accepted or it will wait for evolve it manually before using new plan.
* If it Automatically evolve, when and how will it?
* If it Automatically, will change old plan's flag accepted to No? or add one more accepted plan
I need the effective dates (start and end) of marital status changes in sequential order, without duplicate rows over the same time frame. (Per_all_people_f table only). For example below, I only need the items that are in bold. I am very new to pl/sql and cannot figure out how to do this.
When I do this in sql with Min date and max date; the 1st and 2nd blocks are correct, the 3rd block has wrong end date and 4th block is entirely missing as the 'M' is already counted for in block 1 even though it occurred after other status changes.
Example of the rows and what I need in BOLD below:
So no gaps in time and it captures the effective date range for that particular marital status; I need to get:
1st block 'S' 10/23/2000 - 4/12/2004
2nd block 'M' 4/13/2004 - 10/1/2006
3rd block 'D' 10/2/2006 - 5/23/2007
4th block 'M' 5/24/2007 - 12/31/4712
Actual data in table I do get on a query with no restrictions:
490 *10/23/2000* *4/12/2004* 0 US S F
490 *4/13/2004* *10/1/2006* 0 US M F
490 *10/2/2006* 2/12/2007 0 US D F
490 2/13/2007 *5/23/2007* 0 US D F
490 *5/24/2007* 10/7/2010 0 US M F
490 10/8/2010 11/15/2012 0 US M F
490 11/16/2012 *12/31/4712* 0 US M F
we have 1 server oracle and many clients. when client sends sql statement to server.How to Capture sql statement to analysis before it sent to server and execution.
View 18 Replies View RelatedWhat tool can be used to monitor Oracle host on Windows environment if the database goes down? I know about "Nagios" but like to know is there any other tool available?
View 6 Replies View Relatedi want to hide browsers menu bar and tool bar at form run time and i want to show only form menu in the browser
View 2 Replies View RelatedHow to give ROLE access to users in PL/SQL developer Tool..??
View 3 Replies View Related
