Home » Developer & Programmer » Forms » Select a table at runtime
Select a table at runtime [message #258959] Mon, 13 August 2007 22:35 Go to next message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
Hi
I am using a table for each financial year like tran2004 for 2004-05 and tran2005 for 2005-06. If I am having a query to fetch a record whose trandate is 13-08-2004 then I should select from tran2004. So based on the input I should make my query select the corresponding table. In other words i want to user the tablename as a variable.


Alister
Re: Select a table at runtime [message #258964 is a reply to message #258959] Mon, 13 August 2007 23:19 Go to previous messageGo to next message
bahubcd
Messages: 36
Registered: July 2007
Location: Bangalore
Member
Try and Create a view based on all the tables with the appropriate JOIN condition i.e Financial year.

Then create a data block based on the view.

Please let me know if it solves your issue.
Re: Select a table at runtime [message #259013 is a reply to message #258964] Tue, 14 August 2007 02:29 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Alternatively, use a 'union' statement between a set of 'selects'.

David
Re: Select a table at runtime [message #259540 is a reply to message #259013] Wed, 15 August 2007 22:33 Go to previous messageGo to next message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
In foxpro you have a statement like

mfile='filename'
use &mfile

So the filename (tablename is a variable). Can I do sometime simillar in Oracle like

Select * from &mtable

while using a form I will get the value for mtable as input.
Re: Select a table at runtime [message #260754 is a reply to message #259540] Mon, 20 August 2007 19:28 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Yes, look at dbms_sql.

David
Re: Select a table at runtime [message #261124 is a reply to message #260754] Tue, 21 August 2007 22:09 Go to previous messageGo to next message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
Can you please tell me how to do it with an example.


Alister
Re: Select a table at runtime [message #261407 is a reply to message #261124] Wed, 22 August 2007 09:10 Go to previous messageGo to next message
bbaz
Messages: 138
Registered: April 2007
Senior Member
Quote:

In other words i want to user the tablename as a variable.



Have a look at the followin code, it might help you, for more info refer to the complete trail for message #253032:

CREATE OR REPLACE PROCEDURE cursor_test_1 (colname IN varchar2, tab_name IN varchar2)
IS
test_cur sys_refcursor;
v_value varchar2(32);
sql_stmt VARCHAR2(1000);
BEGIN
 sql_stmt := 'SELECT  '||colname||' FROM '||UPPER(tab_name);
OPEN test_cur FOR sql_stmt;
    LOOP
        FETCH test_cur INTO v_value;
    EXIT when test_cur%notfound;
    DBMS_OUTPUT.PUT_LINE(v_value);
    END LOOP;
   CLOSE test_cur;
END;





Please let us know if this will work or meet your objective.

Regards,
Baz
Re: Select a table at runtime [message #263378 is a reply to message #258959] Wed, 29 August 2007 22:19 Go to previous messageGo to next message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
Dear Baz,
Thank you very much for the procedure. I tried out the procedure you gave me but am getting the following errors when I compile it.

(1): PLS-00201: identifier 'SYS_REFCURSOR' must be declared
(2): PL/SQL: Item ignored
(3): PLS-00320: the declaration of the type of this expression is incomplete or malformed
(4): PL/SQL: Statement ignored
(5): PLS-00320: the declaration of the type of this expression is incomplete or malformed
(6): PL/SQL: SQL Statement ignored
(7): PLS-00320: the declaration of the type of this expression is incomplete or malformed
(8): PL/SQL: Statement ignored
(9): PLS-00320: the declaration of the type of this expression is incomplete or malformed
(10): PL/SQL: SQL Statement ignored

I tried compiling it using the SYSTEM User also.

Alister
Re: Select a table at runtime [message #263395 is a reply to message #263378] Wed, 29 August 2007 23:28 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
What version of Forms and database are you using?

David
Re: Select a table at runtime [message #263738 is a reply to message #263395] Thu, 30 August 2007 22:14 Go to previous messageGo to next message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
I am using Forms (32 Bit) Version 5.0.6.16.0. and Oracle 8.0.1.7

Alister
Re: Select a table at runtime [message #263749 is a reply to message #263738] Thu, 30 August 2007 23:37 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
@others: please confirm that sys_refcursor is only available in Oracle Database 9i or higher.


See http://www.oracle-base.com/articles/8i/UsingRefCursorsToReturnRecordsets.php

David
Re: Select a table at runtime [message #263795 is a reply to message #263749] Fri, 31 August 2007 01:31 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Yes, sys_refcursor was introduced in 9i.
Re: Select a table at runtime [message #263797 is a reply to message #263795] Fri, 31 August 2007 01:33 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Use the link I gave in my response two entries above to rewrite your code so that is works in Oracle 8.

David

Thanks LF

[Updated on: Fri, 31 August 2007 01:33]

Report message to a moderator

Re: Select a table at runtime [message #318460 is a reply to message #258959] Tue, 06 May 2008 22:48 Go to previous messageGo to next message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
Dear Friends
Since I was held up with another assignement I was not able to test the procedures that you have given me. I have used the code what baz and djmartin had given me. Now that I have tested the procedure that I have used below, it executes without any errors but I do not get any output also. Can one of you please check this procedure and tell me where I went wrong. Can I get the output as a variable?

Thank you

Alister
PACKAGE TYPES as
type cursor_type is ref cursor;
end types;


PROCEDURE CURSOR_TEST_1 (colname IN varchar2, tab_name IN varchar2)
IS
test_cur types.cursor_type;
v_value varchar2(32);
sql_stmt VARCHAR2(1000);
BEGIN
 sql_stmt := 'SELECT  '||colname||' FROM '||UPPER(tab_name);
OPEN test_cur FOR sql_stmt;
    LOOP
        FETCH test_cur INTO v_value;
    EXIT when test_cur%notfound;
     DBMS_OUTPUT.PUT_LINE(v_value);
    END LOOP;
CLOSE test_cur;
END;
Re: Select a table at runtime [message #318768 is a reply to message #258959] Wed, 07 May 2008 22:45 Go to previous message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
Dear Friends.

I am able to use the procedure as below by inserting the records into a temporary table and then processing the table. But now about 250,000 records are inserted ie the entire table, I need only about 50 records pertaining to a particurlar employee. Please tell me how to do it.

CREATE OR REPLACE 
PROCEDURE cursor_test_1 (colname IN varchar2, tab_name IN varchar2,
emp_code In varchar2,tr_type in varchar2,tr_drcr in varchar2
,tr_date IN varchar2
) is
test_cur types.cursor_type;
v_value varchar2(60);
sql_stmt VARCHAR2(1000);
L_yearob number;
L_trdate date;
l_trtype varchar2(2);
l_drcr   varchar2(1);

BEGIN
 sql_stmt := 'SELECT ' ||colname||','||emp_code||
 ','||tr_type||','||tr_drcr||
 ','||tr_date||
 ' FROM '||UPPER(tab_name);--||'  where empcode='|| emp_code;
OPEN test_cur FOR sql_stmt;
    LOOP
 
        FETCH test_cur INTO L_yearob,v_value,l_trtype,l_drcr
        ,l_trdate
        ;
        IF l_trtype='L' then
           l_trtype:='OB';
           l_drcr:=NULL;
        END IF;   
    insert into afpfslip (amount,empcode,transactiontype,debitcredit
    ,transactiondate
    )  values (L_yearob,v_value,l_trtype,l_drcr
    ,L_trdate
    );

    EXIT when test_cur%notfound;
      DBMS_OUTPUT.PUT_LINE(L_yearob);
    END LOOP;
    commit;
CLOSE test_cur;
END;
/



Thank you


Alister
Previous Topic: Linking Forms with Finger Scanner
Next Topic: Alignment
Goto Forum:
  


Current Time: Thu Dec 08 16:04:38 CST 2016

Total time taken to generate the page: 0.10308 seconds