Home » Developer & Programmer » Forms » Dynamic FROM clause (FORMS 6i, Windows 2000)
Dynamic FROM clause [message #343211] Tue, 26 August 2008 11:32 Go to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
Hi,
I have a form (in FORMS 6i) on which user inputs the year. I want to use that year in my query everytime to change the table name.
In simple words i want to change the table name in the query dynamically at the runtime?
Is it possible? and if yes the how?
Thanks in advance.
Re: Dynamic FROM clause [message #343357 is a reply to message #343211] Wed, 27 August 2008 00:51 Go to previous messageGo to next message
mudabbir
Messages: 235
Registered: April 2006
Location: Kuwait
Senior Member

use Set_Block_Property
Re: Dynamic FROM clause [message #343504 is a reply to message #343211] Wed, 27 August 2008 07:44 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
Thanks
actually i'm getting a parameter from the user in the FORM and i want to use that parameter in the query which is placed in the REPORT.
Re: Dynamic FROM clause [message #343507 is a reply to message #343504] Wed, 27 August 2008 07:55 Go to previous messageGo to next message
mudabbir
Messages: 235
Registered: April 2006
Location: Kuwait
Senior Member

Which part of query you want to use the parameter in?
Re: Dynamic FROM clause [message #343605 is a reply to message #343211] Wed, 27 August 2008 11:12 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
i want to use the parameter in FROM clause of the query

eg
SELECT * FROM :YEAR

Where :YEAR is the user parameter in the report.
I want to make this YEAR(table name) dynamic...
Re: Dynamic FROM clause [message #343819 is a reply to message #343605] Thu, 28 August 2008 04:53 Go to previous messageGo to next message
mudabbir
Messages: 235
Registered: April 2006
Location: Kuwait
Senior Member

search this forum for EXEC_SQL

Sample Code as follows

DECLARE
  FULL_NAME      varchar2(50);
  nIgn           PLS_INTEGER;
  nRows          PLS_INTEGER := 0;
  connection_id  EXEC_SQL.CONNTYPE;
  cursor_id      EXEC_SQL.CURSTYPE;
  sql_str        VARCHAR2(100);
BEGIN
  connection_id := EXEC_SQL.OPEN_CONNECTION('scott/tiger@orcl');
  cursor_id := EXEC_SQL.OPEN_CURSOR(connection_id);
  sql_str := 'select emp_name from '||:YEAR;
  EXEC_SQL.PARSE(connection_id,cursor_id,sql_str,EXEC_SQL.V7);
  EXEC_SQL.DEFINE_COLUMN(connection_id,cursor_id,1,FULL_NAME);
  nign := EXEC_SQL.EXECUTE(connection_id,cursor_id);
  while (EXEC_SQL.FETCH_ROWS(connection_id,cursor_id) > 0 ) loop
    EXEC_SQL.COLUMN_VALUE(connection_id,cursor_id,1,FULL_NAME);
    nrows := nrows+1;
  End Loop;
  exec_sql.close_cursor(connection_id,cursor_id);
  Exec_sql.close_connection(connection_id);
END;
Re: Dynamic FROM clause [message #344249 is a reply to message #343819] Fri, 29 August 2008 03:55 Go to previous message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
Thank you sir, you solved my problem.
Thank you very much.
Previous Topic: how to know the server name
Next Topic: forms&reports
Goto Forum:
  


Current Time: Sun Dec 04 06:55:45 CST 2016

Total time taken to generate the page: 0.07732 seconds