OraFAQ Forum: Forms

Home » Developer & Programmer » Forms » Question (databas10g-forms 6i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Question [message #485095]

Mon, 06 December 2010 04:07

owais_baba
Messages: 289
Registered: March 2008
Location: MUSCAT

Senior Member

my question is this

i have create database link with msaccess2000 its working fine

problem is this that

in forms 6i i am trying to upload data by using forms 6i
while compiling form is automatically closed
my code is this

begin 
  insert into baba1
  (select * 
   from baba1@access_db 
   where dte between to_date(:block3.fdate,'dd-mon-yyyy') 
                 and to_date(:block3.ldate,'dd-mon-yyyy')
  );
  
  message('TRANSFER COMPLETE');
  message('TRANSFER COMPLETE');
  commit;
end;

pls check this code what is wrong with that

thanks

[EDITED by LF: formatted code, applied [code] tags, removed superfluous empty lines]

[Updated on: Mon, 06 December 2010 05:22] by Moderator

Report message to a moderator

Re: Question [message #485098 is a reply to message #485095]

Mon, 06 December 2010 04:17

cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester

Senior Member

I can only assume forms doesn't like the DB link.
Try putting the insert in a db package and call that from the form.

Report message to a moderator

Re: Question [message #485121 is a reply to message #485098]

Mon, 06 December 2010 05:25

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Or, try to create a synonym in your own schema and use it in the FROM clause. Such as

SQL> create synonym s_emp for emp@dbl_scott;

Synonym created.

SQL> insert into your_table
  2  select * from s_emp
  3  where ...

Report message to a moderator

Re: Question [message #485134 is a reply to message #485121]

Mon, 06 December 2010 06:24

owais_baba
Messages: 289
Registered: March 2008
Location: MUSCAT

Senior Member

thanks for reply

i created synonym like

create synonym s_baba1 for baba1@access_db;

thn

insert into s_baba1(select * from s_baba1 where dte between
to_date(:block3.fdate,'dd-mon-yyyy')
and to_date(:block3.ldate,'dd-mon-yyyy'));

but sir same thing is happening
now the error is

"ORA-00600 internal error code,arguments"

thanks

[Updated on: Mon, 06 December 2010 06:34]

Report message to a moderator

Re: Question [message #485135 is a reply to message #485134]

Mon, 06 December 2010 06:25

cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester

Senior Member

What happens if you do the insert in sqlplus?

Report message to a moderator

Re: Question [message #485136 is a reply to message #485135]

Mon, 06 December 2010 06:37

owais_baba
Messages: 289
Registered: March 2008
Location: MUSCAT

Senior Member

sir in "sqlplus" everything is ok inserting record successfully but problem only comes while using forms 6i

thanks

Report message to a moderator

Re: Question [message #485137 is a reply to message #485136]

Mon, 06 December 2010 06:39

cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester

Senior Member

Then try my original suggestion

Report message to a moderator

Re: Question [message #485138 is a reply to message #485137]

Mon, 06 December 2010 06:45

owais_baba
Messages: 289
Registered: March 2008
Location: MUSCAT

Senior Member

sir i tried also in this way but here some error is like this

"ORA-00000: normal successful completion"

PROCEDURE Synch IS
v_connect EXEC_SQL.CONNTYPE;
sqlstr VARCHAR2(1000);
nRes PLS_INTEGER;
curid EXEC_SQL.CURSTYPE;
V_Empno NUMBER;
V_Ename VARCHAR2(200);
V_Dept NUMBER;
V_Sal NUMBER;

BEGIN

v_connect := EXEC_SQL.OPEN_CONNECTION('SCOTT/TIGER@ODBC:Test_Mrp'); --Replace Your syntax [:TEST_MRP IS SYSTEM ODBC NAME)

curid := EXEC_SQL.OPEN_CURSOR(v_connect);
sqlstr :='Select Empno,Ename,Deptno,Salary From Emp'; --Getting Data From MS ACCESS Table

EXEC_SQL.PARSE(v_connect, curID,sqlstr,exec_sql.v7);

nRes := EXEC_SQL.EXECUTE(v_connect, curID);

EXEC_SQL.DEFINE_COLUMN(v_connect, curID, 1, V_Empno);
EXEC_SQL.DEFINE_COLUMN(v_connect, curID, 2, V_Ename,200);
EXEC_SQL.DEFINE_COLUMN(v_connect, curID, 3, V_Dept);
EXEC_SQL.DEFINE_COLUMN(v_connect, curID, 4, V_Sal);

WHILE (EXEC_SQL.FETCH_ROWS(v_connect, curID)>0)

LOOP
EXEC_SQL.column_value(v_connect, curID ,1 ,V_Empno);
EXEC_SQL.column_value(v_connect, curID, 2 ,V_Ename);
EXEC_SQL.column_value(v_connect, curID, 3 ,V_Dept);
EXEC_SQL.column_value(v_connect, curID, 4 ,V_Sal);

Insert Into SCOTT.Emp_Master(Empno ,EName ,Deptno,Salary)
Values(V_Empno,V_Ename,V_Dept,V_Sal );
Commit;
CLEAR_MESSAGE;
CLEAR_MESSAGE;

End Loop;

COMMIT;
CLEAR_MESSAGE;
exec_sql.close_cursor(v_connect, curID);

EXCEPTION
WHEN exec_sql.package_error THEN

MESSAGE(EXEC_SQL.last_error_mesg);
exec_sql.close_cursor(v_connect, curID);
END;

Report message to a moderator

Re: Question [message #485143 is a reply to message #485138]

Mon, 06 December 2010 07:25

cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester

Senior Member

I said stick it in a database package - you haven't. Do that. One procedure in the DB - which'll just be a single insert/select statement - no need for all this code you've written.

Report message to a moderator

Previous Topic:	invalid asset . . . press f9 to see list of values
Next Topic:	forms9i radio buttons

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 19 00:19:56 CDT 2024