Home » SQL & PL/SQL » SQL & PL/SQL » open-for-using statement (Oracle 10g version 10.2.0.3.0)
open-for-using statement [message #333164] Thu, 10 July 2008 11:55 Go to next message
wtolentino
Messages: 196
Registered: March 2005
Senior Member
i am attempting to debug a code that is using an "open-for-using" for the cursor that throws an oracle error of:
   ORA-00923: FROM keyword not found where expected
   ORA-06512: at "LPA_APP.PRC_AIUD_INCUMB", line 60



it's code is somewhat similar to this code below:
            open c for 'select MUNICIPALITY NAME :org_name from dual'
            using c_bind_var.org_name;
            loop
              fetch c into v_message;
            end loop;
            close c;


i was wondering if the bind variable can be put in the SELECT expression? because most of the sample codes that i have seen online it is used in the WHERE expression. thanks.

[Updated on: Thu, 10 July 2008 11:59]

Report message to a moderator

Re: open-for-using statement [message #333170 is a reply to message #333164] Thu, 10 July 2008 12:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
'select MUNICIPALITY NAME :org_name from dual'

Syntax (for one field) is "SELECT expression alias FROM dual" that is 2 elements, yours contains 3 element before FROM so the error, Oracle is expected FROM after the second one.
If you want to select several fields, you have to separate with comma.

Regards
Michel
Re: open-for-using statement [message #333474 is a reply to message #333170] Fri, 11 July 2008 15:57 Go to previous messageGo to next message
wtolentino
Messages: 196
Registered: March 2005
Senior Member
thank you michael. that wasn't the actual expression in the select. i just given it sample expression for illustration and to simulate. and you are right it wasn't the correct syntax. the actual select expression is coming from a table that is dynamically build. in that data it has the bind variables and some single quotes in the wording on the sentence. found out that this single quotes is causing the issue and tried to correct it by placing another single quotes adjacent to it to make it similar to this:

before:
   Municipality's

after
   Municipality''s

that works.

Re: open-for-using statement [message #333503 is a reply to message #333474] Fri, 11 July 2008 23:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you don't post the actual problem, how could you expect we give you a correct answer?
NEVER do that, you will likely no more have any answer. Who want to spend time to your post knowing that you don't post the real question?

Regards
Michel
Re: open-for-using statement [message #333572 is a reply to message #333503] Sat, 12 July 2008 20:07 Go to previous message
wtolentino
Messages: 196
Registered: March 2005
Senior Member
that is what i was thinking at first. but i realize the code is much more longer and also the expression that builds the dynamic query. i think that would take longer explanation to post. i was trying to make it simple and i guess the samples that i posted is somewhat not clear. anyway next time i'll try to make it more clear and simple.
Previous Topic: Error in code - need help
Next Topic: CLOB & characterset
Goto Forum:
  


Current Time: Sat Dec 10 03:30:18 CST 2016

Total time taken to generate the page: 0.23830 seconds