Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Location
    India
    Posts
    135

    Unanswered: Oracle Equivalent ( Urgent )

    Hi all,

    SQL-Server, System Table SYSOBJECT, SYSCOLUMNS & SYSTYPES and equivalent for his column name equivalent in ORACLE.

    Thanx in advance.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Oracle Equivalent ( Urgent )

    DBA_OBJECTS, DBA_TAB_COLUMNS, DBA_TYPES.

    If you are nor a DBA, change "DBA" to "ALL" or "USER".

  3. #3
    Join Date
    Feb 2004
    Location
    India
    Posts
    135
    Thanx andrew.

    I can't find USER_TAB_COLUMNS gives the TABLE INFORMATION only. I think so. But I want to retrieve the Procedure Information from the Oracle system objects including PROCEDURE PARAMETER, Parameter TYPE, Size, Parameter ID, LENGTH which i given in that procedure.

    Thanx in advance.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Search the Oracle Database Reference for "Data Dictionary Views". There are lots!

  5. #5
    Join Date
    Feb 2004
    Location
    India
    Posts
    135

    Question

    thanx. andrew. I found in the Data Dictionary USER_PROCEDURES, but I can't retrieve all.

    Hereby I giving u the SQLServer Query.

    SELECT CONVERT(VARCHAR(80),sysobjects.name) AS STOREDPROCEDURE,
    CONVERT(VARCHAR(80),syscolumns.name) AS PARAMNAME,
    syscolumns.colid AS PARAMID,
    syscolumns.colorder AS SORTORDER,
    CASE systypes.xtype
    WHEN 34 THEN -1
    WHEN 35 THEN 0
    WHEN 36 THEN 1
    WHEN 48 THEN 1
    WHEN 52 THEN 1
    WHEN 56 THEN 1
    WHEN 58 THEN 2
    WHEN 59 THEN 3
    WHEN 60 THEN 3
    WHEN 61 THEN 2
    WHEN 62 THEN 3
    WHEN 98 THEN -1
    WHEN 99 THEN 0
    WHEN 104 THEN 1
    WHEN 106 THEN 3
    WHEN 108 THEN 3
    WHEN 122 THEN 3
    WHEN 127 THEN 1
    WHEN 165 THEN -1
    WHEN 167 THEN 0
    WHEN 173 THEN -1
    WHEN 175 THEN 1
    WHEN 189 THEN 2
    WHEN 231 THEN 0
    WHEN 239 THEN 0
    END AS VPMTYPE,
    systypes.xtype AS TYPE,
    syscolumns.length AS LENGTH,
    CONVERT(VARCHAR(80),systypes.name) AS TYPENAME
    FROM sysobjects
    JOIN syscolumns ON syscolumns.id = sysobjects.id
    JOIN systypes ON syscolumns.xtype = systypes.xtype
    WHERE sysobjects.xtype = 'P'
    AND sysobjects.name = @PROCEDURENAME
    AND syscolumns.isoutparam = 0
    ORDER BY syscolumns.colorder

    This query gives the PROCEDURE NAME, PROCEDURE PARAMETER, PARAMETER ID, SORTED ORDER ... LENGTH, TYPE NAME for the given procedure

    thanx in advance

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    try USER_ARGUMENTS?

  7. #7
    Join Date
    Feb 2004
    Location
    India
    Posts
    135
    thanx andrew. yes it works.

    Oracle : USER_TYPES is equivalent for SQLServer systypes. If so, I need systypes.xtype column which is equivalent in Oracle. Could u explain me. Also Explain the COLUMNS in USER_ARGUMENTS.

    thanx.

  8. #8
    Join Date
    Feb 2004
    Location
    India
    Posts
    135
    thanx andrew. I GOT it

  9. #9
    Join Date
    Aug 2009
    Posts
    12

    Xtype in Oracle

    Oracle : USER_TYPES is equivalent for SQLServer systypes. If so, I need systypes.xtype column which is equivalent in Oracle. Could u explain me. Also Explain the COLUMNS in USER_ARGUMENTS.

    U have told u have got a solution for this..can you pls let us know what is equalent for xtype in oracle?

    Thanks in advance

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •