Solved

Trigger to run CREATE USER script

Posted on 1998-09-17
3
2,120 Views
Last Modified: 2012-06-21
How can I create a trigger that would allow ORACLE forms to do the equivalent of the sql command:

CREATE USER username IDENTIFIED BY userpsswd DEFAULT TABLESPACE filename TEMPORARY TABLESPACE temp;
GRANT fileuser to username;

I basically want to press a button on a form and have this user created in my user table and granting file access to that user, rather than having to do this separetely in a sql application.

Appreciate any help.

Thanks
0
Comment
Question by:tgbekele
  • 2
3 Comments
 
LVL 3

Accepted Solution

by:
junfeb earned 50 total points
ID: 1081608
Here are ORacle bulletins which explain how to do ddl statements from forms -

The following are ddl commands -

ALTER CLUSTER
ALTER DATABASE
ALTER FUNCTION
ALTER INDEX
ALTER PACKAGE
ALTER PROCEDURE
ALTER PROFILE
ALTER RESOURCE COST
ALTER ROLE
ALTER ROLLBACK SEGMENT
ALTER SEQUENCE
ALTER SNAPSHOT
ALTER SNAPSHOT LOG
ALTER TABLE
ALTER TABLESPACE
ALTER TRIGGER
ALTER USER
ALTER VIEW
ANALYZE
COMMENT
CREATE CLUSTER
CREATE CONTROLFILE
CREATE DATABASE
CREATE DATABASE LINK
CREATE FUNCTION
CREATE INDEX
CREATE PACKAGE
CREATE PACKAGE BODY
CREATE PROCEDURE
CREATE PROFILE
CREATE ROLE
CREATE ROLLBACK SEGMENT
CREATE SCHEMA
CREATE SEQUENCE  
CREATE SNAPSHOT
CREATE SNAPSHOT LOG
CREATE SYNONYM
CREATE TABLE
CREATE TABLESPACE
CREATE TRIGGER
CREATE USER
CREATE VIEW
DROP CLUSTER
DROP DATABASE LINK
DROP FUNCTION
DROP INDEX
DROP PACKAGE
DROP PROCEDURE
DROP PROFILE
DROP ROLE
DROP ROLLBACK SEGMENT
DROP SEQUENCE
DROP SNAPSHOT
DROP SNAPSHOT LOG
DROP SYNONYM
DROP TABLE
DROP TABLESPACE
DROP TRIGGER
DROP USER
DROP VIEW
GRANT
NOAUDIT
RENAME
REVOKE  
TRUNCATE


+==+

Diagnostics and References:

 * {407.6,Y,100}     ISSUING SQL DATA DEFINITION LANGUAGE (DDL)
 * {8152.4,Y,100}     USING ORACLE FORMS 4.X
 * {8188.4,Y,100}     PL/SQL ERROR 103 (PLS-103)


2. Soln# 2009805.6  MUST USE FORMS_DDL() BUILT-IN TO ISSUE DDL FROM FORMS P

Solution ID         : 2009805.6
For Problem         : 1003748.6
Affected Platforms  : Generic: not platform specific
Affected Products   : SQL*Forms
Affected Components : SF40 V04.XX
Affected Oracle Vsn : V07.XX

Summary:
MUST USE FORMS_DDL() BUILT-IN TO ISSUE DDL FROM FORMS PL/SQL CODE

+=+

Solution Description:  
=====================  
 
You must use the Oracle Forms built-in FORMS_DDL to include Data Definition
Language commands (DDL) statements in Forms PL/SQL code.
 
For example:
 
DECLARE
  ddl_stmt VARCHAR2(2000);
BEGIN
 
  /* INCORRECT:                                                   */
  /* The following DDL will cause PL/SQL Error 103 during compile */
 
  CREATE TABLE tmp ( a NUMBER, b NUMBER );
 
  /* CORRECT:                                                      */
  /* The following DDL reference will compile successfully         */
 
  ddl_stmt := '( CREATE TABLE tmp ( a NUMBER, b NUMBER )';
  FORMS_DDL( ddl_stmt );
 
END;
 
 
Solution Explanation:  
=====================
 
You cannot directly issue SQL DDL commands in Forms PL/SQL code.  You can
directly issue SQL Data Manipulation Language (DML) commands ( SELECT, INSERT,
UPDATE, DELETE for example ).
 
The FORMS_DDL built-in is not available prior to Oracle Forms 4.0.12.X.
 
 
----------------------------------------------------------------------------------


1. Prob# 1015153.4  USING FORMS_DDL TO ISSUE DYNAMIC SQL STATEMENTS FROM OR
2. Soln# 2063401.4  USE FORMS_DDL() BUILT-IN


1. Prob# 1015153.4  USING FORMS_DDL TO ISSUE DYNAMIC SQL STATEMENTS FROM OR

Problem ID          : 1015153.4
Affected Platforms  : Generic: not platform specific
Affected Products   : SQL*Forms
Affected Components : SF40 V04.XX
Affected Oracle Vsn : Generic

Summary:
USING FORMS_DDL TO ISSUE DYNAMIC SQL STATEMENTS FROM ORACLE FORMS

+=+

Problem Description:
====================
 
How can I issue dynamic SQL statements, including server-side PL/SQL, DDL and
DML, directly from an Oracle Forms application?
 
 
Problem Explanation:
====================
 
Using Record Groups and the Create_Group_From_Query built-in, the Designer  
can already retrieve and process the result set of a dynamically prepared
SELECT statement.  Forms already supports direct calls to stored procedures,
functions, and packages.  However, until Oracle Forms 4.0.12 and later, there
has been no way to issue SQL statements which were prepared dynamically.
 
 
 
[ Search Words:  
 
server-side PL/SQL, server side,dynamic,FORMS_DDL,DDL,data definition language  
ALTER CLUSTER ALTER DATABASE ALTER FUNCTION ALTER INDEX ALTER PACKAGE  
ALTER PROCEDURE ALTER PROFILE ALTER RESOURCE COST ALTER ROLE ALTER ROLLBACK
SEGMENT ALTER SEQUENCE ALTER SNAPSHOT ALTER SNAPSHOT LOG ALTER TABLE  
ALTER TABLESPACE ALTER TRIGGER ALTER USER ALTER VIEW ANALYZE COMMENT  
CREATE CLUSTER CREATE CONTROLFILE CREATE DATABASE CREATE DATABASE LINK  
CREATE FUNCTION CREATE INDEX CREATE PACKAGE CREATE PACKAGE BODY  
CREATE PROCEDURE CREATE PROFILE CREATE ROLE CREATE ROLLBACK SEGMENT  
CREATE SCHEMA CREATE SEQUENCE  CREATE SNAPSHOT CREATE SNAPSHOT LOG  
CREATE SYNONYM CREATE TABLE CREATE TABLESPACE CREATE TRIGGER CREATE USER  
CREATE VIEW DROP CLUSTER DROP DATABASE LINK DROP FUNCTION DROP INDEX  
DROP PACKAGE DROP PROCEDURE DROP PROFILE DROP ROLE DROP ROLLBACK SEGMENT  
DROP SEQUENCE DROP SNAPSHOT DROP SNAPSHOT LOG DROP SYNONYM DROP TABLE  
DROP TABLESPACE DROP TRIGGER DROP USER DROP VIEW GRANT NOAUDIT RENAME  
REVOKE  TRUNCATE
 
]

+==+

Diagnostics and References:

 * {4493.6,Y,100}     DYNAMIC SQL
 * {8152.4,Y,100}     USING ORACLE FORMS 4.X


2. Soln# 2063401.4  USE FORMS_DDL() BUILT-IN

Solution ID         : 2063401.4
For Problem         : 1015153.4
Affected Platforms  : Generic: not platform specific
Affected Products   : SQL*Forms
Affected Components : SF40 V04.XX
Affected Oracle Vsn : Generic

Summary:
USE FORMS_DDL() BUILT-IN

+=+

Solution Description:
=====================
 
A built-in command has been added to allow the designer to issue dynamic SQL
statements at runtime, including server-side PL/SQL and DDL. Valid syntax is:
 
         Forms_DDL( statement );
 
Where 'statement' can be any string expression up to 32K.  For example, the
string argument can be a string literal:
 
        Forms_DDL('create table temp( n number )');
 
or can be a string expression or variable:
 
         --
         -- Create a table with N Number columns
         -- TEMP( COL1, COL2, ..., COLN )
         --
         PROCEDURE Create_N_Column_Number_Table( n NUMBER ) IS
           my_stmt VARCHAR2(2000);
 
         BEGIN
           my_stmt := 'create table tmp(COL1 NUMBER';
 
           FOR i IN 2..n LOOP
             my_stmt := my_stmt||',COL'||TO_CHAR(i)||' NUMBER';
           END LOOP;
 
           my_stmt := my_stmt||')';
 
           --
           -- Now create the table...
           --
           Forms_DDL( my_stmt );
 
           IF NOT Form_Success THEN
             Message('Table Creation Failed');
           ELSE
             Message('Table Created.');
           END IF;
 
         END;
 
It can even be a block of dynamically created PL/SQL code:
 
         DECLARE
           procname VARCHAR2(30);
         BEGIN
           IF :global.flag = 'TRUE' THEN
             procname := 'Assign_New_Employer';
           ELSE
             procname := 'Update_Current_Employer';
           END IF;
 
           Forms_DDL('Begin '|| procname ||'; End;');
 
           IF NOT Form_Success THEN
             Message('Employee Maintenance Failed');
           ELSE
             Message('Employee Maintenance Successful');
           END IF;
 
         END;
 
 
Solution Explanation:
=====================
 
(1) Single DML statements executed via Forms_DDL should NOT contain a trailing
    semicolon, otherwise "ORA-00911: Invalid character" will result.
 
(2) Any valid PL/SQL block may be executed, and *should* include semicolons  
    where appropriate. PL/SQL statements, including calls to stored  
    procedures, must be enclosed in a valid BEGIN/END block structure. There
    is no need to end the PL/SQL block with a slash '/' character.  While  
    perhaps improving readability of code, there is no need to pay attention
    to line breaks in the string which you pass as a command to Forms_DDL().
 
(3) A successful outcome of Forms_DDL() sets the Form_Success boolean function
    to TRUE. Conversely, a failed outcome sets Form_Failure to TRUE. Use these
    functions to check whether the statement issued via Forms_DDL() executed  
    correctly. In the case that the statement was not successful, you have
    access to the error code and error text with the Dbms_Error_Code and
    Dbms_Error_Text built-in functions, respectively. Note that the
    Dbms_Error_Code and Dbms_Error_Text functions are not (re)set upon
    successful execute of Forms_DDL (or any SQL statement in Forms, for that
    matter), so you should only check their value when you know that an error
    has occurred.
 
(3) The statement passed as an argument to Forms_DDL() may NOT contain bind
    variable references in the string, but the *values* of bind variables can
    be concatenated into the string before passing the result to Forms_DDL.  
    For example the following is NOT supported:
 
        Forms_DDL('Begin Update_Employee(:EMP.EMPNO); End;');
 
    But the following *is* valid:
 
        Forms_DDL('Begin Update_Employee('||TO_CHAR(:EMP.EMPNO)||'; End;');
 
    and would have the same effect. Recall that since Forms 4.0 and PL/SQL
    support calling stored procedures directly, this could also be written  
    more simply:
 
        Update_Employee(:EMP.EMPNO);
 
    and would make better use of Oracle7's shared SQL area over multiple
    executions with different values for EMP.EMPNO.  In this example, the only
    benefit of Forms_DDL is that the PL/SQL block calling the stored procedure
    could be dynamically constructed as a string.
 
    A corollary to this note is that SQL statements and/or PL/SQL blocks  
    executed via Forms_DDL() cannot return results to Forms directly. Using
    the built-in support for Stored Procedures, Functions, and Packages,
    however, you can easily retrieve OUT parameters and Function return values
    so this fact is not an issue in general.
 
(4) Realize that all DDL operations issue an implicit COMMIT and will end the
    current transaction without giving Forms a chance to process any pending
    changes. Issuing DDL commands using Forms_DDL() while a form has pending
    changes to commit, is emphatically discouraged and could have  
    unpredictable results on the pending changes in the form. Make sure all
    pending changes in the form are committed or rolled back before issuing
    such commands.
 
(5) Realize that some pre-supplied stored procedures issue COMMIT and/or  
    ROLLBACK commands within their logic.  Executing these stored subprograms
    will end the current transaction without giving Forms a chance to process
    any pending changes. Make sure all pending changes in the form are  
    committed or rolled back before calling such stored subprograms.
 
(6) You can check the SYSTEM.FORM_STATUS variable to see if there are pending
    changes in the current form before issuing the DDL statement to make  
    certain you will not inadvertently lose any pending locks.
 
    Here is an example procedure which accepts a SQL statement
    string to execute, and returns the result as a numeric code.
 
         FUNCTION Do_Sql( stmt VARCHAR2, check_for_locks BOOLEAN := TRUE )
         RETURN NUMBER
         --
         -- Example using Forms_DDL built-in
         --
         -- Issue the SQL statement passed in as
         -- an argument, and return a number representing
         -- the outcome of the execution. We define
         -- a zero (0) result to be success.
         --
         IS
           SQL_SUCCESS CONSTANT NUMBER := 0;
         BEGIN
 
           IF stmt IS NULL THEN
             Message('DO_SQL: Passed a null statement.');
             RETURN SQL_SUCCESS;
           END IF;
 
           IF Check_For_Locks AND :System.Form_Status = 'CHANGED' THEN
             Message('DO_SQL: Form has outstanding locks pending.');
             RETURN SQL_SUCCESS;
           END IF;
 
           Forms_DDL(stmt);
 
           IF Form_Success THEN
             RETURN SQL_SUCCESS;
           ELSE
             RETURN Dbms_Error_Code;
           END IF;
 
         END;
 
 Hope this helps.
Thanks




0
 

Author Comment

by:tgbekele
ID: 1081609
I will go over some of this material.
Thank you very much JUNFEB.
0
 

Author Comment

by:tgbekele
ID: 1081610
I have tried to get this working using the above suggestion -

DECLARE
      ddl_stmt VARCHAR2(2000);
    BEGIN
     
      /* INCORRECT:                                                   */
      /* The following DDL will cause PL/SQL Error 103 during compile */
     
      CREATE TABLE tmp ( a NUMBER, b NUMBER );
     
      /* CORRECT:                                                      */
      /* The following DDL reference will compile successfully         */
     
      ddl_stmt := '( CREATE TABLE tmp ( a NUMBER, b NUMBER )';
      FORMS_DDL( ddl_stmt );
     
    END;

Using the correct version, ofcourse - I am no longer getting compilation error messages, but when the button is pressed, I have found that the users I am adding are not really added to the table, and putting a message prompt to see if it is actually doing the adduser function and grant to user function, I have found that neither is happening.
Any help much appreciated.

Thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now