Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2012
    Posts
    1

    Unanswered: Converting MSSQL to Oracle SP

    Hi Guys,

    I'm trying to convert a SP from MSSQL to Oracle.
    The problem is that i get errors in Oracle, and I'm not an Oracle Guru.

    I have attached both the MSSQL SP and the Oracle SP that was converted.

    Any help with this would be great.
    Attached Files Attached Files

  2. #2
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    @DKXPAT
    What datatype is a SYSNAME?
    Code:
    CREATE GLOBAL TEMPORARY TABLE Temp (RowId_ int, SchemaName SYSNAME, TableName SY
    SNAME, ColumnName SYSNAME, DataType VARCHAR2(100), DataFound number(1))
                                                               *
    ERROR at line 1:
    ORA-00902: invalid datatype

  3. #3
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Sorry, got it now.
    #1. Change all of your SYSNAME datatypes to varchar2(30).
    #2. Change your int datatypes to number(9, 0).
    Code:
    dayneo@SANDBOX> CREATE GLOBAL TEMPORARY TABLE Temp (RowId_ number(9, 0), SchemaName varchar2(30), TableName varchar2(30), ColumnName varchar2(30), DataType VARCHAR2(100), DataFound number(1))
      2  ON COMMIT PRESERVE ROWS
      3  /
    
    Table created.
    
    dayneo@SANDBOX>

  4. #4
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    How do I search all of my Oracle table/columns for a given string?

    I should have payed more attention to your script description.
    Quote Originally Posted by DKXPAT View Post
    --This will locate any string or number in the database
    --USAGE: FindMyData 'string or number', 1 (0 = like, 1 = specific)
    The question you should actually be asking is:
    How do I search all of my Oracle table/columns for a given string?

    Find my script attached. It's absolutely nothing like yours, but then again it shouldn't be. Oracle is absolutely different from MSSQL. I like to think mine is also way simpler.

    Here is my SQL*PLUS script session showing that the script works:
    Code:
    mssql@SANDBOX> create table my_objects as select * from all_objects
      2  /
    
    Table created.
    
    mssql@SANDBOX> create table my_other_tbl (strcol varchar2(255))
      2  /
    
    Table created.
    
    mssql@SANDBOX> insert into my_other_tbl values('hello');
    
    1 row created.
    
    mssql@SANDBOX> insert into my_other_tbl values('hello world');
    
    1 row created.
    
    mssql@SANDBOX> insert into my_other_tbl values('to the world I say hello');
    
    1 row created.
    
    mssql@SANDBOX> insert into my_other_tbl
      2  select distinct object_type from all_objects
      3  /
    
    21 rows created.
    
    mssql@SANDBOX> commit;
    
    Commit complete.
    
    mssql@SANDBOX> @sln
    Search: hello
    Perform a 'LIKE' search (Y/N): y
    MSSQL.MY_OTHER_TBL.STRCOL
    
    PL/SQL procedure successfully completed.
    
    mssql@SANDBOX> @sln
    Search: INDEX
    Perform a 'LIKE' search (Y/N): y
    
    PL/SQL procedure successfully completed.
    
    mssql@SANDBOX> @sln
    Search: VIEW
    Perform a 'LIKE' search (Y/N): Y
    MSSQL.MY_OBJECTS.OBJECT_TYPE
    MSSQL.MY_OTHER_TBL.STRCOL
    
    PL/SQL procedure successfully completed.
    
    mssql@SANDBOX>
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

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