PL/SQL :: How To Pass Year And Month As Parameter Into A Block

Apr 26, 2013

I am populating a time dimension table. One job is to assign business day with sequence number on monthly basis and by year. Business day does not include weekends and all federal holidaies.

I created a PL/SQL block to handle this job. It works. However, I haven't figure out how to pass the month number and year number into PL/SQL block automatically. Now I have to manually enter year and month number (on Toad) to pass to block to make it work. If I create it as stored procedure and write another block to call it, I can pass year and month number into parameter there like this:

exec my_sp_name ('1', '2013');

But all of these are not good enough. I want to use code to automatically pass yesr and month number into PL/SQL block. So that I can make it better. . here is the PL/SQL block I created.

declare i number := 1;
begin
for r in (select * from time_dim where calendar_month_number = &month and calendar_year= &year and business_day_flag = 'B' order by date_sk_id)
loop

[Code]....

View 11 Replies


ADVERTISEMENT

Interval Year To Month Parameter

Mar 20, 2008

I have a procedure that has a 'INTERVAL YEAR TO MONTH' parameter. What value do I pass to this parameter?

View 1 Replies View Related

SQL & PL/SQL :: Max And Min Values Of Month And Year

Jun 25, 2010

can we take the maximum and minimum value of month and year

View 7 Replies View Related

SQL & PL/SQL :: Year And Month Query

Mar 20, 2011

I have two Queries.

Query 1:
SELECT DISTINCT YR FROM(
SELECT TO_CHAR( ADD_MONTHS (TRUNC (TO_DATE('01/01/2007' ,'DD/MM/YYYY'), 'YYYY'), 1*LEVEL -1) , 'YYYY')
YR FROM Dual
CONNECT BY LEVEL <= MONTHS_BETWEEN(TO_DATE(:to_dt ,'DD/MM/YYYY'), TO_DATE('01/01/2007' ,'DD/MM/YYYY')) + 1
ORDER BY YR
)

Gives the Output as
YR
****
2007
2008
2009
2010
2011

Query 2
*******
SELECT DISTINCT MONTH FROM(
SELECT TO_CHAR( ADD_MONTHS (TRUNC (TO_DATE('01/01/2007' ,'DD/MM/YYYY'), 'MM'), 1*LEVEL -1) , 'MM')
MONTH FROM Dual
CONNECT BY LEVEL <= MONTHS_BETWEEN(TO_DATE(:to_dt ,'DD/MM/YYYY'), TO_DATE('01/01/2007' ,'DD/MM/YYYY')) + 1
ORDER BY MONTH
)
Gives the Output as

MONTH
***
01
02
03
04
05
06
07
08
09
10
11
12

I want to combine these two. I need the output as

2007-01
2007-02
2007-03
....
...

View 8 Replies View Related

SQL & PL/SQL :: Compare The Sales With The Same Month Last Year

Jul 6, 2012

I wanted to compare the sales with the same month last year. But the issue is only one value on each customers.

File Attached Table as JPG file..
-----------------------------------------------------------------------
SELECT WORDERS.BP_ORDER,
(SELECT SUM (WINVITEMS.ITEM_VAL)
FROM WINVITEMS
INNER JOIN WORDERS ON WORDERS.ORD_NO = WINVITEMS.ORD_NO
WHERE WORDERS.DATE_ORDER >= TRUNC(ADD_MONTHS(SYSDATE, -3),'MM')

[Code]....

View 7 Replies View Related

SQL & PL/SQL :: How To Sort Date By Month (JAN) And Year (2009)

Jun 4, 2010

I've done this once before, but can't seem to find the sql.

How can I sort by month and year on a column called ex: TEST_dATE

JAN 2007
FEB 2007
APR 2008
SEP 2009
OCT 2009
FEB 2010
JUN 2010

View 15 Replies View Related

SQL & PL/SQL :: Create Procedure Have Month And Year As Argument

Aug 5, 2010

Need to create a procedure that will retrun me monthly count of records

create table sample ( S_name varchar2(20), S_Date Date);

insert into sample values('1','01-JAN-2005');
insert into sample values('1','1-JAN-2006');
insert into sample values('2','2-JAN-2007');
insert into sample values('3','4-JAN-2005');
insert into sample values('4','11-JAN-2004');
insert into sample values('3','2-JAN-2005');
insert into sample values('2','12-JAN-2006');
insert into sample values('1','21-JAN-2005');
insert into sample values('2','11-JAN-2005');
insert into sample values('7','01-JAN-2005');
[code]....

View 5 Replies View Related

SQL & PL/SQL :: Find Out Date Difference In Day-month-year?

Jul 8, 2010

i want to find out difference between two dates in day-month-year. How can i do it?

For example how many days,months are years are between '01-jul-1979' and '08-jul-2010'

View 4 Replies View Related

PL/SQL :: Insert Only Month And Year Instead Of Full Date

Oct 8, 2012

select b.penjara_id, p.penj_lokasi, a.no_daftar, b.episod, b.nama1,to_char(a.trkh_mula_prl,'dd/mm/yyyy') as trkh_mula_prl, to_char(bulan_proses,'mm/yyyy') as bulan_proses,
        b.epd, b.lpd
from prl_daftar_proses a, senarai_pesalah b, penjara p

where a.no_daftar=b.no_daftar
and a.episod=b.episod
and b.penjara_id = p.penjara_id
and a.setuju_jplp is null
and a.bulan_proses between to_date(:FROM,'dd/mm/yyyy') and to_date(:TO,'dd/mm/yyyy')
order by b.penjara_id, a.bulan_proses,a.no_daftarHi,

how i can insert only month and year from the value that have full date in the database?

for example,the date is 09/18/2012, but i just want to insert 09/2012 as parameter. If i want to insert only one parameter, i can do that..But i have problem when I want to insert two parameters..

View 16 Replies View Related

SQL & PL/SQL :: Calculations - Get Same Month From Previous Year Of Selected Date

Sep 20, 2011

i have a requirement to create a query which gets the same month from the previous year of the selected date .

Example : i select SEP-2011 ( as sysdate )

it will return the SEP-2010 as well .

here is the code which works fine with sysdate, dual :

SELECT
TO_CHAR(ADD_MONTHS(SYSDATE,-12),'MON-YYYY') TMLY, TO_CHAR(SYSDATE,'MON-YYYY') TMTY FROM DUAL;

in my schema table Ive got a filed named PERIOD_NAME (varchar2) , which holds date values in ( Mon-YYYY ) format (e.g Sep-2011)

am unable to apply the above code on it , i guess its the data type pf the field .

View 5 Replies View Related

SQL & PL/SQL :: Checking If Month And Year Is Greater Or Equal To Current One?

Mar 7, 2013

This is the description for the procedure:

ADD_REWARD_sp. Given the identifier of a project, add a new reward for the project. The procedure should return a unique identifier for the reward. The month and year indicated should be greater than or equal to the current month, or an error message should be generated. The pledge amount should be greater than zero. The number of backers, if not NULL, should be greater than 1. If the project is not found, generate an error message.

This is the procedure head:

create or replace
PROCEDURE ADD_REWARD_sp(
p_proj_id IN NUMBER,
p_pledgeAmt IN NUMBER,
p_rewardDesc IN VARCHAR2,

[code]...

Basically, what I am struggling with is how to check if th month and year is greater than or equal to current month. I suppose it would be easy with just checking SYSDATE, but in this case I need to use both month and year.

View 3 Replies View Related

PL/SQL :: Query To Be Displayed Grouped By Year / Month And Then By Assignment?

May 27, 2013

I have the following table structure with values

CREATE TABLE DUMMY
  (
    SR_NUMBER          VARCHAR2(100 CHAR),
    ASSIGNMENT_GROUP   VARCHAR2(100 CHAR),
    REASSIGNMENT_COUNT VARCHAR2(100 CHAR),
    CREATED DATE,

[code]...

?I have the following requirement, the output should be:

Ticket count (sr_number)
% of tickets inside DL
Number of tickets inside DL
Average cycle time (cycle time = closed date - created date)
Total cycle time (cycle time = closed date - created date)
Number of reassignments (sum)

DL - (deadline) formula is, closed date <= target_date

This should be displayed, grouped by year, then month and then by assignment group. The values should be in descending order(dates) Not sure how group by will work here.I am able to write the basic code for the above, but group by based on year, month and assignment group is pretty confusing to me.

View 10 Replies View Related

SQL & PL/SQL :: Parameter For Date - Quarter Year

Jan 26, 2013

IF THE USER ENTER parameter for date(yymm) in a report as 201301

select vndr#,sum(net_sales_value) from mnthly_sales_value where vndr# = 111 and yymm = :yymm group by vndr#;

but I need result of 3 months, but in my table data is stored for one month so how to get 3 months then again 3 months so on for Q1,Q2,Q3,Q4

yymm between 201301 and 201303 and
yymm between 201304 and 201306 and
yymm between 201307 and 201309 and
yymm between 201310 and 201312
in the same query

what i have to do for this

View 3 Replies View Related

SQL & PL/SQL :: How To Pass Parameter In A View

Nov 16, 2012

How to pass parameter in a view.

create or replace view spic_bags as
select distinct(a.item_code) Material_Code,a.uom,
(case when a.card_code = '60' then
case when a.ccn <> '091' then
[code]........

in this above view i have hardcoded dates, well how to pass parameter for this view without hardcoding? here i need to pass the date as a parameter from a select query to view the data from the above view!

View 4 Replies View Related

SQL & PL/SQL :: Pass Date Parameter

Jun 22, 2010

In a procedure i need to pass date parameter, to check how to do this. I create a simple procedure, but it giving

PLS-00049: Bad Bind Variable 'FD'
PLS-00049: Bad Bind Variable 'TD'

How to resolve .......

CREATE PROCEDURE pass_date_parameter
AS
fd VARCHAR2 (10) DEFAULT '01-01-2010';
td VARCHAR2 (10) DEFAULT '01-06-2010';
ffd VARCHAR2 (10) DEFAULT '01-01-2010';
ttd VARCHAR2 (10) DEFAULT '01-06-2010';

[Code]....

View 19 Replies View Related

Forms :: How To Pass Two Parameter For Graphs

Mar 5, 2010

i had a chart developed by graphics builder in which I am passing one parameter(ie year) and displaying the chart.Now i want to pass two parameter into chart.How to do this here i have attached my chart view and procedure which executes and display the chart from forms.

coding when button pressed

PROCEDURE CHART_DISPLAY IS
pl_id ParamList;
pl_name VARCHAR2(100) := 'tempdata';

[Code]....

In the above procedure i had one parameter directly am passing value of year '2009' now i want to pass two parameter like between 'year1' and 'year2' for getting chart between two years.

View 1 Replies View Related

SQL & PL/SQL :: How To Pass Table As Parameter To Procedure

Jun 2, 2010

I am trying to pass a PL/SQL table as a parameter to a procedure and then using that table, update the records, but I am getting an error like:

ORA-06550: line 30, column 10:
PLS-00306: wrong number or types of arguments in call to 'UPDATE_STATUS'

Find the code below:

CREATE TABLE test_pl(empno VARCHAR2(20), empname VARCHAR2(40), empsts VARCHAR2(10));
INSERT INTO test_pl
VALUES ('0001', 'A', 'Y');
INSERT INTO test_pl
VALUES ('0002', 'B', 'N');
INSERT INTO test_pl
VALUES ('0003', 'C', 'Y');
INSERT INTO test_pl
VALUES ('0004', 'D', 'Y');
[code]....

View 4 Replies View Related

Forms :: Best Way To Pass 100+ Parameter In Form A To B?

Dec 18, 2012

If I want to pass lots of informations (100 parameter) from standard HR assignment form to my custom form. I am using fnd_function.execute('called form') to launch new form.

I thinking of creating 100 parameter object but not sure it has limitation of number or not. Another ways I googled are shared PL/SQL library or parameter list which I still doubt.

View 3 Replies View Related

SQL & PL/SQL :: Pass Table As Parameter To Procedure?

Sep 1, 2010

I want to pass a table as a parameter to a procedure.

As an example:

TYPE my_tab IS TABLE OF my_rec INDEX BY BINARY_INTEGER;

However, I want to give this parameter a default of null... is this possible?

procedure myproc(p_param1 in varchar2, p_tab in my_tab default null)

View 11 Replies View Related

PL/SQL :: How To Pass Value From A Table Into Parameter Of A Function

Nov 8, 2012

I have written as function :

create FUNCTION Cov(p_str1_x IN VARCHAR2,p_str2_x IN VARCHAR2, p_str1_y IN VARCHAR2,p_str2_y IN VARCHAR2) RETURN NUMBER is
TYPE ty_varchar_assoc IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100);
v_x ty_varchar_assoc;
v_y ty_varchar_assoc;
v_inter ty_varchar_assoc;

[Code]....

Now i need to pass the values to the parameter from a table? How can i do that?

View 2 Replies View Related

SQL & PL/SQL :: Required Data For Month To Date And Year To Date?

Apr 30, 2012

I want to get data for month to date. For example, If I pass today or any day date as parameter then i should get data for that month(month of passing date) up to passing(parameter) date. As well as i have to get year to date.For example, If I pass today or any day date as parameter then i should get data for that financial year(year of passing date) up to passing(parameter) date. how to get month to date and year to date data.

View 3 Replies View Related

SQL & PL/SQL :: How To Pass Parameter To Cursor And Print Details

Aug 15, 2010

Create a PL/SQL block that declares a cursor called DATE_CUR. Pass a parameter of DATE data type to the cursor and print the details of all the employees who have joined after that date.

DEFINE P_HIREDATE = 08-MAR-00

Test the PL/SQL block for the following hire dates: 08-MAR-00, 25-JUN-97, 28-SEP-98, 07-FEB-99.

I don't know how to pass parameters. So far I have this:

SET serveroutput ON;
DECLARE
p_HIREDATE DATE := 08-MAR-00;
CURSOR c_DATE_CUR(P_HIREDATE DATE) IS
BEGIN
OPEN c_DATE_CUR();

View 6 Replies View Related

Forms :: How To Pass Parameter From Form To Report

Feb 23, 2011

I have created a simple form & report based on 2 tables Cust & cntry.I just want to display all customers of region selected in the criteria form. I am uploading the files.

Other than selection criteria report is fine.

View 4 Replies View Related

SQL & PL/SQL :: Pass More Characters To In Mode Parameter Using Procedure

Feb 26, 2012

I am trying to pass many characters to the in mode parameter using procedure , but i am getting the below error.

ORA-06550:
PLS-00172: string literal too long

CREATE TABLE USR_DETAILS
(
LOAD_ID NUMBER NOT NULL,
LOAD_DATE DATE,
USER_VALUE VARCHAR2(4000 BYTE),
USERID VARCHAR2(4000 BYTE)
)
insert into user_details values('1','2/10/2011','PROD1','USER1');
insert into user_details values('2','2/10/2011','PROD2','USER2');
[code]......

I have written the code as below...

Procedure concept :

Here i will seperate the strings(input parameters) using comma(,) symbol . Here users may pass more than 5000 characters to single in mode parameter, so i have tried with clob datatype but no luck.

CREATE OR REPLACE procedure user_details_proc (user_value1 IN varchar2,
user_value2 IN varchar2,
user_value3 IN varchar2,
user_value4 IN varchar2,
user_value5 IN varchar2,
userid IN varchar2
) as
[code].......

View 2 Replies View Related

Forms :: How To Pass Parameter From Report To Form

Jan 23, 2013

How can i pass the parameter from reports to form. means how can i open particular form from my report. means if i write query

select * from emp

this emp table report open now i just want open only emp no 10 form from this report so which parameter i have to pass.

View 2 Replies View Related

PL/SQL :: How To Pass Input Parameter For Nested Table

May 10, 2013

I have a doubt how to pass input parameter for nested table which is declared as input parameter in procedure.

CREATE TYPE t_example AS OBJECT(msg_text VARCHAR2(100), bundle_msg_text VARCHAR2(100), version NUMBER(10))
/
create type t_msg_details ia table of t_example
/CREATE TABLE table_nested_sample
(msg_codes NUMBER(10),
language_id NUMBER(10),

[Code]...

How to call this procedure I want to insert data like this

Msg_codes               Language_id                 Language_name           msg_text_details
101                            1                              ENG                            "msg_test"         "Bundle_msg_text"   1
                                                                                                    "msg_test_1"  "Bundle_msg_text_2"   2

View 5 Replies View Related

Reports & Discoverer :: Pass Parameter From Forms To Report

Oct 5, 2013

I can not Pass parameter from forms to Report.

I use three parameter for report. I want to show the employee who has been hired between from_date to_date,it work well in report builder. but I want to pass parameter from forms to report and pdf format. I upload my work (this is actually a zip File, change file type txt to zip just before open).

View 5 Replies View Related

Forms :: How To Pass Parameter From Criteria Form To Report

Dec 21, 2011

have a customer table with following columns.
1 - Cust_ID
2 - Cust_nam
3 - Cntry_id

I have another table country.
1 - Cntry_id
2 - Cntry_nam
3 - Region

Now I want to view report for all customers of a certain region selected by user in criteria form.How can I do this?

View 1 Replies View Related

SQL & PL/SQL :: How To Pass Type Object As A Parameter To Stored Procedure

May 9, 2012

I am fairly new to oracle, here's what I am doing.

Create or replace type csc_info as object( source_code varchar2(10),
Docno varchar2(10),
Key_value_1 varchar2(10),
Key_value_2 varchar2(10));

[Code]....

I need to test the procedure how do I pass the inputs for the type object csc_info?

View 2 Replies View Related

SQL & PL/SQL :: Pass Table As Input Parameter In Stored Procedures?

Feb 22, 2011

i need to pass table as input parameter in stored procedures. during the run time, i am getting error

CREATE OR replace TYPE emp_type IS OBJECT (
id NUMBER(4),
ename VARCHAR2(10));
CREATE OR replace TYPE emp_type_tab
IS TABLE OF EMP_TYPE;
CREATE OR replace PROCEDURE Test_proc (in_emp_type IN EMP_TYPE_TAB)
AS
BEGIN
FOR i IN 1.. in_emp_type.COUNT LOOP
dbms_output.Put_line(in_emp_type.Id(i));
END LOOP;
END;
/

View 10 Replies View Related







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