Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to pass a date to a stored proc..(?)

Status
Not open for further replies.
RhythmAddict112

RhythmAddict112

Programmer
Joined
Jun 17, 2004
Messages
625
Location
US
Hi all. I have a stored proc that compiles without issue....it is below. My problem is the error I receive when I try to run it. I have the feeling there is some syntax error in the SP and I'm just not sure what. Any help would be fantastic.

The stored Procedure is basically taking 3 arguments, and doing a coutn(*) query on them. Specifically, it is taking in a value for the WHERE clause, and two dates eg;
dSubmit between (argument1) and (argument2)
thank you in advance.

This is the error I get when I [attempt to] run it:
Code: 
SQL> execute CountSubmitted.CountSubmitted('7','02-apr-2002','30-apr-2002',:r);
BEGIN CountSubmitted.CountSubmitted('7','02-apr-2002','30-apr-2002',:r); END;

                                                                    *
ERROR at line 1:
ORA-01840: input value not long enough for date format
ORA-06512: at line 1


SQL>

This is the entire stored procedure:
Code: 
CREATE OR REPLACE PACKAGE CountSubmitted
AS
	TYPE ref_cur         IS      	REF CURSOR;
	PROCEDURE CountSubmitted
	(
		
		FRM				IN		date,
		TODATE				IN		date,
		REG					IN		VARCHAR2,
		RC1             		IN  OUT 	REF_CUR
	);
END;
/
CREATE OR REPLACE PACKAGE BODY CountSubmitted
AS
	PROCEDURE CountSubmitted
	(	

		FRM				IN		date,
		TODATE				IN		date,
		REG					IN		VARCHAR2,
		RC1                     IN  OUT 	REF_CUR
	)
	AS
	BEGIN 
		
		OPEN RC1 FOR 'select count (*) from tbl_OpenWindowRequests WHERE cRegion = ' || reg || ' AND dSubmit BETWEEN ('' || to_char(frm) || '') AND ('' || to_char(todate) || '') ';
END;


END;
/
 
Sort by date Sort by votes
What is the default date format on your DB? Try selecting SYSDATE from dual to see what you get.

To qualify your dates try this:

Code: 
execute CountSubmitted.CountSubmitted('7',To_Date('02-apr-2002','DD-mon-YYYY'),To_Date('30-apr-2002','DD-mon-YYYY'),:r);
 
Upvote 0 Downvote
Hmm..Alright, So my default date is DD-MMM-YY. Unfortunately, I get the same error running the query with the to_date function...Any other suggestions?

Thank you for your help...code follows

Code: 
SQL> select sysdate from dual;

SYSDATE
---------
29-JUL-04

SQL> execute CountSubmitted.CountSubmitted('7',To_Date('02-apr-2002','DD-mon-YYYY'),To_Date('30-apr-
2002','DD-mon-YYYY'),:r);
BEGIN CountSubmitted.CountSubmitted('7',To_Date('02-apr-2002','DD-mon-YYYY'),To_Date('30-apr-2002','

                                                                    *
ERROR at line 1:
ORA-01840: input value not long enough for date format
ORA-06512: at line 1


SQL>
 
Upvote 0 Downvote
It looks like you're passing in your arguments in the wrong order!
Try
Code: 
execute CountSubmitted.CountSubmitted(To_Date('02-apr-2002','DD-mon-YYYY'),To_Date('30-apr-2002','DD-mon-YYYY'),'7',:r);
 
Upvote 0 Downvote
Okay. You were indeed right about the order of the variables being passwed ( i need to stop doing that! )
But, what is up with the error message I receive (proc still completes successfully)

Code: 
SQL> execute CountSubmitted.CountSubmitted(To_Date('02-apr-2002','DD-mon-YYYY'),To_Date('30-apr-2002
','DD-mon-YYYY'),'7',:r);

PL/SQL procedure successfully completed.

SQL> print r
ERROR:
ORA-01858: a non-numeric character was found where a numeric was expected



no rows selected
 
Upvote 0 Downvote
I don't believe you can print a cursor with a simple print statement. I think you need to write a program that loops through the cursor and prints individual elements. But I could be wrong!
 
Upvote 0 Downvote
To print the ref cursor out try this on sql*plus prompt:

SQL>var r refcursor
SQL>execute CountSubmitted.CountSubmitted(To_Date('02-apr-2002','DD-mon-YYYY'),To_Date('30-apr-2002','DD-mon-YYYY'),'7',:r);

SQL> print r

Also, I think the Numeric Error may be due to the data type of column dSubmit. If the dSubmit column is of Date datatype then why are you using TO_CHAR(frm) and TO_CHAR(todate)

dSubmit BETWEEN ('' || to_char(frm) || '') AND ('' || to_char(todate) || '') '
 
Upvote 0 Downvote
Hmm.. I removed the to_char function, Unfortunately still receiving the same error.


Check it out: (stored proc follows)

Code: 
SQL> execute CountSubmitted.CountSubmitted(To_Date('02-apr-2002','DD-mon-YYYY'),To_Date('30-apr-2002','DD-mon-YYYY'),'7',:r

PL/SQL procedure successfully completed.

SQL> execute countsubmitted.countsubmitted('02-apr-2002','30-apr-2002','7',:r);

PL/SQL procedure successfully completed.

SQL> print r
ERROR:
ORA-01858: a non-numeric character was found where a numeric was expected



no rows selected


the stored proc:

Code: 
CREATE OR REPLACE PACKAGE CountSubmitted
AS
	TYPE ref_cur         IS      	REF CURSOR;
	PROCEDURE CountSubmitted
	(
		
		FRM				IN		date,
		TODATE				IN		date,
		REG					IN		VARCHAR2,
		RC1             		IN  OUT 	REF_CUR
	);
END;
/
CREATE OR REPLACE PACKAGE BODY CountSubmitted
AS
	PROCEDURE CountSubmitted
	(	

		FRM				IN		date,
		TODATE				IN		date,
		REG					IN		VARCHAR2,
		RC1                     IN  OUT 	REF_CUR
	)
	AS
	BEGIN 
		
		OPEN RC1 FOR 'select count (*) from tbl_OpenWindowRequests WHERE cRegion = ' || reg || ' AND dSubmit BETWEEN ('' || frm || '') AND ('' || todate || '') ';
END;


END;
/
 
Upvote 0 Downvote
I do not understand why you have to concatenate the statement and I do not think you are using BETWEEN correctly.
Code: 
select count (*) 
from tbl_OpenWindowRequests 
WHERE cRegion = reg
AND dSubmit BETWEEN frm AND todate;
Marty
 
Upvote 0 Downvote
Can you try somethng and post the result back here.

CREATE OR REPLACE PACKAGE BODY CountSubmitted
AS
PROCEDURE CountSubmitted
(

FRM IN date,
TODATE IN date,
REG IN VARCHAR2,
RC1 IN OUT REF_CUR
)
AS
v_sql_string VARCHAR2(1000);
BEGIN

v_sql_string := 'SELECT count(*) FROM tbl_OpenWindowRequests WHERE cRegion = ' || reg || ' AND dSubmit BETWEEN ' || ''''|| frm || '''' ||' AND '|| ''''|| todate || '''' ;

dbms_output.put_line(v_sql_string );

OPEN RC1 FOR v_sql_string;
END;


Its just that we will be able to see whats the sql stmt its trying to use.

 
Upvote 0 Downvote
The problem is you are not enclosing your "reg" values in quotes correctly.
Try
Code: 
CREATE OR REPLACE PACKAGE BODY CountSubmitted
AS
    PROCEDURE CountSubmitted
    (    

        FRM                IN        date,
        TODATE                IN        date,
        REG                    IN        VARCHAR2,
        RC1                     IN  OUT     REF_CUR
    )
    AS
    BEGIN 
        
        OPEN RC1 FOR 'select count (*) from tbl_OpenWindowRequests WHERE cRegion = '''
|| reg ||''' AND dSubmit BETWEEN ('' || frm || '') AND ('' || todate || '') ';
END;


END;
/
 
Upvote 0 Downvote
no qoutes are needed the frm and todate are being treated as strings because of the quotes. The statement is NOT dynamic and doesn't have to be built just type it in as I did in my previous post and it will run.

SQL> var rtn refcursor
SQL> exec PKG_MGR.DBF_Test(TO_DATE('7/01/1999', 'MM/DD/YYYY'), TO_DATE('6/30/2005', 'mm/dd/yyyy'), '
Direct Contract', :rtn);

PL/SQL procedure successfully completed.

SQL> print rtn

COUNT(*)
----------
237


Marty
 
Upvote 0 Downvote
Hi all. First off I want to thank everyone for their suggestions because it is greatly appreciated and amazing how much great information is available on bb's like this.

but i digress...
Per cappmgr, I removed all the fancy concatentation on Friday and everything worked like a charm afterwards. For future reference, in one instance would I need to concatenate in the way I was?

For reference, the entire stored proc:
Code: 
CREATE OR REPLACE PACKAGE CountSubmitted
AS
	TYPE ref_cur         IS      	REF CURSOR;
	PROCEDURE CountSubmitted
	(
		
		FRM				IN		date,
		TODATE				IN		date,
		REG					IN		VARCHAR2,
		RC1             		IN  OUT 	REF_CUR
	);
END;
/
CREATE OR REPLACE PACKAGE BODY CountSubmitted
AS
	PROCEDURE CountSubmitted
	(	

		FRM				IN		date,
		TODATE				IN		date,
		REG					IN		VARCHAR2,
		RC1                     IN  OUT 	REF_CUR
	)
	AS
	BEGIN 
		
		OPEN RC1 FOR select count (*) from tbl_OpenWindowRequests WHERE cRegion = reg  
		 AND dSubmit BETWEEN  frm  AND todate;
END;


END;
/
SQL> var r refcursor
SQL> execute Countsubmitted.Countsubmitted(To_Date('01-apr-2002','DD-mon-YYYY'),To_Date('30-apr-2002
','DD-mon-YYYY'),'UP',:r);

PL/SQL procedure successfully completed.

SQL> print r

  COUNT(*)
----------
       586

SQL>
 
Upvote 0 Downvote
That was supposed to say:
In *what* instance....would I need to concatenate like that (in terms of oracle pl/sql of course)
 
Upvote 0 Downvote
You could use concatenation when you are passsing in a string (varchar2, ...) because a datatype does not fit your purpose. To use it in a cursor you would have to build the string then use it as IAMUser did.
hth,
Marty
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

J
  • Locked
  • Question Question
Oracle Simphony - Adding tip out percentages to Employee Tip Report
Replies
0
Views
647
jj10438
J
Chopstik
  • Locked
  • Question Question
How to identify a package based on a code snippet
Replies
1
Views
1K
Andrzejek
Andrzejek
sqlPower
  • Locked
  • Question Question
Help with Integer to Char to Datetime stamp
Replies
2
Views
1K
SkipVought
S
Livia8
  • Locked
  • Question Question
How to forcibly logout users
Replies
17
Views
2K
carp
carp
Livia8
  • Locked
  • Question Question
Alternative to "locate" command
Replies
0
Views
1K
Livia8
Livia8

Part and Inventory Search

Sponsor

Back
Top