执行存储过程预言机时出错"from keyword not found where expected"

Question

我有以下存储过程，我得到一个错误"从关键字未找到预期的地方"。在select查询中参数p_usr_tz的使用是否有任何问题?

我已经设置了p_usr_tz为'Europe/London'和p_wi_id为48127808

PROCEDURE getworkitembyid (p_wi_id IN NUMBER,p_usr_tz IN VARCHAR2
    , workitem OUT prvdr_rec  )
   AS
   BEGIN
      OPEN workitem FOR
         SELECT wi.id wi_id
              , wi.type_name
              , wi.status
              , wi.priority
              , wi.crt_usr_id
              , crt_usr.name crt_name
              , crt_usr.pass crt_pass
              , crt_usr.first_name crt_first_name
              , crt_usr.last_name crt_last_name
              , crt_usr.email crt_email
              , from_tz (cast ( (wi.crt_tm) as timestamp), 'America/New_York') at time zone p_usr_tz crt_tm
              , wi.updt_usr_id
              , updt_usr.name updt_name
              , updt_usr.pass updt_pass
              , updt_usr.first_name updt_first_name
              , updt_usr.last_name updt_last_name
              , updt_usr.email updt_email
              , from_tz (cast ( (wi.updt_tm) as timestamp), 'America/New_York') at time zone p_usr_tz updt_tm
              , wi.lock_usr_id
              , lock_usr.name lock_name
              , lock_usr.pass lock_pass
              , lock_usr.first_name lock_first_name
              , lock_usr.last_name lock_last_name
              , lock_usr.email lock_email
              , wi.lock_tm
              , queue.id queue_id
              , queue.name
              , queue.TYPE
              , queue_wi.state
              , queue_wi.queue_entry_tm
           FROM wi
                INNER JOIN sec_usr crt_usr
                   ON wi.crt_usr_id = crt_usr.id
                INNER JOIN sec_usr updt_usr
                   ON wi.updt_usr_id = updt_usr.id
                LEFT OUTER JOIN sec_usr lock_usr
                   ON wi.lock_usr_id = lock_usr.id
                LEFT OUTER JOIN queue_wi
                   ON wi.id = queue_wi.wi_id
                LEFT OUTER JOIN queue
                   ON queue_wi.queue_id = queue.id
          WHERE wi.id = p_wi_id;
   END getworkitembyid;

Answer 1

http://docs.oracle.com/cd/E11882_01/server.112/e26088/expressions007.htm#SQLRF00401

尽管Oracle文档不是很清楚，但AT TIME ZONE子句中的时区似乎不能是绑定变量;它必须是一个文字或表达式。

一个可能的解决方案，不是一个漂亮的，虽然，将使用本地动态SQL和使P_USR_TZ变量值在SELECT中"硬编码"，即

SQL> 
SQL> CREATE OR REPLACE
  2  PROCEDURE example_procedure
  3      ( p_some_date_value IN DATE
  4      , p_usr_tz          IN VARCHAR2
  5      , workitem          OUT SYS_REFCURSOR )
  6  AS
  7  BEGIN
  8      OPEN workitem FOR q'{
  9          SELECT :p_some_date_value AS original_date_value
 10           , from_tz (cast ( (:p_some_date_value) as timestamp), 'America/New_York') at time zone '}'||p_usr_tz||q'{' AS timezone_changed_date_value
 11          FROM dual
 12      }' USING
 13          IN p_some_date_value,
 14          IN p_some_date_value
 15      ;
 16  END example_procedure;
 17  /
Procedure created.
SQL> 
SQL> VAR example_cursor REFCURSOR;
SQL> 
SQL> EXEC example_procedure(sysdate, 'Europe/London', :example_cursor);
PL/SQL procedure successfully completed.
SQL> 
SQL> COL original_date_value FORMAT A30
SQL> COL timezone_changed_date_value FORMAT A45
SQL> 
SQL> PRINT :example_cursor;
ORIGINAL_DATE_VALUE            TIMEZONE_CHANGED_DATE_VALUE                      
------------------------------ ---------------------------------------------    
04.06.14                       04.06.14 17:32:07,000000 EUROPE/LONDON