Archive for May, 2007

F7: Handy SQL*Plus Shortcut Key

May 24, 2007 4 comments

F7 shortcut MenuWhen using SQL*Plus via the Command Prompt in Windows, you can press F7 on the keyboard at any time to view a list of all the recent commands that you have run (see image on the left). You can scroll up and down the list with the arrow keys and replay any of the commands using the Enter key.

This is a feature of the Command Prompt but is particularly useful when working with SQL*Plus.

One other handy feature of the Command Prompt (again with particular relevance to SQL*Plus) is that you can press the UP and DOWN arrows to cycle through a history of commands that you have issued.

Anyone know of any other nifty shortcuts for use in SQL*Plus?

Categories: SQL*Plus

ApEx: Setting session state from within a PL/SQL Package/Procedure/Function

May 17, 2007 7 comments

Within ApEx PL/SQL Processes, regions and items you can use the bind variable syntax (:PX_MY_ITEM) both to read and set the value of that item held in session state. Like so:

:PX_MY_ITEM := 'wibble';
l_my_local_variable := :PX_MY_ITEM;

However, in stored PL/SQL packages, procedures and functions you cannot use the bind variable syntax. Rather, you must use the v('PX_MY_ITEM') syntax. But this is read only.

So how do you set the value held in session state for a given page item from within a stored package, procedure or function?

The answer lies in the set_session_state procedure found in the APEX_UTIL package. E.g.

p_name => 'PX_MY_ITEM'
, p_value => 'wibble');

Categories: ApEx

“Return” and “Print” links on Printer Friendly pages

May 17, 2007 5 comments

A Printer Friendly version of any ApEx page can be viewed by accessing a URL of the form:


where the YES at the end of the URL tells ApEx that we want to view the page in Printer Friendly mode. This causes the page to be displayed using the Printer Friendly page template. This removes lots of the bits and pieces from the screen (such as navigation lists, breadcrumbs etc.) that we have no interest in printing. The user can then click the File menu in their browser and click “Print” to actually print the page. They can then click the “Back” button in their browser to return to the application proper. Great.

But wouldn’t it be nice if we could put our very own buttons/links on the Printer Friendly page to allow the user to print the page and then “go back” without having to use the browser’s File Menu or Back Button? Well, we can…

1) In Application Builder, Select Shared Components > User Interface > Templates
2) Find and click the “Printer Friendly” page template.
3) In the HEADER text area, add the following just before the </head> closing tag:

@media print
.screenOnly {display:none;}

This creates a CSS class called screenOnly which causes those HTML elements to which it is applied not to be rendered when printing.

4) Add the following to the very top of the BODY text area:

<!-- Display PRINT and GO BACK links -->
<a href="javascript:history.go(-1);" class="screenOnly">
<img src="&IMAGE_PREFIX.prev_arrow.gif" align="absmiddle" hspace="2">
Click here to return to the application

<a href="javascript:window.print();" class="screenOnly">
<img src="&IMAGE_PREFIX.print02.gif" align="absmiddle" hspace="2">
Click here to print this page
<hr class="screenOnly"/>
<!--End display of PRINT and GO BACK links -->

Click “Apply” to save your changes.

5) Now run any of your pages in Printer Friendly mode. You’ll see a bar at the top of the page looking like this:

Print Links Screen Capture

Clicking the Print icon will open your browser’s print dialog. Clicking the Go Back link will take you back to your application. And, what’s more, when you print this page neither of these links or the the Horizontal Rule that’s just below them will appear on the printed page spoiling the layout (this is achieved through the screenOnly CSS class applied to the <a> and <hr> tags).

As an example, go to any page in my sample app (such as here: and click the Print link in the top right of the page.

Categories: ApEx, CSS

Gotcha: Privileges, Roles and Procedures

May 14, 2007 1 comment

I came across a phenomenon the other to do with granting privileges via a role:

I had two users, A and B and a role which allowed SELECT on A’s tables. I granted this role to B. As you’d expect, when logged in as user B, I could SELECT FROM A’s tables. Great. I then created a procedure in B’s schema which referenced a table in A’s schema… and it wouldn’t compile. It was complaining that the “table or view does not exist”. But that made no sense since this was the very same table that I was SELECTing FROM successfully when logged in as B. So why was this happening?

The following SQL Plus session demonstrates this:

SQL> connect system@orcl
Enter password:
SQL> create user a identified by a quota 1m on users;

User created.

SQL> grant create table, create session to a;

Grant succeeded.

SQL> create user b identified by b quota 1m on users;

User created.

SQL> grant create session, create table, create procedure to b;

Grant succeeded.

SQL> create table a.a_table (col1 varchar2(10));

Table created.

SQL> insert into a.a_table values(‘testing123’);

1 row created.

SQL> commit;

Commit complete.

SQL> create role selecter;

Role created.

SQL> grant select on a.a_table to selecter;

Grant succeeded.

SQL> grant selecter to b;

Grant succeeded.

SQL> create or replace procedure b.print_a_table
2 is
3 begin
4 for x in (select * from a.a_table)
5 loop
6 dbms_output.put_line(x.col1);
7 end loop;
8 end;
9 /

Warning: Procedure created with compilation errors.

SQL> sho err

——– —————————————————————–
4/10 PL/SQL: SQL Statement ignored
4/26 PL/SQL: ORA-00942: table or view does not exist
6/1 PL/SQL: Statement ignored
6/22 PLS-00364: loop index variable ‘X’ use is invalid

SQL> connect b/b@orcl;

SQL> select * from a.a_table;


SQL> connect system@orcl
Enter password:
SQL> grant select on a.a_table to b;

Grant succeeded.

SQL> create or replace procedure b.print_a_table
2 is
3 begin
4 for x in (select * from a.a_table)
5 loop
6 dbms_output.put_line(x.col1);
7 end loop;
8 end;
9 /

Procedure created.

As you can see above, granting SELECT on A’s table directly to B (rather than via a role) solved the problem.

But why?! Why doesn’t the privilege granted via the role work? Well, after a little digging and forum-posting I found the answer here:

“5.2.5 PL/SQL Blocks and Roles

The use of roles in a PL/SQL block depends on whether it is an anonymous block or a named block (stored procedure, function, or trigger), and whether it executes with definer’s rights or invoker’s rights. Named Blocks with Definer’s Rights

All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer’s rights. Roles are not used for privilege checking and you cannot set roles within a definer’s rights procedure.”

So there you have it. You just can’t use roles in this way. Everyday’s a schoolday…

Categories: PL/SQL