SQL & PL/SQL :: How To Use Date As Input Parameter

May 13, 2013

How to use date as an input parameter,im supposed to use varchar2 as the data type

CREATE OR REPLACE PROCEDURE mail1 ( recievers VARCHAR2 ,p_date in varchar2 )
IS
sender VARCHAR2(30) := 'xyz@gmail.com';
mailhost VARCHAR2(100) := 'host address';
TAB VARCHAR(2) := CHR(9);
mail_conn utl_smtp.connection;

[code].....

cursor c1 is

--select activity_date,procedure_name,status_message,error_desc from staging_activity_log where rownum between 1 and 10 ;
select activity_date,procedure_name,status_message,error_desc from staging_activity_log where error_desc is not null and trunc(activity_date) >= to_date(p_date,'DD-MON-YYYY') ;
BEGIN

[code].....

when i execute i get

BEGIN mail1 ('xxx@gmail.com,yyy@gmail.com,'28-jan-2008'); END;
Error at line 1
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "MAIL1", line 27
ORA-06512: at line 1

View 3 Replies


ADVERTISEMENT

PL/SQL :: Input Parameter - Date Format

Aug 5, 2013

i have below requirement in one program date is input parameter. but that should work whatever date format like DD/mon/yyyyyyyy.mm.dd what shd i do input parameter from dateand to date

View 5 Replies View Related

SQL & PL/SQL :: Convert As Procedure Based On Input Date Parameter

Dec 12, 2012

i have the below query

select to_char(report_date, 'YYYY MM Mon'), count(1) no_of_times
from (
select to_date('&&YYYYMMDD', 'YYYYMMDD')+rownum report_date
, mod(rownum,14) mod_result
from all_objects

[code]...

need to convert as procedure based on input date parameter.I will pass the input date from java environment and need to see the sql query output in front end.

View 7 Replies View Related

PL/SQL :: Retrieve Date Range From 2 Search Input Date Fields

Aug 16, 2012

I would like to inquire how do I code my select statement if my user enter a search date range(search startDt: 01/08/2012 and search endDt :30/09/2012) and I will like to retrieve only the 7records out from my table as shown below ?

STARTDT_     ENDDT_
01/08/2012 01/08/2012
01/06/2012 31/12/2012
01/08/2012 01/08/2012
01/09/2012 01/09/2012
01/09/2012 31/12/2012
01/07/2012 01/07/2012->not retrieved out
01/08/2012 01/08/2012
01/01/2012 31/12/2012

View 6 Replies View Related

SQL & PL/SQL :: Validating Input Parameter

Aug 11, 2010

I'm using Oracle 9i. How can i check for a string if it contains only numbers or letters? Otherwise i should return false.

View 6 Replies View Related

SQL & PL/SQL :: Can Have Input Parameter In Procedure

Oct 11, 2010

i am trying to create a procedure that will take an input and based on that input it will delete or update a row, if the input is wrong we will dbs_ouput a line telling the user to enter the input again. can i have a input parameter in a procedure and insert multiple rows with a single insert on a procedure?

View 6 Replies View Related

SQL & PL/SQL :: Order By Based On Input Parameter?

Jun 29, 2011

I have created the Procedure P_GET_CURRENCIES. I want to sort the cursor query based on the Input parameter P_SORT_ORDER. For example if i pass 2 for P_SORT_ORDER then my query should be sorted by 2nd column. But i'm not getting correct Output.

PROCEDURE P_GET_CURRENCIES(P_START_ROW_NUM IN INTEGER
,P_END_ROW_NUM IN INTEGER
,P_SORT_ORDER IN number
,P_CURRENCY_DATA OUT SYS_REFCURSOR
,P_RETURN_MESSAGE OUT VARCHAR2 )
AS

[code]....

View 3 Replies View Related

SQL & PL/SQL :: How To Collect Input Parameter In Procedure

Mar 4, 2010

I have a procedure, which is getting called from ado.net with a input parameter named p_inp_oids which is a character string like '(122,323,434,444)',

My procedure body contains a query whose WHERE clause contains

AND OID IN p_inp_oids

I cannot post the whole procedure. Also OID is number.How to collect that input parameter in my procedure?

View 24 Replies View Related

SQL & PL/SQL :: Debugging The Procedure With XML As Input Parameter?

Jun 23, 2011

I have a procedure where it contains an XML as input parameter.And i have to debug this procedure in pl/sql developer to check whether this is the correct XML and inserting the XML data in to my table. But when debugging, I am getting an Error "EXPRESSION IS OF WRONG TYPE". Below is my

declare
px_xml xmltype;
begin
px_xml:='Xml content';
--call the procedure
package_name.procedure_name
(parameter1 => :parameter1,
px_xml => px_xml,
pvo_out_mesg => :parameter3);
end;

Is there any other way to debug XML in plsql developer.

View 3 Replies View Related

SQL & PL/SQL :: Passing Input Parameter To Like Variable In Procedure

Dec 27, 2012

I have a procedure named 'GetShipperinfo' which takes i_name as input and needs to build a cursor taking i_name as input

i.e.

The following sql when executed at sqlplus prompt gives correct results.

select dept, supplier, shipper_id
from shippers
where upper(shipper_name) like upper('Frank Robert%');

How can I transform this inside a cursor within a procedure passing 'Frak Robert' value as i_name input.

i.e I should be able to call the procedure as follows

sql> variable v1 varchar2;
sql> exec pkg_shipment.GetShipperinfo('Frank Robert',:v1);
sql> print :v1;

Should the cursor inside the procedure be built as follows

cursor c1 is
select dept, supplier, shipper_id
from shippers
where shipper_name like ''||upper(i_name'%''||)'';

Iam unable to build the sql for the cursor.

View 3 Replies View Related

SQL & PL/SQL :: Ordering The Result Set Based On Input Parameter

Aug 2, 2013

I need to order the result set with different data types based on the input parameter.

select * from scott.emp sc order by decode('&input_parameter',1,sc.empno,2, sc.ename);

If the input_parameter is equals to 1 then,ordering should be based on EMPNO which is Number data type.
If the input_parameter is equals to 2 then,ordering should be based on ENAME which is Character Data type.

Above query was failed for input_parameter 2,as we know that decode should return same data type.

View 6 Replies View Related

SQL & PL/SQL :: Passing CLOB Data As Input Parameter?

May 24, 2010

I have a requirement where in I have to store large data in one of the database columns using stored procedure.

I have declared the column as CLOB as it can store upto 4GB and also the input parameter for the procedure as CLOB. But when I am trying to pass large data it is not allowing to store as it is throwing literal string too large error.

Is there any restriction in the data size to be passed to the stored procedure?

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

PL/SQL :: How To Concatenate Quote To Procedure Parameter Input Value

Dec 6, 2012

create or replace procedure ab(a in varchar2, b in varchar2)
is
test varcha2(8);
begin
if (a is not null) then
for i in(select c
from t
where c between ||'''||a||'''|| and ||'''||b||'''||)
loop
test:=i.c
end loop;
end if;
end;

I want both parameter input values to be enclosed in quotes so that it considers both parameter values as char.Receiving ora 00936 missing expression error.

View 7 Replies View Related

SQL & PL/SQL :: Removal Of Time In Input Parameter In Stored Procedure?

May 15, 2011

I'm creating a stored procedure wherein I'm the field wherein I will use for date input parameter is having a datetime format.

The format that we need for user's to input is 'DD-MON-YYYY' (without the minutes and seconds.)

how should I proceed with the creation of stored procedure for this?

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

Input Parameter From Arraylist To Stored Procedure In Oracle?

Nov 7, 2013

i have created a package & stored procedure which is working fine when i am passing single value to my package or stored procedure. But what approach i should take if i have to pass multiple values (ArrayList) like (from eg empid like I1001,I1002,I1003,I1004,...) in my input parameter . I am using C# & Oracle

CREATE OR REPLACE PACKAGE PKG_x AS type t_cursor is ref cursor;
procedure Proc(cur_x out t_cursor,
param_emp in varchar2 DEFAULT NULL);
END PACKAGE PKG_x;

[Code].....

View 2 Replies View Related

PL/SQL :: Input Parameter Of Table Type And Null Assigned To It

Jul 17, 2012

I have few questions regarding PL/SQL tables

1) If I don't specify the "INDEX BY" clause, it is indexed by PLS_INTEGER by default, right?

2) Consider this package specification

CREATE OR REPLACE PACKAGE Testxyz AS
TYPE tab_Numbers IS TABLE OF PLS_INTEGER;
PROCEDURE TestNumber(ptab_Numbers IN tab_Numbers := NULL);


END Testxyz;So I created a table (I hope it is defaultly indexed by pls_integer data type) and I am passing it as a parameter to a procedure. Because I want this parameter to be optional I am assigning null into it.

Now I change the definition of the table to:

CREATE OR REPLACE PACKAGE Testxyz AS
TYPE tab_Numbers IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
PROCEDURE TestNumber(ptab_Numbers IN tab_Numbers := NULL);
[code]....

3) Because I need this parameter to be optional, I use the first declaration:

CREATE OR REPLACE PACKAGE Testxyz AS
TYPE tab_Numbers IS TABLE OF PLS_INTEGER;
PROCEDURE TestNumber(ptab_Numbers IN tab_Numbers := NULL);
END Testxyz;

Now I create an anonymous block and want to assign something into the table

DECLARE
   vtab_Numbers TESTXYZ.tab_Numbers;
BEGIN
vtab_Numbers(1) := 5;
END;
/When trying to run this, I got:
06531. 00000 - "Reference to uninitialized collection"

Is it possible to have input parameter of PL/SQL table type and have defaultly null assigned to it?

View 5 Replies View Related

SQL & PL/SQL :: Column Access In Table Based On User Parameter Input

Nov 10, 2010

Need to access data in a table base on user parameter input where the data is stored like a spreadsheet with column headings JAN, FEB, MAR... and the rows are the years. Is there a way to create a generic SQL statement so that I don't have to have 12 if statements in the procedure?

View 5 Replies View Related

SQL & PL/SQL :: Pass Multiple Values As Single Input Parameter Into Pipeline Function

Dec 23, 2012

My need is to pass multiple values as single input parameter into pipelined function. For example - "2" and "3" are values of input parameter "t":

with data as (
select 1 as t from dual union all
select 2 as t from dual union all
select 3 as t from dual union all
select 4 as t from dual union all
select 5 as t from dual
)
select * from data where t in (2,3)

View 2 Replies View Related

Client Tools :: How To Pass Ambersand (&) As Input Parameter To A Stored Procedure

Oct 26, 2010

i have a stored procedure whose input parameter is a varchar2 datatype.i created this procedure for an interface and tibco would be calling my procedure by passing input parameters.my problem is when there is a input string with & (ambersand) then its not working.

even i tried to pass the parameter with & in TOAD, it asks me to enter value for string.look at the sample code below which i wrote for testing purpose:

procedure is:

create or replace procedure testproc(p_in in varchar2)
is
begin
null;
end;

i pass parameter as given below:

begin
testproc('abc & def');
end;

if i run above script, it asks me to input some string value as it sees & in the string. attached is the image that shows up in TOAD. if i run below script it works. but i dont know how many &'s will be there in the input parameter. hence i cant do. and also TIBCO cant modify the input paramter while calling the procedure.

begin
testproc('abc &'||'def');
end;

View 9 Replies View Related

SQL & PL/SQL :: Date Query - Input Year In Specified Range

Dec 26, 2012

Getting problem with below query.

select to_date('30-DEC-00','DD-MON-YYYY') from dual

Error starting at line 1 in command:

select to_date('30-DEC-00','DD-MON-YYYY') from dual

Error report:

SQL Error: ORA-01841: (full) year must be between -4713 and +9999, and not be 0 01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0"

*Cause: Illegal year entered
*Action: Input year in the specified range

View 1 Replies View Related

SQL & PL/SQL :: Calculate Balance For Last Two Cooperative Years On Input Date

Aug 24, 2012

I need to calcuate balance for last two cooprative years on input date.

example

if input date is-->"01-AUG-2012" if (month >=7) -->then i need to calculate from 01-july-2010 to 30-june-2011 and 01-july-2011 to 30-june-2012 and 01-july-2012 to input date.

if input date is-->"01-june-2012" if (month<7) -->then i need to calculate from 01-july-2009 to 30-june-2010 and 01-july-2010 to 30-june-2011 and 01-july-2011 to input date.

how to achieve this logic

View 4 Replies View Related

Application Express :: Show Input After Selecting Date In Datepicker?

Jun 11, 2012

I'm using apex 4.1.1 And i want to use a Date Picker item on my page. This works, but.. When a user selects a date you can see the date highlighted. But in the "textarea"corresponding tot the datepicker is not showing the date. This shows the date after clicking the close button.

I would like to show the date directly after selecting a new date.

View 7 Replies View Related

SQL & PL/SQL :: Build Parametric Queries - Input Date Field At Run-time

Aug 24, 2012

When working in MS Access, I can build parametric queries, where I can input the date field for example at run-time. How can I do something similar in SQL Oracle

Example

SELECT Sector.Date, Sector.RNC, Sector.Site, Sector.Cell,Sector.PSC
FROM Sector
WHERE Sector.Date=[Enter Date];

View 15 Replies View Related

SQL & PL/SQL :: Date Format Picture Ends Before Converting Entire Input String

Jun 29, 2011

I am trying to insert a row in a table and getting the below error.

SQL> insert into tbl_force_charging(ANI, date_time, durations,src, circleid)
2 values ('9569333585','29-JUN-11 03.19.41.000000000 PM','1027','51010','BIR'
)
3 ;
values ('9569333585','29-JUN-11 03.19.41.000000000 PM','1027','51010','BIR')
*
ERROR at line 2: ORA-01830: date format picture ends before converting entire input string

Table Structure is

Name Null? Type
----------------------------------------- -------- ---------------------------
ANI VARCHAR2(10)
DATE_TIME DATE
DURATIONS VARCHAR2(10)
SRC VARCHAR2(10)
CIRCLEID VARCHAR2(10)
SQL>

View 11 Replies View Related

SQL & PL/SQL :: ORA-01830 / Date Format Picture Ends Before Converting Entire Input String

Feb 26, 2013

when i run this query i am facing date format error.

select sbrueregister.UEIMSI,sbrueregister.fapid,sbrfapslid.slid,sbrfapslid.ACTIVATION_TS,sbrfapslid.DEACTIVATION_TS from SBRFAPSLID INNER JOIN sbrueregister ON sbrfapslid.fapid=sbrueregister.fapid where sbrfapslid.slid='1234567890' and sbrueregister.registeredat between TO_DATE('2013-02-1.12.0. 10. 123000000','YYYY-MM-DD HH.MI.SS.SSSSSS')and TO_DATE('2013-02-1.12.9.10.123000000', 'YYYY-MM-DD HH.MI.SS.SSSSSS');

ORA-01830: date format picture ends before converting entire input string

View 1 Replies View Related

SQL & PL/SQL :: JSP Limitations - Date Format Picture Ends Before Converting Entire Input String

Feb 21, 2010

I am using

SELECT TO_CHAR(TO_DATE((:BILL_VALUE),'J'),'Jsp') FROM sys.dual;

its givinig right result for

1023411->One Million Twenty-Three Thousand Four Hundred Eleven

but if i will change it to 10234111 then this query giving an error

ORA-01830: date format picture ends before converting entire input string.

Is there any limitation or is there any other built in function to get the number to a word.

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

SQL & PL/SQL :: First Tuesday Date Between Two Parameter

Nov 20, 2010

i need to get first Tuesday per each month between two parameter i will use cursor in forms.

for example

my parameter is '04122007' and '31122009'
i need the first tuesday for each month
like '02122008'
'01�12009'
'03�22009' ext....

View 8 Replies View Related







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