8 Replies Latest reply on Apr 11, 2012 10:20 AM by Gennady Sigalaev

    How can I get systimestamp in TT?

    915434
      Hi all,

      version:11.2
      OS: Windows 2003

      Now I want get timestamp in TT, However it doesn't support systimestamp in TT?

      How can I get timestamp in TT like 'SELECT systimestamp FROM DUAL' in oracle?

      Raul
        • 1. Re: How can I get systimestamp in TT?
          timvincent
          SYSTIMESTAMP is not yet implemented in TT, a workaround is:

          Command> select tt_sysdate from dual;
          < 2012-04-11 18:12:35.801000 >
          1 row found.

          Or you can also use sysdate.

          Tim
          • 2. Re: How can I get systimestamp in TT?
            915434
            Thank you Tim, I got it.
            • 3. Re: How can I get systimestamp in TT?
              915434
              Hi Tim,

              Now I meet a problem that tt_sysdate could not used in package.

              tt_table : create table Test(test_id number(2), test_time timestamp(4));

              Command> INSERT INTO Test values(1,tt_sysdate);
              Command> commit;
              Command> ONE ROW INSERTED.
              Command> SELECT * FROM Test;
              Command> <1, 2012-04-11 09:58:33.718000>
              1 row found.

              CREATE PACKAGE Test_Timestamp AS
              PROCEDURE Test_tt(testId number(2));
              END Test_Timestamp;

              Package created.

              CREATE PACKAGE BODY Test_Timestamp AS
              PROCEDURE Test_tt(testId number(2))
              IS
              INSERT INTO Test VALUES(testId ,tt_sysdate);
              COMMIT;
              END Test_tt;
              END Test_Timestamp ;

              Warning: Package body created with compilation errors.
              Command> show err
              Errors for Package body Test_Timestamp:

              LINE/COL ERROR
              10/13 PL/SQL: SQL Statement ignored
              12/16 PL/SQL: ORA-00984: column not allowed here


              So, why tt_sysdate could used in a single insert command but not in package?

              Thank you.
              • 4. Re: How can I get systimestamp in TT?
                timvincent
                You can get round this error by naming the columns in the insert, for example:

                create procedure timproc(testId number(2))
                as
                begin
                insert into Test (test_id, test_time) values (testId, tt_sysdate);
                end;
                • 5. Re: How can I get systimestamp in TT?
                  Gennady Sigalaev
                  Hi Raul Zhang,

                  Try this

                  Command> create table Test(test_id number(2), test_time timestamp(4));

                  Command> CREATE or replace PACKAGE Test_Timestamp AS
                  > PROCEDURE Test_tt (testId number);
                  > END Test_Timestamp;
                  > /

                  Package created.

                  Command> CREATE or replace PACKAGE BODY Test_Timestamp AS
                  >
                  > PROCEDURE Test_tt(testId number)
                  > IS
                  > begin
                  > insert into Test values (testId ,tt_sysdate);
                  > END Test_tt;
                  >
                  > END Test_Timestamp ;
                  > /

                  Warning: Package body created with compilation errors.

                  Command> show err
                  Errors for PACKAGE BODY TEST_TIMESTAMP:

                  LINE/COL ERROR
                  -------- -----------------------------------------------------------------
                  6/5 PL/SQL: SQL Statement ignored
                  6/38 PL/SQL: ORA-00984: column not allowed here

                  Command> CREATE or replace PACKAGE BODY Test_Timestamp AS
                  >
                  > PROCEDURE Test_tt(testId number)
                  > IS
                  > begin
                  > insert into Test values (testId , 'tt_sysdate');
                  > END Test_tt;
                  >
                  > END Test_Timestamp ;
                  > /

                  Package body created.

                  Command> show err;
                  No errors.
                  Command>

                  Best regards,
                  Gennady
                  • 6. Re: How can I get systimestamp in TT?
                    Gennady Sigalaev
                    Hi TIm,

                    I am getting the same error in your case (Naming column doesnt help):

                    Command> CREATE or replace PACKAGE BODY Test_Timestamp AS
                    >
                    > PROCEDURE Test_tt(testId number)
                    > IS
                    > begin
                    > insert into Test(test_id, test_time) values (testId , tt_sysdate);
                    > END Test_tt;
                    >
                    > END Test_Timestamp ;
                    > /

                    Warning: Package body created with compilation errors.

                    Command> show err;
                    Errors for PACKAGE BODY TEST_TIMESTAMP:

                    LINE/COL ERROR
                    -------- -----------------------------------------------------------------
                    6/5 PL/SQL: SQL Statement ignored
                    6/59 PL/SQL: ORA-00984: column not allowed here


                    Best regards,
                    Gena
                    • 7. Re: How can I get systimestamp in TT?
                      Chrisjenkins-Oracle
                      PL/SQL is Oracle code and uses the Oracle SQL parser. It does not understand any TimesTen specific syntax, types etc. If you want to use something like TT_SYSDATE within PL/SQL you need to do it via something like EXECUTE IMMEDAITE. The following works correctly:

                      PROCEDURE Test_tt(testId number)
                      IS
                      begin
                      EXECUTE IMMEDIATE 'insert into Test(test_id, test_time) values (testId , tt_sysdate)';
                      END;
                      /

                      Chris
                      • 8. Re: How can I get systimestamp in TT?
                        Gennady Sigalaev
                        Hi Chris,

                        You are right. We should use NDS or DBMS_SQL package for it.
                        This code works fine:

                        Command> CREATE or replace PACKAGE BODY Test_Timestamp AS
                        >
                        > PROCEDURE Test_tt(testId number)
                        > IS
                        > begin
                        > EXECUTE IMMEDIATE 'insert into Test values (:par , tt_sysdate)' using testId;
                        > END Test_tt;
                        >
                        > END Test_Timestamp ;
                        > /

                        Package body created.

                        Command> exec Test_Timestamp.Test_tt(1);
                        PL/SQL procedure successfully completed.

                        Command> select * from test;
                        < 1, 2012-04-11 07:12:38.9346 >
                        1 row found.
                        Command>

                        Best regards,
                        Gennady