Home » SQL & PL/SQL » SQL & PL/SQL » Timestamp Error
Timestamp Error [message #303450] Fri, 29 February 2008 06:59 Go to next message
saharookiedba
Messages: 56
Registered: September 2007
Location: PUNE
Member
Hi, i have been trying to create a procedure
as below:-


create or replace
PROCEDURE sp_release_all_software(In_UserID users.ID%TYPE, In_PCIDpc.IDTYPE ) IS
temp_ReleaseTimeStamp VARCHAR2(100);
i NUMBER := 0;

BEGIN


SELECT CURRENT_TIMESTAMP INTO temp_ReleaseTimeStamp FROM DUAL;

FOR i IN 1..ARRAY_LICDET.LAST LOOP
UPDATE installation SET ReleaseTimeStamp = temp_ReleaseTimeStamp
WHERE UserID = In_UserID
AND PCID = In_PCID
AND LicenseDetailID = ARRAY_LICDET(i) ;
END LOOP;

END IF;
END sp_release_all_software;

When i try to compile this i get the following error :
SQL> sho err
Errors for PROCEDURE SP_RELEASE_ALL_SOFTWARE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
37/6 PL/SQL: SQL Statement ignored
37/13 PL/SQL: ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-488: invalid variable declaration: object
'TIMESTAMP' must be a type or subtype

Please help me out..
Re: Timestamp Error [message #303452 is a reply to message #303450] Fri, 29 February 2008 07:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Please help me out..

Please FIRST read OraFAQ Forum Guide, especially "How to format your post?" section as already asked to you.
And copy and paste the execution, don't FAKE it. There are not 37 lines in your code.

Also post your Oracle version (4 decimals).

Regards
Michel

Re: Timestamp Error [message #303481 is a reply to message #303452] Fri, 29 February 2008 08:54 Go to previous messageGo to next message
saharookiedba
Messages: 56
Registered: September 2007
Location: PUNE
Member
Hi Micheal,
sorry for the mistake...

Here's the actual code,,
I was trying to remove the part which is unnecessary..


create or replace PROCEDURE sp_release_all_software(In_UserID users.ID%TYPE,
                                                    In_PCID pc.ID%TYPE,
                                                    In_InputString VARCHAR
                                                    ) IS
temp_inputString VARCHAR2(50);
temp_ReleaseTimeStamp VARCHAR2(100);
--temp varchar2(10) := 'SELECT Current_TimeStamp FROM dual';
i NUMBER := 0;
Flag VARCHAR2(5) := 'False';

TYPE ARRAY_LICDET_TYPE IS TABLE OF VARCHAR2(500)
INDEX BY BINARY_INTEGER;
ARRAY_LICDET ARRAY_LICDET_TYPE ;

BEGIN

 temp_inputString := In_InputString;
 dbms_output.put_line(temp_inputString);
 IF temp_inputString IS NOT NULL THEN
   dbms_output.put_line(temp_inputString);
    WHILE Flag ='False' LOOP
     IF substr(temp_inputString,1,instr(temp_inputString,',')-1) IS NOT NULL THEN
         ARRAY_LICDET(i) := substr(temp_inputString,1,instr(temp_inputString,',')-1);
         dbms_output.put_line(ARRAY_LICDET(i));
         temp_inputString := substr(temp_inputString,instr(temp_inputString,',')+1);
         i := i+1;
     ELSE
         ARRAY_LICDET(i) := temp_inputString;
         --dbms_output.put_line(Array_rolename(i));
         Flag := 'TRUE';
     END IF;
    END LOOP;

  SELECT CURRENT_TIMESTAMP INTO temp_ReleaseTimeStamp FROM DUAL;

   FOR i IN 1..ARRAY_LICDET.LAST LOOP
     UPDATE installation SET ReleaseTimeStamp = temp_ReleaseTimeStamp
     WHERE UserID = In_UserID
     AND PCID = In_PCID
     AND LicenseDetailID = ARRAY_LICDET(i) ;
    END LOOP;

  END IF;
END sp_release_all_software;



Explanation:-
In the above code i am getting a comma seperated string like ('12,34,15') along with two other in inputs..
I am breaking the list into values...... inserting them into array & Updating table Installation..

Error that i am getting:-

LINE/COL ERROR
-------- -----------------------------------------------------------------
37/6 PL/SQL: SQL Statement ignored
37/13 PL/SQL: ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-488: invalid variable declaration: object
'TIMESTAMP' must be a type or subtype


I hope this information is adequate..
Thanking in advance..
Re: Timestamp Error [message #303482 is a reply to message #303481] Fri, 29 February 2008 08:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why do you select current_timestamp into a varchar2 variable?
Re: Timestamp Error [message #303517 is a reply to message #303482] Fri, 29 February 2008 12:16 Go to previous messageGo to next message
saharookiedba
Messages: 56
Registered: September 2007
Location: PUNE
Member
Thanks Frank,

But i have tried Declaring
temp_ReleaseTimeStamp as Timestampas below:-

create or replace
PROCEDURE sp_release_all_software(In_UserID users.ID%TYPE,
                                                    In_PCID pc.ID%TYPE,
                                                    In_InputString VARCHAR
                                                    ) IS
temp_inputString VARCHAR2(50);
temp_ReleaseTimeStamp TIMESTAMP;
--temp varchar2(10) := 'SELECT Current_TimeStamp FROM dual';
i NUMBER := 0;
Flag VARCHAR2(5) := 'False';

TYPE ARRAY_LICDET_TYPE IS TABLE OF VARCHAR2(500)
INDEX BY BINARY_INTEGER;
ARRAY_LICDET ARRAY_LICDET_TYPE ;

BEGIN

 temp_inputString := In_InputString;
 dbms_output.put_line(temp_inputString);
 IF temp_inputString IS NOT NULL THEN
   dbms_output.put_line(temp_inputString);
    WHILE Flag ='False' LOOP
     IF substr(temp_inputString,1,instr(temp_inputString,',')-1) IS NOT NULL THEN
         ARRAY_LICDET(i) := substr(temp_inputString,1,instr(temp_inputString,',')-1);
         dbms_output.put_line(ARRAY_LICDET(i));
         temp_inputString := substr(temp_inputString,instr(temp_inputString,',')+1);
         i := i+1;
     ELSE
         ARRAY_LICDET(i) := temp_inputString;
         --dbms_output.put_line(Array_rolename(i));
         Flag := 'TRUE';
     END IF;
    END LOOP;

  SELECT CURRENT_TIMESTAMP INTO temp_ReleaseTimeStamp FROM DUAL;

   FOR i IN 1..ARRAY_LICDET.LAST LOOP
     UPDATE installation SET ReleaseTimeStamp = temp_ReleaseTimeStamp
     WHERE UserID = In_UserID
     AND PCID = In_PCID
     AND LicenseDetailID = ARRAY_LICDET(i) ;
    END LOOP;

  END IF;
END sp_release_all_software;


but still it is giving the same error...
Re: Timestamp Error [message #303542 is a reply to message #303517] Fri, 29 February 2008 13:48 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
What version of Oracle? TIMESTAMP wasn't available in 8i.

And I am not quite sure I trust you. You posted an error message about TIMESTAMP earlier, yet nowhere in your code was TIMESTAMP used.
Ealier than that, you posted an error reported at line 37, yet your code didn't have 37 lines.

Why don't you cut and paste an entire session rather than try to "remember" what you "think" you did?

[Updated on: Fri, 29 February 2008 13:50]

Report message to a moderator

Re: Timestamp Error [message #303611 is a reply to message #303542] Sat, 01 March 2008 02:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
And, in addition to joy_divisions request: post the structure of your INSTALLATION table.
Re: Timestamp Error [message #304387 is a reply to message #303542] Wed, 05 March 2008 01:00 Go to previous message
saharookiedba
Messages: 56
Registered: September 2007
Location: PUNE
Member
Dear Joy,
Thanks for your response,
But i am not faking anything

To your question,
Quote:
You posted an error message about TIMESTAMP earlier, yet nowhere in your code was TIMESTAMP used.


This was because i tried declaring the variable,
temp_ReleaseTimeStamp
as VARCHAR & TIMESTAMP both, but on both occasions it was giving the same error...


Your next question
Quote:
Ealier than that, you posted an error reported at line 37, yet your code didn't have 37 lines.


This was because, i was trying to paste only that part of the code which i thought was causing the error.
In my subsequent posts i have pasted the entire code.

Below is the structure of the INSTALLATION TABLE.

desc installation
Name Null Type
------------------------------ -------- --------
ID NOT NULL NUMBER
PCID NUMBER
LICENSEDETAILID NUMBER
USERID NUMBER
ADMINUSERID NUMBER
TIMESTAMP TIMESTAMP(6)
RELEASETIMESTAMP TIMESTAMP(6)

7 rows selected

Thank you in advance...

Previous Topic: Help to bring Closing Balance from this SQL
Next Topic: comparing 2 tables
Goto Forum:
  


Current Time: Sat Dec 10 20:18:19 CST 2016

Total time taken to generate the page: 0.12792 seconds