OraFAQ Forum: Forms » fetching values of a cursor with exec

Home » Developer & Programmer » Forms » fetching values of a cursor with exec_sql

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

fetching values of a cursor with exec_sql [message #117321]

Tue, 26 April 2005 09:00

aditya_gangadharam
Messages: 43
Registered: February 2005
Location: Hyderabad

Member

hello to all

i want to open a cursor and fetch some field values dynamically."the fields to be fetched will be selected by the user" i.e i have to get the data of the fields selected by the user.
for this i am using the exec_sql method.
its giving a message as 'FRM-01747: invalid user.table.column,table.column or column specification"

i don't where i am wrong in the code.
this is my code to fetch the values

Declare
Con_id EXEC_SQL.CONNTYPE;
Cur_id EXEC_SQL.CURSTYPE;
sqlstr varchar2(3000);
fld1 number(20,2);
fld2 number(20,2);
fld3 number(20,2);
fld4 number(20,2);
fld5 number(20,2);
fld6 number(20,2);
fld7 number(20,2);
fld8 number(20,2);
nEmpno number(10,4);
nIgn PLS_INTEGER;
totRows number(5);
a number;
Begin
--1
Con_id := EXEC_SQL.DEFAULT_CONNECTION;--('scott/tiger@siil');
go_block('BlkDet');

Cur_id := EXEC_SQL.OPEN_CURSOR();
sqlstr := 'Select A.EmpNo,A.'||:BlkMain.LstFld1||',A.';
sqlstr := sqlstr||:BlkMain.LstFld2||',A.';
sqlstr := sqlstr||:BlkMain.LstFld3||',A.';
sqlstr := sqlstr||:BlkMain.LstFld4||',B.';
sqlstr := sqlstr||:BlkMain.LstFld1||',B.';
sqlstr := sqlstr||:BlkMain.LstFld2||',B.';
sqlstr := sqlstr||:BlkMain.LstFld3||',B.';
sqlstr := sqlstr||:BlkMain.LstFld4;
sqlstr := sqlstr||' from Tempsalmast A, SalMast B';
sqlstr := SqlStr||' where A.EmpNo=B.EmpNo';

Message(SqlStr);
Message(SqlStr);
EXEC_SQL.DEFINE_COLUMN(Con_id,Cur_id,1,nEmpNo);
EXEC_SQL.DEFINE_COLUMN(Con_id,Cur_id,2,Fld1);
EXEC_SQL.DEFINE_COLUMN(Con_id,Cur_id,3,Fld2);
EXEC_SQL.DEFINE_COLUMN(Con_id,Cur_id,4,Fld3);
EXEC_SQL.DEFINE_COLUMN(Con_id,Cur_id,5,Fld4);
EXEC_SQL.DEFINE_COLUMN(Con_id,Cur_id,6,Fld5);
EXEC_SQL.DEFINE_COLUMN(Con_id,Cur_id,7,Fld6);
EXEC_SQL.DEFINE_COLUMN(Con_id,Cur_id,8,Fld7);
EXEC_SQL.DEFINE_COLUMN(Con_id,Cur_id,9,Fld8);

EXEC_SQL.PARSE(Con_id,Cur_id,sqlstr,EXEC_SQL.V7);
nIgn:=EXEC_SQL.EXECUTE(Con_id,Cur_id);
while (EXEC_SQL.FETCH_ROWS(Con_id,Cur_id)>0) Loop

EXEC_SQL.COLUMN_VALUE(Con_id,Cur_id,1,nEmpNo);
EXEC_SQL.COLUMN_VALUE(Con_id,Cur_id,2,Fld1);
EXEC_SQL.COLUMN_VALUE(Con_id,Cur_id,3,Fld2);
EXEC_SQL.COLUMN_VALUE(Con_id,Cur_id,4,Fld3);
EXEC_SQL.COLUMN_VALUE(Con_id,Cur_id,5,Fld4);
EXEC_SQL.COLUMN_VALUE(Con_id,Cur_id,6,Fld5);
EXEC_SQL.COLUMN_VALUE(Con_id,Cur_id,7,Fld6);
EXEC_SQL.COLUMN_VALUE(Con_id,Cur_id,8,Fld7);
EXEC_SQL.COLUMN_VALUE(Con_id,Cur_id,9,Fld8);

:BlkDet.TxtEmpNo:=nEmpNo;
:BlkDet.DisFld1 :=Fld1;
:BlkDet.DisFld2 :=Fld2;
:BlkDet.DisFld3 :=Fld3;
:BlkDet.DisFld4 :=Fld4;
:BlkDet.TxtFld1 :=Fld5;
:BlkDet.TxtFld2 :=Fld6;
:BlkDet.TxtFld3 :=Fld7;
:BlkDet.TxtFld4 :=Fld8;
next_record;
end Loop;
--next_record;
end;

exception
when others then
Message(Dbms_Error_Text);
Message(Dbms_Error_Text);

can any body pls. help me out

aditya

Report message to a moderator

Re: fetching values of a cursor with exec_sql [message #117385 is a reply to message #117321]

Tue, 26 April 2005 19:16

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Try placing a "message('i am here');pause;" between each statement and determine exactly at which statement the form is failing.

David

ps. Please supply description of your table.

Report message to a moderator

Previous Topic:	TIMER
Next Topic:	URL web forms

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 19 18:19:27 CDT 2024