科技行者

行者学院 转型私董会 科技行者专题报道 网红大战科技行者

知识库

知识库 安全导航

至顶网软件频道PL/SQL的小常识

PL/SQL的小常识

  • 扫一扫
    分享文章到微信

  • 扫一扫
    关注官方公众号
    至顶头条

  fact:   PL/SQL 9 ,PL/SQL 8 。

作者:中国IT实验室 来源:中国IT实验室 2007年9月30日

关键字: SQL 数据库 SQL Server

  • 评论
  • 分享微博
  • 分享邮件

  fact:
  PL/SQL 9 ,PL/SQL 8 ,PL/SQL 7
  
  symptom:
  1> Compiling PL/SQL fails
  2> ORA-06550: line %s, column %s:\n%s
  3> PLS-00801: internal error [%s]
  4> PLS-00801: internal Error [74901]
  
  change:
  NOTE ROLE: To reproduce: declare col number(2.0); begin null; end;
  
  cause:
  Incorrectly using a '.' instead of a ',' as a DECIMAL separator in the TYPE
  declaration of the PL/SQL code leads to this error. For example: as var1
  NUMBER (10.0); instead of var1 NUMBER (10,0);
  
  Bug:
  #637990
  PLS-801[74901] OCCURRED WHEN USED NUMBER(2.0)
  INSTEAD OF NUMBER (2,0)
  
  fix:
  This is still not fixed in PL/SQL Workaround: Change '.' to ',' and recompile
  the package. Example: From: var1 NUMBER (10.0); To: var1 NUMBER (10,0);
  
  fact:
  PL/SQL 9 ,PL/SQL 8 ,PL/SQL 7
  EXECUTE IMMEDIATE, DBMS_SQL
  
  symptom:
  1> Calling PROCEDURE in EXECUTE IMMEDIATE fails
  2> ORA-00900: invalid SQL statement
  
  cause:
  When using EXECUTE IMMEDIATE (native dynamic SQL), the SQL server expects
  a valid SQL statement. A PROCEDURE/FUNCTION call on its own is not a valid
  SQL statement (because it is PL/SQL), so the SQL server does not understand
  the statement. The SQL server can process a PL/SQL block, however.
  
  fix:
  Embed the call to the PROCEDURE in an anonymous block - i.e. put BEGIN ...
  END around it - and submit the anonymous block instead
  e.g.
  CREATE OR REPLACE procedure exec_proc1 (arg1 number, arg2 varchar2)
  IS
  BEGIN
  EXECUTE IMMEDIATE 'BEGIN proc1('||arg1||', '||arg2||'); END;';
  END;
  
  or
  
  CREATE OR REPLACE procedure exec_proc1 (arg1 number, arg2 varchar2)
  IS
  plsql_block := 'BEGIN proc1('||arg1||', '||arg2||'); END;';
  BEGIN
  EXECUTE IMMEDIATE plsql_block USING 7788, 500;
  END;
  
  Reference:
  PL/SQL User's Guide and Reference
  
  goal:
  How to verify the enabled roles for a session within a trigger or
  PL/SQL routine
  
  fact:
  Oracle Server - Enterprise Edition
  PL/SQL
  DBMS_SESSION
  
  fix:
  The view 'session_roles' lists the roles that are currently enabled to the user.
  When a PL/SQL routine, like a trigger, is executed, it is executed as the owner
  of the routine. Therefor querying the 'session_roles' view within a PL/SQL
  routine will not give the information of the executing user session.
  
  It is not possible to retrieve a list of enabled roles for a session within a
  PL/SQL routine. By using the DBMS_SESSION.IS_ROLE_ENABLED it is possible to
  check whether a role is enabled or not.
  
  Example:
  create or replace procedure ptst is
  begin
  if dbms_session.is_role_enabled('TEST') then
  dbms_output.put_line ('Role is enabled');
  else
  dbms_output.put_line ('Role is NOT enabled');
  end if;
  end;
  /
  
  Reference:
  : Function
  DBMS_SESSION.IS_ROLE_ENABLED
  
  fact:
  Oracle Server - Enterprise Edition 9.0.1
  Oracle Server - Enterprise Edition 8.1.6.2
  HP Alpha OpenVMS 7.3
  HP Tru64 UNIX 5.1
  compatible = 8.0.5
  
  symptom:
  1> Executing remote packaged PROCEDURE fails
  2> PLS-00801: internal error [%s]
  3> internal error [1407]
  4> No local or remote trace files generated
  
  cause:
  The cause of this problem is still undetermined.
  
  fix:
  Verify the version of the remote database and set the COMPATIBLE parameter of
  the remote database as close as possible to that of the local database.
  
  In this case setting compatible initSID.ora parameter to 8.1.6 from 8.0.5 on
  remote site solved the problem.
  
  fact:
  PL/SQL 9 ,PL/SQL 8 ,PL/SQL 7
  
  symptom:
  The following error occurs while inserting empty blob to return blob locators.
  
  ORA-22990: lob locator cannot span transaction
  
  cause:
  The above sql statements were executed within a for loop.
  After each fetch a call to dbms_lob.copy is made followed by commit within the loop.
  Fetching accross commits is not allowed since it results to cursor invalidation
  because the a COMMIT release any locks held by the session.
  
  From Oracle 8 Server Application Developer's Guide, Chapter 6, Large Objects:
  The insert statement automatically starts a transaction and locks the row.
  
  Once it has occurred, the locator may not be used outside the current
  transaction, since a COMMIT release any locks.
  Therefore any fetch after the lock will result in ORA-22990: LOB locators
  cannot span transactions.
  
  fix:
  It is not advisable to use a COMMIT inside a loop. Use commit after the
  loop ends.
  
  reference:
  Oracle 8 Server Application Developer's Guide
  
  fact:
  PL/SQL 9 ,PL/SQL 8 ,PL/SQL 7
  ORA-01001 results
  
  symptom:
  Preparing a cursor in a function, but executing it in another
  1>. Prepare cursor in a function
  2>. A function is called that opens the cursor and does a fetch.
  3>. The function in step 2 is called again to do another fetch
  and ORA-01001 results
  
  cause:
  What happened is that between calls to the function that does
  the fetch the cursor cache is overwritten and the prepared
  statement is being lost.
  
  Workaround:
  put the prepare, open, and fetch statements in one function.
  
  fact:
  JDBC OCI , pre-compiler
  ORA-01001: maximum open cursors exceeded
  
  symptom:
  The error message “ORA-01001: maximum open cursors exceeded”
  returned in a JDBC connection, even though the OPEN_CURSOR
  (default = 50) has been increased.
  
  cause:
  The number of cursors one client can open at a time on a connection
  is limited (50 is the default value).
  If you don't close these cursors explicitly, you will get this error
  eventually. Simply increasing the "OPEN_CURSORS" limit can help
  you avoid the problem for a while, but that just hides the problem, not
  solve it.
  
  Workaround:
  You do need to explicitly close the statement, by using the method
  stmt.close() in order to close and freeup the cursors. It is your responsibility
  to explicitly close out cursors that you no longer need.

查看本文来源

    • 评论
    • 分享微博
    • 分享邮件