SQL & PL/SQL :: Finding Dependency Of Function In Oracle

Nov 26, 2012

Is it possible to find the dependency of a Function whether it is used somewhere in the package or procedure or some other functions?

Suppose I have function named get_valid_loc , Now my question is how to find out in which place this function has been used.

View 13 Replies


ADVERTISEMENT

SQL & PL/SQL :: Finding Function In Oracle Which Removes All HTML Tags?

Dec 14, 2010

is there any function in oracle which removes all HTML tags . our web pages uses rich text editor, when user enters text into editorand saves it, the database will have text with formatting ie all html tags. this works fine as long we display this text in web pages , but some time we have to provide reports to user just by running sql and ouptut in excel, in this case I want to strip out all html tags within the text .there is any function which can strip all HTML tags?

View 6 Replies View Related

SQL & PL/SQL :: Interchange The Value Which Has Foreign Key Dependency

Mar 29, 2011

I need to interchange the value which has foreign key depednecy,

For ex: I have a table dept

deptno| dname|dseq|
10 | CS | 1
20 | SC | 2

and in emp table

eno | ename | deptno |
1 |Raj | 10 |
2 |Ram | 10 |
3 |Vip | 10 |
4 |Muk | 10 |
5 |Sn | 20 |
6 |Snj | 20 |
7 |Asj | 20 |

there is a forign key define :

ALTER TABLE emp ADD (
CONSTRAINT rop_fk
FOREIGN KEY (deptno)
REFERENCES dept (deptno));

Now I want to change the deptno from 10 to 20 and 20 to 10 in dept table . So the ouput will be like

deptno| dname|dseq|
20 | CS | 1
10 | SC | 2

when i am deleting that row I am getting error "ORA-02292: integrity constraint (ROP_FK) violated - child record found".IF i am trying to update i am getting unique key contractin failure.

(Even I cannot update the rest of the column values in dept table as it is also having foreign key dependency) i need to put a sql script in production so that it will interchange.

View 10 Replies View Related

SQL & PL/SQL :: Role Dependency - Additional Privileges

Aug 6, 2012

I want to find role dependency on another role.

1. If its a basic role (made of priviliges), what data dictionary view I should query?

2. If the role is made of some other role and additional priviliges, what query I should fire to find that?

3. Role is granted to which users?

View 15 Replies View Related

SQL & PL/SQL :: Where Conditions That Have Dependency Don't Get Executed In Order

May 26, 2010

While there's numerous QAs about inserting an image into the DB using the Data Block, how does one remove an image? Obviously there's the "UPDATE ... SET X = EMPTY_BLOB();", however, that kills the Data Block/Form's flow, and it doesn't update.

I've tried a few things:

* :CONTROL.IMG1 := NULL; (bad bind variable)
* :CONTROL.IMG1 := EMPTY_BLOB(); (some other error, probably as above)

View 10 Replies View Related

SQL & PL/SQL :: Mac Address Finding Using Oracle

Sep 26, 2011

we can find the IP address in V$session table. Is it possible to find the mac address of user's computer?

View 13 Replies View Related

Create Dimension - Functional Dependency Between Levels

Sep 28, 2010

the literature equates dimension hierarchies with fuctional dependencies between the levels. I like to tst the strength of this assumption with the implementation of 'CREATE DIMENSION' which allows you to create roll-up hierarchies.

My question to put it simply is this: Given:

CREATE DIMENSION location_dim
LEVEL location IS (location.loc_id)
LEVEL city IS (location.city)
LEVEL state IS (location.state)
HIERARCHY geog_rollup (
location CHILD OF
city CHILD OF
state CHILD
)

Can I insert the following rows into the dimension:
loc_id, city, state
1, Epping, NSW
2, Epping, VIC

Please note that the two Eppings are different cities.

Given the roll-up hierarchy City -> State, will it require that for every city there can be only one state in which case the FD between City and State cannot hold. Or, is it that the roll-up hierarchy defined here has nothing to do with FD.

The second part of the question is if the answer to the above question is that the roll-up is not the same as FD, then is the ATTRIBUTE clause meant to define the n:1 (functional dependency) instead?

View 4 Replies View Related

RAC & Failsafe :: Query On Dependency Between Database Instance And Service?

Jul 8, 2013

suggest on the dependenecy between the database service and the database instance?

srvctl start instance -i db1
will this start service as well for which the above instance is either preferred or available instance?
will it start the listener as well?

srvctl stop instance -i db1
will this stop service as well for which the above instance is either preferred or available instance?
will it stop the listener as well?

srvctl start service -d db -s dbserve
will this start service as well for which the above instance is either preferred or available instance?

srvctl stop servive -d db -s dbserve
will this stop service as well for which the above instance is either preferred or available instance?

srvctl start database -d db
will it start instance, listener and service as well?

srvctl stop database -d db
will it stop instance, listener and service as well?

View 1 Replies View Related

Client Tools :: TOAD Not Finding Oracle 11g?

Jul 2, 2013

I am running TOAD 10.6 on Windows 7 (64-bit). I have two 64-bit Oracle 11g clients installed (all on the same machine). The first is Instant Client (client_1); the second is the Runtime client (client_2). The installation of Instant Client was a mistake, since it doesn't have a Network / Admin folder to drop a tnsnames.ora file into. The Runtime client is my desired client, which has the aforementioned folder that I subsequently dropped the file into.

On starting TOAD, it says that "No valid Oracle Client found. Please note that Toad only supports 32 bit Oracle Client installations". (This is odd, since my colleague is running the same Oracle Runtime client as me and has TOAD 11.6, and yet his is working fine. But, this is aside the point.) When, I click on the ellipsis button next to the "Connecting Using" field, TOAD says that I don't have any Oracle homes installed. I checked my registry key (via regedit) under Computer / HKEY_LOCAL_MACHINE / SOFTWARE / ORACLE. It looks like I have 2 Oracle homes (because of the 2 clients).

Also, I right-clicked on My Computer and brought up Properties, Advanced system settings, Environment Variables (on the Advanced tab). My PATH variable has both of my clients.

How to make my available clients (particularly, my client_2) show up in TOAD?

Here is what I have tried thus far:

-Alter the PATH variable to just have client_2. This didn't do anything.

-Try to uninstall client_1 and client_2 via:

a) There is a Universal Installer under Oracle Installation Products in my Runtime client. I do Deinstall Products, click on any of my Oracle homes, click on Remove, and a Warning window pops up saying to run a command at some location, but the location is truncated since I can't expand the window.

b) In my client_2, there is a deinstall Windows Batch File in the deinstall folder. When I run this, the command window sits there for a little bit, saying that some files were copied and that 1 directory was removed. It then closes on its own. This doesn't seem to do anything.

View 1 Replies View Related

SQL & PL/SQL :: Oracle Table - Finding Transaction Logs?

Dec 9, 2010

How can we see the history of transactions on a table.

For eg, If I have a table called TEST123, I would like to know whatever SQLs fired on that table on today.

View 4 Replies View Related

PL/SQL :: Legacy Data Migration Project - Cyclic Dependency Records

Mar 1, 2013

It's been a while since I worked with SQL Plus . I am using Oracle 11g. We are working on a legacy data migration project. I have a table of records with circular dependency records. i am trying to identify the records. I have the foll. columns- Product,Source,target. I want o identify the records which form a loop. For e.g.

Source Target
A B
B C
C D
D A

Last record forms a loop-I need to identify these records. My query is below-

SELECT DISTINCT SOURCE,TARGET FROM RULESELIB WHERE CONNECT_BY_ISCYCLE=1 CONNECT BY NOCYCLE SOURCE=PRIOR TARGET;

I ran this query on 2 tables- one with 75000 records and the other with 25000 records. It works fine on the table with 75000 records completes within a minute but it does not complete on the other table. I can't seem to be able to figure out the issue with the query or is there something about the data that is causing this query to loop infinitely?

View 11 Replies View Related

Forms :: Finding Out Port Number And Oracle ID For Compilation?

May 21, 2010

Is there any way i could figure out what is the current port number used in an existing setup of oracle forms in a linux server.

Is there any specific oracle id i should use to compile oracle forms on server?

View 5 Replies View Related

Reports & Discoverer :: REP-1241 Circular Column Dependency Originating With Column?

Jul 13, 2010

I receive the error,

REP-1241 Circular column dependency originating with column 'cenvat_opening'

I have the following return statement for a column total_credit;

function total_creditFormula return Number is
begin
Return (return nvl(:cf_cenvat_closing ,0) - nvl(:cf_duty_on_goods_ed,0);
end;

Now i am using this total_creditFormula for the error formula column 'cenvat_opening'

function cenvat_openingFormula return Number is
f_num_opn number(14,2);
begin
SELECT NVL(Amount,0) INTO F_NUM_OPN

[code]...

here the formula column 'total_credit' has the following return statements;

RETURN Nvl(:cenvat_opening,0) + Nvl(:cenvat_credit_manu,0)

so how can i use this formula column? is there any other option to return the value in the same column.

View 6 Replies View Related

PL/SQL :: Lag Function In Oracle

Jul 15, 2012

I am using lag function to display values like below:

order details date starttime
----------------- -------- --------------
main order 1 07/10/12 06:00am
line 1 07/10/12 06:21am
line 2 07/10/12 06:31am
main order 2 07/11/12 07:00am
line 1 07/11/12 07:01am
line 2 07/11/12 07:02am

the data displays correctly when i use lag function except that the line 1 details are never getting displayed ie first line under every order does not get displayed? is using lag function in this case correct?

View 13 Replies View Related

SQL & PL/SQL :: MIN Function In Oracle Sql

Sep 11, 2012

I have written a query which basically retrieves id and created date. IF i put MAX function it is returning id which have max created date. But if i use min function this query is not providing id with min created date,its not returning any rows.

SELECT To_char(OSH.osh_id),
OSH.osh_created
FROM tn_order_status_history osh,
tn_order_status_type ost,
tn_orderline_product op
[code]..........

View 4 Replies View Related

PL/SQL :: Finding The Max Value

Jun 19, 2013

I have the following query where i need to find the max value[code]  with t as 

(   select 'L1' R_nm ,'Data' R_Data , 'Obj' R_Obj , 'Wd' r_prec , '2' val 
from dual   union all   select 'L1' , 'Data', 'Obj' , 'No', '4'
from dualunion all   select 'L2' , 'Data', 'Obj' , 'No', '4' from dual )  
select t.*, max(val) over(partition by r_nm,r_data,r_obj)  rk from t[/code] 

My expected output should be
[code]

r_nm    r_data     r_obj   r_prec   val     rkL1        Data         Obj     Wd       2       1L1        Data         Obj     No       4        2L2        Data         Obj     Yes      1       1

[/code]

View 1 Replies View Related

Oracle 9i To 10g To Char Function?

Feb 11, 2009

Well the company i work for has just recently upgraded from Oracle 9i to 10g. We are having various problems with the migration, and with certain code breaking. My question regards this piece of code;

[code]

to_number(to_char('a_date','YYYY'))

[code]

There are various statements like the one above scattered throughout a query i am trying to fix. When run, the query returns an "invalid number ora-01722" error, which i know is caused by the above code.

if this method of converting from date, to character was discontinued from 9i to 10g?it just seems strange as there are a lot of statements like the one above and they must have worked at some point, but now i cant even get one to work on its own.

View 3 Replies View Related

VPD Function With Loop - Oracle 11g

Jul 5, 2012

I have the following function:

CREATE OR REPLACE FUNCTION get_project_id(
schema_p IN VARCHAR2,
table_p IN VARCHAR2)
RETURN VARCHAR2
IS
projects_pred VARCHAR2 (400);
[code].......

I am trying to get the projects a user has from the works_on table (user_id, project_id). The user_id is retrieved from the context projects_ctx. I am getting the error Function created with compilations errors.

View 6 Replies View Related

SQL & PL/SQL :: Can Use Htp Package In Oracle Function

May 14, 2010

can we use htp package in function?

something like

if event_id = p_event_id THEN
htp.tableRowOpen;
htp.tableData(htf.bold('Evnt'), 'RIGHT', cattributes=>'CLASS=bptext');
htp.tableRowClose;
end if;

View 4 Replies View Related

SQL & PL/SQL :: Oracle Analytical Function

Jul 11, 2011

Here is the test-table creation script:

CREATE TABLE TEST1 (AGG_DATE DATE, COL1 NUMBER(9), COL2 NUMBER(9), COL3 NUMBER(9));
Here is the test-data population script:
insert into TEST1 (AGG_DATE, COL1, COL2, COL3)
values (to_date('01-01-2012', 'dd-mm-yyyy'), 1, 1, 1);
[code]....

The problem is when I wrote an analytical query, it is giving the BEGIN_DATE and END_DATE by taking all the partition values together and so instead of the values above, it is creating an answer as follows:

Wrong Dataset

BEGIN_DATEEND_DATECOL1COL2COL3
1/1/20121/8/2012111
1/1/20121/8/2012111
1/1/20121/8/2012111
1/1/20121/8/2012111
1/4/20121/11/2012222
1/4/20121/11/2012222
[code]....

Only the last row is correct. What can I do to get the right answer as I know am falling short? Here is my current query:

SELECT MIN(AGG_DATE) OVER(PARTITION BY COL1, COL2, COL3) BEGIN_DATE,
MAX(AGG_DATE) OVER(PARTITION BY COL1, COL2, COL3) END_DATE,
COL1,
COL2,
COL3
FROM TEST1;

View 6 Replies View Related

SQL & PL/SQL :: Oracle 11g Function Error

Apr 16, 2013

I'm using the following code for a function which is working fine in Oracle 9i, but throwing error like ORA-06512 (Numeric value error) in Oracle 11g.

CREATE OR REPLACE FUNCTION Decrypt(toconvert VARCHAR2) RETURN VARCHAR2 IS
str_original VARCHAR2(30);
str_new VARCHAR2(30);
sub_str VARCHAR2(1);
j NUMBER;
[code]......

View 17 Replies View Related

SQL & PL/SQL :: Oracle Function Working In DB And Not In Another?

Jun 16, 2010

I have an oracle package that i am using to search for a string in a blob entry. I compiled the package and the package body in one environment, it had no errors, when i execute, i get my results.I went ahead and created the same package and function in another environment and it fails by giving me the below error

ORA-06503: PL/SQL: Function returned without value ORA-06512: at "SYSTEM.IMPACTUS_PCODE", line 158 for sysadm

I have used this on other environments often and have never had an issue.

View 7 Replies View Related

Oracle 6i Refresh Function

May 18, 2009

when i press the save button in my application the record will be saved but the value in my "display item" wont be refreshed. when i minimize the form designer and maximize it again, the "display item" is refreshed.

so i am searching for a "refresh" function

SYNCHRONIZE;
redisplay;

these functions doesn't work.

View 3 Replies View Related

PL/SQL :: Oracle Date Function

Jun 16, 2013

I have a table with date column (16/06/1996 15:03:59) as value in the displayed format. As I have tried with the below query format I could not able to retrieve the

records. select * from <table> where DATE_INSERT=SYSDATE;

Retrieve the rows for the tables ? 

View 2 Replies View Related

SQL & PL/SQL :: Not Finding The Foreign Key?

Dec 4, 2011

I'm not really sure why oracle is not finding my Foreing Key, I'm creating an easy set of table for a company and I'm declaring all Primary keys and foreing keys as necessary and this is my

CREATE TABLE Items
(ItemID NUMBER(5) NOT NULL,
Description VARCHAR2(30),
ListPrice NUMBER(9,2) NOT NULL,
QtyOnHand Number(5),
ReorderPoint NUMBER(3),
ReorderQty NUMBER(3),
OnBackOrder Number(3),
CONSTRAINT ItemsPK

[Code] ............

There's three sets of inserts the first two successfully inserts the data NO problem but when i try to insert the data on SupplierItems comes as:

ERROR at line 1:
ORA-02291: integrity constraint (jcarter.SUPPLIERITEMSFK2) violated - parent key not found

I looked on the forum and it appears this happens when someone is doing a Insert ALL but this is not my case..

View 2 Replies View Related

PL/SQL :: Finding ID Max (salary)

Oct 31, 2012

Select * from one:

ID SALARY
----------------------
10 1000
20 2000
30 3200

Select * from two:

ID SALARY
----------------------
10     1000
20     2000
40     4000
10     3000
20     4000
30     3000

When i try to find ID,MAX(SALARY) from these two tables, i am getting this output:

Select id,max(salary)
from
(select * from one
union
select * from two)
group by id
order by max(salary) desc;

OUTPUT:

ID MAX(SALARY)
--------------------------------------
20     4000
40     4000
30     3200
10     3000

I want OUTPUT to be like this:

ID MAX(SALARY)
--------------------------------------
20     4000
40     4000

View 3 Replies View Related

Using WITH Statement In Oracle Procedure Or Function

May 24, 2011

Is it possible to use WITH statement in an Oracle procedure or function?

I've had problems compiling a procedure with "WITH" statement.

View 1 Replies View Related

SQL & PL/SQL :: Function Very Slow After Oracle Upgrade

Oct 11, 2012

We have a function that is called in various other PL/SQL packages, and performance has always been very good. On 29th Sept we upgraded our db to 10.2.0.5.0 and since then, a package that calls the function has gone from ~4mins, to ~2.5hrs to run.

In PL/SQL Developer, a simple select that calls the function has gone from ~0.5secs to retrieve the first 100 rows, to ~12secs. I ran a profile of the main package, which highlighted the where the bottleneck was (a fetch from an explicit cursor). Running an explain plan on the cursor SQL doesn't really show up anything untoward.

However, I found that if I subtly changed the cursor SQL, (so that it did the same thing, but was written differently), it fixed the performance problems.

where ade_start_date between cpDate-cpDays and cpDate-1
/*and ade_start_date < cpDate
and ade_start_date >= (cpDate-cpDays)*/

From this, we thought that there may have been a bad cached execution plan which the change of code forced a recalculation of. However, about 2 hours later, the changed code ran slowly again. So a further subtle change was made, which fixed the issue again. Until this morning, when it was running slowly again.

This feels like it is CBO/stats related potentially, but is out of my area of knowledge unfortunately. We have our DBA investigating this, but there may be things I can test to narrow down the possibilities in the meantime.

View 5 Replies View Related

SQL & PL/SQL :: Does Oracle 10g Supports Regexp_Like Function

Jan 6, 2009

I am using REGEXP_LIKE function, but it is giving me error like this.

SQL>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

5 rows selected.

SQL>select regexp_like(testcol,'^ab[cd]ef$') from test;
select regexp_like(testcol,'^ab[cd]ef$') from test
*
ERROR at line 1:
ORA-00904: "REGEXP_LIKE": invalid identifier

But in Oracle documentation it is given under heading of 10g. Does Oracle 10g supports this function?

View 11 Replies View Related

SQL & PL/SQL :: Left Function Equivalent In Oracle 9i

Dec 22, 2006

Is there is an equivalent to the 'LEFT' Function of SQLServer in Oracle9i?

View 10 Replies View Related







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