CODEWITHSUNDEEP
oracle-apex
Ziad Adnan
Ziad Adnan

Reputation: 822

How can I execute DDL command CREATE or ALTER or DROP in oracle apex process?

I want to kill session from oracle apex form , how can I execute kill session command ,

I tried the following :

1- created an interactive report with form using this SELECT statement

select sid , serial# , blocking_session from v$session;

2- In the form linked with this grid I select SID and SERIAL

3- I created process and used the code :

begin
alter system kill session ''' ||  :P90_SID || ',' || :P90_SERIAL_H  || ''';
 end;

but I got the error :

ORA-06550: line 2, column 1: PLS-00103: Encountered the symbol "ALTER" when expecting one of the following: ( begin case declare exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge json_exists json_value json_query json_object json_array

how to solve this error and execute kill session ?

update :

I am using database link apex installed on 19c database and production database run on 10g database , the issue for this error was when I select the sessions i select using database link

select sid , serial# , blocking_session from v$session@kaash;

but when I run the command its try to kill the session on the current database and show the error ORA:00030 User session ID does not exist. how can I add the database link to kill session command @kaash ?

and thank you for professional ansewrs .

Upvotes: 0

Views: 188

Answers (2)

Koen Lostrie
Koen Lostrie

Reputation: 18650

In apex, the API APEX_STRING can be used to avoid multiple concatenations and make code a bit more readable. This does the same as @Littlefoots' answer.

DECLARE
  l_statement VARCHAR2(1000);
BEGIN
  l_statement := q'!ALTER SYSTEM KILL SESSION '%0,%1'!';
  l_statement := apex_string.format(l_statement, :P90_SID, :P90_SERIAL_H);
  apex_debug.info(
    p_message => q'#Ziad debug: Statement: %0#',
    p0        => l_statement);

  EXECUTE IMMEDIATE l_statement;
END;

This code can be execute as part of a page process after page submit or as part of a dynamic action, depending on the flow of your page.

-- update --

one way of debugging the code is the apex API APEX_DEBUG. Run the page in debug mode, check the debug logs and look for the string "Ziad debug".

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 142713

To me, it is easier to compose statement to be executed; doing that, it is simple to verify whether it looks OK or not. Then execute it.

I'd create a button on form page which runs a process that looks like this:

declare
  l_str varchar2(200);
begin
  l_str := 'alter system kill session ' || chr(39) || :P90_SID || ', '|| :P90_SERIAL_H || chr(39);
  execute immediate l_str;
end;

Of course, user (who pushes that button) has to have appropriate privileges. Not everyone is allowed to alter system.

Upvotes: 2

Related Questions