Solved

Oracle Syntax

Posted on 2016-11-09
8
41 Views
Last Modified: 2016-11-11
I need an experienced Oracle developer to look at my ORACLE sql at the bottom of this post. Can you tell me if you can spot
any syntax errors? I won't have access to an ORACLE code compiler for at least a week, so that is why I need someone to look
at my code. Basically I have a block of sql server code that I converted to ORACLE code.

--- SQL Server code : ----
IF UPPER(@pchOnOff) ='OFF'
  BEGIN
    PRINT @tempSqlString
    EXEC sp_executesql @tempSqlString, @holderSqlString, @lnAuditInd OUT

    IF @lnAuditInd = 1
      BEGIN
          SET @tempSqlString = N'UPDATE ' + @pvcLVLVTgt + N' SET LVL_AUDIT_IND = 0 WHERE LVL_NAME = ''' + @tgtTableName + ''''
          EXEC sp_executesql @tempSqlString
          SET @myAuditFlag = 'Y'
      END
    ELSE
      BEGIN
         SET @myAuditFlag = 'N'
      END
   END
 ELSE

--------------- CODE converted to ORACLE------------
IF UPPER(@pchOnOff) ='OFF'
  BEGIN
    DBMS_OUTPUT.put_line(@tempSqlString)
    EXEC sp_executesql @tempSqlString, @holderSqlString, @lnAuditInd OUT

    IF @lnAuditInd = 1
      BEGIN
          SELECT @tempSqlString := N'UPDATE ' + @pvcLVLVTgt + N' SET LVL_AUDIT_IND = 0 WHERE LVL_NAME = ''' + @tgtTableName + ''''
          EXEC sp_executesql @tempSqlString
          SELECT @myAuditFlag := 'Y'
      END
    ELSE
      BEGIN
         SELECT @myAuditFlag := 'N'
      END
   END
 ELSE
0
Comment
Question by:brgdotnet
8 Comments
 
LVL 10

Accepted Solution

by:
HuaMinChen earned 252 total points
ID: 41881675
Hi,
Only SQL server database is having '@' to the variable and you can remove it within PL/SQL. Here is one Dynamic SQL in Oracle.

CREATE TABLE employees_temp AS
  SELECT * FROM EMPLOYEES
/
DECLARE
  a_null  CHAR(1);  -- Set to NULL automatically at run time
BEGIN
  EXECUTE IMMEDIATE 'UPDATE employees_temp SET commission_pct = :x'
    USING a_null;
END;
/

Open in new window

0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 62 total points
ID: 41881773
try the below :

IF UPPER(pchOnOff) ='OFF' then
  BEGIN
    DBMS_OUTPUT.put_line(@tempSqlString);
    Execute immediate sp_executesql using tempSqlString, holderSqlString, lnAuditInd  ;

    IF lnAuditInd = 1 then
      BEGIN
          tempSqlString := N'UPDATE ' + @pvcLVLVTgt + N' SET LVL_AUDIT_IND = 0 WHERE LVL_NAME = ''' + @tgtTableName + '''';
          EXECute immediate sp_executesql using tempSqlString;
         myAuditFlag := 'Y';
      END;
    ELSE
 
         myAuditFlag := 'N';

   END if;
 ELSE
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 62 total points
ID: 41881776
you do not need select @.... to assign value to variables. So it is just an assignment to a variable as shown below :

mytemp_string := 'select ' || ' emp_name from ' || ' employees where emp_id = 100';
0
 

Assisted Solution

by:sarankumards
sarankumards earned 62 total points
ID: 41881926
In Oracle to execute the dynamic SQL, EXECUTE IMMEDIATE need to be used.
More over @ symbol is not required in pl/sql and to concatenate the string use || symbol instead of +

DBMS_OUTPUT.PUT_LINE has been used in the code. to display the output on the screen use following sqlplus command
set serveroutput on
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 62 total points
ID: 41882060
>> I won't have access to an ORACLE code compiler for at least a week

Why?  Oracle XE is 100% free:
http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html
0
 
LVL 31

Assisted Solution

by:awking00
awking00 earned 62 total points
ID: 41882207
There are a lot of syntax errors in your "converted" Oracle code. Oracle uses no @ signs, has no meaning for using N', needs an execute immediate statement, select statements require a from keyword, uses double pipe ("||") instead of plus sign for concatenation, requires end if statements for each if statement, and needs semi-colons after all end and end if statements. Rather than trying to convert, can you state in plain language what the procedure needs to accomplish, perhaps with some sample data and table structures? It will probably easier for Oracle experts to write the code from scratch.
0
 
LVL 2

Author Comment

by:brgdotnet
ID: 41884398
Thank you
0
 
LVL 2

Author Comment

by:brgdotnet
ID: 41884399
Thank you everyone.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now