1 Reply Latest reply on May 20, 2002 8:18 PM by 6466

    very urgent get another database connection within a connection

    273422
      Dear All,

      I am using an oracle form by connecting with oracle database and or runtime I have connected succesfully with ms sql server database wtih EXEC_SQL package. The syntax is as follows:

      open connection with odbc

      open cursor

      parse ddl statement in the cursor

      execute cursor

      close cursor

      close connection

      All the above steps are working except the last one. while closeing the odbc connection it's giving internal errro.

      So please show me how to disconnect succesfully through this package.


      Thansk & Regards,
      Habeeb.
      habeeb_kh@hotmail.com
        • 1. re:very urgent get another database connection within a connection
          6466
          First you should move all your data from sql server to Oracle
          but if you can't do this
          second try something like this
          (I have no idea if sql exactly like this would run on sqlserver
          You have to write valid sql)

          PROCEDURE spif_up_address(addr1 IN OUT varchar2
          ,addr2 IN OUT varchar2
          ,city IN OUT varchar2
          ,state IN OUT varchar2
          ,zip IN OUT varchar2
          ,code OUT boolean
          ,msg OUT varchar2)
          IS

               v_addr1 varchar2(35);
               v_addr2 varchar2(35);
               v_city varchar2(19);
               v_state varchar2(2);
               v_code varchar2(4);
               v_msg varchar2(50);
               v_zip     varchar2(9);
          v_connection_String CONFIG.OPTION%TYPE;
               cid EXEC_SQL.CONNTYPE;
               bIsConnected BOOLEAN;
               cursorID EXEC_SQL.CURSTYPE;
               sqlstr VARCHAR2(1000);
               nIgn PLS_INTEGER;
          --     nRows PLS_INTEGER := 0;
          --     nTimes PLS_INTEGER := 0;
          BEGIN
          BEGIN
               SELECT OPTION
               INTO v_connection_string
               FROM config
               WHERE OPTION_NAME='SQLSERVER';
          EXCEPTION
               WHEN OTHERS THEN
               v_connection_string:='';
               message('Connection string for SQLSERVER not set in configuration table.',ACKNOWLEDGE);
          RAISE FORM_TRIGGER_FAILURE;
               END;

               code := TRUE; -- assume completion
          -- Get OCA connection via SYSQL
               cid := EXEC_SQL.OPEN_CONNECTION(v_connection_string);
               -- IS connection complete
               bIsConnected := EXEC_SQL.Is_OCA_Connection(cid);
               IF bIsConnected = FALSE THEN
                    code := FALSE;
                    RETURN;
               END IF;
               cursorID := EXEC_SQL.OPEN_CURSOR(cid);

               if (addr2 is not null) then
                    sqlstr := 'SELECT ADDR1,ADDR2,CITY,STATE,ZIP,CODE,MSG '||
                    'FROM ADDRESS_REPOSITORY WHERE ' ||
                    'ADDR1 = '''||replace(addr1,'''','''''')||''''||
                    ' AND ADDR2 = '''||replace(addr2,'''','''''')||''''||
                    ' AND CITY = '''||replace(city,'''','''''')||''''||
                    ' AND STATE = '''||replace(state,'''','''''')||''''||
                    ' AND ZIP = '''||replace(zip,'''','''''')||'''';
               else     
                    sqlstr := 'SELECT ADDR1,1ADDR2,CITY,1STATE,ZIP,CODE,1MSG '||
                    'FROM ADDRESS_REPOSITORY WHERE ' ||
                    'ADDR1 = '''||replace(addr1,'''','''''')||''''||
                    ' AND CITY = '''||replace(city,'''','''''')||''''||
                    ' AND STATE = '''||replace(state,'''','''''')||''''||
                    ' AND ZIP = '''||replace(zip,'''','''''')||'''';
               end if;
               EXEC_SQL.PARSE(cid,cursorID, sqlstr, exec_sql.V7);
               EXEC_SQL.DEFINE_COLUMN(cid,cursorID, 1, v_addr1, 35);
               EXEC_SQL.DEFINE_COLUMN(cid,cursorID, 2, v_addr2, 35);
               EXEC_SQL.DEFINE_COLUMN(cid,cursorID, 3, v_city, 19);
               EXEC_SQL.DEFINE_COLUMN(cid,cursorID, 4, v_state, 2);
               EXEC_SQL.DEFINE_COLUMN(cid,cursorID, 5, v_zip, 9);
               EXEC_SQL.DEFINE_COLUMN(cid,cursorID, 6, v_code, 4);
               EXEC_SQL.DEFINE_COLUMN(cid,cursorID, 7, v_msg, 50);
               nIgn := EXEC_SQL.EXECUTE(cid,cursorID);
               IF (EXEC_SQL.FETCH_ROWS(cid,cursorID) > 0) THEN
                    EXEC_SQL.COLUMN_VALUE(cid,cursorID, 1, v_addr1 );
                    EXEC_SQL.COLUMN_VALUE(cid,cursorID, 2, v_addr2 );
                    EXEC_SQL.COLUMN_VALUE(cid,cursorID, 3, v_city );
                    EXEC_SQL.COLUMN_VALUE(cid,cursorID, 4, v_state );
                    EXEC_SQL.COLUMN_VALUE(cid,cursorID, 5, v_zip );
                    EXEC_SQL.COLUMN_VALUE(cid,cursorID, 6, v_code );
                    EXEC_SQL.COLUMN_VALUE(cid,cursorID, 7, v_msg );
               ELSE
                    code := FALSE;
                    msg := 'Failure to Fetch Row';
                    return;
               END IF;
               -- Close SYSQL connection
               EXEC_SQL.CLOSE_CURSOR(cid,cursorID);
               EXEC_SQL.CLOSE_CONNECTION;
               -- return results

               if (v_code = 'FAIL') then
                    code := FALSE;
               msg := v_msg;
               ELSE
                    code := TRUE;
               msg := null;
                    addr1 := v_addr1;
               addr2 := v_addr2;
               city := v_city;
               state := v_state;
               zip := v_zip;
               end if;     
          EXCEPTION
          WHEN OTHERS THEN
          msg_alert('Address revamp Failed!','E',FALSE);          
          END;