Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 551
  • Last Modified:

help converting sql server procedure to oracle sql developer syntax

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
itortu
Asked:
itortu
  • 5
  • 4
2 Solutions
 
mrjoltcolaCommented:
It might help to provide the table structure from COLLECTIONS
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
mrjoltcolaCommented:
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
itortuAuthor Commented:
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
 
itortuAuthor Commented:
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
 
mrjoltcolaCommented:
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
 
itortuAuthor Commented:
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
 
mrjoltcolaCommented:
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
 
itortuAuthor Commented:
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
 
mrjoltcolaCommented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now