Oracle may be able to do the conversion for you (eg, select * from some Table where char Field = 0), but it will only return rows where the conversion holds true and is possible.
This is also far from ideal situation performance wise.
Let’s say your business has a third party system like Agile to maintain your inventory items but your item creation should start from Oracle Apps.
As we don’t want to give our base table MTL_SYSTEM_ITEMS_B table access to agile system we will create a custom table (preferred in custom schema) with limited columns which need to be shared with agile system.
PUT_LINE(w x y z); END; / DECLARE a NUMBER := 4; b NUMBER := 7; plsql_block VARCHAR2(100); BEGIN plsql_block := 'BEGIN calc_stats(:x, :x, :y, :x); END;'; EXECUTE IMMEDIATE plsql_block USING a, b; package defines an entity called a SQL cursor number.
Because the SQL cursor number is a PL/SQL integer, you can pass it across call boundaries and store it.
You must use the CREATE OR REPLACE TYPE vc_array IS TABLE OF VARCHAR2(200); / CREATE OR REPLACE TYPE numlist IS TABLE OF NUMBER; / CREATE OR REPLACE PROCEDURE do_query_1 ( placeholder vc_array, bindvars vc_array, sql_stmt VARCHAR2 ) IS TYPE curtype IS REF CURSOR; src_cur curtype; curid NUMBER; bindnames vc_array; empnos numlist; depts numlist; ret NUMBER; isopen BOOLEAN; BEGIN CREATE OR REPLACE PROCEDURE do_query_2 (sql_stmt VARCHAR2) IS TYPE curtype IS REF CURSOR; src_cur curtype; curid NUMBER; desctab DBMS_SQL.Navigation: Inventory Master Items If it prompts for selection of inventory organization select anything as per your preference.This form being a master form your selection doesn’t matter here in anyway.For each node, I would check to see if a matching column named existed and if it didn’t I would update the table to add one, before loading the data. There can be other versions of this table like “DBA_TAB_COLS” depending on how you set up your accounts and what you logged in as. So to get a list of the columns you would do something like this: Play around with this and see what other kinds of uses you can come up with.Doing a “SELECT *” will show you that there are several other values in there like the column’s data type, data length, if its nullable, and several others. is a programming methodology for generating and executing SQL statements at run time.It is useful when writing general-purpose and flexible programs like ad hoc query systems, when writing programs that must execute DDL statements, or when you do not know at compilation time the full text of a SQL statement or the number or data types of its input and output variables.If most of the data is numeric, on the other hand, this function is pretty efficient.This also takes care of non-integer numbers which Saish's regular expression does not and does not allow non-numbers like /whatever then the question is more about the system/code that generated the number and not necessarily Oracle itself (imagine a world outside Oracle).Sometimes you need to know the column names for a specific table.I have needed this in the past when loading XML data into an empty table. All you need to do is to get a list of the column names from the system table “USER_TAB_COLS”.