Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00382: expression is of wrong type
PLS-00382: expression is of wrong type [message #250169] Mon, 09 July 2007 00:26 Go to next message
mandesh
Messages: 2
Registered: July 2007
Junior Member
Hi,

I am new to strings and procedures and am getting this error message when trying to create a procedure.

the code is as below -

CREATE OR REPLACE FUNCTION CREATE_VIEW_FT_OB_DTLS (REPORTDATE IN DATE) RETURN NUMBER IS
SQL_STMT VARCHAR2(1000);
BEGIN
BEGIN
SQL_STMT:= 'CREATE OR REPLACE VIEW VW_FT_OB_DETAILS'
|| ' AS SELECT '
|| 'PID,'
|| 'TRANSACTION_AMOUNT TX_AMOUNT,'
|| 'TRANSACTION_DATE TXN_DATE,'
|| 'TRANSACTION_TYPE TXN_TYPE,'
|| 'FROM '
|| 'TABLE_TEMP'
|| 'WHERE '
|| 'TRANSACTION_DATE BETWEEN TRUNC(TO_DATE('
|| ''''
|| REPORTDATE
|| ''''
|| ' ,''MM/DD/YYYY''),''MONTH'') AND LAST_DAY('
|| ''''
|| REPORTDATE
|| ''''
|| ')';

EXCEPTION
WHEN OTHERS THEN
ORA_RAISERROR (SQLCODE,'ERROR WHILE ASSIGNING VALUE', 40);
END;
BEGIN
EXECUTE IMMEDIATE SQL_STMT;
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
ORA_RAISERROR (SQLCODE,'ERROR WHILE EXECUTING STATEMENT',50);
END;

EXCEPTION
WHEN OTHERS THEN
ORA_RAISERROR (SQLCODE,'ERROR WHILE CREATING VIEW',50);

END;
/


I am getting error -

ORA-06550: line 6, column 17:
PLS-00382: expression is of wrong type
ORA-06550: line 6, column 3:
PL/SQL: Statement ignored

Can you please help me?

Re: PLS-00382: expression is of wrong type [message #250171 is a reply to message #250169] Mon, 09 July 2007 00:32 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>ORA-06550: line 6, column 17:
OK, I give up wha is "line 6, column 17:"


Please follow posting guidelines as spcified is STICKY posts at top of this forum.

[Updated on: Mon, 09 July 2007 00:34] by Moderator

Report message to a moderator

Re: PLS-00382: expression is of wrong type [message #250215 is a reply to message #250169] Mon, 09 July 2007 03:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
No space between "from" and tablename and between tablename and "where".

Besides, why don't you just use your where-clause on the view itself, instead of creating a view based on each different date?
Re: PLS-00382: expression is of wrong type [message #250225 is a reply to message #250169] Mon, 09 July 2007 03:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
a "," just before FROM.

And so on.

Regards
Michel
Re: PLS-00382: expression is of wrong type [message #250279 is a reply to message #250169] Mon, 09 July 2007 07:56 Go to previous message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
Why are you using a TO_DATE function on report_date, which is to be used on character string, in one part of the WHERE clause and then using a function (ie. last_day) which requires a DATE type in the other part?
Previous Topic: I want to get output of my procedure which is a string in Bold Can any one can help me
Next Topic: Passing variables in cursor
Goto Forum:
  


Current Time: Wed Dec 07 10:47:40 CST 2016

Total time taken to generate the page: 0.08118 seconds