Solved

Oracle Syntax

Posted on 2016-11-09
8
55 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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Error when running a simple alter statement in Aqua studio for Oracle 6 43
Oracle Subquery bad Join 11 59
report returning null 21 79
Oracle Pivot Question 8 43
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

919 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

14 Experts available now in Live!

Get 1:1 Help Now