Improve company productivity with a Business Account.Sign Up

x
?
Solved

Oracle Syntax

Posted on 2016-11-09
8
Medium Priority
?
87 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 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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 79

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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

586 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