?
Solved

Oracle Syntax

Posted on 2016-11-09
8
Medium Priority
?
79 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 11

Accepted Solution

by:
HuaMinChen earned 1008 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 248 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 248 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Assisted Solution

by:sarankumards
sarankumards earned 248 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
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 248 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 32

Assisted Solution

by:awking00
awking00 earned 248 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

771 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