Oracle 全局临时表以及使用存储过程和函数
我们最近将我开发的数据库之一从 Oracle 帐户更改为 LDAP 登录帐户，对于访问系统的员工使用的前端来说，一切进展顺利。然而，我们有第二种仅限于管理人员的输入方法，用于将数据加载到数据库中，并且使用 dbms_scheduler 调用大量处理。
大多数数据库表都有一个created_by 列，默认从sys_context 中获取用户名，但是当从dbms_scheduler 运行数据加载时，此信息不可用，因此created_by 列全部填充为APP_GLOBAL。
我已经设法使用 sys_context 值填充全局临时表（GTT），并使用它从 dbms_scheduler 调用的存储过程填充created_by，因此我的下一个逻辑步骤是将其放入函数中并调用它，以便可以使用它整个系统，甚至可以从插入前触发器引用。
问题是，当将代码放入函数中时，找不到 GTT 中的数据。该表设置为保留行。
我已经在许多网站上寻找答案，但没有找到任何可以帮助我的东西，这里有人可以提供解决方案吗？
we recently changed one of the databases I develop on from Oracle accounts to LDAP login accounts and all went well for the front end used by the staff that access the system. However, we have a second method of entry restricted to admin staff that load the data onto the database and a lot of processing is called using the dbms_scheduler.
Most of the database tables have a created_by column which is defaulted to pick up their user name from a sys_context but when the data loads are run from dbms_scheduler this information is not available and hence the created_by columns all get populated with APP_GLOBAL.
I have managed to populate a Global Temporary Table (GTT) with the sys_context value and use this to populate the created_by from a stored procedure called by dbms_scheduler so my next logical step was to put this in a function and call it so it could be used throughout the system or even be referenced from a before insert trigger.
The problem is, when putting the code into a function the data from the GTT is not found. The table is set to preserve rows.
I have trawled many a site for an answer but have found nothing to help me can anyone here provide a solution?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
调度程序将使用与创建作业的会话不同的会话 -
保留行不会使 GTT 数据在不同的会话中可见。我假设
created_by列有一个默认值，如nvl(sys_context(...),'APP_GLOBAL')。考虑将用户名作为参数传递给作业，并将上下文设置为作业的第一步。The scheduler will be using a different session than the session that created the job -
preserve rowswill not make the GTT data visible in a different session.I am assuming the
created_bycolumns have a default value likenvl(sys_context(...),'APP_GLOBAL'). Consider passing the user name as a parameter to the job and set the context as the first step in the job.周末休息并仔细查看我的代码，发现我的语法存在致命缺陷，永远不会从 GTT 中选择数据。快速调整并重新编译，一切都很好。
杰克，谢谢你的帮助。
A weekend off and a closer look at my code showed a fatal flaw in my syntax where the selection of data from the GTT would never happen. A quick tweak and recompile and all is well.
Jack, thanks for your help.