Home » SQL & PL/SQL » SQL & PL/SQL » guys oracle 10g migration problem..pls help :-(!!
guys oracle 10g migration problem..pls help :-(!! [message #219541] Wed, 14 February 2007 21:44 Go to next message
spell128
Messages: 7
Registered: February 2007
Location: asdas
Junior Member

i have converted the sql stored procedure codes to pl/sql. but i have been receiving the following error:

LINE/COL ERROR
-------- -----------------------------------------------------------------
11/3 PL/SQL: SQL Statement ignored
53/13 PL/SQL: ORA-00932: inconsistent datatypes: expected CHAR got
INTERVAL YEAR TO MONTH

the following would be my SP:
CREATE OR REPLACE PROCEDURE CustomerGet
(
	Customer                   IN      	VARCHAR2 DEFAULT NULL,
	RecStatus                  IN      	CHAR DEFAULT NULL,
	RCT1                        IN  OUT 	GLOBALPKG.RCT1
)
AS  
	BEGIN
		
		OPEN RCT1 FOR 
		##[COLOR=orange]SELECT[/COLOR]-- THIS WOULD BE THE LINE 11				 C.CustomerCode AS CustomerCode,
				 C.CashAccountNo AS CashAccountNo,
				 C.CustomerName AS CustomerName,
				 C.ApproveUser AS ApproveUser,
				 C.ApproveTS AS ApproveTS,
				 C.CreateUser AS CreateUser,
				 C.CreateTS AS CreateTS,
				 C.UpdateUser AS UpdateUser,
				 C.UpdateTS AS UpdateTS,
				 NVL(CL.RecStatus, 'AC') AS RecStatus
		FROM  CUSTOMER C,
			 CUSTOMER_LOG CL 
		WHERE	 C.CustomerCode  = CL.CustomerCode (+)
		 AND	C.CashAccountNo  = CL.CashAccountNo (+)
		 AND	(1  = 
			CASE
				 WHEN RTRIM(CustomerGet.Customer)  = '' THEN 1
				 WHEN RTRIM(CustomerGet.Customer)  <> ''
				 AND	C.CustomerCode  = CustomerGet.Customer THEN 1
				 WHEN RTRIM(CustomerGet.Customer)  <> ''
				 AND	C.CashAccountNo  = CustomerGet.Customer THEN 1
				 WHEN RTRIM(CustomerGet.Customer)  <> ''
				 AND	C.CustomerName  LIKE CustomerGet.Customer || '%' THEN 1
				 ELSE 0
			 END
		 AND	C.RecStatus  = CustomerGet.RecStatus)
		UNION ALL
		 SELECT
				 CL.CustomerCode AS CustomerCode,
				 CL.CashAccountNo AS CashAccountNo,
				 CL.CustomerName AS CustomerName,
				 CL.ApproveUser AS ApproveUser,
				 CL.ApproveTS AS ApproveTS,
				 CL.CreateUser AS CreateUser,
				 CL.CreateTS AS CreateTS,
				 CL.UpdateUser AS UpdateUser,
				 CL.UpdateTS AS UpdateTS,
				 
					/*CL.RecStatus        AS RecStatus,*/
					CASE
						 WHEN C.RecStatus  IS NULL THEN 'NW'
						 ##[COLOR=orange]ELSE [/COLOR]CL.RecStatus-- THIS WOULD BE THE LINE 53
					 END
		FROM  CUSTOMER_LOG CL,
			 CUSTOMER C 
		WHERE	 CL.CustomerCode  = C.CustomerCode (+)
		 AND	CL.CashAccountNo  = C.CashAccountNo (+)
		 AND	(1  = 
			CASE
				 WHEN RTRIM(CustomerGet.Customer)  = '' THEN 1
				 WHEN RTRIM(CustomerGet.Customer)  <> ''
				 AND	CL.CustomerCode  = CustomerGet.Customer THEN 1
				 WHEN RTRIM(CustomerGet.Customer)  <> ''
				 AND	CL.CashAccountNo  = CustomerGet.Customer THEN 1
				 WHEN RTRIM(CustomerGet.Customer)  <> ''
				 AND	CL.CustomerName  LIKE CustomerGet.Customer || '%' THEN 1
				 ELSE 0
			 END
		 AND	CL.RecStatus  = CustomerGet.RecStatus);
	END;
/

THIS WOULD BE THE TABLE RELATED:
SQL> DESCRIBE CUSTOMER
Name Null? Type
----------------------------------------- -------- ----------------------------
CUSTOMERCODE CHAR(30)
CASHACCOUNTNO CHAR(30)
CUSTOMERNAME VARCHAR2(50)
APPROVEUSER CHAR(20)
APPROVETS CHAR(10)
CREATEUSER CHAR(20)
CREATETS CHAR(10)
UPDATEUSER CHAR(20)
UPDATETS CHAR(10)
RECSTATUS CHAR(2)

PLEASE reply me asap Sad i have been trying to solve this problem for 3 days!! nonstop... plEASEE..

[Updated on: Wed, 14 February 2007 22:33]

Report message to a moderator

Re: guys oracle 10g migration problem..pls help :-(!! [message #219542 is a reply to message #219541] Wed, 14 February 2007 21:53 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to use "code" tags to make what you post readable.
You need to either post line #s or HIGHLIGHT the actual lines with the errors.
You should post DESCRIPTION of your tables.
Re: guys oracle 10g migration problem..pls help :-(!! [message #219544 is a reply to message #219541] Wed, 14 February 2007 22:15 Go to previous messageGo to next message
spell128
Messages: 7
Registered: February 2007
Location: asdas
Junior Member

i have already updated the post... please help....
Re: guys oracle 10g migration problem..pls help :-(!! [message #219545 is a reply to message #219541] Wed, 14 February 2007 22:22 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
LINE/COL ERROR
-------- -----------------------------------------------------------------
11/3 PL/SQL: SQL Statement ignored
53/13 PL/SQL: ORA-00932: inconsistent datatypes: expected CHAR got
INTERVAL YEAR TO MONTH

OK, where is line #11 & #53???
Re: guys oracle 10g migration problem..pls help :-(!! [message #219546 is a reply to message #219541] Wed, 14 February 2007 22:28 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
IMO, all the CHAR datatypes should be VARCHAR2.
A 2nd table CUSTOMER_LOG needs to be DESCRIBEd
Re: guys oracle 10g migration problem..pls help :-(!! [message #219547 is a reply to message #219541] Wed, 14 February 2007 22:35 Go to previous messageGo to next message
spell128
Messages: 7
Registered: February 2007
Location: asdas
Junior Member

the symbol ## indicates the lines.
customer_log table as describe:
SQL> describe customer_log
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CUSTOMERCODE                              NOT NULL INTERVAL DAY(2) TO SECOND(6)
 CASHACCOUNTNO                             NOT NULL INTERVAL DAY(2) TO SECOND(6)
 CUSTOMERNAME                              NOT NULL CLOB
 APPROVEUSER                                        INTERVAL DAY(2) TO SECOND(6)
 APPROVETS                                          TIMESTAMP(6)
 CREATEUSER                                NOT NULL INTERVAL DAY(2) TO SECOND(6)
 CREATETS                                  NOT NULL TIMESTAMP(6)
 UPDATEUSER                                NOT NULL INTERVAL DAY(2) TO SECOND(6)
 UPDATETS                                  NOT NULL TIMESTAMP(6)
 RECSTATUS                                 NOT NULL INTERVAL YEAR(2) TO MONTH


[code tags added by Administrator.]
thanks guys.... in advance

[Updated on: Thu, 15 February 2007 12:40] by Moderator

Report message to a moderator

Re: guys oracle 10g migration problem..pls help :-(!! [message #219549 is a reply to message #219541] Wed, 14 February 2007 22:50 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
WOW!
Until minutes ago I never realized the INTERVAL datatype existed.
From what I can see, I suspect that the parser is confused where the SQL statements actually end.
I'd break out the parts of the UNIONs to verify parsing.
Re: guys oracle 10g migration problem..pls help :-(!! [message #219552 is a reply to message #219541] Wed, 14 February 2007 23:06 Go to previous messageGo to next message
spell128
Messages: 7
Registered: February 2007
Location: asdas
Junior Member

what does that mean?? i am a new bie to oracle.. can u correct the code for me?? thanks you for your asap reply
Re: guys oracle 10g migration problem..pls help :-(!! [message #219578 is a reply to message #219541] Thu, 15 February 2007 00:55 Go to previous messageGo to next message
spell128
Messages: 7
Registered: February 2007
Location: asdas
Junior Member

guys please help me out ... Sad
Re: guys oracle 10g migration problem..pls help :-(!! [message #219684 is a reply to message #219547] Thu, 15 February 2007 09:08 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
spell128 wrote on Wed, 14 February 2007 23:35
the symbol ## indicates the lines.
customer_log table as describe:
SQL> describe customer_log
Name Null? Type
----------------------------------------- -------- ----------------------------
CUSTOMERCODE NOT NULL INTERVAL DAY(2) TO SECOND(6)
CASHACCOUNTNO NOT NULL INTERVAL DAY(2) TO SECOND(6)
CUSTOMERNAME NOT NULL CLOB
APPROVEUSER INTERVAL DAY(2) TO SECOND(6)
APPROVETS TIMESTAMP(6)
CREATEUSER NOT NULL INTERVAL DAY(2) TO SECOND(6)
CREATETS NOT NULL TIMESTAMP(6)
UPDATEUSER NOT NULL INTERVAL DAY(2) TO SECOND(6)
UPDATETS NOT NULL TIMESTAMP(6)
RECSTATUS NOT NULL INTERVAL YEAR(2) TO MONTH

thanks guys.... in advance


Tell me, if someone posted this for you to decipher, would you be able to? Why not be considerate and use code tags so others don't get a case of vertigo?
Re: guys oracle 10g migration problem..pls help :-(!! [message #219700 is a reply to message #219541] Thu, 15 February 2007 10:25 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
You need to check your use of RECSTATUS, it's declaration is different in each table.

Re: guys oracle 10g migration problem..pls help :-(!! [message #219721 is a reply to message #219700] Thu, 15 February 2007 11:59 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Like Scorpio stated, the probleem seems to be in the different formats for recstatus. Just to help, I want to add an explanation to that.

First, you declare:
RecStatus                  IN      	CHAR

Okay. But in the table definitions, we see:
SQL> describe customer_log
RECSTATUS NOT NULL INTERVAL YEAR(2) TO MONTH

and:
SQL> DESCRIBE CUSTOMER
RECSTATUS CHAR(2)

And in various locations in your code, you use cl.recstatus (so, recstatus from the customer log, so the interval format) as if it's a char. Hence the error.

BTW this code is a bit weird overall, like stated previously, you OPEN and never seem to do anything with that...
Re: guys oracle 10g migration problem..pls help :-(!! [message #219723 is a reply to message #219721] Thu, 15 February 2007 12:12 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
skooman wrote on Thu, 15 February 2007 11:59
you OPEN and never seem to do anything with that...

Aha but RTC1 is an IN OUT refcursor. I don't know why it isn't just OUT though, maybe I'm missing something. The code isn't exactly formatted for readability though.

I liked this too:
spell128 wrote on Wed, 14 February 2007 21:44
i have converted the sql stored procedure codes to pl/sql.

Re: guys oracle 10g migration problem..pls help :-(!! [message #219763 is a reply to message #219541] Thu, 15 February 2007 20:06 Go to previous messageGo to next message
spell128
Messages: 7
Registered: February 2007
Location: asdas
Junior Member

guys...thanks for everything.. i managed to run the procedures without error..
sorry for my first post..as i realise it is unreadable. i changed the table. copied from customer table to customer_log which had actually solve the problem. But now here comes another one. i tried to execute the stored procedure.
      
SQL> execute customerget('a','a')
BEGIN customerget('a','a'); END;

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

--BELOW would be the package codes
CREATE OR REPLACE PACKAGE GLOBALPKG
AS
	TYPE RCT1 IS REF CURSOR;
	TRANCOUNT INTEGER := 0;
	IDENTITY INTEGER;
END;
/


i am not familiar at all with packages and i don't even know what argument should i put in based on this stored procedure.
could you guys help me out?? i believe its a problem with the argument but somewhere i am not able to solve it.
From what i know is, packages are actually a group of similiar stored procedures. But on this case, why do we need to use packages?It would be much easier for me without packages. PLEASE help me out guys.. and many many thanks to u all!!
Re: guys oracle 10g migration problem..pls help :-(!! [message #219764 is a reply to message #219763] Thu, 15 February 2007 20:23 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
You defined the procedure as
CREATE OR REPLACE PROCEDURE CustomerGet
    ( Customer    IN VARCHAR2 DEFAULT NULL
    , RecStatus   IN CHAR DEFAULT NULL
    , RCT1        IN OUT GLOBALPKG.RCT1 )

so when you call it you have to pass in the three things it says.

e.g. in SQL*Plus:

set autoprint on

var results refcursor

exec customerget('a', 'a', :results);

As you can see, you don't really pass anything in for the third argument, so I would have thought if could be plain OUT rather than IN OUT.

Packages make life simpler IMHO so I'm not really seeing your argument. They also make package-level types, variables and constants possible, and allow private code and overloading, to name a bunch of random advantages that come to mind.

[Updated on: Thu, 15 February 2007 20:26]

Report message to a moderator

Re: guys oracle 10g migration problem..pls help :-(!! [message #219766 is a reply to message #219541] Thu, 15 February 2007 20:42 Go to previous messageGo to next message
spell128
Messages: 7
Registered: February 2007
Location: asdas
Junior Member

IS IT POSSIBLE FOR ME not to use packages???can i ignore the package and deleting this codes?
RCT1        IN OUT GLOBALPKG.RCT1
OPEN RCT1 FOR

i don't see anyway why this would not work. But it prompts out an error requiring some 'INTO' statemend needed. If it is not possible, could u please tell me the changes to be made??
Re: guys oracle 10g migration problem..pls help :-(!! [message #219777 is a reply to message #219766] Thu, 15 February 2007 23:30 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Well you can re-write your procedure to (if the query returns all columns from customer in the right order):
CREATE OR REPLACE PROCEDURE CustomerGet
(
	Customer                   IN      	VARCHAR2 DEFAULT NULL,
	RecStatus                  IN      	CHAR DEFAULT NULL
)
AS  
	RCT1                       table of CUSTOMER%ROWTYPE;
BEGIN
SELECT * BULK COLLECT INTO RCT1
FROM (<YOUR_QUERY>);
END;
/

So you have a procedure. You call it, it performes the query however you are not able to use its result. So it just wastes time to provide nothing. Why to call this procedure then?

It would not be bad if you would specify FOR WHAT PURPOSE you want to use this query. However the usage (and its syntax in sqlplus) was already described to you by William Robertson.

I do not want to re-type the documentation pages, just read here about reasons why to use stored procedures.
Re: guys oracle 10g migration problem..pls help :-(!! [message #219825 is a reply to message #219766] Fri, 16 February 2007 04:06 Go to previous message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
spell128 wrote on Thu, 15 February 2007 20:42
IS IT POSSIBLE FOR ME not to use packages???can i ignore the package and deleting this codes?
RCT1        IN OUT GLOBALPKG.RCT1
OPEN RCT1 FOR

i don't see anyway why this would not work. But it prompts out an error requiring some 'INTO' statemend needed. If it is not possible, could u please tell me the changes to be made??

That depends on what you want it to do. What is GLOBALPKG.RCT1? It doesn't make sense to have a parameter called RCT1 which is of type RCT1, it's like having a parameter called 'INTEGER'. I don't know what you mean by 'deleting the codes'.
Previous Topic: Displaying records in Pages
Next Topic: Running Total
Goto Forum:
  


Current Time: Fri Dec 09 21:14:04 CST 2016

Total time taken to generate the page: 0.06814 seconds