Dynamic Query [message #550374] |
Mon, 09 April 2012 00:26 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
mvmkandan
Messages: 68 Registered: May 2010 Location: Trivendrum
|
Member |
|
|
Hi,
I am using Oracle : Forms [32 Bit] Version 10.1.2.2.0 (Production)
I have build the following query in When_NEW_FORM_INSTANCE trigger of form test.fmb
lv_sql := 'Select EmpNo, DeptName From (
Select EmpNo, DeptName from Emp a, Dept B
where a.DeptId = b.DeptID) EmpDept';
SET_BLOCK_PROPERTY('header',QUERY_DATA_SOURCE_name, lv_sql);
GO_BLOCK('header');
clear_block;
execute_query;
in the form, i have 2 text fields and set the data base iterm as YES and column Name as EmpNo and DeptName respectively, and for
the datablock property Database_Datablock as YES and Query_Data_source_Name as EmpDEpt.
While open this form, it raise unable to Perform the Query error.
Thanks
Veera
|
|
|
Re: Dynamic Query [message #550390 is a reply to message #550374] |
Mon, 09 April 2012 04:01 ![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) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
So use the Display error option on the run time help menu to see what query it tried to run and what the error was.
|
|
|
|
Re: Dynamic Query [message #550451 is a reply to message #550410] |
Mon, 09 April 2012 10:03 ![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) |
owais_baba
Messages: 289 Registered: March 2008 Location: MUSCAT
|
Senior Member |
|
|
friend let me give u some examples regarding synamic sql what of fall concept should be cleared what is Dynamic Sql?
http://www.dulcian.com/papers/ECO/1999/1999_ECO_DynamicSQLOracleForms.htm
http://www.csee.umbc.edu/portal/help/oracle8/server.815/a68022/dynsql.htm#588
-----------------------------------------
and here is some practical examples
------------dynamic cursor-----------------
PROCEDURE dynamic_cursor ( PC$Clause IN Varchar2 )
IS
cursor_number EXEC_SQL.CursType;
LN$Empno Number;
LC$Name Varchar2(30);
LN$count Number;
LC$sql_order Varchar2(256);
BEGIN
cursor_number := Exec_SQL.Open_cursor;
LC$sql_order := 'Select empno,ename From EMP ' || PC$clause ;
EXEC_SQL.PARSE(cursor_number, LC$sql_order);
EXEC_SQL.DEFINE_COLUMN(cursor_number,1,LN$Empno);
EXEC_SQL.DEFINE_COLUMN(cursor_number,2,LC$Name,30);
LN$count := EXEC_SQL.EXECUTE(cursor_number);
While EXEC_SQL.FETCH_ROWS(cursor_number) > 0 Loop
EXEC_SQL.COLUMN_VALUE(cursor_number,1,LN$Empno);
EXEC_SQL.COLUMN_VALUE(cursor_number,2,LC$Name);
message('Fetched: '||LC$Name||' Empno='||to_char(LN$Empno));
End Loop ;
EXEC_SQL.CLOSE_CURSOR(cursor_number);
EXCEPTION
When EXEC_SQL.INVALID_CONNECTION Then
message('Unexpected Invalid Connection error from EXEC_SQL');
When EXEC_SQL.PACKAGE_ERROR Then
message('Unexpected error from EXEC_SQL: '||to_char(EXEC_SQL.LAST_ERROR_CODE)|| EXEC_SQL.LAST_ERROR_MESG);
If EXEC_SQL.IS_OPEN(cursor_number) Then
EXEC_SQL.CLOSE_CURSOR(cursor_number);
message('Exception - Cleaned up Cursor');
End if;
END;
and offcourse ----calling procedure---------------
Dynamic_Cursor( :BL.IN_CLAUSE );
----------another example---------
declare
type empdtlrec is record (empno number(4),
ename varchar2(20),
deptno number(2));
empdtl empdtlrec;
begin
execute immediate 'select empno, ename, deptno ' ||
'from emp where empno = 7934'
into empdtl;
end;
hope u got it
[Updated on: Mon, 09 April 2012 10:04] Report message to a moderator
|
|
|
Re: Dynamic Query [message #550497 is a reply to message #550451] |
Mon, 09 April 2012 17:38 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
owais_baba
Messages: 289 Registered: March 2008 Location: MUSCAT
|
Senior Member |
|
|
if u are still confusing try this according to your requirment
try this
you need to change some properties of data block
Database Data Block = YES
Query Allowed = YES
Query Data Source Type = FROM clause query
Query Data Source Name =select empno,to_char(name) name from emp
block has a pre-query
declare
lv_sql varchar2(1000);
begin
pause;
lv_sql:=
'(select empno, to_char(name) name from emp)';
set_block_property
('block_name',
QUERY_DATA_SOURCE_NAME,
lv_sql);
----when-new-form-instance--
go_block('block_name');
execute_query;
end;
i didn't try this u just try hope u will get something from it
regards baba
[Updated on: Mon, 09 April 2012 17:42] Report message to a moderator
|
|
|