Home » Developer & Programmer » Forms » Problem in connecting to other database user inside a form (merged 2) (10g)
Problem in connecting to other database user inside a form (merged 2) [message #546687] |
Thu, 08 March 2012 02:59 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
emadnabil
Messages: 179 Registered: August 2007
|
Senior Member |
|
|
Dear all
i have a problem concerning change of the connection inside a form
i have this code in when validate item trigger
LOGOUT ;
LOGON ('FIFES_HN','FIFES_HN@PRD_253');
MESSAGE (GET_APPLICATION_PROPERTY(USERNAME));
MESSAGE (GET_APPLICATION_PROPERTY(USERNAME));
MESSAGE (:DAILY_TRX_CONT.ORG_ID);
MESSAGE (:DAILY_TRX_CONT.ORG_ID);
IF (:DAILY_TRX_CONT.ORG_ID = '1000') THEN
LOGOUT ;
LOGON ('FIFES_HN','FIFES_HN@PRD_253');
-- ASSUIT
ELSIF (:DAILY_TRX_CONT.ORG_ID = '1490') THEN
LOGOUT ;
LOGON ('FIFES_HN','FIFES_HN@PRD_253');
ELSIF (:DAILY_TRX_CONT.ORG_ID = '1491') THEN
LOGOUT ;
LOGON ('SIFESD','SIFESD@PRD_253');
END IF;
MESSAGE (GET_APPLICATION_PROPERTY(USERNAME));
MESSAGE (GET_APPLICATION_PROPERTY(USERNAME));
i will give you the sequence of the message shown
FIFES_HN
1491
FIFES_HN
how the last messae is 'FIFES_HN' it show be 'SIFESD'
thanks
please help
|
|
|
|
|
|
|
|
Re: Problem in connecting to other database user inside a form (merged 2) [message #546707 is a reply to message #546697] |
Thu, 08 March 2012 05:21 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/1229663c86eb1a441385fe710cd9116e?s=64&d=mm&r=g) |
mughals_king
Messages: 392 Registered: January 2012 Location: pakistan
|
Senior Member |
|
|
Yes @emadnabil You can changed the user/@connect_String in the form yes Mr.cookiemonster i have acomplished this task many time aga see the details as below.This task acomplished on emp table for easy to understand
FUNCTION CHANGE_CONNECTION (
v_conHandle IN OUT EXEC_SQL.ConnType,
v_conInfo IN OUT VARCHAR2,
v_userName IN VARCHAR2 DEFAULT NULL,
v_password IN VARCHAR2 DEFAULT NULL,
v_datasource IN VARCHAR2 DEFAULT NULL
) RETURN BOOLEAN IS
-- This procedure tries to open a connection to
-- v_username/v_password@v_datasource. If it succeeds,
-- it disconnects from the connection on v_conHandle (if there
-- is one), and sets v_conHandle to point to the new
-- connection. If the user hits Cancel on the LOGON_SCREEN
-- dialog, this function silently returns with the same connection.
-- If it fails to log on, it will display an alert and bring you
-- back to the LOGON screen. You get a maximum of
c_attempts CONSTANT INTEGER := 3;
-- attempts to log on. If after all attempts, you still fail, the
-- function returns with the same connection handle as it was
-- passed.
-- If the connection is changed, v_conInfo will contain
-- the string v_userName@v_datasource, or if datasource is NULL,
-- v_userName@(Oracle)
un VARCHAR2(80);
pw VARCHAR2(80);
cn VARCHAR2(80);
tmpConInfo VARCHAR2(255);
tmpConHandle EXEC_SQL.ConnType;
logonSuccessful BOOLEAN := FALSE;
BEGIN
FOR i IN 1..c_attempts LOOP
IF v_username is not null or
v_password is not null or
v_datasource is not null THEN
IF i > 1 THEN
-- Only one attempt if the username/password is specified
EXIT;
END IF;
un := v_username;
pw := v_password;
cn := v_datasource;
ELSE
LOGON_SCREEN;
Get_Connect_Info ( un, pw, cn );
END IF;
IF ((un is null) and (pw is null) and (cn is null)) THEN
-- We assume this is cancel, as we have no other way
-- of telling.
EXIT;
END IF;
BEGIN
tmpConHandle := EXEC_SQL.OPEN_CONNECTION(un, pw, cn);
EXCEPTION
WHEN EXEC_SQL.PACKAGE_ERROR THEN
-- We failed to log on.
DECLARE
tmpAlert PLS_INTEGER;
BEGIN
tmpAlert := SHOW_ALERT('LOGON_FAILED');
END;
END;
IF EXEC_SQL.VALID_CONNECTION(tmpConHandle) THEN
-- We succeeded in connection. Disconnect old handle,
-- and set all output variables.
IF cn IS NULL THEN
cn := '(Oracle)';
END IF;
EXEC_SQL.CLOSE_CONNECTION(v_conHandle);
v_conHandle := tmpConHandle;
v_conInfo := un || '@' || cn;
logonSuccessful := TRUE;
-- And break the loop.
EXIT;
END IF;
END LOOP;
IF logonSuccessful THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
--------------------------------------------------
PACKAGE CON1 IS
-- This package spec is used to contain global variables related
-- to the primary connection. In particular, we stash the
-- username, password and connection string used on the
-- primary connection.
un VARCHAR2(80);
pw VARCHAR2(80);
cn VARCHAR2(80);
PROCEDURE STASH_CURRCONN;
END CON1;
-------------------------
PACKAGE BODY CON1 IS
PROCEDURE STASH_CURRCONN IS
BEGIN
un := GET_APPLICATION_PROPERTY(USERNAME);
pw := GET_APPLICATION_PROPERTY(PASSWORD);
cn := GET_APPLICATION_PROPERTY(CONNECT_STRING);
IF cn IS NULL THEN
:control.con1_connected_as := un || '@(Oracle)';
ELSE
:control.con1_connected_as := un || '@' || cn;
END IF;
END STASH_CURRCONN;
END;
--------------------
PACKAGE CON2 IS
-- This package contains global variables used by the second
-- Connection block. We use packages so we can maintain variables
-- that are global to this form, but are not of type CHAR (:GLOBAL
-- variables are restricted to CHAR types).
conHandle EXEC_SQL.ConnType;
selectCursor EXEC_SQL.CursType;
updateCursor EXEC_SQL.CursType;
deleteCursor EXEC_SQL.CursType;
insertCursor EXEC_SQL.CursType;
commitCursor EXEC_SQL.CursType;
doneFetching BOOLEAN := FALSE;
PROCEDURE NEW_CONNECT (
copyConnect BOOLEAN DEFAULT FALSE -- If set to true, then con2
-- CONNECT connects to the same datasource as the primary forms
-- connection.
);
-- Commits the current connection
PROCEDURE DO_COMMIT( rollback IN BOOLEAN DEFAULT FALSE);
-- Called to delete each record
PROCEDURE DO_DELETE;
-- Fetches N records into the block
PROCEDURE DO_FETCH;
-- Inserts a records into the database
PROCEDURE DO_INSERT;
-- Executes a select statement against the database
PROCEDURE DO_SELECT;
-- Updates records in the database
PROCEDURE DO_UPDATE;
END;
-------------------------
PACKAGE BODY CON2 IS
PROCEDURE NEW_CONNECT (
copyConnect BOOLEAN DEFAULT FALSE -- If set to true, then con2
-- CONNECT connects to the same datasource as the primary forms
-- connection.
) IS
connectStatus BOOLEAN;
BEGIN
-- This procedure connects the con2 connection handle to a
-- datasource.
IF copyConnect THEN
connectStatus := CHANGE_CONNECTION(con2.conHandle,
:control.con2_connected_as,
con1.un, con1.pw, con1.cn);
ELSE
connectStatus := CHANGE_CONNECTION(con2.conHandle,
:control.con2_connected_as,
'','','');
END IF;
IF NOT connectStatus THEN
-- Logon failed
IF NOT EXEC_SQL.VALID_CONNECTION(conHandle) THEN
:control.con2_connected_as := 'Not Connected';
END IF;
ELSE
-- Logon succeeded.
IF NOT copyConnect THEN
-- This was a user-selected logon.
GO_BLOCK('CON2');
CLEAR_BLOCK;
END IF;
END IF;
END NEW_CONNECT;
PROCEDURE DO_COMMIT( rollback IN BOOLEAN DEFAULT FALSE) IS
retVal PLS_INTEGER;
BEGIN
IF NOT EXEC_SQL.IS_OPEN(conHandle, commitCursor) THEN
commitCursor := EXEC_SQL.OPEN_CURSOR(conHandle);
EXEC_SQL.PARSE(conHandle, commitCursor, 'commit');
END IF;
retVal := EXEC_SQL.EXECUTE(conHandle, commitCursor);
EXCEPTION
WHEN EXEC_SQL.PACKAGE_ERROR THEN
HANDLE_ERROR(conHandle);
END DO_COMMIT;
PROCEDURE DO_DELETE IS
retVal PLS_INTEGER;
BEGIN
IF NOT EXEC_SQL.IS_OPEN(conHandle, deleteCursor) THEN
deleteCursor := EXEC_SQL.OPEN_CURSOR(conHandle);
EXEC_SQL.PARSE(conHandle, deleteCursor,
'delete from emp where empno = :empno');
END IF;
-- Bind the variables.
EXEC_SQL.BIND_VARIABLE(conHandle, deleteCursor,
':empno', :CON2.EMPNO);
-- And execute.
retVal := EXEC_SQL.EXECUTE(conHandle, deleteCursor);
EXCEPTION
WHEN EXEC_SQL.PACKAGE_ERROR THEN
HANDLE_ERROR(conHandle);
END DO_DELETE;
PROCEDURE DO_FETCH IS
fetchCode PLS_INTEGER;
BEGIN
FOR i IN 1..TO_NUMBER(GET_BLOCK_PROPERTY('CON2', RECORDS_TO_FETCH)) LOOP
IF NOT doneFetching THEN
fetchCode := EXEC_SQL.FETCH_ROWS(conHandle, selectCursor);
IF fetchCode > 0 THEN
-- We have values to retrieve. Do so.
CREATE_QUERIED_RECORD;
EXEC_SQL.COLUMN_VALUE(conHandle, selectCursor,
1, :CON2.EMPNO);
EXEC_SQL.COLUMN_VALUE(conHandle, selectCursor,
2, :CON2.ENAME);
EXEC_SQL.COLUMN_VALUE(conHandle, selectCursor,
3, :CON2.MGR);
EXEC_SQL.COLUMN_VALUE(conHandle, selectCursor,
4, :CON2.JOB);
EXEC_SQL.COLUMN_VALUE(conHandle, selectCursor,
5, :CON2.HIREDATE);
EXEC_SQL.COLUMN_VALUE(conHandle, selectCursor,
6, :CON2.SAL);
EXEC_SQL.COLUMN_VALUE(conHandle, selectCursor,
7, :CON2.COMM);
EXEC_SQL.COLUMN_VALUE(conHandle, selectCursor,
8, :CON2.DEPTNO);
ELSE
doneFetching := TRUE;
END IF;
END IF;
END LOOP;
EXCEPTION
WHEN EXEC_SQL.PACKAGE_ERROR THEN
HANDLE_ERROR(conHandle);
END DO_FETCH;
PROCEDURE DO_INSERT IS
retVal PLS_INTEGER;
BEGIN
IF NOT EXEC_SQL.IS_OPEN(conHandle, insertCursor) THEN
insertCursor := EXEC_SQL.OPEN_CURSOR(conHandle);
EXEC_SQL.PARSE(conHandle, insertCursor,
'insert into emp (empno, ename, job, mgr, hiredate, sal,
comm, deptno) values (:empno, :ename, :job,
:mgr, :hiredate, :sal, :comm, :deptno)');
END IF;
-- Bind the variables.
EXEC_SQL.BIND_VARIABLE(conHandle, insertCursor,
':empno', :CON2.EMPNO);
EXEC_SQL.BIND_VARIABLE(conHandle, insertCursor,
':ename', :CON2.ENAME);
EXEC_SQL.BIND_VARIABLE(conHandle, insertCursor,
':job', :CON2.JOB);
EXEC_SQL.BIND_VARIABLE(conHandle, insertCursor,
':mgr', :CON2.MGR);
EXEC_SQL.BIND_VARIABLE(conHandle, insertCursor,
':hiredate', :CON2.hiredate);
EXEC_SQL.BIND_VARIABLE(conHandle, insertCursor,
':sal', :CON2.sal);
EXEC_SQL.BIND_VARIABLE(conHandle, insertCursor,
':comm', :CON2.comm);
EXEC_SQL.BIND_VARIABLE(conHandle, insertCursor,
':deptno', :CON2.deptno);
-- And execute.
retVal := EXEC_SQL.EXECUTE(conHandle, insertCursor);
EXCEPTION
WHEN EXEC_SQL.PACKAGE_ERROR THEN
HANDLE_ERROR(conHandle);
END DO_INSERT;
PROCEDURE DO_SELECT IS
numRows PLS_INTEGER;
BEGIN
IF NOT EXEC_SQL.IS_OPEN(conHandle, selectCursor) THEN
-- We need to open the cursor.
selectCursor := EXEC_SQL.OPEN_CURSOR(conHandle);
EXEC_SQL.PARSE(conHandle, selectCursor,
'select empno, ename, mgr, job, hiredate, sal, comm, deptno from emp');
-- And define all the columns we need.
EXEC_SQL.DEFINE_COLUMN(conHandle, selectCursor,
1, :CON2.EMPNO);
EXEC_SQL.DEFINE_COLUMN(conHandle, selectCursor,
2, :CON2.ENAME, 30);
EXEC_SQL.DEFINE_COLUMN(conHandle, selectCursor,
3, :CON2.MGR);
EXEC_SQL.DEFINE_COLUMN(conHandle, selectCursor,
4, :CON2.JOB, 30);
EXEC_SQL.DEFINE_COLUMN(conHandle, selectCursor,
5, :CON2.HIREDATE);
EXEC_SQL.DEFINE_COLUMN(conHandle, selectCursor,
6, :CON2.SAL);
EXEC_SQL.DEFINE_COLUMN(conHandle, selectCursor,
7, :CON2.COMM);
EXEC_SQL.DEFINE_COLUMN(conHandle, selectCursor,
8, :CON2.DEPTNO);
END IF;
doneFetching := FALSE;
numRows := EXEC_SQL.EXECUTE(conHandle, selectCursor);
EXCEPTION
WHEN EXEC_SQL.PACKAGE_ERROR THEN
HANDLE_ERROR(conHandle);
END DO_SELECT;
PROCEDURE DO_UPDATE IS
retVal PLS_INTEGER;
oldEmpno NUMBER;
BEGIN
IF NOT EXEC_SQL.IS_OPEN(conHandle, updateCursor) THEN
updateCursor := EXEC_SQL.OPEN_CURSOR(conHandle);
EXEC_SQL.PARSE(conHandle, updateCursor,
'update emp set empno = :empno, ename = :ename, job = :job,
mgr = :mgr, hiredate = :hiredate, sal = :sal, comm = :comm,
deptno = :deptno where empno = :key');
END IF;
-- Bind the variables.
EXEC_SQL.BIND_VARIABLE(conHandle, updateCursor,
':empno', :CON2.EMPNO);
EXEC_SQL.BIND_VARIABLE(conHandle, updateCursor,
':ename', :CON2.ENAME);
EXEC_SQL.BIND_VARIABLE(conHandle, updateCursor,
':job', :CON2.JOB);
EXEC_SQL.BIND_VARIABLE(conHandle, updateCursor,
':mgr', :CON2.MGR);
EXEC_SQL.BIND_VARIABLE(conHandle, updateCursor,
':hiredate', :CON2.hiredate);
EXEC_SQL.BIND_VARIABLE(conHandle, updateCursor,
':sal', :CON2.sal);
EXEC_SQL.BIND_VARIABLE(conHandle, updateCursor,
':comm', :CON2.comm);
EXEC_SQL.BIND_VARIABLE(conHandle, updateCursor,
':deptno', :CON2.deptno);
EXEC_SQL.BIND_VARIABLE(conHandle, updateCursor,
':key', TO_NUMBER(GET_ITEM_PROPERTY('CON2.EMPNO',
DATABASE_VALUE)));
-- And execute.
retVal := EXEC_SQL.EXECUTE(conHandle, updateCursor);
EXCEPTION
WHEN EXEC_SQL.PACKAGE_ERROR THEN
HANDLE_ERROR(conHandle);
END DO_UPDATE;
END CON2;
----------------------------------------------
PROCEDURE DROP_CONNECTION (
conHandle IN OUT EXEC_SQL.ConnType
) IS
BEGIN
-- This procedure disconnects from the datasource we connected
-- to via EXEC_SQL.
EXEC_SQL.CLOSE_CONNECTION(conHandle);
END;
-----------------------------
PROCEDURE Get_Connect_Info (
un OUT VARCHAR2,
pw OUT VARCHAR2,
cn OUT VARCHAR2
) IS
BEGIN
un := GET_APPLICATION_PROPERTY(USERNAME);
pw := GET_APPLICATION_PROPERTY(PASSWORD);
cn := GET_APPLICATION_PROPERTY(CONNECT_STRING);
END;
------------------------------
PROCEDURE HANDLE_ERROR (
v_conHandle IN EXEC_SQL.ConnType
) IS
alert_retval NUMBER;
BEGIN
-- This is called after a EXEC_SQL.PACKAGE_ERROR is raised.
-- It displays the raised error and continues.
SET_ALERT_PROPERTY('DISPLAY_ERROR', ALERT_MESSAGE_TEXT,
'Error ' || TO_CHAR(EXEC_SQL.LAST_ERROR_CODE(v_conHandle))
|| ': ' || EXEC_SQL.LAST_ERROR_MESG(v_conHandle));
alert_retval := SHOW_ALERT('DISPLAY_ERROR');
EXCEPTION
WHEN EXEC_SQL.PACKAGE_ERROR THEN
SET_ALERT_PROPERTY('DISPLAY_ERROR', ALERT_MESSAGE_TEXT,
'Error: Unknown EXEC_SQL error');
alert_retval := SHOW_ALERT('DISPLAY_ERROR');
WHEN OTHERS THEN
SET_ALERT_PROPERTY('DISPLAY_ERROR', ALERT_MESSAGE_TEXT,
'Error: Unknown Forms error');
alert_retval := SHOW_ALERT('DISPLAY_ERROR');
END;
-----WHEN-NEW-FORM-INSTANCE
CON1.STASH_CURRCONN; -- Stash the current connection values.
CON2.NEW_CONNECT(TRUE);
--------POST-FORMS-COMMIT-------
CON2.DO_COMMIT;
--------WHEN-TAB-PAGE-CHANGED
IF :SYSTEM.TAB_NEW_PAGE = 'CON2' THEN
go_block('CON2');
elsif :SYSTEM.TAB_NEW_PAGE = 'DYNAMIC' THEN
go_block('DYNAMIC');
else
go_block('EMP');
end if;
if u have still a problem then tell me i will send u a FMB file upload your presonal email address
Best regards
mughal
[Updated on: Thu, 08 March 2012 05:29] Report message to a moderator
|
|
|
|
Goto Forum:
Current Time: Sun Sep 08 07:15:19 CDT 2024
|