locked
Problem when using "call some_func()" with SQLEXEC-Connectivity error: [Oracle][ODBC][Ora]ORA-00907: missing right parenthesis RRS feed

  • Question

  • Hi,
    I have problem when using "call some_func()" with SQLEXEC.
    Is this ODBC problem or FOXPRPO 9.0SP2?

    nHandle=SQLSTRINGCONNECT("DSN=pcv;DBQ=192.168.0.123;UID=pcv;PWD=pcv;BTD=F;NUM=NLS;FWC=T")
    id_doc=61
    doc_nr='TEST0000001'
    ?SQLEXEC(nHandle,"call pcv.mp.test_call(?id_doc,?doc_nr)")
    =1 commited
    ?SQLEXEC(nHandle,"update pcv.b50_headeriai set b50_op_numeris=?doc_nr where id_header=?id_doc")
    =1 commited
    ?SQLEXEC(nHandle,"call pcv.mp.test_call(?id_doc,?doc_nr)")
    =-1 failed
    AERROR(aOraError)
    ?aOraError(2)
    t=Connectivity error: [Oracle][ODBC][Ora]ORA-00907: missing right parenthesis
    ?SQLEXEC(nHandle,"call pcv.mp.test_call(61,'TEST0000001')")
    =1 commited
    ?SQLEXEC(nHandle,"update pcv.b50_headeriai set b50_op_numeris=?doc_nr where id_header=?id_doc")
    =1 commited
    ?SQLEXEC(nHandle,"call pcv.mp.test_call(?id_doc,?doc_nr)")
    =-1 failed
    nHandle=SQLSTRINGCONNECT("DSN=pcv;DBQ=192.168.0.123;UID=pcv;PWD=pcv;BTD=F;NUM=NLS;FWC=T")
    ?SQLEXEC(nHandle,"call pcv.mp.test_call(?id_doc,?doc_nr)")
    =1 commited
    *********
    This is the procedure code:
    PROCEDURE test_call(in_doc_id NUMBER,in_doc_nr NVARCHAR2) IS
    BEGIN
    UPDATE b50_headeriai SET B50_op_numeris=in_doc_id where id_header=in_doc_id;
    END test_call;

    Thank you.
    Friday, September 14, 2007 6:59 AM

Answers

All replies

  • You may check if you have the last version of oldb provider:

     

    http://www.microsoft.com/downloads/details.aspx?familyid=e1a87d8f-2d58-491f-a0fa-95a3289c5fd4&displaylang=en

     

    and you may check oracle error:

     

    "ORA-00907: missing right parenthesis
    Cause: A left parenthesis has been entered without a closing right parenthesis, or extra information was contained in the parentheses. All parentheses must be entered in pairs.
    Action: Correct the syntax and retry the statement."

    Friday, September 14, 2007 12:19 PM
  • Hi,
    Thank You for replay.

    I think I dont need the vfpoledb.dll. I just installed this update and it was not useful for me.
    I'm trying to call the procedure in Oracle database mp package .
    Problem only with procedures and only in this sequence :

    1) call procedure any times yuo nead - everything is ok....
    2) sqlexec any other sql command - everything is ok....
    3) call the same procedure with ?parameters ("call pcv.mp.test_call(?id_doc,?doc_nr)") - error...
    !!!
    4) call prodedure with "call pcv.mp.test_call(61,'TEST0000001')") --- ok


    sorry for my english
    Friday, September 14, 2007 2:41 PM
  • I can not see any parameters in your procedure....

     

    Friday, September 14, 2007 4:46 PM
  • Smile
    This is FoxPro SQLEXEC command and Oracle database procedure and very easy sample:
    Parameters in foxpro code :

    pcv - schema (owner)
    mp - package
    test_call - procedure

    SQLEXEC(nHandle,"call pcv.mp.test_call(?id_doc,?doc_nr)")

    Procedure body with parameters:

    PROCEDURE test_call(in_doc_id NUMBER,in_doc_nr NVARCHAR2) IS
    BEGIN
    UPDATE b50_headeriai SET B50_op_numeris=in_doc_id where id_header=in_doc_id;
    END test_call;

    In my opinion this is  99% bug. Odbc ?  ,FoxPro ? ,Oracle DB ? - I dont know.
    Friday, September 14, 2007 5:58 PM
  • May be it is necesary to use (IN,OUT,IN OUT) and "IS" when define parameters and use declaration section in order to "help" sqlexec with a full syntax. I think SP2 for vfp 9 is still a beta version....

    You may also check:

    http://fox.wikis.com/wc.dll?Wiki~DirectODBCcalls~VFP

    Friday, September 14, 2007 6:28 PM
  • This problem is in FoxPro 9,FoxPro 9SP1 FoxPro 9SP2 , so this is not SP2 problem.
    And yet, this error is on another computer with the same sample without SP1 and SP2...

    PL/SQL
    User's Guide and Reference
    10g Release 1 (10.1)
    Part No. B10807-01
    Page 201. : Its parameter mode (IN, OUT, or IN OUT). If you omit the mode, the default is IN.

    Where is the syntax mistake in my sample ?
    Where is the logical mistake in my sample ?

     Smile


    Friday, September 14, 2007 7:22 PM
  • CREATE OR REPLACE PACKAGE "HR"."MP" AS

      PROCEDURE test_call(in_country IN CHAR,in_name IN VARCHAR2) ;

    END MP;
    /
    CREATE OR REPLACE PACKAGE BODY "HR"."MP" AS

       PROCEDURE test_call(in_country IN CHAR,in_name IN VARCHAR2)  AS
      BEGIN
        update countries set country_name=in_name where country_id=in_country;
      END test_call;

    END MP;
    /
     
    nHandle=SQLSTRINGCONNECT("DSN=hr;DBQ=192.168.1.123;UID=hr;PWD=hr;BTD=F;NUM=NLS;FWC=T")

    ?SQLEXEC(nHandle,"{call hr.mp.test_call(?id_country,?country_name)}")  = 1 ok
    ?SQLEXEC(nHandle,"update countries set country_name=?country_name where country_id=?id_country") =1 ok
    ?SQLEXEC(nHandle,"{call hr.mp.test_call(?id_country,?country_name)}") = -1 failed
    AERROR(SqlError)
    ?SqlError(2)
    =Connectivity error: [Oracle][ODBC][Ora]ORA-06550: line 1, column 22:
    PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
       ( ) - + case mod new not null others <an identifier>
       <a double-quoted delimited-identifier> <a

    ?SQLEXEC(nHandle,"{call hr.mp.test_call('US','United States of America')}") =1 ok

    Help Help Help

    Thank You




    Friday, September 14, 2007 7:55 PM
  •  

    Before calling ?SQLEXEC(nHandle,"{call hr.mp.test_call(?id_country,?country_name)}") = -1 failed statement

    see the values of id_Country and Country_name variables.

     

    Surinder Singh

    Monday, September 17, 2007 5:41 AM
  • Values :

    id_country='US'
    country_name='United States of America'


    Monday, September 17, 2007 7:27 AM