Results 1 to 6 of 6

Thread: Execute PL/SQL Procedure using DB Execute

  1. #1

    Default Execute PL/SQL Procedure using DB Execute

    Hi - I have a question, hopefully someone will be able to help me.

    We have a new commission system based on Oracle. In order for me to query the back-end using TOAD, I need to execute a procedure using a command:

    EXEC xx_apps_readonly_logon_proc;

    then I can run any sql select statement.

    I have been trying to use DBExecute to run the execute procedure, but it keeps giving me error message "ORA00900: Invalid SQL Statement".

    Anyone can help?

    Regards,
    Michael

  2. #2
    Contributor
    Join Date
    Mar 2010
    Location
    Atlanta, GA
    Posts
    29

    Default Oracle Packages and Procedures

    Michael,

    Here is a DbQuery node that works for me.

    node:Oracle_PackageProcedure
    bretype:core::DB Execute
    editor:Label=Oracle Package/Procedure
    editor:sortkey=4d2508625ac64752_2
    prop:DbInterface=DbInterface
    prop:DbPassword=DbPassword
    prop:DbService=DbService
    prop:DbUser=DbUser
    prop:Query=<<EOX
    BEGIN
    schema.package.procedure(:1,:2);
    END;

    EOX
    prop:Script=<<EOX
    sqlNonSelect
    (
    "{{^Query^}}",
    0,
    "Y"
    )
    EOX
    editor:XY=100,250
    editor:propdef=Query|sql|1||None
    end:Oracle_PackageProcedure

  3. #3

    Default

    Hi - thanks for the reply.

    It still does not work for me. I need to do select statement, but the script part contains sqlNonSelect. Even after I changed them to sqlSelect, it is saying view or table does not exist.

    The issue that I have now is that every time before I can run any query, I need to execute the procedure. Normally I use TOAD by typing
    exec xx_apps_readonly_logon_proc;

    then the sql statement.

    Now in BRE, everytime I only put in the sql statement, it is saying table/view does not exist.

  4. #4
    Contributor
    Join Date
    Mar 2010
    Location
    Atlanta, GA
    Posts
    29

    Default

    Can you post a copy of the node so we can take a look?
    Does the procedure return anything or just execute?

    Thanks,

  5. #5

    Default

    Hi,

    The node returns dataset from select statement.

    This is the node that I have - with the query.

    node:CN_COMM_LINES_API_ALL_2
    bretype:core::DB Query
    editor:Label=CN_COMM_LINES_API_ALL
    editor:sortkey=4be270c200a118d6_4
    output:@40fd2c7444283da6/CN_COMM_LINES_API_ALL=
    prop:DbInterface=oci
    prop:DbPassword={{^OicPwd^}}
    prop:DbService={{^OicService^}}
    prop:DbUser={{^OicUser^}}
    prop:SqlQuery=<<EOX
    exec xx_apps_readonly_logon_proc;

    select * from CN_COMM_LINES_API_ALL
    where PROCESSED_DATE >= TO_DATE('{{^OrderStartDate^}}', 'YYYY-MM-DD') and
    PROCESSED_DATE <= TO_DATE('{{^OrderEndDate^}}', 'YYYY-MM-DD')




    EOX
    editor:XY=140,170
    end:CN_COMM_LINES_API_ALL_2

  6. #6
    Contributor
    Join Date
    Mar 2010
    Location
    Atlanta, GA
    Posts
    29

    Default

    If your {{^OicUser^}} is not the owner of the schema where the table resides you will need to prefix the table with the schema name.

Similar Threads

  1. DBC Execute Committing
    By michaelslowey in forum Nodes
    Replies: 10
    Last Post: 02-04-2015, 07:30 AM
  2. Replies: 5
    Last Post: 06-10-2011, 02:32 AM
  3. How to us the Execute BRX node
    By ryanmacdonald in forum Nodes
    Replies: 2
    Last Post: 03-03-2010, 06:41 PM
  4. Pass information through DB Execute
    By robby123 in forum Nodes
    Replies: 3
    Last Post: 05-08-2008, 01:21 AM

Posting Permissions

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