LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: micesam

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

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: jodycrutchfield

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

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: micesam

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.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: jodycrutchfield

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

Thanks,

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: micesam

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

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: jodycrutchfield

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.