Home » Developer & Programmer » Forms » error in query (FORMS 6i, Windows 2000)
error in query [message #346943] Wed, 10 September 2008 02:44 Go to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
Guys look at the following code
DECLARE
    TYPE AUTHOR_CUR IS REF CURSOR;
    C1 AUTHOR_CUR;
    R_C1 VARCHAR2(50);
    QRY VARCHAR2(50);
BEGIN
    QRY := 'SELECT DISTINCT Abc FROM table';
    OPEN C1 FOR QRY;
    FETCH C1 INTO R_C1;
        IF C1%ISOPEN THEN
            DBMS_OUTPUT.PUT_LINE('CURSOR OPEN');
            END IF;
            DBMS_OUTPUT.PUT_LINE(C1%ROWCOUNT);
    CLOSE C1;    
END;

This code works fine in the database procedure. But i want to use it at form level (FORMS 6i). When i use it in the form it gives an error:

ERROR 103: ENCOUNTERED THE SYMBOL "QRY" WHEN EXPECTING ONE OF THE FOLLOWING SELECT

Any solution for this??? or is there any alternate way to do it???
[EDITED by DJM: added [code] tags]

[Updated on: Wed, 10 September 2008 02:53] by Moderator

Report message to a moderator

Re: error in query [message #346946 is a reply to message #346943] Wed, 10 September 2008 02:51 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Why are you trying to run this code? What's wrong in basing your block on a table?

David
Re: error in query [message #346947 is a reply to message #346943] Wed, 10 September 2008 03:01 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
Actually sir i want to make this query dynamic. I want to change the FROM clause at run time by taking the table name from the user.
I am gonna use the same idea at report as well.
Thats why i am doing it like this.
Re: error in query [message #346990 is a reply to message #346943] Wed, 10 September 2008 06:08 Go to previous messageGo to next message
cookiemonster
Messages: 12418
Registered: September 2008
Location: Rainy Manchester
Senior Member
Do you really need to do this?
Becuase this sort of dynamic sql is generally regarded as being a bad idea as:
a) It hurts performance.
b) It's a pain to maintain.

If you really do need to do it then I would suggest putting the dynamic sql in a procedure in the database and basing the block in the form on that.
Have a look in form builder help if you're not sure how to do this.
Re: error in query [message #346992 is a reply to message #346943] Wed, 10 September 2008 06:19 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
Thanks
But i actually want it to use in report. If i make a database procedure then how will i call it from a report???
Re: error in query [message #346999 is a reply to message #346943] Wed, 10 September 2008 06:48 Go to previous messageGo to next message
cookiemonster
Messages: 12418
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can base oracle reports on procedures that return ref cursors.
Have a look in report builder help for details
Re: error in query [message #347009 is a reply to message #346943] Wed, 10 September 2008 07:31 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
ok thanks i'll check
Re: error in query [message #347225 is a reply to message #347009] Thu, 11 September 2008 02:13 Go to previous message
mudabbir
Messages: 235
Registered: April 2006
Location: Kuwait
Senior Member

Searech for EXEC_SQL ... It will slove your problems i hope
Previous Topic: Forms Error FRM-41050
Next Topic: missing plugins during Forms application runtime
Goto Forum:
  


Current Time: Thu Dec 08 04:27:22 CST 2016

Total time taken to generate the page: 0.08733 seconds