Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Posts
    5

    Unanswered: Error creating ORACLE procedure

    Hi, I'm new here and new to ORACLE. I would appreciate any help you can give on this problem I am having. I am trying to create the stored procedure below and am getting the following error. I will be calling the procedure and passing it the parameter txti from a vbScript.

    ERROR:
    SQL>@D:\qualidigm\Inetpub\WWWROOT\pnp_web\oracle\s toredpc.sql;
    19 /

    Warning: Procedure created with compilation errors.

    SQL> show errors procedure insdata;
    Errors for PROCEDURE INSDATA:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    14/3 PL/SQL: SQL Statement ignored
    15/13 PLS-00306: wrong number or types of arguments in call to '+'
    SQL>
    ERROR:


    PROCEDURE:
    Create or Replace Procedure InsData (txti IN clob, rws out varchar2)
    AS

    BEGIN
    SELECT count(*)
    INTO rws
    FROM tbl_pnp_test
    WHERE id = '2';
    If rws = 0 Then
    INSERT INTO tbl_pnp_test
    VALUES ('2', txti);

    Else
    UPDATE tbl_pnp_test
    SET txt = txt + txti
    WHERE id = '2';
    End If;
    END InsData;
    PROCEDURE:

    thanks in advance,
    k....

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    String concatenation is performed via the || operator rather than the +.

    Change your line
    SET txt = txt + txti

    to
    SET txt = txt || txti

    Hth
    Bill

  3. #3
    Join Date
    Oct 2003
    Posts
    5
    I get the same error. but instead it will read.

    wrong number or types of arguments in call to '||'

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    A couple of possibilities

    1 - Make sure you're using the correct bar character. It is ASCII 124 (or atleast it is my my UK CP here). I once had difficulty with two similar looking bar characters on a German CP. Ie make sure you're not using 179 or 221 :-)

    Test it by doing select 'Hello '||'World' from dual; to check that isn't the issue.

    2 - There is a column called txt in tbl_pnp_test?

    3 - Is it a CLOB, LOB or BLOB column. If so I think you need to use the DBMS_LOB functions for concatenation in certain circumstances.

    Hth
    Bill

  5. #5
    Join Date
    Oct 2003
    Posts
    5
    yeah there is a column called txt in the tbl_pnp_test table and it is a clob. What i'm trying to do here is insert character data larger then 2000 characters. Every time I try a plain insert from my webpage I get and error saying string literal to long. So i'm trying to break the string up and piece it together in 2k chunks.. My whole program rests on being able to do this.

  6. #6
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Then yes you'll need to use the DBMS_LOB package to manage the column.

    If you don't have the docs with you, you should be able to find it on OTN or google etc.

    Hth
    Bill

  7. #7
    Join Date
    Oct 2003
    Posts
    5
    Changed my data type to long and used the "||" and it compiled without errors. gonna put my webpage together and see if it works using long.

    tx...

  8. #8
    Join Date
    Oct 2003
    Posts
    5
    ok, i think i'm giving up on the stored procedure idea. two reasons, i can't determine the stored procedure information i need to use the thing from a vbscript, also if i do get all my data into one field i may not be able to get it back out to display on a web page. DO anyone know how to get more then 2k of text into an ORACLE clob, or long field using vbscript. otherwise i think i will just create a new record for each 2k chunk.


    tx....

Posting Permissions

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