Does TRUNCATE invalid package?

Every wondered in an Oracle Database if a TRUNCATE of a table would invalidate the dependent PL/SQL.????

Well the answer is NO.

Try out this code.

Create a table
create table tim_testing_truncate (x number, vc varchar2(100));

Now create a package with dependency to this table.
create or replace package tim_testing_truncate_pkg
as
procedure abc(p_x IN tim_testing_truncate.x%TYPE);
end tim_testing_truncate_pkg;
/
create or replace package body tim_testing_truncate_pkg
as
procedure abc(p_x IN tim_testing_truncate.x%TYPE)
IS
begin
execute immediate 'truncate table tim_testing_truncate';
end;
end tim_testing_truncate_pkg;
/

Now run the code
begin
tim_testing_truncate_pkg.abc(1);
end;
/

Now have a look at use_objects…

select * from user_objects where object_name like 'TIM_TESTING%' order by last_ddl_time asc;

What you’ll see is the status is still valid and the DDL time is still before the running of the procedure ABC.

TIM_TESTING_TRUNCATE TABLE 07/09/2015 13:20:26 07/09/2015 13:20:26 VALID
TIM_TESTING_TRUNCATE_PKG PACKAGE 07/09/2015 13:20:31 07/09/2015 13:20:31 VALID
TIM_TESTING_TRUNCATE_PKG PACKAGE BODY 07/09/2015 13:20:32 07/09/2015 13:20:32 VALID

Camping Fabulous, Ostia

Having been camping, we’ll living it up in a cabin, we struggled to find ….. tea towels.

Well we found a shop. Achilia is a little area near the camp. There is a shop there a little like the NZ  $2 shop, selling all sorts.  Here we found tea towels and also ice cube trays and freezer blocks to go with cooler bag from home.

That’s all for now.

Looking to Fake APP_USER in SQL Developer???

So, you are looking to check some code in SQL Developer but not wanting to use APEX and you need access to APP_USER.

First you need login into the schema that is the parsing user.  Next set the security group in  APEX.  Lastly set the G_USER variable in the APEX_APPLICATION package to the value you want and the app_id.

begin
apex_util.set_security_group_id 
    (p_security_group_id => APEX_UTIL.FIND_SECURITY_GROUP_ID('<WORKSPACE_NAME>'));
APEX_APPLICATION.G_USER := '<Username>';
APEX_APPLICATION.G_FLOW_ID := ;
END;
/

Now run this SQL, what you should see is the value set above.

SELECT V('APP_USER') FROM DUAL;

All done.

Application Express Show Hide using a checkbox

Have a look at the screen shot below. Using Click of the item and a JavaScript expression, $(‘#P141_ITEM_0’).prop(‘checked’). The ID for the item when APEX creates the item is two levels. The name of the item in APEX is P141_ITEM, the thing cliicked in the INPUT with the P141_ITEM_0,

<fieldset tabindex="-1" id="P141_ITEM" class="checkbox_group">
<legend class="hideMeButHearMe">Include Fx</legend>
<input type="checkbox" id="P141_ITEM_0" name="p_v04" value="Y"></fieldset>

This finds the value of “checked” from the item and does the show and hide.
Image

UTL_HTTP and ORA-12541

I recently hit this error while developing an OAuth client using PL/SQL in the database.

The solution turned out to be straight forward…. but could I find the answer using my favourite search engine? Nope.

If you get this error, the first thing to think about is whether you want your request to follow any redirects from the web server(s). By default, UTL_HTTP will attempt to follow 3 levels of redirect. If you don’t want to follow any redirects, include the following call before you perform the request.

  utl_http.set_follow_redirect(0);

In my case, this stopped the error and return a NULL response.
The other issue I had was wanting to read the HTML response when there was nothing to read, e.g. when the HTTP response code was not 200.
Below is a pseudo-code version of the example code given in the package specification for UTL_HTTP. I have modified the code, my extras are in bold.

DECLARE
  req utl_http.req;
  resp utl_http.resp;
  resp_name  VARCHAR2(256);
  resp_value VARCHAR2(1024);
  value VARCHAR2(32767);
  l_wallet_location VARCHAR2(200) := 'c:\oracle_home\mywallet';
BEGIN
  utl_http.set_follow_redirect(0);
  utl_http.set_wallet('file:'||g_wallet_location);
  
  req := utl_http.begin_request('http://www.google.com');
  resp := utl_http.get_response(req);

  FOR i IN 1..utl_http.get_header_count(resp) LOOP
    utl_http.get_header(resp, i, resp_name, resp_value);
    dbms_output.put_line(resp_name || ': ' || resp_value);
  END LOOP;

  if resp.status_code = 200 then

  LOOP
    utl_http.read_line(resp, value, TRUE);
    dbms_output.put_line(value);
  END LOOP;
  utl_http.end_response(resp);

EXCEPTION
    WHEN utl_http.end_of_body THEN
      utl_http.end_response(resp);
END;
/

These changes have allowed me to understand the original ORA-12541 error and what is really happening with the HTTP response from the web server.

I hope this helps someone else.