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!

run a plsql script that is stored in a database in forms

Discussion in 'Oracle Forms and Reports' started by gmakinana, Jan 22, 2009.

  1. gmakinana

    gmakinana Guest

    Hi there is it possible to run a plsql script that is stored in a database as a CLOB with parameter placeholders that will be replaced with the contents that will be inserted in a form? If it's possible how do I do this?
     
  2. apps_expert

    apps_expert Forum Expert

    Messages:
    325
    Likes Received:
    28
    Trophy Points:
    330
    Location:
    Chennai, India
    hi i don't knw technically much about oracle forms but you cannot run any plsql script from a CLOB variable untill oracle 11g.

    So What DB version are you on? Only since 11g one could pass a clob variable to execute immediate without problems.
     
  3. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    hi gmakinana, you can use DBMS_LOB.SUBSTR to manipulate the plsql stored in a CLOB because scripts cannot be run as execute immediate from a CLOB until 11g. For example if i give
    Code (Text):

    Begin
    ------
    ------
    EXECUTE IMMEDIATE DBMS_LOB.SUBSTR (p_sql, 10000, 1);
    ------
    End;
     
    where p_sql is a CLOB datatype, it will execute the script stored in CLOB