Solved

Oracle Syntax

Posted on 2016-11-09
8
78 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 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
Independent Software Vendors: 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!

 

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
 
LVL 77

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 32

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup

695 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