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


ADVERTISEMENT

Oracle In Loop Evaluation

Sep 25, 2012

I have an assignment to check the consistency of Oracle database for different conditions.I am having some 200+ columns i a table i need to populate data using prepare statement in .net and check the conditions how it works for each loop values.I am having loop values starting from 1,5,50,100.... 10000k i will increment the loop value as 1,5,50,100.... 10000k and need to get the start time and end time of each loop, i need to check the consistency of the database.

Value for the loop(1,5,50,100.... 10000k) will be from a xml file or text file
For the 200+ Columns i am having 2PK's.

CONDIDTIONS

1)Whether i need to restart for every loop value say if i run loop value as 1 then i need to restart the system (not the services) and run for loop value 5 and restart and so on.
2)Whether i need to restart for every loop value say if i run loop value as 1 then i need to restart the services and run for loop value 5 and restart services and so on.
3)Whether i can continue my loop value starting from 1,5...10000k without restart or any other process continuously running starting from 1...10000k in single go

why i am asking this is whether Oracle will act as differently when i do the above process and will there be any performance improvement or performance variations from the above steps if i followed. Whether there be same value for all the three steps or will there be variation in from time and end time for each conditions.

I need to check for insertion time(time difference b/w from time and then loop condition and then end time) for each loop.

View 18 Replies View Related

PL/SQL :: Possible To Use Forall Instead Of For Loop - End Loop

Nov 19, 2012

CREATE OR REPLACE PROCEDURE IND_MONITOR(P_tab VARCHAR2)
is
type ind_table is table of varchar2(20);
p_ind ind_table;
v_sql varchar2(2000);
begin
select index_name bulk collect into P_Ind from user_indexes where table_name=upper(P_tab);
for i in 1..p_ind.count loop
v_sql :='alter index '||p_ind(i)|| ' monitoring usage'
execute immediate v_sql using p_ind(i);
end loop;
end;

can i use forall instead of 'for loop ..end loop'

View 10 Replies View Related

Performance Tuning :: How Oracle Optimizer Choose Joins (hash / Merge And Nested Loop Join)

Oct 18, 2012

I want to know how the Oracle optimizer choose joins and apply them while executing the query. So that I will insure about optimizer join before writing any query.

View 2 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

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

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

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 :: 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 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

SQL & PL/SQL :: How To Create Identity Function In Oracle

Apr 15, 2010

i have a project that needs to have a identity number in the table.this is the table:

create global temporary table BOOK_INFO
( row_id number(5),
BOOK_TITLE char(20),
BOOK_AUTHOR char(15)
) on commit preserve rows;

i dont know how can i generate row_id, in sybase i can only set the datatype of row_id in IDENTITY and it will automatically generates number and as the transaction ended the row_id will automatically back to 1.

in oracle i tried to create sequence but it didnt back to 1 when the transaction/procedure ended.. Like for example if the procedure ended and the last row_id inserted in the table is 300 the next run of the procedure will start in 301. but i need to start in 1 in every run of the procedure.

Also, i tried to set the variable v_row_id to 0 and it increment by 1 as it insert in the table, but the problem is there are times that in 1 book_title there is more than 1 author. so the v_row_id of the book_title with more than 1 author is all the same.Here is my query:

insert into BOOK_INFO
(select v_row_id, BOOK_TITLE, BOOK_AUTHOR from LIBRARY);

The result is:

ROW_ID BOOK_TITLE BOOK_AUTHOR
1 BOOK_1 AUTHOR_1
1 BOOK_1 AUTHOR_2
1 BOOK_1 AUTHOR_3
1 BOOK_1 AUTHOR_4
2 BOOK_2 AUTHOR_5

View 8 Replies View Related

SQL & PL/SQL :: Output Of Oracle Function Dump

Oct 18, 2010

what the following output of the oracle function "dump" means?

query: select dump(:new.messaging_state) from dual

output:

Typ=2 Len=1: 193

I need this debug output because I want to know which value the program wants to write in the columns messaging_state.

The update fails because of a parent Key violation.

View 2 Replies View Related

Any Built-in Function (HOST) In Oracle

May 30, 2012

saw in one procedure using host function in below manner.

v_status:= host(v_cmd);

But i searched for the host function it is not available.

My question is --Is there any pre defined built-in function "host" in oracle 10g.

View 1 Replies View Related

Oracle Function To Retrieve Records Randomly?

Jun 19, 2013

I need function to pick the record from DB random manner.

For example
, say we have 500 records and input value to the function is 5 means, it should display the records randomly between 1 to 5

If user inputted value is 10 means, it should display one record between 1 to 10.

View 1 Replies View Related

Error While Calling External C Function From Oracle?

Apr 7, 2011

I tried to use external C procedure from the database and i did all required steps as below.
1. create a C program
2. compile and link the C program
3. copy it to the Oracle_home/bin directory
4. configure listener.ora and tnsnames.ora files

listener.ora
=======
callout_listener =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP) (HOST = ip_address)(PORT = 1521)

[code]...

View 4 Replies View Related

Executing Function To Return Username In Oracle

Feb 6, 2006

I have created a Package Body and Package Spec for a function to select a username and a password from a table and return the username.

The code i have created is this:-

CREATE OR REPLACE PACKAGE BODY USER_LOGIN
AS
FUNCTION user_select (USERNAME_IN VARCHAR2,
PASSWORD_IN VARCHAR2)
RETURN VARCHAR2 IS
USERNAME_OUT VARCHAR2(12);
BEGIN
[code]........

The package body and spec compiles successfully but i am having trouble when i execute this function. I am trying to do this :-

VARIABLE RETVAL VARCHAR2(12)

EXEC User_login.user_select('HELLO','HELLO',:RETVAL );

but i am getting the following error

ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'USER_SELECT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

View 5 Replies View Related

How To Get SQL Statement That Caused Exception In Oracle Function

Jul 18, 2011

I am trying to search a way to get the SQL statement that caused an exception withing an oracle function.
I tried:

SELECT sql_text
from v$session ses, v$sql sql
where sql.sql_id = ses.prev_sql_id
and ses.sid = sys_context('userenv','SID') AND ROWNUM = 1;

but this doesn't always return the last statement that the function has executed. if needed i can send the complete script for the function and its tables and stored procedures for testing.

View 3 Replies View Related

Calculate Working Days With Oracle Function

Mar 13, 2009

how do i can make oracle function for the following vb6 function.

This function is used for calculating working days between two date.

****************************************
Public Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer

' Note that this function does not account for holidays.

Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

On Error GoTo Err_Work_Days
BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
[code]......

View 3 Replies View Related

SQL & PL/SQL :: Can Oracle Database Function Return Images

Dec 12, 2012

Can a Oracle Function return Images? I have been getting and able to read lot of solutions but still unconfirmed. As in certain forums it has been mentioned that the difference between procedure and function is that. With procedures you are able to return images but not with function.

View 1 Replies View Related

Forms :: Oracle Apps 11i To Use Text_io Function

Dec 21, 2010

We create a form on 6i then deploye it on our Oracle apps Server with EBS and forms 11i Linux based. I want to create a form using form 6i which read a text file from client side and insert data into table i try text_io function but its not working after deployed on server and also try webutil function but its not working with oracle forms 6i.

View 6 Replies View Related

Forms :: Call Function With Parameter In Oracle

Jul 17, 2012

i have this function

create function xxx_sal (p_number in number)
return number is
v_sal number;
begin
select sum(sal)
into v_sal
from emp
where empno = p_number;
return v_sal;
end;

how can called it in oracle forms

View 8 Replies View Related

SQL & PL/SQL :: Reading Oracle Statements From A Procedure / Function?

Sep 15, 2010

I want to read the oracle procedure/function and find out the no of valid sql statment lines in a procedure:--

Consider the procedure is like :--

PROCEDURE TESTVAR IS
l_local number;
i integer;
j integer;
BEGIN
SELECT real_id_number

[code]....

Now i want to read this procedure in such a way that it should return me below output :-

Seq_No Sql_Statements

1 SELECT real_id_number
2 into l_local
3 FROM master_var v
4 where v.variable_type = i
5 AND V.ID_NUMBER = j ;

how to achieve this output.

View 5 Replies View Related







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