Solved

help converting sql server procedure to oracle sql developer syntax

Posted on 2009-07-07
10
538 Views
Last Modified: 2013-12-07
can anyone help me to convert the ms sql server syntax to oracle sql developer 1.2.1 syntax?

thank you so much.
CREATE PROCEDURE usp_GetFullPath

   @SelectedID  AS INT,

   @FullPath    AS VarCHar(4000) OUTPUT

AS

BEGIN

 

   SET NOCOUNT ON;

 

   DECLARE  @ParentID AS INT;

 

   SET @ParentID = @SelectedID;

   SET @FullPath = '';

 

   WHILE (@ParentID IS NOT NULL) DO

   BEGIN

   

      SELECT  @FullPath = dcollectionname + @FullPath,

              @ParentID = @dparentcollectionid 

      FROM    collections 

      WHERE dparentcollectionid = (@ParentID;

 

   END

 

   SET NOCOUNT OFF;

 

   RETURN;

END

Open in new window

0
Comment
Question by:itortu
  • 5
  • 4
10 Comments
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24798397
It might help to provide the table structure from COLLECTIONS
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 200 total points
ID: 24798424
this should do (including some corrections in the logic)
CREATE OR REPLACE PROCEDURE usp_GetFullPath
   (v_SelectedID  NUMBER,
  , v_FullPath    VarCHar2 OUT
)
AS
DECLARE v_parentD number;
BEGIN
   v_ParentID := v_SelectedID;
   v_FullPath := '';
 
   WHILE (v_ParentID IS NOT NULL) 
   LOOP
      SELECT  dcollectionname || v_FullPath , dparentcollectionid 
       INTO v_FullPath, v_ParentID
      FROM    collections 
      WHERE collectionid = v_ParentID;
   END LOOP;
END

Open in new window

0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24798528
a3, I must have missed a previous question, I do not see collectionid anywhere. Your version makes sense, but the original did not, I was about to suggest not using a loop at all, but I think I see the reasoning.



0
 

Author Comment

by:itortu
ID: 24798540
table structure
DCOLLECTIONID	NUMBER	No		1	1	

DCOLLECTIONGUID	VARCHAR2(36 BYTE)	No		2		

DPARENTCOLLECTIONID	NUMBER	Yes		3		

DCOLLECTIONNAME	VARCHAR2(255 BYTE)	No		4		

DCOLLECTIONTYPE	NUMBER	No		5		

DCOLLECTIONMARK	VARCHAR2(255 BYTE)	Yes		6		

DDEPENDENT	VARCHAR2(30 BYTE)	Yes		7		

DCOLLECTIONQUERIES	NUMBER(1,0)	No	(0) 	8		

DCOLLECTIONENABLED	NUMBER(1,0)	No	(0) 	9		

DCOLLECTIONINHERIT	NUMBER(1,0)	Yes	(0) 	10		

DCHILDMANIPULATION	NUMBER(1,0)	Yes	(1) 	11		

DCREATEDATE	DATE	Yes		12		

DINDATE	DATE	Yes		13		

DOUTDATE	DATE	Yes		14		

DRELEASEDATE	DATE	Yes		15		

DLASTMODIFIEDDATE	DATE	Yes		16		

DDOCNAME	VARCHAR2(30 BYTE)	Yes		17		

DDOCTYPE	VARCHAR2(30 BYTE)	Yes		18		

DDOCTITLE	VARCHAR2(80 BYTE)	Yes		19		

DDOCAUTHOR	VARCHAR2(50 BYTE)	Yes		20		

DREVLABEL	VARCHAR2(10 BYTE)	Yes		21		

DSECURITYGROUP	VARCHAR2(30 BYTE)	Yes		22		

DDOCACCOUNT	VARCHAR2(30 BYTE)	Yes		23		

DCOLLECTIONOWNER	VARCHAR2(50 BYTE)	Yes		24		

Open in new window

0
 

Author Comment

by:itortu
ID: 24798567
i get Error(5,25): PLS-00103: Encountered the symbol "OUT" when expecting one of the following := .), @ % default character.

it points to this line:  v_FullPath    VarCHar2 OUT,
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 40

Expert Comment

by:mrjoltcola
ID: 24798601
THere is a 2nd comma there.

   (v_SelectedID  NUMBER,
  , v_FullPath    VarCHar2 OUT

Should be:L

   (v_SelectedID  NUMBER,
    v_FullPath    VarCHar2 OUT


Plus, you should rename "collectionid" to "dcollectionid" in his sample. He was pretty smart to figure it out without seeing your table, in any case.


0
 

Author Comment

by:itortu
ID: 24798640
i made that change but still get the same error.
i agree he was did a great job figuring it out. thank you very much.
CREATE OR REPLACE

PROCEDURE USP_GETFULLPATH 
 

   (v_SelectedID  NUMBER,

    v_FullPath    VarCHar2 OUT

  )
 
 

AS
 

DECLARE v_parentD number;
 

BEGIN
 

   v_ParentID := v_SelectedID;

   v_FullPath := '';
 
 

WHILE (v_ParentID IS NOT NULL) 

   LOOP

      SELECT  dcollectionname || v_FullPath , dparentcollectionid 

       INTO v_FullPath, v_ParentID

      FROM    collections 

      WHERE dcollectionid = v_ParentID;

   END LOOP;
 
 

END USP_GETFULLPATH;

Open in new window

0
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 300 total points
ID: 24798688
Try this, fixed several syntax errors in angelIII's original, so don't credit me with it.


CREATE OR REPLACE PROCEDURE USP_GETFULLPATH 

    (v_SelectedID IN NUMBER,

    v_FullPath OUT VarCHar2

  )

AS

   v_parentID number;

BEGIN

 

   v_ParentID := v_SelectedID;

   v_FullPath := '';
 

WHILE (v_ParentID IS NOT NULL) 

   LOOP

      SELECT  dcollectionname || v_FullPath , dparentcollectionid 

       INTO v_FullPath, v_ParentID

      FROM    collections 

      WHERE dcollectionid = v_ParentID;

   END LOOP;

END USP_GETFULLPATH;

/

Open in new window

0
 

Author Comment

by:itortu
ID: 24806322
thank you, that took all the errors away.

now i am trying to execute the sp but I think I am doing it wrong passing the parameters:

exec USP_GETFULLPATH (2 in,  '' out)

Error:

Error starting at line 1 in command:
exec USP_GETFULLPATH (2 in,  '' out)
Error report:
ORA-06550: line 1, column 28:
PLS-00103: Encountered the symbol "," when expecting one of the following:

   (
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24806343
When calling it you don't use IN or OUT keywords.

exec USP_GETFULLPATH (2,  '')


Or if you want to use other variables, call it in a PL/SQL block like so:

DECLARE
  -- put variables here
BEGIN
   USP_GETFULLPATH(2, '');
END;
/

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

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

747 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

12 Experts available now in Live!

Get 1:1 Help Now