topic Native SQL Error exception in Application Development and Automation Discussions

Native SQL Error exception

naimkhans_babi — Mon, 11 Apr 2011 05:06:40 GMT

Dear friends!

Good day. I am trying to create an account in Oracle server over DBCON. I have written a query in Native SQL but it gives me the Native SQL exception error.. please look into my query and help me to correct it if I am making any mistake there.

DATA: w_user TYPE zbc_assignment-network_id,
        password TYPE string,
        wa_orausr TYPE zbc_orausr,
        wa_orausr_log TYPE zbc_orausr_log,
        lv_server TYPE zbc_oradb-dbname,
        zbc_assignment TYPE zbc_assignment.

  zbc_assignment-network_id = iv_ora_line-network_id.  +<---ORAACCOUNT (will pass oracle account id)+

* connect to Oracle Server.
  EXEC SQL.
    CONNECT TO :iv_server   +<---- will pass the Oracle dbcon- con_name here ( from the list of Oracle servers. i.e ORA01)+  ENDEXEC.
* Check user in oracle.   +<--- Sy-subrc = 0  connection is successful.+  
EXEC SQL.   
    SELECT USERNAME INTO :w_user
           FROM DBA_USERS WHERE
           USERNAME = :zbc_assignment-network_id    
  ENDEXEC.   +<--- Sy-subrc = 4.  since no user is exist in the system.+ 
 IF sy-subrc NE '0' AND w_user IS INITIAL.
    IF iv_ora_line-request_type = '1'.
      " Create Account in oracle
     wa_user = zbc_assignment-network_id. +<--- will pass the username form the user which is not exist in the ora server.+  
    IF iv_ora_line-acc_type = 'USER ACCOUNT'.
        EXEC SQL.  +<---- Native SQL Error is here.....!!! :,(+          
          CREATE USER w_user IDENTIFIED BY password  +<---- password "Init$123+          
          DEFAULT TABLESPACE ts_user_data_01
          TEMPORARY TABLESPACE Ts_temp_DATA_01
          PROFILE PROFILE_NONPRIVILEGED
        ENDEXEC.
 ....

Please check my sql query and help me. your any help will be appreaciated.

Regards

Naeem

Re: Native SQL Error exception

Former Member — Mon, 11 Apr 2011 05:11:21 GMT

Hi,

you have an EXEC SQL within an EXEC SQL.

 EXEC SQL.
    CONNECT TO :iv_server   +<---- will pass the Oracle dbcon- con_name here ( from the list of Oracle servers. i.e ORA01)+  ENDEXEC.
* Check user in oracle.   +<--- Sy-subrc = 0  connection is successful.+  
EXEC SQL.

1st EXEC SQL isn't closed with ENDEXEC!

Regards,

Klaus

Edited by: Klaus Babl on Apr 11, 2011 7:11 AM

Re: Native SQL Error exception

naimkhans_babi — Mon, 11 Apr 2011 05:44:47 GMT

It is there! while formating.. it is deleted.. I think my problem is more with variable passing. I am feeling that password variable is not able to pass the data to my native sql block. but not sure... thats the reason only.

Re: Native SQL Error exception

naimkhans_babi — Mon, 11 Apr 2011 06:39:27 GMT

convert native sql into internal table using concatenate and generate a dynamic program call and run it.

this I am not passing variables to native sql script.