1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to get inputs as CSV and display each value in new line as output??!!

Discussion in 'SQL PL/SQL' started by Vicky, Feb 26, 2014.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    I need to give input as n no of csv values and the should get the output as each csv values in new lines???

    For eg:

    I/p: Sam, John, Tom, Charlie, ......

    o/p: Sam
    John
    Tom
    Charlie
    .......

    Thanks.,,.
     
  2. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi

    Try with below code.

    Code (SQL):

     WITH TEST AS
        (SELECT 'Sam,John,Tom,Charlie,ABC' str FROM dual
        )
        SELECT REGEXP_SUBSTR (str, '[^,]+', 1, rownum) split
          FROM test
        CONNECT BY level <= LENGTH (regexp_replace (str, '[^,]+'))  + 1
        /
     

    Regards
    Sambasiva Reddy.K
     
    Vicky likes this.
  3. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Thanks Sambasiva Reddy.K ., But, I need to give the inputs at run-time.,. Can you tell me how to do that.,?!
     
  4. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi Vicky,

    instead of 'Sam,John,Tom,Charlie,ABC' use input variable like &P_INPUT or :p_INPUT.

    I have tested with :p_INPUT in TOAD . It's working fine.

    Regards
    Sambasiva Reddy.K
     
    Vicky likes this.
  5. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Thanks Sambasiva Reddy., Since I'm a fresher I've never seen the code like this., so, I'll try understand the code first and ask you, if I've any doubts., And I've doubt in executing sys_refcursor.,

    I know how to execute the procedure using sys_refcursor to display the output in sql plus.,. Can you tell me how to execute that procedure in sql developer or isqlplus.,?!
     
  6. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Why do you feel executing a procedure in SQL*Plus is different from doing the same thing in SQL Developer?
     
  7. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Yes, of course it's different dude.,.,Isn't?!

    In sql plus, we've to execute the procedure using the keyword exec,
    While in sql developer, we've to execute the procedure using a Anonymous block , right?!

    Here, in this case, to display the o/p using sys_refcursor, in sql plus, we've to declare bind variable. But in sql developer, I dunno how to declare a bind variable to execute the procedure., Even after, trying many times, I ended up with error.,. I'm lukng for the answr for dis quesn for a long tijme.,,. Still waiting.,.
     
  8. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Not entirely...open up SQL Developer, click Help, select Search. Look for "Using the SQL Worksheet", scroll about halfway down and you'll find :


    SQL*Plus Statements Supported and Not Supported in SQL Worksheet


    The SQL Worksheet supports some SQL*Plus statements. SQL*Plus statements must be interpreted by the SQL Worksheet before being passed to the database; any SQL*Plus that are not supported by the SQL Worksheet are ignored and not passed to the database.

    The following SQL*Plus statements are supported by the SQL Worksheet:
    ...
    exec[ute]
    ...​


    Note that I shortened that list. :cool:

    The only commands not support in SQL Dev are :

    Append, archive, attr, break, btitle, change, column, compute, copy, del, disconnect, edit, get, input, list, newpage, oradebug, password, run, recover, repfooter, repheader, save, startup, shutdown, store and ttitle

    Most of these have no context in SQL Developer which explains why they don't work.

    If you type in the SQL Dev Worksheet window :

    help execute​

    ...you are rewarded with :

    EXECUTE
    ---------

    Executes a single PL/SQL statemnet or runs a stored procedure.

    EXEC[UTE] statement​


    HTH

    CJ
     
    Vicky likes this.