Function To Return Last Friday Of Month

Sep 26, 2010

I was just wondering that do we have any function available in oracle which returns the last Friday of the month.

In our company we close our monthly books on last Friday of the month and there are few activities that we have to do on the following Monday as a part of month-end activity. Now the following Monday can fall on the same month or at times it falls on the following month.

I have to schedule a report to be sent to a user on the following Monday after the month-end. I need to schedule it using the cron job.
We are using Oracle 9i on Linux platform.

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: Return Name Of Month On Passing Number

Feb 17, 2010

I need a query to whome i will pass month in number and that should return name of month. If i input 1 it should return Jan,2,Feb but don't use decode etc.

Is there any query with Julian format or any other date function?

I dont want to use ,Case and Decode ,level etc.

View 2 Replies View Related

PL/SQL :: Function To Find Nth Month Name (V 10g)?

Sep 17, 2013

find the last nth month name [Single month].

View 5 Replies View Related

Function Return Parameters

Dec 5, 2006

The function definition in PL/SQL has IN OUT parameter as well as return statement. Using both we can return the values. Basic definition of a function is function can return only one value at a time.

The question is, Can we return a number thru return statement and a char value thru INOUT parameter. Is it possible to return two different values using these?

View 1 Replies View Related

SQL & PL/SQL :: How To Return More Data From Function

Nov 14, 2011

Following is the scenario:

CREATE OR REPLACE TYPE OBJ IS OBJECT
(
TEST_ID NUMBER(9),
TEST_DESC VARCHAR(30)
)
/

[Code]..

I WANT USED VALUE TEST_ID AND TEST_DESC THE EXISTING IN FUNCTION FN_MY_DATA WITH A VARIABLES :

DECLARE
X NUMBER(9);
Y VARCHAR(30);
BEGIN
X := -- VALUE TEST_ID EXISTING IN FN_MY_DATA;
Y := -- VALUE TEST_DESC EXISTING IN FN_MY_DATA;
END;

View 3 Replies View Related

SQL & PL/SQL :: Function Return Two Values?

Aug 2, 2010

whether a function can return two values?

View 4 Replies View Related

SQL & PL/SQL :: Function That Accepts 2 Dates - Not A Valid Month Error

Apr 15, 2011

I have function that accepts 2 dates namely sDate and eDate i.e start and end dates. See the declaration part of the function:

startDate eab.eod_date%type;
endDate eab.eod_date%type;

begin
startDate := to_date(sDate,'DD-MON-YY');
endDate := to_date(eDate,'DD-MON-YY');
end

When I called the function it gives me the following error: ORA-01843: not a valid month..And I tried to simulate the process, the below code also show the same error:

select TO_DATE(to_date('13-DEC-09', 'dd-MON-yy', 'nls_date_language=english'),'DD-moN-YY') from dual;

how I can check that error. I need to pass dates to my function.

View 8 Replies View Related

SQL & PL/SQL :: How To Return Multiple Values From A Function

Aug 28, 2010

I want multiple values from a function. I want to use this function in a SQL query. Here i'm giving my try.

SQL> CREATE TABLE TEMP
2 (
3 ID NUMBER(1),
4 SAMPTYPE VARCHAR2(20 BYTE),
5 SALARY NUMBER(10)
6 )
7 /

Table created.

SQL> INSERT INTO TEMP VALUES(1,'ABC',10000);

1 row created.

SQL> INSERT INTO TEMP VALUES(2,'PQR',20000);

1 row created.

SQL> INSERT INTO TEMP VALUES(3,'JPD',5000);

1 row created.

SQL> COMMIT;

Commit complete.
[code]...

Here i get result as ABC*10000, but i want two separate values as ABC,10000. how can i do this via function.

View 6 Replies View Related

SQL & PL/SQL :: Create A Function That Return XmlType

Apr 29, 2010

i want to create a function that build a return xml (XmlType):

create or replace
function plainLanguageSummary(nip varchar2,id number,code_language varchar2) return XmlType
as

[Code].....

but in the compilation i got the following error :

Error(10,62): PLS-00103: Encountered the symbol "SELECT" when expecting one of the following: ( - + case mod new not null others <identificateur> <identificateur entre guillemets> <variable bind> avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date <un littéral de chaîne avec spécification de jeu de caractères> <un nombre> <une chaîne SQL entre apostrophes> pipe <constante de chaîne éventuellement entre guillemets avec indication du jeu de

he seems to not like the first "select" he encounter!.

View 9 Replies View Related

SQL & PL/SQL :: Insert Function / Procedure Return New ID

Jun 22, 2010

i'm trying to make a sp/function for inserting a record and return the new index.The previous code used regular inserts and needed an additional round-trip to get the id before inserting,, since this is part of a import routine performance is an issue.

CREATE OR REPLACE PROCEDURE SaveAisHeader (
P_Id Out Number,
P_ImportedOn IN Date,
P_Aisimporttypeid In Number,
P_Description In Varchar2,
P_Importedby In Varchar2
[code].....

View 12 Replies View Related

SQL & PL/SQL :: Function Return Multiple Rows

Jun 23, 2006

can i have a pl/sql function that can return multiple rows

may be the syntax will be like

create or replace function multiple() returns ...
begin
select candidateid from tbl_candidateinfo;

..code to return the result of above statement to calling program..

end;

and functions will be called as

select candidateid from .. where candidateid in( select multiple());

View 13 Replies View Related

PL/SQL :: Function That Return Multiple Rows

Jun 6, 2013

I need a function that should return output of this query

SELECT b.branding_code, c.name_desc
FROM
development.brandings b, godot.company c
WHERE b.company_id = c.company_id;

This above function return 30 rows and I am not giving any input

Function using cursor,pipeline

View 5 Replies View Related

PL/SQL :: Executing A Function With RETURN X%ROWTYPE Is Used

Jul 6, 2012

This error is returned when executing a Function with the RETURN X%ROWTYPE is used.Here is the code.

CREATE OR REPLACE function FE_GET_addr (
p_PIDM IN addr.addr_PIDM%TYPE,
p_atyp1 IN VARCHAR2,
p_atyp2 IN VARCHAR2,
p_atyp3 IN VARCHAR2,
p_atyp4 IN VARCHAR2
[code]....

View 5 Replies View Related

SQL & PL/SQL :: Use A Procedure Or Function If Have To Return Only 1 Datatype

Feb 1, 2012

how to decide whether to use a procedure or function if i have to return only 1 datatype.

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

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

SQL & PL/SQL :: Return Ref Cursor To Temporary Table From Function

Feb 17, 2011

I have strange problem when i try to return a ref cursor holding data from a select on a oracle global temporary table. If i iterate through the cursor , i can see the values but the function as such returns nothing through the ref cursor. I tried the temporary table as both delete on commit and preserve on commit

create or replace
PACKAGE BODY BILL AS

FUNCTION FILTERI RETURN BILL.refcursor IS
testcursor BILL.refcursor;

ttstatus INT;
iSuccess INT;
returns INT;
TruncatedSQL1 VARCHAR2(32767);
BEGIN
[code].........

View 12 Replies View Related

PL/SQL :: Create Function To Return Table Type

Jul 25, 2012

I am trying to create a function which would return a nested table with 3 columns of a table as a type.

my query is like

select col1,col2,col3 from table_1;

View 4 Replies View Related

SQL & PL/SQL :: Data Type To Return Decimal Values In Function

Feb 26, 2013

I have the following database function.

GetRegionDetails(id in varchar2, o_lat out number, o_lon out number);

The problem is, the output values are returning as whole numbers ie. 38.108766567 is being returned as 38 and -78.16423574566 is returned as 78

what data type I should use so that my output is returns all the decimal values?

View 5 Replies View Related

SQL & PL/SQL :: Call Function With Row Type Return In Oracle Select Statement

Jul 3, 2012

How to call a function with a row type return in an Oracle select statement.

For e.g. :

If I had this function with a rowtype return:
------------------------------
create function abc
return xyz%rowtype
is
rec xyz%rowtype;
begin
select * into rec from xyz where col1 = n;
return rec;
end;
--------------------------------
How could I use this in a select clause, as there is a multi column return by the function ?

View 5 Replies View Related

Reports & Discoverer :: Clob In 6i - Error Function Return Must Char

Jun 23, 2011

I having issue when i try to use CLOB as varchar2 is not enough in my case. I'm developing function column in oracle report. I'm using developer 6i. I get error function return must char?

How I can use CLOB in oracle report?

function CF_RnoFormula return Char is
--v_release_num CLOB;
v_release_num varchar2(32767);
begin
FOR rec IN
[code]........

View 3 Replies View Related

SQL & PL/SQL :: Pipe Line Function Taking Time To Return Table Record

Mar 15, 2011

I want to use a function in join clause. so i go for pipelined function(using for loop to get record & 1 more loop to fetch in table type variable). i achieved what i required. but problem is it takes much time to fetch data. is there any other approach which returns table records without pipelined function.

View 2 Replies View Related

SQL & PL/SQL :: How Many Days Thursday And Friday Between Two Dates

Nov 13, 2011

I want function return me how many days Thursday and Friday between two dates.

View 1 Replies View Related

PL/SQL :: Return Past Timestamp Values On Base Of Input Hour To User Function

Nov 22, 2012

resolve issue while modified the user function code for returns the values as timestamps

---function code

create or replace
function fun_test_timestamp(P_HOUR varchar2) return varchar2
is
sql_stmt varchar2(1000);
begin

[Code].....

Input:-
select fun_test_timestamp('5') from dual;

Output:-

SELECT CURRENT_TIMESTAMP - INTERVAL '5' HOUR FROM DUAL;

Modified Fun Code:

create or replace
function fun_test_timestamp(P_HOUR varchar2) return timestamp
is
sql_stmt varchar2(1000);

[Code]...

Input:-
select fun_test_timestamp('5') from dual;

Output:-

ORA-00911: invalid character
ORA-06512: at "NETVERTEXTRUNK.FUN_TEST_TIMESTAMP", line 8
00911. 00000 - "invalid character"

*Cause:    identifiers may not start with any ASCII character other than letters and numbers. $#_ are also allowed after the first character. Identifiers enclosed by doublequotes may contain
any character other than a doublequote. Alternative quotes (q'#...#') cannot use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL Language Reference Manual.

*Action: 

View 6 Replies View Related

SQL & PL/SQL :: Show Week Days Without Friday And Saturday

Oct 25, 2011

how can create function or sql statement to show week of days without Friday and Saturday

View 13 Replies View Related

Performance Tuning :: Materialized View Which Should Refresh Every Tuesday And Friday At 5am

Mar 20, 2012

I need to create a Materialized View, which should refresh every Tuesday and Friday at 5am.

CREATE MATERIALIZED VIEW DUAL_MV
TABLESPACE USERS_SMALL
REFRESH NEXT NEXT_DAY(TRUNC(SYSDATE), 'TUESDAY') + 05/24
AS SELECT * FROM DUAL;

I can do it for single day like above, but how to refresh on multiple days, using create materialized view syntax .

View 2 Replies View Related

SQL & PL/SQL :: How To List Data Dynamically Month Wise Picking Month From The Same Table

Jun 4, 2013

I have a requirement to list the data month wise dynamically where month data is also in the same table, hopefully the below posts should bring more clarity to my requirements.

1. Table creation:
Create table T1 (account_no varchar2(15), area_code varchar2(2), bill_month date, consumption number);

2. List table content:
select * from T1;

account_no area_code bill_month consumption

Q00001Q31-Jan-12125
Q00002Q31-Jan-1265
Q00003Q28-Feb-12219
Q00004Q28-Feb-12805
Q00005Q28-Feb-1254
Q00001Q31-Mar-12234
Q00002Q31-Mar-12454
Q00003Q31-Mar-12232
Q00004Q30-Apr-1221
Q00005Q30-Apr-12218
Q00001Q30-Apr-1254
Q00002Q31-May-1219
Q00003Q31-May-1287
Q00004Q30-Jun-12187
Q00005Q30-Jun-1278
so on......so on......so on......so on......

3. Expected output:
account_no area_code Jan-12 Feb-12 Mar-12 Apr-12 May-12Jun-12Jul-12Aug-12Sep-12Oct-12Nov-12Dec-12

Q00001 Q 125 548 2345487423154821518738721512
Q00002 Q 65 127 45487819357831585683152878
Q00003 Q 545 219 2328738735188745897313
Q00004 Q 78 805 1221218187885718387389787138
Q00005 Q 541 54 2621878778386538698182

With the conventional query I hope this is impossible,

View 2 Replies View Related

SQL & PL/SQL :: Dynamic Function - Create Physical Table And Return Table Name In Out Variable

Aug 30, 2011

I am trying to execute dynamic SQL in Stored Function and I don't know how to do this.

Explanation:

In the function I am calling pr_createtab is procedure which will create a physical table and return the table name in the out variable v_tbl_nm.

I need to query on this dynamic table and return the result as return result. But i am not able to do it.

Here T_web_loylty_report_table is a type.

CREATE OR REPLACE function CDW_DSS.f_ReturnTable(i_mrkt_id in number, i_cmpgn_year in number)
return T_web_loylty_report_table is
v_tbl_nm varchar2(50);
i_cntry_cd varchar2(20);
v_sql_str varchar2(32567);
[code]......

View 2 Replies View Related

PL/SQL :: How To Call A Function Having OBJECT Type As Return Type

Mar 26, 2013

I've the following function returning OBJECT type. how to call this function

CREATE OR REPLACE TYPE GET_EMP_OBJ is object
   ( emp_name varchar2(50) ,
     mgr_id   number,
     dept_id  number
   );

[Code]...

The above function got created successfully. And i'm confused how to call this functions. I tried like below but didn't work

DECLARE
  t_emp_info_1  GET_EMP_OBJ ;
BEGIN
   t_emp_info_1 := get_emp(7566) ;
   for i in 1..t_emp_info_1.COUNT
      LOOP
         DBMS_OUTPUT.put_line ('Values are'||i.emp_name ) ;
     END LOOP;
END;  

View 7 Replies View Related

Application Express :: 4.2 Item - Validation Return Boolean And Return Error Text Are Switched

Oct 17, 2012

In Apex 4.2, the item validation of "Function Returning Boolean" and "Function Returning Error Text"; They seam to be backwards.

Is there a simple statement that can be used to fix this in the apex dictionary?

View 1 Replies View Related







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